How to generate a Graph for Excel data using Python Pandas?

We have written an article on How to add borders in excel using Python Pandas? Very similar to that is this article where we will be using mostly the same kind of packages of python that we have used in the other one.

If you are new to python (Python can help you develop websites!), you can read this tutorial on how to install python from the below links for each of your preferred operating systems.

Operating System	URL to install Python
Windows	https://www.tutorialspoint.com/how-to-install-python-in-windows
Ubuntu	https://phoenixnap.com/kb/how-to-install-python-3-ubuntu
OSX	https://docs.python-guide.org/starting/install3/osx/

Once we are through with installing python on our respective operating systems, it is time for us to go ahead and install pip. Pip will mostly be installed along with python. If not please follow this link in one of our other articles on how to install the same.

By installing pip, we enable the tool to install packages. In this article, we need to install pandas, an essential tool to work with beautifying excels, be it with borders that we have done in this article or charts or anything similar.

Installing pandas using pip is just one bash command. All you have to do is execute the below command.

pip install pandas

After installing pandas, start writing your program to generate graphs in an excel sheet. The first thing that goes into your program is to import pandas. So, we will start the program by writing the following line of code, which is pretty self-explanatory.

Please follow the link to know How to add border in excel using Python Pandas?

import pandas as pan

We are writing this python program to display the chart. As a part of the input for the chart, let us create a list and store some data that will be helpful for us to build the bar chart, further down the article.

data_for_chart = [20, 10, 15, 25, 10, 20, 60]

Once we have created an array list, it has to be added to a data frame using pandas. This will enable us to further export this data into an excel sheet. This simple line of code looks as below:

data_frame = pan.DataFrame(data_for_chart)

Once we have assigned the array to a data frame, the next step is to create the excel file, along with the sheet name, that will be waiting for us to populate the data along with the chart that has to be created.

Let us write both the lines of code below.

chart_excel = 'chart.xlsx'
chart_sheet = 'bar_code'

These two lines are not doing much but assigning the name of the excel file and the sheet.

writer = pan.ExcelWriter(chart_excel , engine='xlsxwriter')
data_frame.to_excel(writer, sheet_name=bar_code)

Using the xlsxwriter engine, we are programmatically creating the pandas excel writer enabling the write functionality to the excel file.

In the next step, we are going to be accessing the XlsxWriter workbook and worksheet objects from the data frame.

workbook = writer.book
worksheet = writer.sheets[bar_code]

Now is the time to create the chart object.

chart = workbook.add_chart({'type': 'column'})

By configuring the series of the chart from the data frame, you get the bar graph. That is what we are going to be doing in the next step.

If you observe here, we have mentioned the cell details of the data frame from where it imports the data.

chart.add_series({
    'values':     '=bar_code!$B$2:$B$8',
    'gap':        2,
})

Set the axes of the bar chart in the next step. In our case, the bar chart grows against the y axis.

chart.set_y_axis({'major_gridlines': {'visible': False}})

To insert the chart that you just created into excel, all you have to do is, write the below code.

worksheet.insert_chart('D2', chart)

The final code to save the excel to your hard disk is as below:

writer.save()

import pandas as pan

data_for_chart = [20, 10, 15, 25, 10, 20, 60]

df = pan.DataFrame(data_for_chart)

chart_excel = 'chart.xlsx'
chart_sheet = 'bar_code'

writer = pan.ExcelWriter(chart_excel , engine='xlsxwriter')
df.to_excel(writer, sheet_name=chart_sheet)

workbook = writer.book
worksheet = writer.sheets[chart_sheet]

chart = workbook.add_chart({'type': 'column'})

chart.add_series({
    'values':     '=bar_code!$B$2:$B$8',
    'gap':        2,
})

chart.set_y_axis({'major_gridlines': {'visible': False}})

worksheet.insert_chart('D2', chart)

writer.save()