RE: st: correlation-like table to dyadic pairs transformation

[Sean Taylor <Sean.Taylor@chicagopartners.com>]

Hi Sorin,

I found your table a little confusing to read, but if your data are stored as a dataset such as this:

    pointA   distanceABW   distanceAFG   distanceAGO   distanceALB   distanceANT  
       ABW             0             1             2             3             4  
       AFG             1             0             5             6             7  
       AGO             2             5             0             8             9  
       ALB             3             6             8             0            10  
       ANT             4             7             9            10             0  

You can transform it using reshape:

reshape long distance, i(pointA) j(pointB) string

To get this:

    pointA   pointB   distance  
       ABW      ABW          0  
       ABW      AFG          1  
       ABW      AGO          2  
       ABW      ALB          3  
       ABW      ANT          4  
       AFG      ABW          1  
       AFG      AFG          0  
       AFG      AGO          5  
       AFG      ALB          6  
       AFG      ANT          7  
       AGO      ABW          2  
       AGO      AFG          5  
       AGO      AGO          0  
       AGO      ALB          8  
       AGO      ANT          9  
       ALB      ABW          3  
       ALB      AFG          6  
       ALB      AGO          8  
       ALB      ALB          0  
       ALB      ANT         10  
       ANT      ABW          4  
       ANT      AFG          7  
       ANT      AGO          9  
       ANT      ALB         10  
       ANT      ANT          0  

You can then write some simple code to get rid of the duplicates:

gen points = pointA+"-"+pointB if pointA<=pointB
replace points = pointB+"-"+pointA if pointA>pointB
drop pointA pointB
duplicates drop

To get this:

    distance    points  
           0   ABW-ABW  
           1   ABW-AFG  
           2   ABW-AGO  
           3   ABW-ALB  
           4   ABW-ANT  
           0   AFG-AFG  
           5   AFG-AGO  
           6   AFG-ALB  
           7   AFG-ANT  
           0   AGO-AGO  
           8   AGO-ALB  
           9   AGO-ANT  
           0   ALB-ALB  
          10   ALB-ANT  
           0   ANT-ANT

Best,
Sean

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Stas Kolenikov
Sent: Wednesday, January 19, 2011 10:04 AM
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: correlation-like table to dyadic pairs transformation

matrix D = (input your distance matrix here)
clear
set obs 5
generate byte i = _n
expand 5
bysort i : generate byte j = _n
generate double distance = .
generate str7 points = ""
local thenames "ABW AFG AGO ALB ANT"
forvalues i=1/5 {
  replace points = "`: word `i' of `thenames''" if i == `i'
  forvalues j=1/5 {
    replace distance = D[`i',`j'] if i == `i' & `j' == j
    replace points = points + "-" + "`: word `j' of `thenames'" if i
== `i' & `j' == j
  }
}

There are other solutions of course; it would probably be a little
shorter in Mata, but you'd need to transfer the results back to Stata.

On Wed, Jan 19, 2011 at 8:20 AM, Sorin Krammer <sorin.krammer@gmail.com> wrote:
>
> Dear All,
>
> I have a problem in transforming a table that looks like this:
>
>            ABW    AFG            AGO                            ALB
>            ANT
> ABW    0    13074,04493    10155,77746    9108,047048    135,1991754
> AFG    13074            0          7072,696531    4052,529947    13005,47268
> AGO    10156    7072,69653                   0      5958,121764  10020,59095
> ALB    9108    4052,52995    5958,121764    0
>  9025,064452
> ANT    135    13005,47268    10020,59095    9025,064452                0
>
>
> This is distance data between two points.
>
> What I need is basically a dyadic dataset:
>
> ABW - AFG        13074,04493
> ABW- AGO        10155,77746
> ABW-ALB            9108,047048
> ..............
>
> ALB-ANT                9025,064452
>
> Any suggestions are welcomed. My programming is basic so I incline for
> command-based solution , if possible.
>
> Many thanks,
> Sorin
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
>



-- 
Stas Kolenikov, also found at http://stas.kolenikov.name
Small print: I use this email account for mailing lists only.

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


This communication is from Navigant Consulting Inc. E-mail text or attachments may contain information which is confidential and may also be privileged. This communication is for the exclusive use of the intended recipient(s). If you have received this communication in error, please return it with the title "received in error" to NCISecurity@navigantconsulting.com, and then delete the email and destroy any copies of it. In addition, this communication is subject to, and incorporates by reference, additional disclaimers found in Navigant Consulting's "Email Disclaimer" section at www.NavigantConsulting.com. 

Navigant Consulting, Inc.
Company Registration Number: UK Ltd. 3641719
Registered in Delaware, USA
Registered Office Address: 30 South Wacker Drive, Suite 3400, Chicago, Illinois 60606



*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/