Conditional Counting in Data Tables

1
votes

Suppose I have the following data table:

 hs_code   country        city           company
1:  apples  Canada     Calgary          West Jet
2:  apples  Canada     Calgary            United 
3:  apples      US     Los Angeles        Alaska
4:  apples      US     Chicago            Alaska 
5:  oranges  Korea       Seoul          West Jet
6:  oranges  China    Shanghai John's Freight Co
7:  oranges  China      Harbin John's Freight Co
8:  oranges  China      Ningbo John's Freight Co

Output:

 hs_code   countries        city           company
1:  apples       2          1,2             2,1,1
2:  oranges      2          1,3           1,1,1,1

The logic is as follows:

For each good, I want the first column to summarize the number of unique countries. For apples it is 2. Based on this value, I want a 2-tuple in the city column that summarizes the unique number of cities for each country. So, since there is only unique city for Canada and two for the US, the value becomes (1,2). Notice that the sum of this tuple is 3. Finally, in the company column, I want a 3-tuple, that summarizes the unique number of companies per city and country possibility. So, since there is West Jet and United for the (Canada, Calgary) pair, I assign a 2. The next two values are 1 and 1 because Los Angeles and Chicago only have one transportation company listed.

I understand this is pretty confusing and involved. But any help would be greatly appreciated. I've tried using data table methods such as

DT[, countries := .uniqueN(country), by =.(hs_code)] 
DT[, city:= .uniqueN(city), by = .(hs_code, country)]

but I'm not sure how to get this conveniently into a list form into a data.table recursively.

Thanks!

2
rdata.table
what is behind the .uniqueN? - Bulat

2 Answers

0
votes

Well, this is some sort of nested transformation that you can do in three steps:

dt[, .(companies = length(unique(company))), by = .(hs_code, country, city)][, 
 .(cities = length(unique(city)), 
   companies = paste0(companies, collapse = ",")), by = .(hs_code, country)][, 
 .(countries = length(unique(country)), 
   cities = paste0(cities, collapse = ","),
   companies = paste0(companies, collapse = ",")), by = hs_code ]
  # hs_code countries cities companies
  # 1:  apples         2    1,2     2,1,1
  # 2: oranges         2    1,3   1,1,1,1
0
votes

You can use .SD[] notation to create subgroups with more granular grouping:

dt[, .(
    countries = uniqueN(country),
    company = c(.SD[, uniqueN(city), .(country)][, .(V1)]),
    company = c(.SD[, uniqueN(company), .(country, city)][, .(V1)])
    ), .(hs_code)]

#    hs_code countries company company
# 1:  apples         2     1,2   2,1,1
# 2: oranges         2     1,3 1,1,1,1