Plot each column mean grouped by specific date range

user_10925655 0
Viewed 287

I have 7 columns of data, indexed by datetime (30 minutes frequency) starting from 2017-05-31 ending in 2018-05-25. I want to plot the mean of specific range of date (seasons). I have been trying groupby, but I can't get to group by specific range. I get wrong results if I do df.groupby(df.date.dt.month).mean().

A few lines from the dataset (date range is from 2017-05-31 to 2018-05-25)

                     50          51         56          58
date                                                                   
2017-05-31 00:00:00  200.213542  276.929198 242.879051  NaN
2017-05-31 00:30:00  200.215478  276.928229 242.879051  NaN
2017-05-31 01:00:00  200.215478  276.925324 242.878083  NaN 
2017-06-01 01:00:00  200.221288  276.944691 242.827729  NaN
2017-06-01 01:30:00  200.221288  276.944691 242.827729  NaN
2017-08-31 09:00:00  206.961886  283.374453 245.041349  184.358250
2017-08-31 09:30:00  206.966727  283.377358 245.042317  184.360187
2017-12-31 09:00:00  212.925877  287.198416 247.455413  187.175144
2017-12-31 09:30:00  212.926846  287.196480 247.465097  187.179987
2018-03-31 23:00:00  213.304498  286.933093 246.469647  186.887548
2018-03-31 23:30:00  213.308369  286.938902 246.468678  186.891422
2018-04-30 23:00:00  215.496812  288.342024 247.522230  188.104749
2018-04-30 23:30:00  215.497781  288.340086 247.520294  188.103780

I have created these variables (These are the ranges I need)

increment_rates_winter = df['2017-08-30'].mean() - df['2017-06-01'].mean()
increment_rates_spring = df['2017-11-30'].mean() - df['2017-09-01'].mean()
increment_rates_summer = df['2018-02-28'].mean() - df['2017-12-01'].mean()
increment_rates_fall = df['2018-05-24'].mean() - df['2018-03-01'].mean()

Concatenated them:

df_seasons =pd.concat([increment_rates_winter,increment_rates_spring,increment_rates_summer,increment_rates_fall],axis=1)

and after plotting, I got this:

enter image description here

However, I've been trying to get this:

enter image description here

df_seasons
Out[664]: 
      Winter    Spring    Summer       Fall
50  6.697123  6.948447 -1.961549   7.662622
51  6.428329  4.760650 -2.188402   5.927087
52  5.580953  6.667529  1.136889  12.939295
53  6.406259  2.506279 -2.105125   6.964549
54  4.332826  3.678492 -2.574769   6.569398
56  2.222032  3.359607 -2.694863   5.348258
58       NaN  1.388535 -0.035889   4.213046

The seasons in x and the means plotted for each column.

Winter = df['2017-06-01':'2017-08-30']
Spring = df['2017-09-01':'2017-11-30']
Summer = df['2017-12-01':'2018-02-28']
Fall = df['2018-03-01':'2018-05-30']

Thank you in advance!

pandas matplotlib plot python-3.x python
2 Answers

We can get a specific date range in the following way, and then you can define it however you want and take the mean 

import pandas as pd

df = pd.read_csv('test.csv')

df['date'] = pd.to_datetime(df['date'])

start_date = "2017-12-31 09:00:00"
end_date = "2018-04-30 23:00:00"

mask = (df['date'] > start_date) & (df['date'] <= end_date)

f_df = df.loc[mask]

This gives the output 

                  date           50  ...            58                                                   
8  2017-12-31 09:30:00   212.926846  ...    187.179987                                                NaN
9  2018-03-31 23:00:00   213.304498  ...    186.887548                                                NaN
10 2018-03-31 23:30:00   213.308369  ...    186.891422                                                NaN
11 2018-04-30 23:00:00   215.496812  ...    188.104749                                                NaN

Hope this helps

user_111182510

How about transpose it:

df_seasons.T.plot()

Output:

enter image description here

user_42384080
Related