R: Turning row data from one dataframe into column data by group in another

user_18194698 0
Viewed 66

I have data in the following format:

ID Age Sex
1 29 M
2 32 F
3 18 F
4 89 M
5 45 M

and;

ID subID Type Status Year
1 3 Car Y
1 11 Toyota NULL 2011
1 23 Kia NULL 2009
2 5 Car N
3 2 Car Y
3 4 Honda NULL 2019
3 7 Fiat NULL 2006
3 8 Mitsubishi NULL 2020
4 1 Car N
5 7 Car Y

Each ID in the second table has a row specifying if they have a car, and additional rows stating the brand of car/s they own. Each person has a maximum of 3 cars. I want to simplify this data into a single table as so.

ID Age Sex Car? Car.1 Car1.year Car.2 Car2.year Car.3 Car3.year
1 29 M Y Toyota 2011 Kia 2009 NULL NULL
2 32 F N NULL NULL NULL NULL NULL NULL
3 18 F Y Honda 2019 Fiat 2006 Mitsubishi 2020
4 89 M N NULL NULL NULL NULL NULL NULL
5 45 M Y NULL NULL NULL NULL NULL NULL

I've tried using the mutate function in dplyr with the case_when function, but I can't check conditions in another dataframe. If I try to join the tables together, I would have multiple rows for each ID which I want to avoid. The non-standard set up of the second table makes things complicated. My only remaining idea is to switch to Python/Pandas and create a for loop that slowly loops through each ID, searches the second dataframe if the person has a car and the car brands, then mutates a column in the first dataframe. But given the size of my dataset, this would be inefficient and take a long time.

What is the best way to do this?

simplify dplyr dataframe r
1 Answers

You can try the following codes:

library(tidyverse)

df1

# A tibble: 5 x 3
     ID   Age Sex  
  <dbl> <dbl> <chr>
1     1    29 M    
2     2    32 F    
3     3    18 F    
4     4    89 M    
5     5    45 M  

df2

# A tibble: 10 x 5
      ID subID Type       Status  Year
   <dbl> <dbl> <chr>      <chr>  <dbl>
 1     1     3 Car        Y         NA
 2     1    11 Toyota     Y       2011
 3     1    23 Kia        Y       2009
 4     2     5 Car        N         NA
 5     3     2 Car        Y         NA
 6     3     4 Honda      Y       2019
 7     3     7 Fiat       Y       2006
 8     3     8 Mitsubishi Y       2020
 9     4     1 Clothed    N         NA
10     5     7 Clothed    Y         NA


df2 <- df2 %>% mutate(Status = if_else(Status == "NULL", "Y", Status))
df3 <- df2 %>% filter(!is.na(Year)) %>% group_by(ID) %>% mutate(index = row_number())  
df4 <- df3  %>% pivot_wider(id_cols = c(ID), values_from = c(Type, Year), names_from = index )

So your desired output will be produced:

df1 %>% left_join(df2 %>% select(ID, Status) %>% distinct()) %>% left_join(df4) 

# A tibble: 5 x 10
     ID   Age Sex   Status Type_1 Type_2 Type_3     Year_1 Year_2 Year_3
  <dbl> <dbl> <chr> <chr>  <chr>  <chr>  <chr>       <dbl>  <dbl>  <dbl>
1     1    29 M     Y      Toyota Kia    NA           2011   2009     NA
2     2    32 F     N      NA     NA     NA             NA     NA     NA
3     3    18 F     Y      Honda  Fiat   Mitsubishi   2019   2006   2020
4     4    89 M     N      NA     NA     NA             NA     NA     NA
5     5    45 M     Y      NA     NA     NA             NA     NA     NA
user_179732870
Related