Programming mechanics: Assigning group IDs

Question

So let's say I have some airline flight data. One field is Origin Airport and another field is Destination Airport. I'd like to group observations by route (a unique Origin to Destination combination). The issue is that for each unique route the corresponding return route would also need to be included. For example, if I had Airport A to Airport B for one bunch of observation, but Airport B to Airport A in the next few observation, I'd like them all to have the same route ID.

I can use SAS, Stata, or R for this. Even Python, if it's easier.

Rep. code below:

df1 <- structure(list(airl = c("US", "US", "US", "US", "US", "US", "US", 
"US", "US", "US", "US", "US", "US", "US", "US", "US", "US", "US", 
"US", "US", "US", "US", "US", "US", "US", "US", "US", "US", "US", 
"US", "US", "US", "US", "US", "US", "US", "US", "US", "US", "US", 
"US", "US", "US", "US", "US", "US", "US", "US", "US", "US", "US", 
"US", "US", "US", "US", "US", "US", "US", "US", "US", "US", "US", 
"US", "US", "US", "US", "US", "US", "US"), ORIGIN = c("ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "CLT", "CLT", "CLT", 
"CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "CLT", 
"CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "PHL", "PHL", "PHL", 
"PHL", "PHL", "PHL", "PHL", "PHL", "PHL", "PHL", "PHL", "PHL", 
"PHL", "PHL", "PHL", "PHL", "PHL"), DESTINATION = c("CLT", "CLT", 
"CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "CLT", 
"CLT", "CLT", "CLT", "CLT", "CLT", "CLT", "PHL", "PHL", "PHL", 
"PHL", "PHL", "PHL", "PHL", "PHL", "PHL", "PHL", "PHL", "PHL", 
"PHL", "PHL", "PHL", "PHL", "PHL", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", "ABE", 
"ABE", "ABE", "ABE", "ABE"), miles = c(480, 480, 480, 480, 480, 
480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 54, 
54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 54, 
480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 480, 
480, 480, 480, 480, 480, 54, 54, 54, 54, 54, 54, 54, 54, 54, 
54, 54, 54, 54, 54, 54, 54, 54), orig_area = c(23, 23, 23, 23, 
23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 
23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 36, 36, 
36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 
23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 
23), dest_area = c(36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 36, 
36, 36, 36, 36, 36, 36, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 
23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 
23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 23, 
23, 23, 23, 23, 23, 23, 23, 23, 23, 23), month = c(1, 2, 3, 4, 
5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 4, 5, 6, 7, 8, 9, 10, 
11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 12, 1, 2, 3, 4, 5, 6, 
7, 8, 9, 10, 11, 12, 1, 2, 3, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
1, 2, 3, 4, 5, 6, 7), freq = c(88, 80, 89, 78, 88, 83, 85, 80, 
76, 79, 76, 81, 86, 65, 62, 60, 82, 137, 138, 142, 144, 149, 
147, 150, 143, 150, 138, 128, 151, 145, 148, 146, 147, 149, 79, 
76, 81, 86, 65, 62, 60, 82, 82, 82, 84, 81, 83, 81, 85, 84, 76, 
85, 143, 137, 138, 142, 143, 151, 147, 150, 143, 150, 137, 128, 
151, 145, 148, 146, 147), seats = c(8146, 7352, 7599, 6920, 6759, 
6060, 6189, 5939, 6137, 6504, 6440, 6804, 6862, 5330, 5242, 5068, 
6204, 6460, 6276, 6047, 6095, 6306, 6102, 6265, 7085, 7344, 6809, 
6348, 6965, 6626, 6893, 6741, 6765, 6865, 6504, 6440, 6804, 6862, 
5330, 5242, 5068, 6204, 6104, 6030, 6278, 6034, 6944, 6816, 6544, 
6494, 5872, 6544, 6747, 6460, 6276, 6034, 6058, 6380, 6102, 6278, 
7085, 7344, 6759, 6348, 6952, 6613, 6919, 6728, 6765), year = c(2009, 
2009, 2009, 2009, 2009, 2009, 2009, 2009, 2009, 2009, 2009, 2009, 
2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 
2010, 2010, 2010, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 2011, 
2009, 2009, 2009, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 
2010, 2010, 2010, 2010, 2011, 2011, 2011, 2010, 2010, 2010, 2010, 
2010, 2010, 2010, 2010, 2010, 2010, 2011, 2011, 2011, 2011, 2011, 
2011, 2011)), .Names = c("airl", "ORIGIN", "DESTINATION", "miles", 
"orig_area", "dest_area", "month", "freq", "seats", "year"), class = "data.frame", row.names = c(NA, 
69L))

The [r] folks like it if you provide a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — GSee, Sep 28 '12 at 19:47

score 2 · Answer 1 · answered Sep 28 '12 at 20:18

Taking the simplest labeling (the two airport codes, listed in alphabetical order so that origin and destination doesn't matter):

df1$group <- apply(df1[c("ORIGIN", "DESTINATION")], 
                   1, 
                   function(cit) {paste(sort(cit),collapse="-")})

In brief, pull out just the airport codes, and for each row, sort the codes alphabetically and then paste them together separate by a hyphen. Assign that the the group variable.

> df1$group
 [1] "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT"
 [8] "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT"
[15] "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL"
[22] "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL"
[29] "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-CLT"
[36] "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT"
[43] "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-CLT"
[50] "ABE-CLT" "ABE-CLT" "ABE-CLT" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL"
[57] "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL"
[64] "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL" "ABE-PHL"

score 1 · Answer 2 · answered Sep 28 '12 at 22:14

In Stata, you can utilize the relations between strings:

assert "ABE"<"CLT"

is fine, although of course any of

assert "ABE"<"CLT"
assert "ABE"<1

will produce an error (the first one is false, and second one is comparing incompatible data types). So mirroring Brian's suggestion in R, we could have

gen str7 route = origin + "-" + destination if origin < destination & !missing(origin) & !missing(destination)
replace  route = destination + "-" + origin if destination < origin & !missing(origin) & !missing(destination)
list origin destination route if missing( route )

All the missing value checks are just paranoia, of course. But who knows how bad your data are :).

score 1 · Answer 3 · answered Oct 02 '12 at 03:49

1

An approach using R and data.table (for coding elegance and memory efficiency)

library(data.table)
DT <- as.data.table(df1)


DT[, id := paste(sort(c(ORIGIN, DESTINATION)), collapse ='-') ,
      by = list(ORIGIN, DESTINATION)]

answered Oct 02 '12 at 03:49

mnel

113,303
27
265
254

score 0 · Answer 4 · answered Sep 28 '12 at 20:43

SAS solution, using PROC FORMAT. I take a lot more steps here than you probably will need to - just splitting out each step to make it clear what I'm doing. This assigns a single value to each pair and assigns both directions of the pair to that same value.

You could do exactly the same as the R solution with the label itself being the group definition, by using a format to convert 'ABQ-DEN' or 'DEN-ABQ' into 'ABQ-DEN' - do just like this, but set label=start in the pre-proc sort data step. You would need to convert INFORMAT to FORMAT, as well.

proc format;
*What this will look like - this is an example and NOT used in the final solution;
invalue $AIRRT
'ABE-CLT'=1
'CLT-ABE'=1
'ABE-PHL'=2
'PHL-ABE'=2
'ABQ-DEN'=3
'DEN-ABQ'=3
'ABQ-ELP'=4
'ELP-ABQ'=4
'MDW-MCI'=5
;
*Only used to create sample data;
value AIRPORT
1="ABE"
2="CLT"
3="PHL"
4="ABQ"
5="ELP"
6="DEN"
7="MDW"
8="MCI"
;
quit;

*create sample data;
data have;
do _t = 1 to 100;
    origin=put(ceil(8*ranuni(7)),$AIRPORT.);
    do until (destination ne origin);
        destination=put(ceil(8*ranuni(7)),AIRPORT.);
    end;
    output;
end;
run;

*create preliminary dataset for format, creating combined field;
data for_format_pre;
 set have;
 call sortc(of origin destination);
 start = catx('-',origin,destination); 
 keep start origin destination;
run;

*sort down to one per route;
proc sort nodupkey data=for_format_pre;
 by start;
run;

*create final format dataset, with group counter;
data for_format;
set for_format_pre;
 retain fmtname "AIRRT" type 'j';
 label+1;
 output;
 start=catx('-',destination,origin);
 output;
run;

*import into formats;
proc format cntlin=for_format;
quit;

*apply to dataset;
data want;
set have;
combined=catx('-',origin,destination);
group_sort = input(combined,$AIRRT.);
drop _:;
run;

Programming mechanics: Assigning group IDs

4 Answers4