CODEWITHSUNDEEP
pythonpython-3.xpandaspandas-groupby
Himanshu Sharma
Himanshu Sharma

Reputation: 71

How to merge rows in Pandas Dataframe based on date range

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.

Upvotes: 1

Views: 1150

Answers (1)

iDrwish
iDrwish

Reputation: 3103

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

Upvotes: 4

Related Questions