What will we cover in this tutorial?

We will cover the basic process of saving data from a Pandas DataFrame to an Excel sheet. This includes

• The simple export of a DataFrame to Excel from Python.
• How to set the Sheet name in Excel from Python.
• To set which columns to export from the DataFrame to Excel.

Step 1: The simple export of a full DataFrame

Often you just need to figure out how to save the full DataFrame to an Excel sheet. This is often needed, as you have collected the data in Python, but want to explore it or work more on it in Excel.

If you are new to Pandas, we can recommend to read this tutorial.

Pandas is a great library to work with data similar to an Excel sheet. The advantage of Pandas is working with the data in Python and automate many things. That could be collecting data from API’s on the internet or simply automate the stuff you do manually in Excel.

Another beauty of Pandas is that it has great built in methods that does all the work for you.

Here we will demonstrate how a call to to_excel(…) can be done on a DataFrame.

import pandas as pd
import numpy as np


# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)


df.to_excel("output.xlsx")

The code above creates a random DataFrame using the random function from NumPy.

Then it continues to call to_excel on the data frame with the output Excel file name. This will create an Excel sheet, which could look similar to this.

Step 2: How to set the sheet name in Excel from Python

Sometimes you want your data more organized. Here we are going to show how to set the sheet name where you export your data.

The default sheet name is Sheet1, hence in the previous step you would notice that the created sheet has that name.

Sometimes you want a different name. Luckily, this is simple.

import pandas as pd
import numpy as np


# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)


df.to_excel("output.xlsx", sheet_name="My Sheet")

Notice that we only set the sheet_name parameter in the call. That is all it takes.

How did I know? I read the documentation.

This creates an Excel file with the desired sheet name.

Step 3: Only export the specific columns

Often it happens that you are only interested in exporting some of the columns in your DataFrame. Luckily, this is also simple to do from the standard export function on the DataFrame.

import pandas as pd
import numpy as np


# Generate some random increasing data
df = pd.DataFrame(
    {'A': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'B': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'C': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'D': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'E': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)],
     'F': [np.random.uniform(0.1*i, 0.1*i + 1) for i in range(100)]}
)


df.to_excel("output.xlsx", sheet_name="My Sheet", columns=['B', 'E', 'F'])

In the above example we create a DataFrame with a few columns. We only export columns B, E, and F.

This is set directly in the to_excel method, which can set the parameter columns. The parameter can accept a list of the column names to export to the Excel sheet.

Next step

This is only to get you started and see how simple it is to work with data from Python using Pandas. This enables you to make great things with only a few lines of code.