Imagine you’re a space explorer, and data is your star map. It’s bulky, confusing, and absolutely essential. After thorough filtering and analysis, this data needs to be organized and saved in an easy-to-read format. And here comes our hero — exporting data to Excel. It lets you share your findings with colleagues or prepare reports for that important meeting. In a world where data is the new oil, a skill like creating reports in just a few lines of code can be worth a million.
1. Key Aspects of Data Export
Exporting data to Excel isn’t just converting from one format to another. It’s the ability to set up reports so that they're user-friendly and easy to interpret. As one programmer friend of mine used to say, “Python can work magic, but even Harry Potter needs a wand.” In our case, the wand is the to_excel method from the pandas library.
Basics of to_excel
Let’s start with a basic example of exporting data from a DataFrame to an Excel file. Suppose we have a DataFrame with records of marathon participants:
import pandas as pd
# Creating a DataFrame
data = {
'Name': ['Anna', 'Boris', 'Victor', 'Galina'],
'Age': [29, 34, 22, 28],
'City': ['Moscow', 'Saint Petersburg', 'Kazan', 'Novosibirsk'],
'Finish Time': ['03:15:30', '03:45:10', '03:25:45', '03:50:05']
}
df = pd.DataFrame(data)
# Export DataFrame to Excel
df.to_excel('marathon_participants.xlsx', index=False)
In this example, we created a simple DataFrame and exported it to the file marathon_participants.xlsx. Notice that we set the parameter index=False to avoid exporting DataFrame indexes to Excel, if they’re not needed.
Adding Format and Styles
Moving on to the next level — adding some style. Who said data can’t look good? With the pandas and openpyxl libraries, you can easily format tables and add styles.
import pandas as pd
from openpyxl import Workbook
# Creating a DataFrame
data = {
'Name': ['Anna', 'Boris', 'Victor', 'Galina'],
'Age': [29, 34, 22, 28],
'City': ['Moscow', 'Saint Petersburg', 'Kazan', 'Novosibirsk'],
'Finish Time': ['03:15:30', '03:45:10', '03:25:45', '03:50:05']
}
df = pd.DataFrame(data)
# Writing to Excel with formatting
with pd.ExcelWriter('styled_marathon.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, index=False, sheet_name='Participants')
worksheet = writer.sheets['Participants']
for col in worksheet.columns:
max_length = 0
column = col[0].column_letter # get column letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = (max_length + 2) * 1.2
worksheet.column_dimensions[column].width = adjusted_width
In this example, not only did we export data, but we also adjusted column widths to match the content, so it looks neater in Excel. We used openpyxl to access the sheet and apply styling.
Working with Multiple Sheets
Creating reports with multiple sheets might sound like a task for Superman, but it’s actually pretty simple. Imagine you want to keep data from different annual competitions on separate sheets.
import pandas as pd
# Data for different years
data_2022 = {
'Name': ['Dmitriy', 'Elena'],
'Age': [30, 29],
'City': ['Omsk', 'Vladivostok'],
'Finish Time': ['03:20:05', '03:35:40']
}
data_2023 = {
'Name': ['Igor', 'Katerina'],
'Age': [31, 27],
'City': ['Chelyabinsk', 'Ekaterinburg'],
'Finish Time': ['03:29:10', '03:40:20']
}
df_2022 = pd.DataFrame(data_2022)
df_2023 = pd.DataFrame(data_2023)
# Writing data to different sheets
with pd.ExcelWriter('marathon_data.xlsx') as writer:
df_2022.to_excel(writer, sheet_name='2022', index=False)
df_2023.to_excel(writer, sheet_name='2023', index=False)
This code creates the file marathon_data.xlsx with two sheets containing data from different years. It’s super handy for separating data by years, projects, or any other categories.
2. There’s Always Room for Improvement
Try to think about how automating export can be integrated into more complex workflows in your project. Maybe you’ll want to add automatic chart creation after export? Or integrate reports with a web interface for easier access?
In any case, exporting data to Excel is just the beginning of creating beautiful and informative reports that can make you the star of analytics at work or in school!
Now you have the tools to automate data export, and you know how to make your Excel reports more readable and presentable. So go ahead, and let your data work for you!
GO TO FULL VERSION