CODEWITHSUNDEEP
pythonexcelpandasdataframexlsxwriter
Johann Spies
Johann Spies

Reputation: 33

How to set x-axis data plotting a pandas dataframe using xlsxwriter

I am trying to create a plot in excel using xlsxwriter with python3 and pandas. How do I get xlsxwriter to use the pubyear column for the x_axis values?

I can plot successfully using matplotlib, but I am required to produce excel-charts.

This code 

n [248]: df1.describe
Out[248]: 
<bound method NDFrame.describe of          africa
pubyear        
2018      57371
2017      70838
2016      66250
2015      58572
2014      52453
2013      46733
2012      42521
2011      38851
2010      33463
2009      29603
2008      25947
2007      22573
2006      19188
2005      16701>

writer = pd.ExcelWriter('/tmp/pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
chart = workbook.add_chart({'type': 'column'})

# Configure the series of the chart from the dataframe data.
chart.add_series({'values': '=Sheet1!$D$1:$D$15'})
chart.set_x_axis({'name': 'Pubyear', 'min': '=Sheet1!$A$2', 
                  'max':  '=Sheet1!$A$14', 
                  "date_axis" : "=Sheet1!$A$2:$A15$" })
chart.set_y_axis({'name': 'Output'})
worksheet.insert_chart('I2', chart)

writer.save()

produced an file containing this data in the spreadsheet where pubyear is in column A and africa in column B:

  A       B
pubyear africa
2018    57371
2017    70838
2016    66250
2015    58572
2014    52453
2013    46733
2012    42521
2011    38851
2010    33463
2009    29603
2008    25947
2007    22573
2006    19188
2005    16701

The plot shows a bar chart with x-axis: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

I want pubyear as in column A to be the x-labels.

Upvotes: 1

Views: 1525

Answers (1)

Bobbylank
Bobbylank

Reputation: 1946

When you add the series you add the categories (pubyear) at the same time

chart.add_series({'categories':'=Sheet1!$A$2:$A$15'
                   'values': '=Sheet1!$B$2:$B$15'})

Upvotes: 2

Related Questions