0

I have a dataset that contains ID and Address associated with the ID. An example would be:

ID   Address
1001 123 E example rd, 12300
1001 123 E example rd, 12300
1001 456 W example rd, 45600
1002 789 N example rd, 78900
1002 123 E example rd, 12300
1003 789 N example rd, 78900
1004 456 W example rd, 45600
1004 789 N example rd, 78900
1004 789 N example rd, 78900
1004 123 E example rd, 12300

Now, in the above example, we have 3 unique ID's. I want to label them as Place 1, Place 2 and Place 3. Finally, I want to have a data structure as below:

ID     x1        x2        x3          x4 
1001   Place 1   Place 1   Place 2
1002   Place 3   Place 1
1003   Place 3
1004   Place 2   Place 3   Place 3     Place 1

Since in my real dataset I have around 3000 unique addresses, I am looking for code that can loop around and label all 3000 addresses from Place 1 to Place 3000.

Ben
  • 28,684
  • 5
  • 23
  • 45
Farah
  • 5
  • 1

1 Answers1

2

We can replace unique addresses with "Place" + suffix values using match and unique, create a unique index for each ID and get the data in wide format using pivot_wider. 

library(dplyr)

df1 <- df %>%
  mutate(Address = paste0('Place', match(Address, unique(Address)))) %>%
  group_by(ID) %>%
  mutate(row = paste0('x', row_number())) %>%
  tidyr::pivot_wider(names_from = row, values_from = Address)

df1

#    ID   x1     x2     x3     x4    
#  <int> <chr>  <chr>  <chr>  <chr> 
#1  1001 Place1 Place1 Place2 NA    
#2  1002 Place3 Place1 NA     NA    
#3  1003 Place3 NA     NA     NA    
#4  1004 Place2 Place3 Place3 Place1

To export into csv, we can use write.csv 

write.csv(df1, 'newfile.csv', row.names = FALSE)

data

df <- structure(list(ID = c(1001L, 1001L, 1001L, 1002L, 1002L, 1003L, 
1004L, 1004L, 1004L, 1004L), Address = structure(c(1L, 1L, 2L, 
3L, 1L, 3L, 2L, 3L, 3L, 1L), .Label = c("123 E example rd, 12300", 
"456 W example rd, 45600", "789 N example rd, 78900"), class = "factor")), 
class = "data.frame", row.names = c(NA, -10L))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213