CODEWITHSUNDEEP
pythonpandasdataframe
dim_yf_95
dim_yf_95

Reputation: 59

Python - Pandas, Split time series dataframe per week

I have a dataframe with 2 columns that are filled with timestamps along with their corresponding values. The df has around 7000 rows (2 months data) and I wanted to split them into smaller dataframes for each week. I can split them of course into days etc but I don't know about weeks. The dataframe looks like this: 

2018-08-29T00:03:09      12310                  
2018-08-29T00:08:10      21231              
2018-08-29T00:13:10      19.6                   
2018-08-29T00:18:10     19321                   
2018-08-29T00:23:10     182134                  
2018-08-29T00:28:10     172319                  
2018-08-29T00:33:10     1734                    
2018-08-29T00:38:10     1764                    
2018-08-29T00:43:10     169743                  
2018-08-29T00:48:10     16747                   
2018-08-29T00:53:10     17830

Thanks, everyone in advance!

Upvotes: 1

Views: 3453

Answers (3)

wwnde
wwnde

Reputation: 26676

Another way of doing it

df.set_index(pd.to_datetime(df['time']), inplace=True)
df['week']=df.index.week

Explanation 

 #pd.to_datetime(df['time'])- Coerces time to datetime
    #df.set_index(pd.to_datetime(df['time']), inplace=True)#Sets time as index
    #df.index.week; extracts time period from the index

enter image description here

Upvotes: 3

Mayank Porwal
Mayank Porwal

Reputation: 34046

You can do something like this:

Consider below df:

In [2712]: df                                                                                                                                                                                               
Out[2712]: 
                  Date      Value
0  2018-08-29 00:03:09  12,310.00
1  2018-08-29 00:08:10  21,231.00
2  2018-08-29 00:13:10      19.60
3  2018-08-29 00:18:10  19,321.00
4  2018-08-29 00:23:10 182,134.00
5  2018-08-29 00:28:10 172,319.00
6  2018-08-29 00:33:10   1,734.00
7  2018-08-29 00:38:10   1,764.00
8  2018-08-29 00:43:10 169,743.00
9  2018-08-29 00:48:10  16,747.00
10 2018-08-29 00:53:10  17,830.00

In [2711]: df['Date'] = pd.to_datetime(df['Date'])
In [2714]: df['week'] = df['Date'].dt.week

In [2715]: df                                                                                                                                                                                               
Out[2715]: 
                  Date      Value  week
0  2018-08-29 00:03:09  12,310.00    35
1  2018-08-29 00:08:10  21,231.00    35
2  2018-08-29 00:13:10      19.60    35
3  2018-08-29 00:18:10  19,321.00    35
4  2018-08-29 00:23:10 182,134.00    35
5  2018-08-29 00:28:10 172,319.00    35
6  2018-08-29 00:33:10   1,734.00    35
7  2018-08-29 00:38:10   1,764.00    35
8  2018-08-29 00:43:10 169,743.00    35
9  2018-08-29 00:48:10  16,747.00    35
10 2018-08-29 00:53:10  17,830.00    35

Now group the df on week.

In [2717]: grp = df.groupby('week') 

In [2721]: for x,y in grp: 
      ...:     print(x,y) 
      ...:                                                                                                                                                                                                  
35                Date      Value  week
0  2018-08-29 00:03:09  12,310.00    35
1  2018-08-29 00:08:10  21,231.00    35
2  2018-08-29 00:13:10      19.60    35
3  2018-08-29 00:18:10  19,321.00    35
4  2018-08-29 00:23:10 182,134.00    35
5  2018-08-29 00:28:10 172,319.00    35
6  2018-08-29 00:33:10   1,734.00    35
7  2018-08-29 00:38:10   1,764.00    35
8  2018-08-29 00:43:10 169,743.00    35
9  2018-08-29 00:48:10  16,747.00    35
10 2018-08-29 00:53:10  17,830.00    35

This will give you smaller dataframes for each week.

Upvotes: 4

Quang Hoang
Quang Hoang

Reputation: 150735

You can do something like this:

df['Week'] = df['timestamp'].dt.to_period('W-MON')

And you get a week column in your dataframe. Then if you want to browse through the weeks:

for w, data in df.groupby('Week'):
    print(w, data)

You can also query the week containing certain days like this:

df[df['Week']=='2018-08-29']

Upvotes: 3

Related Questions