How To Write Pandas Dataframe to CSV – Definitive Guide

Pandas Dataframe is a two-dimensional data structure that stores records in rows and columns format.

You can write pandas dataframe to CSV using the df.to_csv(‘csvfilename.CSV’) method.

In this tutorial, you’ll learn how to write pandas dataframe to a CSV file.

If you’re in Hurry

To export the dataframe to a CSV file, then you can pass the file path.

Code

df.to_csv('c:/temp/sample_dataframe.csv')

This is how you can save dataframe as CSV.

Writing a CSV file will replace an already existing file with the same name.

If You Want to Understand Details, Read on…

In this tutorial, you’ll learn the different methods available to write pandas dataframe to CSV.

Sample Dataframe

This is the sample dataframe used throughout the tutorial.

Dataframe Looks Like

import pandas as pd

data = {"product_name":["Keyboard","Mouse", "Monitor", "CPU", "Speakers",pd.NaT],
        "Unit_Price":[500,200, 5000.235, 10000.550, 250.50,350],
        "No_Of_Units":[5,5, 10, 20, 8,pd.NaT],
        "Available_Quantity":[5,6,10,"Not Available", pd.NaT,pd.NaT],
        "Available_Since_Date":['11/5/2021', '4/23/2021', '08/21/2021','09/18/2021','01/05/2021',pd.NaT]
       }

df = pd.DataFrame(data)

df = df.astype({"Unit_Price": float})

df

Dataframe Looks Like

product_nameUnit_PriceNo_Of_UnitsAvailable_QuantityAvailable_Since_Date
0Keyboard500.0005511/5/2021
1Mouse200.000564/23/2021
2Monitor5000.235101008/21/2021
3CPU10000.55020Not Available09/18/2021
4Speakers250.5008NaT01/05/2021
5NaT350.000NaTNaTNaT

Write Pandas Dataframe to CSV

You can save the pandas dataframe as CSV using the to_csv() method.

To convert the dataframe to a CSV file,

  • Pass the file path to the to_csv() method.
  • It’ll create a CSV file object in the mentioned location.

Code

df.to_csv('csvfilename.csv')

Customising Delimiter

CSV file uses , as the default delimiter which separates each column in the row.

  • To use a custom delimiter, you can pass it using the parameter sep.

Code

For example, to use ; as a delimiter, you can use sep=';'

df.to_csv('csvfilename.csv', sep=';')

CSV output where columns are separated using the ;.

CSV Output

    ;product_name;Unit_Price;No_Of_Units;Available_Quantity;Available_Since_Date
    0;Keyboard;500.0;5;5;11/5/2021
    1;Mouse;200.0;5;6;4/23/2021
    2;Monitor;5000.235;10;10;08/21/2021
    3;CPU;10000.55;20;Not Available;09/18/2021
    4;Speakers;250.5;8;;01/05/2021
    5;;350.0;;;

Handle Missing Values

When converting a dataframe to CSV,

  • The missing values are made empty by default.
  • Use custom values in place of the missing or the NaN values using the parameter na_rep.

Code

The missing values will be replaced with the string Value Missing text in the following code snippet.

df.to_csv('csvfilename.csv', na_rep='Value Missing') 

CSV object where the missing values are printed as Value Missing

CSV Output

    0,Keyboard,500.0,5,5,11/5/2021
    1,Mouse,200.0,5,6,4/23/2021
    2,Monitor,5000.235,10,10,08/21/2021
    3,CPU,10000.55,20,Not Available,09/18/2021
    4,Speakers,250.5,8,Value Missing,01/05/2021
    5,Value Missing,350.0,Value Missing,Value Missing,Value Missing

Write Pandas Dataframe to CSV Without Index

In this section, you’ll learn how to write pandas dataframe to CSV without an index column.

  • Ignore the index column by using the parameter index=False, as shown below.

Code

df.to_csv('csvfilename.csv', index=False)

CSV Output

    product_name,Unit_Price,No_Of_Units,Available_Quantity,Available_Since_Date
    Keyboard,500.0,5,5,11/5/2021
    Mouse,200.0,5,6,4/23/2021
    Monitor,5000.235,10,10,08/21/2021
    CPU,10000.55,20,Not Available,09/18/2021
    Speakers,250.5,8,,01/05/2021
    ,350.0,,,

Write Pandas Dataframe to CSV Without Header

In this section, you’ll learn how to write pandas dataframe to CSV without a header row. Header rows are typically the column names of the dataframe.

  • Ignore the header by using the parameter header=False as shown below.

Code

df.to_csv('csvfilename.csv', header=False)

You can see the CSV object doesn’t have the header in it.

CSV Output

    0,Keyboard,500.0,5,5,11/5/2021
    1,Mouse,200.0,5,6,4/23/2021
    2,Monitor,5000.235,10,10,08/21/2021
    3,CPU,10000.55,20,Not Available,09/18/2021
    4,Speakers,250.5,8,,01/05/2021
    5,,350.0,,,

Write Pandas Dataframe to CSV With Column Names

In this section, you’ll learn how to write pandas dataframe to CSV with column names.

You ca write pandas dataframe to CSV with column names using the parameter header=True.

  • By default, the to_csv() method includes header while writing
  • Even if you ignore this parameter, the headers will be included in the CSV file.

Code

df.to_csv('csvfilename.csv', header=True)

You can see that the column names are included as headers.

CSV Output

,product_name,Unit_Price,No_Of_Units,Available_Quantity,Available_Since_Date
    0,Keyboard,500.0,5,5,11/5/2021
    1,Mouse,200.0,5,6,4/23/2021
    2,Monitor,5000.235,10,10,08/21/2021
    3,CPU,10000.55,20,Not Available,09/18/2021
    4,Speakers,250.5,8,,01/05/2021
    5,,350.0,,,

Write Pandas Dataframe Specific Columns to CSV

In this section, you’ll learn how to export a specific columns of the dataframe to a CSV objects.

  • customise the columns by using the columns parameter
  • Pass the list of columns to be exported as the value of this parameter

Code

df.to_csv('csvfilename.csv' , columns=['product_name','Unit_Price'])

You can see that only two columns product_name and Unit_Price exported as CSV.

CSV Output

    ,product_name,Unit_Price
    0,Keyboard,500.0
    1,Mouse,200.0
    2,Monitor,5000.235
    3,CPU,10000.55
    4,Speakers,250.5
    5,,350.0

Write Pandas Dataframe to CSV Line by line

You should not write the dataframe to CSV line by line, considering the performance issues. However, if you still want to do it, then you can iterate over rows in the dataframe and use the to_csv() method to create a CSV object.

Additional Resources

Leave a Comment