Working with Pandas and XlsxWriter
Pandas write Excel files using the XlsxWriter or Openpyxl module. This can be used to read, filter, and re-arrange either small or large datasets and output them in a range of formats including Excel. The ExcelWriter() method of the pandas library creates a Excel writer object using XlsxWriter. Then the to_excel() method is used to write the dataframe to the excel.
Example 1:
Python3
# import pandas as pdimport pandas as pd# Create a Pandas dataframe from some data.df = pd.DataFrame({'Data': ['Geeks', 'For', 'geeks', 'is', 'portal', 'for', 'geeks']})# Create a Pandas Excel writer# object using XlsxWriter as the engine.writer = pd.ExcelWriter('sample.xlsx', engine='xlsxwriter')# Write a dataframe to the worksheet.df.to_excel(writer, sheet_name='Sheet1')# Close the Pandas Excel writer# object and output the Excel file.writer.save() |
Output:
Example 2: Writing to multiple dataframes.
Python3
# import pandas as pdimport pandas as pd# Create some Pandas dataframes from some data.df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})df4 = pd.DataFrame({'Data': [41, 42, 43, 44]})# Create a Pandas Excel writer object# using XlsxWriter as the engine.writer = pd.ExcelWriter('sample.xlsx', engine='xlsxwriter')# write and Positioning the dataframes in the worksheet.# Default position, cell A1.df1.to_excel(writer, sheet_name='Sheet1')df2.to_excel(writer, sheet_name='Sheet1', startcol=3)df3.to_excel(writer, sheet_name='Sheet1', startrow=6)# It is also possible to write the# dataframe without the header and index.df4.to_excel(writer, sheet_name='Sheet1', startrow=7, startcol=4, header=False, index=False)# Close the Pandas Excel writer object# and output the Excel file.writer.save() |
Output:
Example 3: Converting a Pandas dataframe with datetimes to an Excel file with a default datetime and date format using Pandas and XlsxWriter.
Python3
# import pandas library as pdimport pandas as pd# from datetime module import# datetime and date methodfrom datetime import datetime, date# Create a Pandas dataframe from some datetime data.# datetime(year,month,date,hour,minute,second)# date(year,month,date)dataframe = pd.DataFrame({ 'Date and time': [datetime(2018, 1, 11, 11, 30, 55), datetime(2018, 2, 12, 1, 20, 33), datetime(2018, 3, 13, 11, 10), datetime(2018, 4, 14, 16, 45, 35), datetime(2018, 5, 15, 12, 10, 15)], 'Dates only': [date(2018, 6, 21), date(2018, 7, 22), date(2018, 8, 23), date(2018, 9, 24), date(2018, 10, 25)], })# Create a Pandas Excel writer# object using XlsxWriter as the engine.# Also set the default datetime and date formats.# mmmm dd yyyy => month date year# month - full name, date - 2 digit, year - 4 digit# mmm d yyyy hh:mm:ss => month date year hour: minute: second# month - first 3 letters , date - 1 or 2 digit , year - 4 digit.writer_object = pd.ExcelWriter("sample.xlsx", engine='xlsxwriter', datetime_format='mmm d yyyy hh:mm:ss', date_format='mmmm dd yyyy')# Write a dataframe to the worksheet.dataframe.to_excel(writer_object, sheet_name='Sheet1')# Create xlsxwriter worksheet objectworksheet_object = writer_object.sheets['Sheet1']# set width of the B and C columnworksheet_object.set_column('B:C', 20)# Close the Pandas Excel writer# object and output the Excel file.writer_object.save() |
Output:
Example 4: Converting a Pandas dataframe to an Excel file with a user defined header format using Pandas and XlsxWriter.
Python3
# import pandas lib as pdimport pandas as pddata1 = ["Math", "Physics", "Computer", "Hindi", "English", "chemistry"]data2 = [95, 78, 80, 80, 60, 95]data3 = [90, 67, 78, 70, 63, 90]# Create a Pandas dataframe from some data.dataframe = pd.DataFrame( {'Subject': data1, 'Mid Term Exam Scores Out of 100': data2, 'End Term Exam Scores Out of 100': data3})# Create a Pandas Excel writer# object using XlsxWriter as the engine.writer_object = pd.ExcelWriter("sample.xlsx", engine='xlsxwriter')# Write a dataframe to the worksheet.# we turn off the default header# and skip one row because we want# to insert a user defined header there.dataframe.to_excel(writer_object, sheet_name='Sheet1', startrow=1, header=False)# Create xlsxwriter workbook object .workbook_object = writer_object.book# Create xlsxwriter worksheet objectworksheet_object = writer_object.sheets['Sheet1']# Create a new Format object to formats cells# in worksheets using add_format() method .# here we create a format object for header.header_format_object = workbook_object.add_format({ 'bold': True, 'italic': True, 'text_wrap': True, 'valign': 'top', 'font_color': 'green', 'border': 2})# Write the column headers with the defined format.for col_number, value in enumerate(dataframe.columns.values): worksheet_object.write(0, col_number + 1, value, header_format_object)# Close the Pandas Excel writer# object and output the Excel file.writer_object.save() |
Output:
Creating Charts using Pandas and XlsxWriter
Till now we have seen different methods for adding the data to the Excel files using the Pandas and the XlsxWriter module. Now after the data is inserted we can simply create the charts using the add_chart() method as we have seen above.
Example:
Python3
# import pandas library as pdimport pandas as pd# Create a Pandas dataframe from some data.dataframe = pd.DataFrame({ 'Subject': ["Math", "Physics", "Computer", "Hindi", "English", "chemistry"], 'Mid Exam Score': [90, 78, 60, 80, 60, 90], 'End Exam Score': [45, 39, 30, 40, 30, 60]})# Create a Pandas Excel writer# object using XlsxWriter as the engine.writer_object = pd.ExcelWriter('sample.xlsx', engine='xlsxwriter')# Write a dataframe to the worksheet.dataframe.to_excel(writer_object, sheet_name='Sheet1')# Create xlsxwriter workbook object .workbook_object = writer_object.book# Create xlsxwriter worksheet objectworksheet_object = writer_object.sheets['Sheet1']# set width of the B and C columnworksheet_object.set_column('B:C', 20)# Create a chart object that can be added# to a worksheet using add_chart() method.# here we create a column chart object .chart_object = workbook_object.add_chart({'type': 'column'})# Add a data series to a chart# using add_series method.# Configure the first series.# syntax to define ranges is :# [sheetname, first_row, first_col, last_row, last_col].chart_object.add_series({ 'name': ['Sheet1', 0, 2], 'categories': ['Sheet1', 1, 3, 6, 3], 'values': ['Sheet1', 1, 2, 6, 2],})# Configure a second series.chart_object.add_series({ 'name': ['Sheet1', 0, 1], 'categories': ['Sheet1', 1, 3, 6, 3], 'values': ['Sheet1', 1, 1, 6, 1],})# Add a chart title.chart_object.set_title({'name': 'Exam Score Distribution'})# Add x-axis labelchart_object.set_x_axis({'name': 'Subjects'})# Add y-axis labelchart_object.set_y_axis({'name': 'Marks'})# add chart to the worksheet with given# offset values at the top-left corner of# a chart is anchored to cell E2worksheet_object.insert_chart('B10', chart_object, {'x_offset': 20, 'y_offset': 5})# Close the Pandas Excel writer# object and output the Excel file.writer_object.save() |
Output:
Refer to the below articles to get detailed information about working with Xlsxwriter and Pandas.
Working with XlsxWriter module – Python
XlsxWriter is a Python module that provides various methods to work with Excel using Python. It can be used to read, write, applying formulas. Also, it supports features such as formatting, images, charts, page setup, auto filters, conditional formatting, and many others.
This tutorial aims at providing knowledge about the XlsxWriter module from basics to advance with the help well explained examples and concepts.