Skip to content Skip to sidebar Skip to footer

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)"