0

There are two datasets.

Dataset A

ID       year     absencePercentage    FrenquentAbsentee     VioFlagEver
0110     2014     6.88                 0                     1
0110     2015     20.27                1                     0
0111     2014     7.82                 0                     1
0111     2015     6.12                 0                     1
0112     2014     1.32                 0                     0
0112     2015     11.2                 1                     0

Dataset B

ID       school     name    gender     
0110     Apple      John    Male       
0111     Banana     Jane    Female  
0111     Apple      Rohn    Male

I was to merge two datasets and create new columns for the dataset B using rows of dataset A.

The final dataset should be like this:

ID       school     name    gender     2014    2014FA     2015     2015FA     VioFlagEver
0110     Apple      John    Male       6.88    0          20.27    1          1
0111     Banana     Jane    Female     7.82    0          6.12     0          1
0111     Apple      Rohn    Male       1.32    0          11.2     1          0

Basically, 2014FA will be 1, if the person was a frequent absentee (10% or more) in the year. VioFlagEver will be 1 if the person ever had VioFlagEver as 1 in ANY year (so ID 0110 will be 1, because he has 1 for the year 2014, even though for 2015, it's 0).

Thank you so much!

Ysabelle Boo
  • 29
  • 4
  • 2
    This is two questions, I suggest you focus on one at a time. First is how to `merge` the two datasets, and that should be straight-forward with `merge` or `left_join` or whichever, there are a lot of questions on SO that ask about this. And because this will produce more rows than you want (1-per-ID), you need to reshape from long-to-wide. A popular function for this is `tidyr::pivot_wider`, while `data.table::dcast` also works very well. – r2evans Feb 22 '21 at 21:05

1 Answers1

1

We could use

library(dplyr)
library(tidyr)
left_join(datB, datA, by = 'ID') %>% 
   pivot_wider(names_from = year, values_from = absencePercentage:VioFlagEver)
akrun
  • 874,273
  • 37
  • 540
  • 662