Expanding a data.table to create grouped principal and alias combinations

user_2449656 0
Viewed 73

I have the following data.table:

require(data.table)
dt = data.table(structure(list(postcode = c("4101", "4101", "4101", "4101", "4101", 
"4101", "4101", "4101", "4272", "4272", "4272", "4272", "4272"
), suburb = c("SOUTH BRISBANE", "HIGHGATE HILL", "WEST END", 
"WOOLLOONGABBA", "DUTTON PARK", "HIGHGATE HILL", "SOUTH BRISBANE", 
"WEST END", "BENOBBLE", "EAGLE HEIGHTS", "NORTH TAMBORINE", "MOUNT TAMBORINE", 
"TAMBORINE MOUNTAIN"), type = c("A", "A", "A", "A", "A", "P", 
"P", "P", "A", "A", "A", "A", "P")), class = "data.frame", row.names = c(NA, 
-13L)))

 dt
    postcode             suburb type
 1:     4101     SOUTH BRISBANE    A
 2:     4101      HIGHGATE HILL    A
 3:     4101           WEST END    A
 4:     4101      WOOLLOONGABBA    A
 5:     4101        DUTTON PARK    A
 6:     4101      HIGHGATE HILL    P
 7:     4101     SOUTH BRISBANE    P
 8:     4101           WEST END    P
 9:     4272           BENOBBLE    A
10:     4272      EAGLE HEIGHTS    A
11:     4272    NORTH TAMBORINE    A
12:     4272    MOUNT TAMBORINE    A
13:     4272 TAMBORINE MOUNTAIN    P

I need to expand it in such a way that for a given postcode group, it assigns all possible principal variations to all alias (type = A) and principal (type = P) types within the postcode group

e.g. For the first postcode group:

dt[postcode == 4101]

   postcode         suburb type
1:     4101 SOUTH BRISBANE    A
2:     4101  HIGHGATE HILL    A
3:     4101       WEST END    A
4:     4101  WOOLLOONGABBA    A
5:     4101    DUTTON PARK    A
6:     4101  HIGHGATE HILL    P
7:     4101 SOUTH BRISBANE    P
8:     4101       WEST END    P

There are 5 alias types (type = A) and 3 principal types (type = P).

The output will be (5 (type=A) * 3 (type=P)) + 3 (type=P)) = 18 rows, and will look like this:

postcode         suburb type  principal
    4101 SOUTH BRISBANE    A  HIGHGATE HILL
    4101 SOUTH BRISBANE    A  SOUTH BRISBANE
    4101 SOUTH BRISBANE    A  WEST END
    4101  HIGHGATE HILL    A  HIGHGATE HILL
    4101  HIGHGATE HILL    A  SOUTH BRISBANE
    4101  HIGHGATE HILL    A  WEST END
    4101       WEST END    A  HIGHGATE HILL
    4101       WEST END    A  SOUTH BRISBANE
    4101       WEST END    A  WEST END
    4101  WOOLLOONGABBA    A  HIGHGATE HILL
    4101  WOOLLOONGABBA    A  SOUTH BRISBANE
    4101  WOOLLOONGABBA    A  WEST END
    4101    DUTTON PARK    A  HIGHGATE HILL
    4101    DUTTON PARK    A  SOUTH BRISBANE
    4101    DUTTON PARK    A  WEST END
    4101  HIGHGATE HILL    P  HIGHGATE HILL
    4101 SOUTH BRISBANE    P  SOUTH BRISBANE
    4101       WEST END    P  WEST END

This is example data, in reality I have thousands of postcode groups.

data.table r
4 Answers

data.table version using a self-join of the principal and alias subsets, with the principal rows bound on the bottom afterwards:

rbind(
  dt[type=="P",][dt[type=="A"], on="postcode", 
     .(postcode,suburb=i.suburb,type=i.type,principal=suburb),  allow.cartesian=TRUE],
  dt[type=="P", c(.SD, .(principal=suburb))]
)


#    postcode             suburb type          principal
# 1:     4101     SOUTH BRISBANE    A      HIGHGATE HILL
# 2:     4101     SOUTH BRISBANE    A     SOUTH BRISBANE
# 3:     4101     SOUTH BRISBANE    A           WEST END
# 4:     4101      HIGHGATE HILL    A      HIGHGATE HILL
# 5:     4101      HIGHGATE HILL    A     SOUTH BRISBANE
# 6:     4101      HIGHGATE HILL    A           WEST END
# 7:     4101           WEST END    A      HIGHGATE HILL
# 8:     4101           WEST END    A     SOUTH BRISBANE
# 9:     4101           WEST END    A           WEST END
#10:     4101      WOOLLOONGABBA    A      HIGHGATE HILL
#11:     4101      WOOLLOONGABBA    A     SOUTH BRISBANE
#12:     4101      WOOLLOONGABBA    A           WEST END
#13:     4101        DUTTON PARK    A      HIGHGATE HILL
#14:     4101        DUTTON PARK    A     SOUTH BRISBANE
#15:     4101        DUTTON PARK    A           WEST END
#16:     4272           BENOBBLE    A TAMBORINE MOUNTAIN
#17:     4272      EAGLE HEIGHTS    A TAMBORINE MOUNTAIN
#18:     4272    NORTH TAMBORINE    A TAMBORINE MOUNTAIN
#19:     4272    MOUNT TAMBORINE    A TAMBORINE MOUNTAIN
#20:     4101      HIGHGATE HILL    P      HIGHGATE HILL
#21:     4101     SOUTH BRISBANE    P     SOUTH BRISBANE
#22:     4101           WEST END    P           WEST END
#23:     4272 TAMBORINE MOUNTAIN    P TAMBORINE MOUNTAIN
#    postcode             suburb type          principal
user_4968030 
A = dt[type=="A",]
P = dt[type=="P",]
df = merge(A, P, by="postcode", allow.cartesian=TRUE)
df = df[,1:4]
colnames(df) = c('postcode', 'suburb', 'type','principal')
P[,"principal"] =  P[,"suburb"]
df = rbind(df, P)
user_70957610

I wrote a for loop. Surely, one can make this faster.

DT <- data.table(postcode = NULL,  suburb = NULL, type = NULL, principal = NULL)

for (j in 1:nrow(dt)) 
  if(dt$type[j] == "A") {
    DT <- rbind(DT, cbind(dt[j],
            principal = dt[postcode == dt$postcode[j] & type == "P"]$suburb))
  } else {
    DT <- rbind(DT, cbind(dt[j],
                          principal = dt[j]$suburb))
  }

DT[order(type)]

  postcode             suburb type          principal
 1:     4101     SOUTH BRISBANE    A      HIGHGATE HILL
 2:     4101     SOUTH BRISBANE    A     SOUTH BRISBANE
 3:     4101     SOUTH BRISBANE    A           WEST END
 4:     4101      HIGHGATE HILL    A      HIGHGATE HILL
 5:     4101      HIGHGATE HILL    A     SOUTH BRISBANE
 6:     4101      HIGHGATE HILL    A           WEST END
 7:     4101           WEST END    A      HIGHGATE HILL
 8:     4101           WEST END    A     SOUTH BRISBANE
 9:     4101           WEST END    A           WEST END
10:     4101      WOOLLOONGABBA    A      HIGHGATE HILL
11:     4101      WOOLLOONGABBA    A     SOUTH BRISBANE
12:     4101      WOOLLOONGABBA    A           WEST END
13:     4101        DUTTON PARK    A      HIGHGATE HILL
14:     4101        DUTTON PARK    A     SOUTH BRISBANE
15:     4101        DUTTON PARK    A           WEST END
16:     4272           BENOBBLE    A TAMBORINE MOUNTAIN
17:     4272      EAGLE HEIGHTS    A TAMBORINE MOUNTAIN
18:     4272    NORTH TAMBORINE    A TAMBORINE MOUNTAIN
19:     4272    MOUNT TAMBORINE    A TAMBORINE MOUNTAIN
20:     4101      HIGHGATE HILL    P      HIGHGATE HILL
21:     4101     SOUTH BRISBANE    P     SOUTH BRISBANE
22:     4101           WEST END    P           WEST END
23:     4272 TAMBORINE MOUNTAIN    P TAMBORINE MOUNTAIN
    postcode             suburb type          principal
user_36515290

Here is one way to do this in tidyverse :

library(tidyverse)

dt %>%
  group_split(postcode) %>%
  map_df(function(x) {
    x %>% 
    filter(type == 'P') %>%
    select(principal = suburb) %>%
    crossing(x %>% filter(type != 'P')) %>%
    bind_rows(x %>% filter(type == 'P') %>% mutate(principal = suburb))
  })

# A tibble: 23 x 4
#   principal      postcode suburb         type 
#   <chr>          <chr>    <chr>          <chr>
# 1 HIGHGATE HILL  4101     DUTTON PARK    A    
# 2 HIGHGATE HILL  4101     HIGHGATE HILL  A    
# 3 HIGHGATE HILL  4101     SOUTH BRISBANE A    
# 4 HIGHGATE HILL  4101     WEST END       A    
# 5 HIGHGATE HILL  4101     WOOLLOONGABBA  A    
# 6 SOUTH BRISBANE 4101     DUTTON PARK    A    
# 7 SOUTH BRISBANE 4101     HIGHGATE HILL  A    
# 8 SOUTH BRISBANE 4101     SOUTH BRISBANE A    
# 9 SOUTH BRISBANE 4101     WEST END       A    
#10 SOUTH BRISBANE 4101     WOOLLOONGABBA  A    
# … with 13 more rows
user_39629140
Related