CODEWITHSUNDEEP
pythonpandasdatedataframe
zosh
zosh

Reputation: 83

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 date 13th November falls between 12th November and 16th November & therefore the location should be Seattle. The date 17th November falls between 17th November and 18th November and must be New York.

Below is an example of the data frame I want to achieve

Dates     | Location (column I want to add)
.............................................
11/12/2017| Seattle
11/13/2017| Seattle
11/14/2017| Seattle
11/15/2017| Seattle
11/16/2017| Seattle
11/17/2017| New York
11/18/2017| New York
11/19/2017| London
11/20/2017| London

Upvotes: 1

Views: 612

Answers (1)

jezrael
jezrael

Reputation: 862431

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

Upvotes: 1

Related Questions