'R How to add rows based on another variable then transform from wide format to long format

I would like to restructure my data frame in two steps.

Original data set:

structure(list(ID = c("1", "2", "3", "4", "5", "6"), Type = c("A", 
"B", "B", "A", "A", "C"), A_Var1 = c("Yes", "None", "None", "Yes", 
"None", "None"), A_Var2 = c("Yes", "None", "None", "Yes", "Yes", 
"None"), A_Var3 = c("Yes", "None", "None", "Yes", "Yes", "Yes"
), B_Var1 = c("NA", "None", "None", "NA", "NA", "None"), B_Var2 = c("NA", 
"None", "None", "NA", "NA", "Yes"), B_Var3 = c("NA", "None", 
"None", "NA", "NA", "Yes")), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

First, I am hoping to add new rows for each ID based on the sequential order of Type. There are 3 types in the data frame, and the order is A,B,C. For each ID, if I see type B, I would like to add a row with the same ID but type A above; if I see type C, then I will add two rows with same ID but type A and type B above.

Second, I am hoping to transform the data frame after step 1 from a wide format to a long format. So I fill Var1, Var2, Var3 to its corresponding type.

Excepted data set:

structure(list(ID = c("1", "2", "2", "3", "3", "4", "5", "6", 
"6", "6"), Type = c("A", "A", "B", "A", "B", "A", "A", "A", "B", 
"C"), Var1 = c("Yes", "None", "None", "None", "None", "Yes", 
"None", "None", "None", NA), Var2 = c("Yes", "None", "None", 
"None", "None", "Yes", "Yes", "None", "Yes", NA), Var3 = c("Yes", 
"None", "None", "None", "None", "Yes", "Yes", "Yes", "Yes", NA
)), row.names = c(NA, -10L), class = "data.frame")

I have a hard time figuring out how to accomplish both steps in a nice order. Would appreciate any comment!

rformattransform


Solution 1:[1]

Use the following code:

df1 %>% 
  mutate(across(everything(), na_if, 'NA')) %>%
  select(-Type) %>%
  pivot_longer(-ID, names_to = c('Type', '.value'),
               names_sep = '_', values_drop_na = TRUE)

# A tibble: 9 x 5
  ID    Type  Var1  Var2  Var3 
  <chr> <chr> <chr> <chr> <chr>
1 1     A     Yes   Yes   Yes  
2 2     A     None  None  None 
3 2     B     None  None  None 
4 3     A     None  None  None 
5 3     B     None  None  None 
6 4     A     Yes   Yes   Yes  
7 5     A     None  Yes   Yes  
8 6     A     None  None  Yes  
9 6     B     None  Yes   Yes

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 onyambu

Related Questions