How to export data from Pandas to Excel or CSV

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