CODEWITHSUNDEEP
pythonpandas
Hello lad
Hello lad

Reputation: 18790

How to group time series data by Monday, Tuesday .. ? pandas

I have time series pandas DataFrame looks like

              value
12-01-2014    1
13-01-2014    2
....
01-05-2014    5

I want to group them into

1 (Monday, Tuesday, ..., Saturday, Sonday)
2 (Workday, Weekend)

How could I achieve that in pandas ?

Upvotes: 2

Views: 1255

Answers (1)

Liam Foley
Liam Foley

Reputation: 7822

Make sure your dates column is a datetime object and use the datetime attributes:

df = pd.DataFrame({'dates':['1/1/15','1/2/15','1/3/15','1/4/15','1/5/15','1/6/15',
                    '1/7/15','1/8/15','1/9/15','1/10/15','1/11/15','1/12/15'],
                    'values':[1,2,3,4,5,1,2,3,1,2,3,4]})
df['dates'] = pd.to_datetime(df['dates'])                    
df['dayofweek'] = df['dates'].apply(lambda x: x.dayofweek)

        dates  values  dayofweek
0  2015-01-01       1          3
1  2015-01-02       2          4
2  2015-01-03       3          5
3  2015-01-04       4          6
4  2015-01-05       5          0
5  2015-01-06       1          1
6  2015-01-07       2          2
7  2015-01-08       3          3
8  2015-01-09       1          4
9  2015-01-10       2          5
10 2015-01-11       3          6
11 2015-01-12       4          0

df.groupby(df['dates'].apply(lambda x: x.dayofweek)).sum()

df.groupby(df['dates'].apply(lambda x: 0 if x.dayofweek in [5,6] else 1)).sum()

Output:

In [1]: df.groupby(df['dates'].apply(lambda x: x.dayofweek)).sum()
Out[1]: 
       values
dates        
0           9
1           1
2           2
3           4
4           3
5           5
6           7

In [2]: df.groupby(df['dates'].apply(lambda x: 0 if x.dayofweek in [5,6] else 1)).sum()
Out[2]: 
       values
dates        
0          12
1          19

Upvotes: 2

Related Questions