Key Error while creating Stacked Bar Graph in Pandas

I am trying to make a folded table with bokeh. I keep getting KeyError: '1'

but can't figure out why. My pivot_table looks like this:

pivot_table.head(3)
Out[23]: 
Month                      1   2   3   4   5   6   7   8   9   10  11  12
CompanyName                                                              
Company1   11   3   2   3   5   7   3   6   8   3   5   8
Company2   3   1   2  18   3   4   5   4   5   5   3   2
Company3   2   6   1   3   2   0   5   6   4   8   4   7

      

Here is my code:

from collections import OrderedDict
import pandas as pd
from bokeh.charts import Bar, output_file, show
import datetime as datetime


df = pd.read_csv('MYDATA.csv', usecols=[1, 16, 18]) #One is CompanyName, 16 is recvd_dttm, 18 is machinetype

# filter by countries with at least one medal and sort
df['recvd_dttm'] = pd.to_datetime(df['recvd_dttm'])

#Only retrieve data before now (ignore typos that are future dates)
mask = df['recvd_dttm'] <= datetime.datetime.now()
df = df.loc[mask]
# get first and last datetime for final week of data

range_max = df['recvd_dttm'].max()
range_min = range_max - datetime.timedelta(days=365)

# take slice with final week of data
df = df[(df['recvd_dttm'] >= range_min) & 
               (df['recvd_dttm'] <= range_max)]



df = df.set_index('recvd_dttm')
df.index = pd.to_datetime(df.index, format='%m/%d/%Y %H:%M')

result = df.groupby([lambda idx: idx.month, 'CompanyName']).agg(len).reset_index()
result.columns = ['Month', 'CompanyName', 'NumberCalls']
pivot_table = result.pivot(index='Month', columns='CompanyName', values='NumberCalls').fillna(0)
s = pivot_table.sum().sort(ascending=False,inplace=False)
pivot_table = pivot_table.ix[:,s.index[:40]]
pivot_table = pivot_table.transpose()



pivot_table = pivot_table.reset_index()
pivot_table['CompanyName'] = [str(x) for x in pivot_table['CompanyName']]
Companies = list(pivot_table['CompanyName'])
months = ["1","2","3","4","5","6","7","8","9","10","11","12"]
pivot_table = pivot_table.set_index('CompanyName')
pivot_table.to_csv('pivot_table.csv')




# get the months
Jan = pivot_table['1'].astype(float).values
Feb = pivot_table['2'].astype(float).values
Mar = pivot_table['3'].astype(float).values
Apr = pivot_table['4'].astype(float).values
May = pivot_table['5'].astype(float).values
Jun = pivot_table['6'].astype(float).values
Jul = pivot_table['7'].astype(float).values
Aug = pivot_table['8'].astype(float).values
Sep = pivot_table['9'].astype(float).values
Oct = pivot_table['10'].astype(float).values
Nov = pivot_table['11'].astype(float).values
Dec = pivot_table['12'].astype(float).values
# build a dict containing the grouped data
months = OrderedDict(Jan=Jan, Feb=Feb, Mar=Mar, Apr=Apr, May=May,Jun=Jun,Jul=Jul,Aug=Aug,Sep=Sep,Oct=Oct,Nov=Nov,Dec=Dec)


# any of the following commented are also alid Bar inputs
#medals = pd.DataFrame(medals)
#medals = list(medals.values())

output_file("stacked_bar.html")

bar = Bar(months, Companies, title="Stacked bars", stacked=True)

show(bar)

      

I could do it fine in matplotlib, but I like the hovertool function in bokeh. if i did import matplotlib.pyplot as plt

and add these lines i get a line-by-line graph.

plot = pivot_table.plot(kind='bar',stacked=True)
show(plot)

      

I suppose the key error comes from when I get the months for the OrderedDict? I don't know how to fix this. Basically I'm trying to get away from this example: http://bokeh.pydata.org/en/latest/docs/gallery/stacked_bar_chart.html

+3


source to share


1 answer


It seems if I use Jan = pivot_table[1].astype(float).values

instead Jan = pivot_table['1'].astype(float).values

it works



+1


source







All Articles