Add A Column Value Depending On A Date Range (if-else)
I have a date column in my dataframe and want to add a column called location. The value of location in each row should depend on which date range it falls under. For example, the
Solution 1:
Create dictionary of locations with start and end dates first, then loop by dict and set values by loc
and between
:
d = {'Seattle':['2017-11-12','2017-11-16'],
'New York':['2017-11-17','2017-11-18'],
'London':['2017-11-19','2017-11-20']}
df['Dates'] = pd.to_datetime(df['Dates'], format='%m/%d/%Y')
for k, (s,e) in d.items():
df.loc[df['Dates'].between(s,e), 'Loc'] = k
print (df)
Dates Location Loc
0 2017-11-12 Seattle Seattle
1 2017-11-13 Seattle Seattle
2 2017-11-14 Seattle Seattle
3 2017-11-15 Seattle Seattle
4 2017-11-16 Seattle Seattle
5 2017-11-17 New York New York
6 2017-11-18 New York New York
7 2017-11-19 London London
8 2017-11-20 London London
EDIT:
d = {'Seattle':[('2017-11-12','2017-11-13'), ('2017-11-15','2017-11-16')],
'New York':[('2017-11-17','2017-11-18')],
'London':[('2017-11-19','2017-11-20'), ('2017-11-14','2017-11-14')]}
df['Dates'] = pd.to_datetime(df['Dates'], format='%m/%d/%Y')
for k, v in d.items():
for s, e in v:
df.loc[df['Dates'].between(s,e), 'Loc'] = k
print (df)
Dates Location Loc
0 2017-11-12 Seattle Seattle
1 2017-11-13 Seattle Seattle
2 2017-11-14 Seattle London
3 2017-11-15 Seattle Seattle
4 2017-11-16 Seattle Seattle
5 2017-11-17 New York New York
6 2017-11-18 New York New York
7 2017-11-19 London London
8 2017-11-20 London London
Post a Comment for "Add A Column Value Depending On A Date Range (if-else)"