Use merge with one data frame in R

Question

I have one data frame in R with duplicate indexes stored in the first column.

df <- data.frame("Index" = c(1,2,1), "Age" = c("Jane Doe","John Doe","Jane 
Doe"), "Address" = c("123 Fake Street","780 York Street","456 Elm 
Street"),"Telephone" = c("xxx-xxx-xxxx","zzz-zzz-zzzz","yyy-yyy-yyyy"))

Index    Name        Address          Telephone
  1    Jane Doe  123 Fake Street   xxx-xxx-xxxx
  2    John Doe  780 York Street   zzz-zzz-zzzz
  1    Jane Doe  456 Elm Street    yyy-yyy-yyyy

I would like to combine the above data frame to look like:

Index   Name        Address         Telephone     Address 2       Telephone 2
1    Jane, Doe  123 Fake Street   xxx-xxx-xxxx  456 Elm Street  yyy-yyy-yyyy
2    John Doe   780 York Street   zzz-zzz-zzzz       NA             NA

Can I use "merge" on the same data frame or is their another command in R that would accomplish this task? Thank you.

This is a reshape operation - going from long to wide by `Index + Name` - there should be plenty of previous questions if you search here using `[r] reshape long wide` — thelatemail, Jun 19 '18 at 01:30
`reshape(transform(df, time=ave(Index,Index,FUN=seq_along)), idvar=c("Index","Age"), direction="wide")` for instance. — thelatemail, Jun 19 '18 at 01:33

moodymudskipper · Accepted Answer · 2018-06-19T01:59:52.913

with tidyverse

df %>%
  group_by(Age) %>%
  summarize_at(vars(Telephone,Address),paste, collapse="|") %>%
  separate(Address,into=c("Address1","Address2"),sep="\\|") %>%
  separate(Telephone,into=c("Telephone1","Telephone2"),sep="\\|")

# # A tibble: 2 x 5
#   Age      Telephone1   Telephone2   Address1        Address2      
#   <fct>    <chr>        <chr>        <chr>           <chr>         
# 1 Jane Doe xxx-xxx-xxxx yyy-yyy-yyyy 123 Fake Street 456 Elm Street
# 2 John Doe zzz-zzz-zzzz <NA>         780 York Street <NA>

To be more general, we can nest the values using summarize and list, and reformat the content to unnestit with the right format:

df %>%
  group_by(Age) %>%
  summarize_at(vars(Telephone,Address),
               ~lst(setNames(invoke(tibble,.),seq_along(.)))) %>%
  unnest(.sep = "")

# # A tibble: 2 x 5
#   Age      Telephone1   Telephone2   Address1        Address2      
#   <fct>    <fct>        <fct>        <fct>           <fct>         
# 1 Jane Doe xxx-xxx-xxxx yyy-yyy-yyyy 123 Fake Street 456 Elm Street
# 2 John Doe zzz-zzz-zzzz <NA>         780 York Street <NA>

The function inside of summarize is a bit scary but you can wrap it into a friendlier name if you want to use it again (I added a names parameter just in case):

nest2row <- function(x,names = seq_along(x))
  lst(setNames(invoke(tibble,x),names[seq_along(x)])) 

df %>%
  group_by(Age) %>%
  summarize_at(vars(Telephone,Address), nest2row) %>%
  unnest(.sep = "")

And this would be the recommended tidy way I suppose :

df %>%
  group_by(Age) %>%
  mutate(id=row_number()) %>%
  gather(key,value,Address,Telephone) %>%
  unite(key,key,id,sep="") %>%
  spread(key,value)

# # A tibble: 2 x 6
# # Groups:   Age [2]
#   Index Age      Address1        Address2       Telephone1   Telephone2  
#   <dbl> <fct>    <chr>           <chr>          <chr>        <chr>       
# 1     1 Jane Doe 123 Fake Street 456 Elm Street xxx-xxx-xxxx yyy-yyy-yyyy
# 2     2 John Doe 780 York Street <NA>           zzz-zzz-zzzz <NA>

With my second solution you keep your factors and there's not this awkward forcing different types of variables in the same column that the idiomatic way has.

score 0 · Answer 2 · answered Jun 19 '18 at 00:48

Try something like this:

df <- data.frame("Index" = c(1,2,1), "Age" = c("Jane Doe","John Doe","Jane Doe"), 
"Address" = c("123 Fake Street","780 York Street","456 Elm Street"),
"Telephone" = c("xxx-xxx-xxxx","zzz-zzz-zzzz","yyy-yyy-yyyy"),
                 stringsAsFactors = F)

df$unindex=paste(df$Index,df$Age)

sapply(unique(df$unindex),function(li){ # li="1 Jane Doe"
  dft=df[li==df$unindex,3:4]
  if(nrow(dft)==1)dft else c(t(dft))
})

Use merge with one data frame in R

2 Answers2