Working with Pandas and XlsxWriter

Introduction

Excel files are widely used for storing tabular data. In Python, the pandas library provides a convenient way to read Excel files and convert them into DataFrame objects, which are powerful structures for data manipulation and analysis. The XlsxWriter module allows for advanced formatting and customization of Excel files.

Basic Operations

To get started, let's explore some basic operations:

1. Converting a Pandas DataFrame to an Excel File

import pandas as pd

df = pd.DataFrame({'Data': ['Geeks', 'For', 'geeks', 'is', 'portal', 'for', 'geeks']})

writer = pd.ExcelWriter('pandasEx.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

This code creates a DataFrame and writes it to an Excel file named pandasEx.xlsx using the XlsxWriter engine.

2. Writing Multiple DataFrames to Different Worksheets

df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})

writer = pd.ExcelWriter('pandas_multiple.xlsx', engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
df3.to_excel(writer, sheet_name='Sheet3')
writer.save()

This code writes three DataFrames to three different worksheets within the same Excel file.

3. Positioning DataFrames in a Worksheet

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]})

writer = pd.ExcelWriter('pandas_positioning.xlsx', engine='xlsxwriter')
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)
df4.to_excel(writer, sheet_name='Sheet1', startrow=7, startcol=4, header=False, index=False)
writer.save()

This code demonstrates how to position DataFrames at specific locations within a worksheet.

Formatting and Customization

Advanced formatting and customization can be achieved using the XlsxWriter module:

1. Formatting Cells

import pandas as pd

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

writer = pd.ExcelWriter('pandas_formatted.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets['Sheet1']

format1 = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
worksheet.conditional_format('B2:B8', {'type': '3_color_scale', 'format': format1})

writer.save()

This code applies a conditional format to the cells in column B, changing their background color based on the cell value.

2. Adding a Chart

import pandas as pd

df = pd.DataFrame({'Subject': ['Math', 'Physics', 'Computer', 'Hindi', 'English', 'Chemistry'],
                   'Mid Exam Score': [95, 78, 80, 80, 60, 95],
                   'End Exam Score': [90, 67, 78, 70, 63, 90]})

writer = pd.ExcelWriter('pandas_chart.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets['Sheet1']

chart = workbook.add_chart({'type': 'column'})
chart.add_series({'name': 'Mid Exam Score', 'categories': 'Sheet1!$A$2:$A$7', 'values': 'Sheet1!$B$2:$B$7'})
chart.add_series({'name': 'End Exam Score', 'categories': 'Sheet1!$A$2:$A$7', 'values': 'Sheet1!$C$2:$C$7'})

chart.set_title({'name': 'Exam Score Distribution'})
chart.set_x_axis({'name': 'Subjects'})
chart.set_y_axis({'name': 'Marks'})

worksheet.insert_chart('E2', chart)

writer.save()

This code adds a column chart to the worksheet, visualizing the exam scores for each subject.

Conclusion

By combining the power of Pandas for data manipulation with the advanced formatting capabilities of XlsxWriter, you can create well-structured and visually appealing Excel reports. This integration enhances your ability to present data effectively, making it easier to analyze and share insights.