How to Write DataFrame to CSV Files With Python Pandas?


CSV files are comma-separated values and are a convenient format for storing and working with data. In this blog post, I’ll show you how to use the pandas library to write DataFrames to CSV files.

Writing DataFrame to CSV Files With Python Pandas

The first step is to import the pandas library into your Python environment. You can do this by running the following code in your terminal:

pip install pandas

Once you have pandas installed, you can Import it into your Python script like so:

import pandas as pd
Code language: JavaScript (javascript)

Now that you have imported the pandas library, let’s create a DataFrame. We can do this by passing a list of dictionaries to the pd.DataFrame() function. The keys in the dictionaries will become the column names, and the values will be populated in the rows:

df = pd.DataFrame([{'Name': 'David', 'Age': 21}, {'Name': 'Marry', 'Age': 31}]) print(df) #[2 rows x 2 columns] # Name Age #0 David 21 #1 Marry 31
Code language: Python (python)

We can also write our DataFrame to a CSV file using the to_csv() method. This method accepts a file path and returns a string that represents the contents of the CSV file:

df.to_csv('example.csv')
Code language: Python (python)

example.csv file contains:

data in csv file
Data in example.csv file

Notice that an index (0,1) was automatically created when we wrote our DataFrame to a CSV file. We can avoid this by setting the index parameter to False: (index=False)

df.to_csv('example_noindex.csv', index=False)
Code language: PHP (php)

example_noindex.csv file contains:

data in csv file
Data in example_noindex.csv file

Now that we know how to write DataFrame objects to CSV files, let’s learn about some different parameters that we can use with the to_csv() method.

sep parameter: Specifies the character used to separate values in our CSV file. The default is a comma (“,”). so if we wanted to use a tab character instead of a comma, we could do so like this:

df.to_csv('example_tabs_separating_values.csv', sep='\t', index=False)
Code language: PHP (php)

Then example_tabs_separating_values.csv file would contain:

# Name \t Age # David \t 21 # Marry \t 31"
Code language: Python (python)

Note that if you use tab characters for separating values, You can read your own tab-separated values string back in with read_csv() method.

import pandas as pd df1 = pd.read_csv('example_tabs_separating_values.csv', sep='\t') print(df1) # Name Age #0 David 21 #1 Marry 31
Code language: Python (python)

na_rep parameter: A string representation of a missing value like NaN. The default value is ”.

df.to_csv('example_tabs_separating_values.csv', na_rep='Unkown') # missing values save as Unknown
Code language: Python (python)

float_format: Format string for floating-point numbers.

df.to_csv('example.csv',float_format='%.3f') # rounded to three decimals
Code language: PHP (php)

columns: Columns to write. The default value is None, and every column will export to CSV format. If set, only columns will be exported.

df.to_csv('example_columns.csv', columns=['Name'], index=False) #Output: only column "Name" wrote #Name #David #Marry
Code language: PHP (php)

Saving CSV without headers and index

df.to_csv('without_headers_index.csv', header=False, index=False) # without_headers_index.csv file contains: #David,21 #Marry,31
Code language: PHP (php)

If you getUnicodeEncodeError , simply add encoding=’utf-8′ ;

df.to_csv('without_headers_index.csv', encoding='utf-8', header=False, index=False)
Code language: Python (python)

Save the CSV file to a specified location

df.to_csv(r'D:\MyDataAnalytic\without_headers_index.csv', header=False, index=False)
Code language: PHP (php)

Append DataFrame to existing CSV File

When you write a Pandas DataFrame to a CSV file that is already there, the new data overwrites the old data. To add a DataFrame to an existing CSV file, use mode=’a’ to set the append to write mode.

example_noindex.csv file contains:

#Name,Age #David,21 #Marry,31
Code language: CSS (css)
import pandas as pd df_a = pd.DataFrame([{'Name': 'John', 'Age': 25}, {'Name': 'Suka', 'Age': 16}]) df_a.to_csv("example_noindex.csv", header=False, index=False, mode='a') #example_noindex.csv file after Appending #Name,Age #David,21 #Marry,31 #John,25 #Suka,16
Code language: Python (python)

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts