Pandas: bar plot with multiIndex dataframe

Question

I have a pandas DataFrame with a TIMESTAMP column (not the index), and the timestamp format is as follows:

2015-03-31 22:56:45.510

I also have columns called CLASS and AXLES. I would like to compute the count of records for each month separately for each unique value of AXLES (AXLES can take an integer value between 3-12).

I came up with a combination of resample and groupby:

resamp = dfWIM.set_index('TIMESTAMP').groupby('AXLES').resample('M', how='count').CLASS

This seems to give me a multiIndex dataframe object, as shown below.

In [72]: resamp

Out [72]:

AXLES  TIMESTAMP 
3      2014-07-31      5517
       2014-08-31     31553
       2014-09-30     42816
       2014-10-31     49308
       2014-11-30     44168
       2014-12-31     45518
       2015-01-31     54782
       2015-02-28     52166
       2015-03-31     47929
4      2014-07-31      3147
       2014-08-31     24810
       2014-09-30     39075
       2014-10-31     46857
       2014-11-30     42651
       2014-12-31     48282
       2015-01-31     42708
       2015-02-28     43904
       2015-03-31     50033

From here, how can I access different components of this multiIndex object to create a bar plot for the following conditions?

show data when AXLES = 3
show x ticks in the Month - Year format (no days, hours, minutes etc.)

Thanks!

EDIT: Following code gives me the plot, but I could not change the xtick formatting to MM-YY.

resamp[3].plot(kind='bar')

EDIT 2 below is a code snippet that generates a small sample of the data similar to what I have:

dftest = {'TIMESTAMP':['2014-08-31','2014-09-30','2014-10-31'], 'AXLES':[3, 3, 3], 'CLASS':[5,6,7]}
dfTest = pd.DataFrame(dftest)
dfTest.TIMESTAMP = pd.to_datetime(pd.Series(dfTest.TIMESTAMP))
resamp = dfTest.set_index('TIMESTAMP').groupby('AXLES').resample('M', how='count').CLASS
resamp[3].plot(kind='bar')

EDIT 3: Here below is the solution:

A.Plot the whole resampled dataframe (based on @Ako 's suggestion):

df = resamp.unstack(0)
df.index = [ts.strftime('%b 20%y') for ts in df.index]
df.plot(kind='bar', rot=0)

B.Plot an individual index from the resampled dataframe (based on @Alexander 's suggestion):

df = resamp[3]
df.index = [ts.strftime('%b 20%y') for ts in df.index]
df.plot(kind='bar', rot=0)

Try resamp.unstack(0).plot(kind='bar'). That turns axles to series plotted separately. — ako
– ako, Commented Nov 11, 2015 at 2:11
Thanks. That comes handy when showing all data in one plot. Is there a way to change the format of xtick labels from 2015-03-31 00:00:00 to March 2015. No matter what I tried I could not change the format. — marillion
– marillion, Commented Nov 11, 2015 at 2:38

unutbu · Accepted Answer · 2015-11-11 21:36:55Z

You could generate and set the labels explicitly using ax.xaxis.set_major_formatter with a ticker.FixedFormatter. This will allow you to keep your DataFrame's MultiIndex with timestamp values, while displaying the timestamps in the desired %m-%Y format:

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import matplotlib.ticker as ticker

dftest = {'TIMESTAMP':['2014-08-31','2014-09-30','2014-10-31'], 'AXLES':[3, 3, 3], 'CLASS':[5,6,7]}
dfTest = pd.DataFrame(dftest)
dfTest.TIMESTAMP = pd.to_datetime(pd.Series(dfTest.TIMESTAMP))
resamp = dfTest.set_index('TIMESTAMP').groupby('AXLES').resample('M', how='count').CLASS

ax = resamp[3].plot(kind='bar')
ticklabels = [timestamp.strftime('%m-%Y') for axle, timestamp in resamp.index]
ax.xaxis.set_major_formatter(ticker.FuncFormatter(lambda x, pos: ticklabels[int(x)]))
plt.gcf().autofmt_xdate()

plt.show()

yields

Alexander · Accepted Answer · 2015-11-11 18:19:23Z

1

The following should work, but it is difficult to test without some data.

Start by resetting your index to get access to the TIMESTAMP column. Then use strftime to format it to your desired text representation (e.g. mm-yy). Finally, reset the index back to AXLES and TIMESTAMP.

df = resamp.reset_index()
df['TIMESTAMP'] = [ts.strftime('%m-%y') for ts in df.TIMESTAMP]
df.set_index(['AXLES', 'TIMESTAMP'], inplace=True)
>>> df.xs(3, level=0).plot(kind='bar')

edited Nov 11, 2015 at 18:19

answered Nov 11, 2015 at 5:15

Alexander

111k32 gold badges212 silver badges208 bronze badges

Collectives™ on Stack Overflow

Pandas: bar plot with multiIndex dataframe

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related