How to determine the region based on postal code

Question

I have 2 data frames, one contains data with postal code, the other contains regions with a set of postal codes

I want to add a "Regions" column in Dataframe 1, based on the postal codes, how can I do that? (note: regions in dataframe 2 can contains several postal codes.

Thanks for help.

split df2$postcodes to multiple columns (data.table::tstrsplit() would be my choice). Then melt df2. Finally, left join df2 to df1. — Wimpel, Jun 28 '19 at 09:30
@Humpelstielzchen it's not generally unwelcome to post images of drawings, though. — jay.sf, Jun 28 '19 at 09:34
Use [strsplit](https://stackoverflow.com/questions/15347282/split-delimited-strings-in-a-column-and-insert-as-new-rows) on `df2` and then [left_join](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) `df1` and `df2` — nghauran, Jun 28 '19 at 09:35
@Humpelstielzchen in this case a [mcve] should be given by OP ;) — jay.sf, Jun 28 '19 at 09:37

score 1 · Accepted Answer · answered Jun 28 '19 at 12:54

This can be solved with dplyr and tidyr. I'm sure there are other solutions too.

# create the data
df1 <- data.frame(pcodes = c(1001, 1002, 1003))
df2 <- data.frame(regions = c(1, 2), 
                  pcodes = c("1001, 1002, 1003", "1004, 1005"),
                  stringsAsFactors = FALSE)

library(dplyr)
library(tidyr)

# separate postcodes column and reshape long
# (from https://stackoverflow.com/a/33288868/2633645)
df2 <- df2 %>% 
  mutate(to = strsplit(pcodes, split = ",")) %>% 
  unnest(to) %>% 
  mutate(to = as.numeric(to)) %>% 
  select(-pcodes) %>% 
  rename(pcodes = to) # rename `to` to `pcodes` for join purpose

# join the data sets by the common variable pcodes
df_both <- left_join(df1, df2)
df_both

  pcodes regions
1   1001       1
2   1002       1
3   1003       1

Hi Mate, your solution has solved my problem perfectly, thank you so much! — Yneedtobeserious, Jul 01 '19 at 01:44

How to determine the region based on postal code

1 Answers1