How to merge rows in Pandas Dataframe based on date range

Question

I have a DataFrame which has two columns with dates and string value as below:

    date        Name     Cost        
0  2010-09-15  ABC XYZ   100
1  2010-09-16  PQR RTS    30
2  2010-09-17  DEF GHI    20
3  2010-09-18  LKJ POD    80
4  2010-09-19  WER HDF    10
5  2010-09-20  WFG MNB    30

I want to merge rows with dates in period of 3 days as below:

    date        Name                    Cost        
0  2010-09-15  ABC XYZ PQR RTS DEF GHI   50
1  2010-09-18  LKJ POD WER HDF WFG MNB   40

Cost column takes mean value and strings just add as strings. I tried making date ranges in different DataFrame and then merging with conditions but I am not getting desired values.

iDrwish · Accepted Answer · 2018-04-19 12:02:41Z

4

You can use the resample function over a 3 days period after converting your date column to a datetime.

df.set_index('date', inplace=True)
df.index = pd.to_datetime(df.index)

df = df.resample('3D', kind='period', convention='start').agg(\
{'Name': lambda x: ' '.join(x), 'Cost' : 'mean'})

Output

df
Out[35]: 
                               Name  Cost
date                                     
2010-09-15  ABC XYZ PQR RTS DEF GHI    50
2010-09-18  LKJ POD WER HDF WFG MNB    40

edited Apr 19, 2018 at 12:02

answered Apr 19, 2018 at 11:22

iDrwish

3,1131 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to merge rows in Pandas Dataframe based on date range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related