How to query a second dataframe to fill values in a column of first dataframe

Question

I have two dataframes:

df1.head()
    ParticipantId   EventTime                    DayofWeek
0   1               2019-10-29 19:47:20.797000   Tuesday
1   7               2019-08-26 16:18:31.410000   Monday
2   5               2019-10-10 20:50:54.757000   Thursday
3   1               2019-10-22 16:21:21.470000   Tuesday
4   9               2019-10-07 21:44:27.073000   Monday
    .
    .

df2.head()
    ParticipantId   WeekDay     WakeTime    
0   1               Monday      09:00:00    
1   1               Tuesday     09:00:00    
2   1               Wednesday   09:00:00    
3   1               Thursday    09:00:00    
4   1               Friday      09:00:00
    .
    .
7   2               Monday      09:30:00
    .
    .

Essentially, what I want to do is, add a column to df1 with the wake time of participant from df2. So that I get something like this:

df1.head()
    ParticipantId   ScheduleName                 DayofWeek  WakeTime
0   1               2019-10-29 19:47:20.797000   Tuesday    09:00:00
1   7               2019-08-26 16:18:31.410000   Monday     10:00:00
2   5               2019-10-10 20:50:54.757000   Thursday   09:00:00
3   2               2019-10-22 16:21:21.470000   Tuesday    09:30:00
4   9               2019-10-07 21:44:27.073000   Monday     08:00:00

What I have so far is:

df1['WakeTime'] = [df2[(df2.ParticipantId == x[0]) & (df2.WeekDay == x[1])].values[0][2] for x in df1[['ParticipantId','DayofWeek']].values]

Although this works, I wonder if there is a better way to do this because it is almost unreadable.

It looks like a case for merge, but I don't understand your output. — Quang Hoang, Dec 12 '19 at 16:02
Yes, `df2` has multiple duplicated pair `(ParticipantId, Weekday)`, which one do you choose to pick? — Quang Hoang, Dec 12 '19 at 16:04
The pair `(ParticipantId, Weekday)` is unique in `df2`. Every participant has 7 rows in `df2` for each day of the week and the wake time for that day. — Akshay Gaur, Dec 12 '19 at 16:06
Then a simple `df1.merge(df2, on=['ParticipantId', 'Weekday'], how='left')` would do. — Quang Hoang, Dec 12 '19 at 16:08
Thanks! I only needed to change the column name of `df1` from `DayofWeek` to `WeekDay`. Man, I don't know what I was thinking. — Akshay Gaur, Dec 12 '19 at 16:17

How to query a second dataframe to fill values in a column of first dataframe

0 Answers0