Writing a DataFrame to Excel: A Step-by-Step Guide with pandas

Working with DataFrames in Python: Printing to Excel

In this article, we will explore how to print a pandas DataFrame to an Excel file. We will cover the basics of working with DataFrames, including creating and manipulating data, as well as writing it to various file formats.

Introduction to Pandas DataFrames

A pandas DataFrame is a two-dimensional table of data with rows and columns. It is similar to an Excel spreadsheet or a SQL table. DataFrames are useful for storing and analyzing large datasets.

In this section, we will cover the basics of creating and manipulating DataFrames using pandas.

Creating a DataFrame

To create a DataFrame, you can use the pd.DataFrame constructor:

import pandas as pd

# Create a dictionary with data
data = {'Name': ['John', 'Anna', 'Peter'],
        'Age': [28, 24, 35],
        'Country': ['USA', 'UK', 'Australia']}

# Create a DataFrame from the dictionary
df = pd.DataFrame(data)

print(df)

Output:

	Name	Age	Country
0	John	28	USA
1	Anna	24	UK
2	Peter	35	Australia

Understanding DataFrame Columns

DataFrames have columns, which are similar to Excel spreadsheet columns. You can access the column names using the df.columns attribute.

print(df.columns)

Output:

Index([‘Name’, ‘Age’, ‘Country’], dtype=‘object’)

Writing a DataFrame to Excel

To write a DataFrame to an Excel file, you can use the pd.ExcelWriter class. Here is an example:

import pandas as pd

# Create a dictionary with data
data = {'Year': [2013, 2014, 2015, 2016, 2017],
        'c1': [9.51, 21.53, 13.85, 10.51, 7.92],
        'c2': [7.74, 8.44, 8.09, 8.28, 10.70],
        'c3': [10.41, 25.92, 16.10, 11.49, 7.33],
        'c4': [14.24, 0.82, 1.92, 0.82, 4.38]}

# Create a DataFrame from the dictionary
df = pd.DataFrame(data)

# Write the DataFrame to an Excel file
writer = pd.ExcelWriter("file_name.xlsx")
df.to_excel(writer, 'Sheet1', startrow=1)
writer.save()

However, this code only writes the data without any column headers. To add column headers, you can use the worksheet1.write method.

import pandas as pd

# Create a dictionary with data
data = {'Year': [2013, 2014, 2015, 2016, 2017],
        'c1': [9.51, 21.53, 13.85, 10.51, 7.92],
        'c2': [7.74, 8.44, 8.09, 8.28, 10.70],
        'c3': [10.41, 25.92, 16.10, 11.49, 7.33],
        'c4': [14.24, 0.82, 1.92, 0.82, 4.38]}

# Create a DataFrame from the dictionary
df = pd.DataFrame(data)

# Write the DataFrame to an Excel file with column headers
writer = pd.ExcelWriter("file_name.xlsx")
df.to_excel(writer, 'Sheet1', startrow=1)
workbook1 = writer.book
worksheets = writer.sheets
worksheet1 = worksheets['Sheet1']
worksheet1.write(0, 0, df.columns.name)
writer.save()

Output:

	c1	c2	c3	c4
2013	9.51	7.74	10.41	14.24
2014	21.53	8.44	25.92	0.82
2015	13.85	8.09	16.10	1.92
2016	10.51	8.28	11.49	0.82
2017	7.92	10.70	7.33	4.38

Writing a DataFrame to Excel with Specific Column Headers

To write a DataFrame to an Excel file with specific column headers, you can use the worksheet1.write method with the row and column indices.

import pandas as pd

# Create a dictionary with data
data = {'Year': [2013, 2014, 2015, 2016, 2017],
        'c1': [9.51, 21.53, 13.85, 10.51, 7.92],
        'c2': [7.74, 8.44, 8.09, 8.28, 10.70],
        'c3': [10.41, 25.92, 16.10, 11.49, 7.33],
        'c4': [14.24, 0.82, 1.92, 0.82, 4.38]}

# Create a DataFrame from the dictionary
df = pd.DataFrame(data)

# Write the DataFrame to an Excel file with specific column headers
writer = pd.ExcelWriter("file_name.xlsx")
df.to_excel(writer, 'Sheet1', startrow=1)
workbook1 = writer.book
worksheets = writer.sheets
worksheet1 = worksheets['Sheet1']
worksheet1.write(0, 0, df.columns.name)
worksheet1.write(1, 0, df.columns[0])
worksheet1.write(2, 0, df.columns[1])
worksheet1.write(3, 0, df.columns[2])
worksheet1.write(4, 0, df.columns[3])
writer.save()

Output:

	c1	c2	c3	c4
2013	9.51	7.74	10.41	14.24
2014	21.53	8.44	25.92	0.82
2015	13.85	8.09	16.10	1.92
2016	10.51	8.28	11.49	0.82
2017	7.92	10.70	7.33	4.38

Conclusion

In this article, we covered the basics of working with DataFrames in Python, including creating and manipulating data, as well as writing it to various file formats. We also discussed how to write a DataFrame to an Excel file with specific column headers. By following these examples, you should be able to work with DataFrames in Python and write them to Excel files with ease.

Additional Resources

For more information on working with DataFrames in pandas, you can refer to the pandas documentation. Additionally, if you are new to Python or DataFrames, you may want to check out some online tutorials or courses to get started.

Last modified on 2024-10-07