How to Export Pandas DataFrame to a CSV File

For this excercise, lets create a small Pandas dataframe from scratch. To read data from csv use df.read_csv()

Lets first import the Python Pandas.

In [1]:
import pandas as pd

We will use pd.DataFrame to create the new dataframe.

In [3]:
from pandas import DataFrame

president = {'lastname': ['Obama','Trump','Clinton','Carter'],
        'firstyear': [2009,2017,1993,1977]
        }

df = DataFrame(president, columns= ['lastname', 'firstyear'])

Lets print our dataframe.

In [5]:
print(df)

  lastname  firstyear
0    Obama       2009
1    Trump       2017
2  Clinton       1993
3   Carter       1977

We can also use df.head()

In [6]:
df.head()
Out[6]:
lastname firstyear
0 Obama 2009
1 Trump 2017
2 Clinton 1993
3 Carter 1977

Pandas DataFrame Write To CSV Using df.to_csv()

Once we have the data in dataframe, we can write to csv file with df.to_csv()

In [7]:
df.to_csv("presidents.csv")

df.to_csv() will save Pandas dataframe to csv in your current directory. Lets check that.

In [9]:
ls -lrt presidents.csv

-rw-r--r-- 1 root root 75 Dec 19 22:32 presidents.csv

Yes we got the file written. Lets check the content of this file using unix cat command.

In [10]:
cat presidents.csv

,lastname,firstyear
0,Obama,2009
1,Trump,2017
2,Clinton,1993
3,Carter,1977

There you go we got our dataframe in to csv file format.

If you do df.to_csv?, you would see lot of options. Lets go through sep option

Lets say we want to use a different delimtter other than comma, use sep option.

In [14]:
df.to_csv("presidents.csv",sep="-")

Lets checkout again

In [16]:
cat presidents.csv | head -2

-lastname-firstyear
0-Obama-2009

As we above the csv has "-" as delimtter now.

How to save selected columns of dataframe to csv

Lets say we want to save column lastname of Pandas dataframe to csv file.

In [29]:
df['lastname'].to_csv("presidents.csv")

/home/anaconda3/envs/condapy374/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: The signature of `Series.to_csv` was aligned to that of `DataFrame.to_csv`, and argument 'header' will change its default value from False to True: please pass an explicit value to suppress this warning.
  """Entry point for launching an IPython kernel.

We got the above warning about the header. Lets cat the file to see what happened.

In [30]:
cat presidents.csv | head -2

0,Obama
1,Trump

We lost the headers. Lets use the option header to False to see if warning goes away.

In [31]:
df['lastname'].to_csv("presidents.csv",header=False)

The warning has gone way. One thing to notice here is that df['lastname'] is series not a dataframe object. Therefore when we use to_csv() on Pandas series, headers are lost.

But how can we get the headers back.

In [36]:
df['lastname'].__class__
Out[36]:
pandas.core.series.Series

Wrap Up!

This post is a primer for users who are new to Python Pandas. I will do second post where I will cover other options of df.to_csv().

Related Topics:

how to plot histogram in python

convert pandas dataframe to list

pandas sort dataframe values by column pandas sort by multi columns

Related Notebooks