CODEWITHSUNDEEP
pythonindexingpandastime-series
Mannaggia
Mannaggia

Reputation: 4829

How to split a pandas dataframe or series by day (possibly using an iterator)

I have a long time series, eg. 

import pandas as pd
index=pd.date_range(start='2012-11-05', end='2012-11-10', freq='1S').tz_localize('Europe/Berlin')
df=pd.DataFrame(range(len(index)), index=index, columns=['Number'])

Now I want to extract all sub-DataFrames for each day, to get the following output:

df_2012-11-05: data frame with all data referring to day 2012-11-05
df_2012-11-06: etc.
df_2012-11-07
df_2012-11-08
df_2012-11-09
df_2012-11-10

What is the most effective way to do this avoiding to check if the index.date==give_date which is very slow. Also, the user does not know a priory the range of days in the frame.

Any hint do do this with an iterator?

My current solution is this, but it is not so elegant and has two issues defined below:

time_zone='Europe/Berlin'
# find all days
a=np.unique(df.index.date) # this can take a lot of time
a.sort()
results=[]
for i in range(len(a)-1):
    day_now=pd.Timestamp(a[i]).tz_localize(time_zone)
    day_next=pd.Timestamp(a[i+1]).tz_localize(time_zone)
    results.append(df[day_now:day_next]) # how to select if I do not want day_next included?

# last day
results.append(df[day_next:])

This approach has the following problems:

Upvotes: 24

Views: 25228

Answers (2)

Peque
Peque

Reputation: 14831

If you want to group by date (AKA: year+month+day), then use df.index.date:

result = [group[1] for group in df.groupby(df.index.date)]

As df.index.day will use the day of the month (i.e.: from 1 to 31) for grouping, which could result in undesirable behavior if the input dataframe dates extend to multiple months.

Upvotes: 46

Woody Pride
Woody Pride

Reputation: 13965

Perhaps groupby?

DFList = []
for group in df.groupby(df.index.day):
    DFList.append(group[1])

Should give you a list of data frames where each data frame is one day of data.

Or in one line:

DFList = [group[1] for group in df.groupby(df.index.day)]

Gotta love python!

Upvotes: 24

Related Questions