CODEWITHSUNDEEP
python-3.xpandaspandas-groupby
Egor Maksimov
Egor Maksimov

Reputation: 62

Group dataframe by week and get min and max dates within a week to new column

I have a dataframe which includes columns "call_date", which is date of call and "call_week", which is number of week (week does not necessarily start on Monday or Sunday and does not necessarily last exactly 7 days):

enter image description here

What I want to do is add new column to dataframe which would contain boundary dates of week separated by " - ". For example, if we have a WEEK 68 which has minimum CALL_DATE of 2019-04-25 and maximum CALL_DATE of 2019-04-30, new column should contain value 2019-04-25 - 2019-04-30.

I tried: dfg = df.groupby('WEEK')['CALL_DATE'].agg(['min', 'max']).reset_index()

dfg:

enter image description here

Then I added those min and max columns to df via join:

df = df.join(dfg, lsuffix = 'WEEK', rsuffix = 'WEEK')

Now I am trying to apply lambda function to concatenate those columns in one which contains the result:

df['WEEK_TEXT'] = df.apply(lambda x : x['min'].strftime("%d.%m.%Y") + ' - ' + x['max'].strftime("%d.%m.%Y"))

But I get an error: KeyError: ('min', 'occurred at index CONTACT_ID')

How do I fix that?

Upvotes: 1

Views: 589

Answers (1)

jezrael
jezrael

Reputation: 862611

Better is use Series.dt.strftime:

df['WEEK_TEXT'] = df['min'].dt.strftime("%d.%m.%Y") + ' - ' + df['max'].dt.strftime("%d.%m.%Y")

In your solution is necessary axis=1 for processes by rows:

f = lambda x : x['min'].strftime("%d.%m.%Y") + ' - ' + x['max'].strftime("%d.%m.%Y")
df['WEEK_TEXT'] = df.apply(f, axis=1)

Upvotes: 1

Related Questions