0

I have this code below, it produces my data frame exactly how i want it, but i can't seem to graph it via a grouped bar chart,

I'd like to have the department on the X axis and on the Y axis have completed with the remaining information on top

enter image description here

import pandas as pd
import matplotlib

data = pd.read_excel('audit.xls', skiprows=2, index_col = 'Employee Department')
data.rename(columns = {'Curriculum Name':'Curriculum','Organization Employee Number':'Employee_Number', 'Employee Name': 'Employee','Employee Email':'Email', 'Employee Status':'Status', 'Date Assigned':'Assigned','Completion Date':'Completed'}, inplace=True)
data.drop(['Employee_Number', 'Employee','Assigned', 'Status', 'Manager Name', 'Manager Email', 'Completion Status','Unnamed: 1', 'Unnamed: 5', 'Unnamed: 6'], axis=1, inplace=True)

new_data = data.query('Curriculum ==["CARB Security Training","OIS Support Training","Legal EO Training"]')
new_data2 = new_data.groupby('Employee Department').count().eval('Remaining = Email - Completed', inplace=False)

new_data2

I assume i need to convert it to a pivot table somehow since that's how it is in excel

Vico
  • 579
  • 3
  • 13
davidjbeiler
  • 133
  • 2
  • 15
  • Please provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – ako Oct 24 '17 at 20:14
  • Does it need to be visualized with matplotlib or is a webframework possible? If so: I would say try to put it into [ChartJS](http://chartjs.org) or [C3](http://c3js.org) which is a wrapper for [D3](http://d3js.org). In both cases you would need to get them into a JSON format though... – Max Kah Oct 24 '17 at 20:08
  • what type of example? – davidjbeiler Oct 24 '17 at 20:29
  • 1
    Welcome to SO. Unfortunately this isn't a tutorial service or a code writing service. Please take the time to read [ask] and the links it contains. Usually I start by looking through the examples in the docs and for matplotlib, I try to find something in [the gallery](https://matplotlib.org/gallery/index.html) that has features I want then look at the code that created it. – wwii Oct 24 '17 at 20:34

1 Answers1

1

Have you tried something like this: new_data2[['Completed','Remaining']].plot.bar(stacked=True)

The following example works for me:

df = pd.DataFrame(np.arange(1,10).reshape(3,3), columns=['Email', 'Completed', 'Remaining'], index=['A', 'B', 'C'])
df[['Completed', 'Remaining']].plot.bar(stacked=True)

enter image description here

Longwen Ou
  • 859
  • 4
  • 4
  • the new_data2 one didn't work, got an error, max_open_warning, runtimewarning – davidjbeiler Oct 24 '17 at 20:27
  • `max_open_warning` is given usually because you are generating too many figures. How many figures do your code generate? – Longwen Ou Oct 24 '17 at 20:34
  • what do you mean? I only have about 1600 lines in the excel document – davidjbeiler Oct 24 '17 at 20:38
  • @davidjbeiler I mean you are generating too many matplotlib figures. check [this](https://stackoverflow.com/questions/21884271/warning-about-too-many-open-figures) out, and [this](https://stackoverflow.com/questions/27476642/matplotlib-get-rid-of-max-open-warning-output?noredirect=1&lq=1). I think you should restart your kernel if you are using jupiter notebook to clear the memory. – Longwen Ou Oct 24 '17 at 20:43
  • Thanks, i got my output to json fine, but how do i assign it to the axis with chartjs? – davidjbeiler Oct 24 '17 at 20:46
  • @davidjbeiler This is only for visualization in matplotlib. Unfortunately I have not used chartjs. You can try to find some tutorials of chartjs elsewhere. – Longwen Ou Oct 24 '17 at 20:50