After all the complex analysis that you do using Pandas in Python, it is often important to export the output data to Excel or CSV. Pandas module doesn’t disappoint us and comes packaged with a few great functions that let’s you get this export done easily.
Export Data from Pandas to Excel
You can export data from an Pandas to an excel file using the to_excel function. To Excel writes the data from a Pandas DataFrame to an Excel workbook.
In an earlier article, I explained how to read an excel file to a Pandas DataFrame.
The final DataFrame that we got was as follows:
Name | Score (Maths) | Score (English | |
0 | A | 33 | 96 |
---|---|---|---|
1 | B | 12 | 90 |
2 | C | 63 | 96 |
3 | D | 49 | 64 |
4 | E | 64 | 46 |
5 | F | 69 | 56 |
6 | G | 60 | 92 |
7 | H | 26 | 65 |
8 | I | 74 | 93 |
9 | J | 32 | 88 |
Let’s assume that this DataFrame is stored as a variable called df.
We want to check whether each of the students have passed or failed. The criteria for passing is a score above 40 in both English & Maths. The following code adds the pass/fail status column to the DataFrame:
L1=list(df['Score(Maths)'])
L2=list(df['Score(Maths)'])
status=[]
for x,y in zip(L1,L2):
if x>=40:
if y>=40:
status.append("Pass")
else:
status.append("Fail")
else:
status.append("Fail")
df["Status"]=status
df
The new DataFrame looks as follows:
Name Score(Maths) Score(English) Status
0 A 33 96 Fail
1 B 12 90 Fail
2 C 63 96 Pass
3 D 49 64 Pass
4 E 64 46 Pass
5 F 69 56 Pass
6 G 60 92 Pass
7 H 26 65 Fail
8 I 74 93 Pass
9 J 32 88 Fail
To export this updated DataFrame back to Excel – we need use the to_excel function and pass the name of the file to the function:
df.to_excel("New_Excel.xlsx")
Storing the Exported Excel File to a particular location
In the above example, we just passed the name of the excel file to the to_excel function. It works only if we want to store the output excel file in the same folder as the code. If we want to store the excel in a different folder, we need to specify the path of the file.
df=pandas.to_excel("C:\\folder\\sub_folder\\New_Excel.xlsx")
df
Storing Data in a particular sheet in the exported Excel file
Now that the basics are in place, we could choose to get more fancy! How about storing the data in a particular sheet with an unique sheet name ? The below code stores the exported data in “New_Excel” tab within the file named “New_Excel”.
df.to_excel("New_Excel.xlsx",sheet_name="New_Excel")
Export Data from Pandas to CSV
Just like excel, it’s easy to write data to a CSV file too. The function in this case is to_csv.
df=pandas.to_csv("New_CSV.csv")
df
You need to mention the file-path if you want to keep the export csv file in a folder separate from that of the code:
df=pandas.to_csv("C:\\folder\\sub_folder\\New_csv.csv")
df
Do be careful about the file-type to which you are exporting the DataFrame data. If you try to export an excel file using to_csv you will see an error (and vice versa).
Further Reading
If you found the above tutorial useful, you may want to check out some of my other articles on Python.
FAQs : Pandas to Excel & CSV
You can export data from python to excel by converting the data to a Pandas dataframe & then using the to_excel function.
You can export data from python to csv by converting the data to a Pandas dataframe & then using the to_csv function.
2 thoughts on “How to export data from Pandas to Excel or CSV”