Collapse duplicated rows with different values in different columns using R

user_16120405 0
Viewed 401

I have a data frame with 500 observations, but I am only showing 3 in my example. These are duplicates with different values in different columns (except ID column, which includes which person is duplicated). I am replicating how the data frame is (df) and how it should look like after treatment (df_new). Is this possible ? The data frame is 10 variables, so I am not worried about 'doubling' them. The values in the variables are a,b,c,d,0,''. I however kept them more general in the tables.

df <- data.frame(ID =  c('1','1','2', '2', '3','3'),
                 Year = c('smaller year.1', 'bigger year.1', 'bigger year.2', 'smaller year.2', 'same year.3', 'same year.3'),
                 V1 = c('a', 'b','c','d','e','f'),
                 V2 = c('g', 'h', 'i', 'j', 'k', 'l'),
                 Vn = c('n1', 'n2','n3','n4','n5','n6'))


df_new <- data.frame(ID = c('1','2','3'),
                     Year_smaller = c('smaller year.1', 'smaller year.2', 'same year.3'),
                     Year_bigger = c('bigger year.1', 'bigger year.2', 'same year.3'),
                     V1 = c('a','c','e'),
                     V1.1 = c('b','d','f'),
                     V2 = c('g','i','k'),
                     V2.1 = c('h','j','l'),
                     Vn = c('n1','n3','n5'),
                     Vn.1 = c('n2','n4','n6'))
tidyr r
4 Answers

For edited data and as per revised requirements. Since in alphabet b comes before s therefor bigger_year is shown before smaller_year however, in the real data you'll have correctly sorted years. Still if you want to sort strings like that use sort(desc(Year)) instead of sort(Year)

df <- data.frame(ID =  c('1','1','2', '2', '3','3'),
                 Year = c('smaller year.1', 'bigger year.1', 'bigger year.2', 'smaller year.2', 'same year.3', 'same year.3'),
                 V1 = c('a', 'b','c','d','e','f'),
                 V2 = c('g', 'h', 'i', 'j', 'k', 'l'),
                 Vn = c('n1', 'n2','n3','n4','n5','n6'))


library(tidyverse)

df %>% group_by(ID) %>% mutate(Year = sort(Year)) %>% 
  mutate(rid = row_number()) %>%
  pivot_wider(id_cols = ID, names_from = rid, values_from = c(Year:Vn), names_sep = '')

#> # A tibble: 3 x 9
#> # Groups:   ID [3]
#>   ID    Year1         Year2          V11   V12   V21   V22   Vn1   Vn2  
#>   <chr> <chr>         <chr>          <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 1     bigger year.1 smaller year.1 a     b     g     h     n1    n2   
#> 2 2     bigger year.2 smaller year.2 c     d     i     j     n3    n4   
#> 3 3     same year.3   same year.3    e     f     k     l     n5    n6

Created on 2021-06-19 by the reprex package (v2.0.0)

library(tidyverse)

df %>% group_by(ID) %>% mutate(rid = row_number()) %>%
  pivot_wider(id_cols = ID, names_from = rid, values_from = c(Year:Variable_n), names_sep = '')

# A tibble: 3 x 9
# Groups:   ID [3]
  ID    Year1          Year2          Variable_a1 Variable_a2 Variable_b1 Variable_b2 Variable_n1 Variable_n2
  <chr> <chr>          <chr>          <chr>       <chr>       <chr>       <chr>       <chr>       <chr>      
1 1     smaller year.1 bigger year.1  va11        va12        vb11        vb12        vn11        vn12       
2 2     bigger year.2  smaller year.2 va21        va22        vb21        vb22        vn21        vn22       
3 3     same year.3    same year.3    va31        va32        vb31        vb32        vn31        vn32

Do u mean this?


df %>% group_by(ID) %>% arrange(desc(Year)) %>% mutate(rid = row_number()) %>%
  pivot_wider(id_cols = ID, names_from = rid, values_from = c(Year:Variable_n), names_sep = '')

# A tibble: 3 x 9
# Groups:   ID [3]
  ID    Year1          Year2         Variable_a1 Variable_a2 Variable_b1 Variable_b2 Variable_n1 Variable_n2
  <chr> <chr>          <chr>         <chr>       <chr>       <chr>       <chr>       <chr>       <chr>      
1 2     smaller year.2 bigger year.2 va22        va21        vb22        vb21        vn22        vn21       
2 1     smaller year.1 bigger year.1 va11        va12        vb11        vb12        vn11        vn12       
3 3     same year.3    same year.3   va31        va32        vb31        vb32        vn31        vn32
user_28848590

Here is one data.table option -

library(data.table)

cols <- grep('Variable', names(df), value = TRUE)
dcast(setDT(df), ID~rowid(ID), value.var = c('Year', cols))


#   ID         Year_1         Year_2 Variable_a_1 Variable_a_2 Variable_b_1
#1:  1 smaller year.1  bigger year.1         va11         va12         vb11
#2:  2  bigger year.2 smaller year.2         va21         va22         vb21
#3:  3    same year.3    same year.3         va31         va32         vb31

#   Variable_b_2 Variable_n_1 Variable_n_2
#1:         vb12         vn11         vn12
#2:         vb22         vn21         vn22
#3:         vb32         vn31         vn32
user_39629140

We can also use the following solution:

library(dplyr)
library(tidyr)
library(purrr)

df %>%
  group_split(ID) %>%
  map_dfr(~ .x %>% 
            mutate(id = row_number()) %>%
            pivot_wider(names_from = id, values_from = c(Year, Variable_a, Variable_b, Variable_n),
                        names_sep = "") %>%
            rename(Year_smaller = Year1,
                   Year_bigger = Year2)) %>%
  select(starts_with("Year"))

# A tibble: 3 x 9
  ID    Year_smaller   Year_bigger    Variable_a1 Variable_a2 Variable_b1 Variable_b2 Variable_n1
  <chr> <chr>          <chr>          <chr>       <chr>       <chr>       <chr>       <chr>      
1 1     smaller year.1 bigger year.1  va11        va12        vb11        vb12        vn11       
2 2     bigger year.2  smaller year.2 va21        va22        vb21        vb22        vn21       
3 3     same year.3    same year.3    va31        va32        vb31        vb32        vn31       
# ... with 1 more variable: Variable_n2 <chr>
user_143145200

Here is my inelegant answer. This might give you some idea.

library(tidyverse)

year_df <- df %>% 
  select(ID, Year) %>% 
  filter(!str_detect(Year, "^same")) %>% 
  mutate(year_group = map_chr(Year, ~str_split(., " ")[[1]][1])) %>% 
  pivot_wider(
    names_from = year_group,
    values_from = Year
  ) %>% 
  add_row(ID = "3", smaller = "same year.3", bigger = "same year.3")

df_new <- df %>% 
  select(-Year) %>%
  pivot_longer(-ID) %>% 
  mutate(
    group = paste0(str_sub(name, -1), str_sub(value, -1)),
    name = str_remove(name, "_[a-z]")
  ) %>%
  pivot_wider(
    names_from = c(name, group),
    values_from = value
  ) %>% 
  left_join(year_df, by = "ID") %>% 
  relocate(c(smaller, bigger), .after = ID)

df_new
user_86994630
Related