coms coms - 2 months ago 8
Python Question

input column based on conditional statement pandas

I have two Pandas data frames and wish to insert day and week columns from DF2 values in DF1 where the dates match. e.g. for example below, day - 4 and week - 1 would be extracted from DF2 row 1 and inserted to all of the day and week columns in DF1.

DF 1

montgomery year date day week
0.0 2016 04/01/2016
0.0 2016 04/01/2016
0.0 2016 04/01/2016
0.0 2016 04/01/2016


DF 2

date day week
04/01/2016 4 1
05/01/2016 5 1
06/01/2016 6 1


I have looked into using the numpy conditional statements but haven't reached a solution, thanks

Answer

IIUC you need to_datetime with merge:

#if empty columns in DF1, remove them
DF1 = DF1.drop(['day','week'], axis=1)

#convert columns to datetimes
DF1.date = pd.to_datetime(DF1.date)
DF2.date = pd.to_datetime(DF2.date)

print (pd.merge(DF1,DF2, how='left', on='date'))
   montgomery  year       date  day  week
0         0.0  2016 2016-04-01    4     1
1         0.0  2016 2016-04-01    4     1
2         0.0  2016 2016-04-01    4     1
3         0.0  2016 2016-04-01    4     1