Last Updated on July 14, 2022 by Jay
We have already learned how to read data from Excel files. Another important thing to know is how to save data back into Excel file using Python. Why Excel again? Well, because we are office workers and most our managers and coworkers only know Excel, we have to speak their language. But, we can use another language to make our jobs easier 🙂
Save data to an Excel file
Saving data to Excel file is also easy using pandas. The simplest way is like this: df.to_excel(), which saves the dataframe into an Excel file. Similar to df.read_excel(), this to_excel() method also has many optional arguments. We will look at only a few of the arguments here, if you want to learn the full list of arguments, I suggest you read the pandas official documentation. Let’s look at an example, first we need to have a dataframe ready for saving. We’ll use the same file used for the read_excel() example. You can find the Excel file here.
import pandas as pd
df = pd.read_excel()
>>> df
User Name Country City Gender Age
0 Forrest Gump USA New York M 50
1 Mary Jane CANADA Tornoto F 30
2 Harry Porter UK London M 20
3 Jean Grey CHINA Shanghai F 30
df.to_excel('saved_file.xlsx')
After executing the above code, we’ll have a new file called “saved_file.xlsx”, which was created by Python. Let’s open up the file and see if it has the same data inside.
We immediately notice something weird… column A contains something looks like a list starting from 0. If you are okay with leaving it there, fine. But for me, that column always bothers me when I look at my files, I have to get rid of it…
Removing the starting index when saving an Excel file using pandas
The .to_excel() method provides an optional argument index, which is for controlling that list we just saw. We can remove that list from our Excel output file by:
df.to_excel('saved_file.xlsx', index = False)Other useful optional arguments
sheet_name: you can name the sheet if you don’t like “Sheet1” by default.na_rep: value to replace the “Null” values in the dataframe, by default this is an empty string “”. However, if your dataframe contains numbers, you might want to set this tonp_rep = 0instead.columns: choose the columns you want to output. I normally don’t use this, as I drop the columns in the dataframe before saving to file.
Saving to CSV file
We can save the same dataframe to a csv file by using df.to_csv(). The arguments are similar to to_excel() so I won’t repeat them here. Just want to point out a minor difference, but this is really a difference between Excel and CSV file.
CSV file is basically a text file, it contains only 1 sheet, so we can’t rename the sheet.
That’s it for today. Note this short post talks about how to save one dataframe into an Excel file. If you want to save multiple dataframes into the same Excel file, check out this post here.