Split dataframe for chunk of dates

Question

I have a Pandas dataframe with dates column as datetime objects, not strings.

   Datetime               col1   col2

1  2021-05-19 05:05:00     3      7
2  ...

I would like to split it to multiple dataframes by days.

For example currently i split it by rows with a simple num var :

split  = range(0,len(data.index),num)
results = []
for c in split:
    results.append(data.iloc[c:(c+num)])
return results

How would I split it by days ? for example if num = 10 create dataframes of 10 days each, so first will have all rows with days 1-10, the second will have 10-20, etc. (not day of the month, but absolute groups of 10 days)

user_15862569 · Answer

You can group DataFrame by date intervals using pd.Grouper:

import pandas as pd

# test data
index = pd.date_range('2021-01-01', '2021-05-01')
data = pd.DataFrame({'a': range(len(index))}, index=index)

results = [part for _, part in data.groupby(pd.Grouper(freq='10D'))]

The most important part is pd.Grouper(freq='10D'). It allows you to specify an interval for .groupby() ('10D' means '10 days'). In my case it works on index. You can specify the column name using pd.Grouper(col_name, freq='10D') or specify the level in the MultiIndex using pd.Grouper(level=name, freq='10D').

When iterating through the GroupBy object, it returns a tuple (name, group). List comprehension is used to remove the name from the iterable.

Check pd.Grouper docs for the details.

user_9307834 · Answer

I would make a new column for date then group by the date

df['date'] = df['DateTime'].apply(lambda x: x.date()) # make date
dfs = [df[df['date'] == date] for date in df['date'].unique()] # group by date

now dfs is a dataframe split by dates

dfs[0]

you can chunk them into 10

from functools import reduce
def chunks(L, n): return [L[x: x+n] for x in range(0, len(L), n)]
groups = chunks(dfs, 10) # 10 is number of dates to group
groupDfs = [reduce(lambda x,y: x.append(y), group) for group in groups]

Split dataframe for chunk of dates

2 Answers