If any rows contain the same data in some columns, move other columns in that row into new columns i-CodePudding

Sample data:

df<-data.frame(Country = c("FR", "FR", "US", "US", "US", "US", "AU", "UK", "UK", "UK"), 
Name = c("Jean","Jean","Rose","Rose","Rose","Rose","Liam","Mark","Mark","Mark"),
A=c(2,NA,NA,1,3,NA,1,2,NA,NA),
B=c(2,5,NA,1,NA,2,1,NA,3,NA),
C=c(2,NA,4,1,NA,NA,NA,NA,NA,NA),
D=c(NA,3,NA,NA,4,4,1,2,4,4))

Input:   
  Country Name   A  B  C  D  
1      FR Jean   2  2  2  NA 
2      FR Jean   NA 5  NA 3  
3      US Rose   NA NA 4  NA 
4      US Rose   1  1  1  NA 
6      US Rose   3  NA NA 4  
7      US Rose   NA 2  NA 4  
8      AU Liam   1  1  NA 1  
9      UK Mark   2  NA NA 2  
10     UK Mark   NA 3  NA 4  
11     UK Mark   NA NA NA 4  

Desired output:
  Country Name   A  B  C  D  A  B  C  D  A  B  C  D  A  B  C  D 
1      FR Jean   2  2  2  NA NA 5  NA 3
2      US Rose   NA NA 4  NA 1  1  1  NA 3  NA NA 4  NA 2  NA 4    
3      AU Liam   1  1  NA 1  
4      UK Mark   2  NA NA 2  NA 3  NA 4  NA NA NA 4

As you can see from the data, the goal is:

if Country and Name contains identical data for each subsequent row, move the data in columns ABCD in those rows into new ABCD columns.
the actual table that I have do not contain just 11 rows. In subsequent rows (after row 11), the Country and Name data may be repeated 1, 2, 3,...n times. How do I make a CONDITIONAL such that, as long as the row below is identical, automatically move the data in ABCD to create new ABCD columns?

CodePudding user response：

You won't be able to have two columns with the same name. I don't know if it will help you, but you could do this:

Code

library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(cols = A:D) %>%
  group_by(Country,name) %>% 
  mutate(name = paste0(name,row_number())) %>% 
  pivot_wider(names_from = name,values_from = value)

Output

# A tibble: 4 x 18
# Groups:   Country [4]
  Country Name     A1    B1    C1    D1    A2    B2    C2    D2    A3    B3    C3    D3    A4    B4
  <chr>   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 FR      Jean      2     2     2    NA    NA     5    NA     3    NA    NA    NA    NA    NA    NA
2 US      Rose     NA    NA     4    NA     1     1     1    NA     3    NA    NA     4    NA     2
3 AU      Liam      1     1    NA     1    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA
4 UK      Mark      2    NA    NA     2    NA     3    NA     4    NA    NA    NA     4    NA    NA
# ... with 2 more variables: C4 <dbl>, D4 <dbl>