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
0A3396
1B1290
2C6396
3D4964
4E6446
5F6956
6G6092
7H2665
8I7493
9J3288

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

How do I export data from python to excel?

You can export data from python to excel by converting the data to a Pandas dataframe & then using the to_excel function.

How do I export data from python to csv?

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”

Leave a Reply

Your email address will not be published. Required fields are marked *