plot average value every 30 minutes

user_15378421 0
Viewed 81

I am trying to plot the average value of "Spread" every 30 minutes. My data look like this

                        Bid      Ask    Spread     relative_spread       relative_quoted_half_spread
Date
2021-02-01 00:01:02  1.21291  1.21336  0.00045         0.000371                     0.000185
2021-02-01 00:01:21  1.21290  1.21336  0.00046         0.000379                     0.000190
2021-02-01 00:01:31  1.21287  1.21336  0.00049         0.000404                     0.000202
2021-02-01 00:01:32  1.21290  1.21336  0.00046         0.000379                     0.000190
2021-02-01 00:02:08  1.21290  1.21338  0.00048         0.000396                     0.000198

My first attempt is

plt.figure('Average spread over time')
minutes_dfs = df.resample('30M').mean()
print(Date)

ax3 = minutes_dfs.plot(x='Date', y='Spread',ls='-',color='k')
ax3.set_title('Spread introday every 30mins')
ax3.set_ylabel('Spread change')
ax3.legend(loc='best')
ax3.grid(True)
plt.show()

I got an error message as "Exception has occurred: KeyError 'Date'" However, I have tried this before I plot

print(Date)

I got the Date column results like this

0       2021-02-01 00:01:02
1       2021-02-01 00:01:21
2       2021-02-01 00:01:31
3       2021-02-01 00:01:32
4       2021-02-01 00:02:08

My second try is

plt.figure('Average spread over time')
minutes_dfs = df.groupby(pd.Grouper(key = 'Date', freq='30M')).mean()
print(Date)

ax3 = minutes_dfs.plot(x='Date', y='Spread',ls='-',color='k')
ax3.set_title('Spread introday every 30mins')
ax3.set_ylabel('Spread change')
ax3.legend(loc='best')
ax3.grid(True)
plt.show()

and then I got an error message like this 'The grouper name Date is not found'

Does anyone know where did I go wrong? Thanks in advance!

pandas plot date datetime python
1 Answers

Because Date is index need remove x:

x3 = minutes_dfs.plot(y='Spread',ls='-',color='k')

Or add reset_index:

x3 = minutes_dfs.reset_index().plot(x='Date', y='Spread',ls='-',color='k')

In second solution is removed key parameter, because DatetimeIndex:

minutes_dfs = df.groupby(pd.Grouper(freq='30M')).mean()

x3 = minutes_dfs.plot(y='Spread',ls='-',color='k')
user_29010020
Related