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() method.

In this tutorial, you’ll learn how to save a dataframe in a CSV format or how to export a dataframe into a CSV file itself.

If You’re in Hurry…

You can use the below code snippet to save the dataframe to CSV format.

Write Pandas Dataframe to CSV Object

You can use the to_csv() method to save the dataframe in CSV Format. It’ll return the CSV object, which can be used in your program.

Snippet

csv_data = df.to_csv()

print(csv_data)

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 to CSV File

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

Snippet

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. Hence, you can list the files in a directory and see to ensure a file with the same name doesn’t exist.

To learn how to handle Missing Data, Headers, labels while exporting dataframe, refer to the details section below.

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.

It accepts multiple optional parameters.

Without any parameter, it’ll convert the dataframe to a CSV object which can be used in the program itself. Usage of the parameters is explained in the further sections.

Snippet

csv_data = df.to_csv()

print(csv_data)

Where,

  • df.to_csv() – Converts the dataframe df to a csv object and its saved in variable csv_data
  • print(csv_data) – Prints the CSV Data

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,,,

If you want to convert the dataframe to a CSV file, you can pass the file path to the to_csv() method. Then it’ll create a CSV file object in the mentioned location.

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

Customising Delimiter

CSV file uses , as the default delimiter which separates each column in the row. If you want to use a custom delimiter, you can pass it using the parameter sep.

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

Snippet

csv_data = df.to_csv(sep=';') # Use ; to separate data

print(csv_data)

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;;;

If you would like to save the dataframe to a file with a custom delimiter, then you can use the below snippet. (Which has a path and the separator parameters).

df.to_csv('c:/temp/sample_dataframe_Tab_separator.csv',sep=';') # Use ; to seperate data

Next, you’ll see the missing values in CSV.

Missing Values

When converting a dataframe to CSV, the missing values are made empty by default. However, you can use custom values in place of the missing or the NaN values using the parameter na_rep.

In the below code snippet, the missing values will be replaced with the string Value Missing text.

Snippet

csv_data = df.to_csv(na_rep='Value Missing') 
print(csv_data)

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

If you would like to export the CSV object with missing values as a custom text to a file, you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_Missing_Values.csv', na_rep='Value Missing') # missing value save as Unknown

Next, you’ll see about the headers.

Handling Headers

When converting a CSV file, you may need to hide or show the headers for various use cases.

You can handle the display of the header information using the header parameter.

  • header='False' hides the header
  • header='True' shows the header

Snippet

csv_data = df.to_csv(header=False)
print(csv_data)

Since header=false, the header is not added to the CSV object as shown below.

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,,,

If you would like to control headers while exporting the CSV to a file object, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_Without_Headers.csv',header=False)

This is how you can control headers in CSV objects.

Next, you’ll learn about customizing columns.

Customising columns

When saving a dataframe as a CSV object, you can specify which column needs to be added to the CSV object using the columns parameter.

If you would like to drop the column directly in the dataframe itself rather than hiding in the CSV object, refer to Drop Column in pandas Dataframe.

You can pass the list of columns to be included in the dataframe in the columns parameter as shown below.

Snippet

csv_data = df.to_csv(columns=['Column_1','Column_2'])
print(csv_data)

Where,

  • Columns-Parameter to control the list of columns to be exported
  • ['Column_1','Column_2'] – List of columns to be exported.

If you would like to save the CSV object to file with specific columns, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_With_Two_Columns.csv',columns=['Column_1','Column_2'])

This is how you can handle the columns to be exported to the CSV file.

Next, let’s see how to use this method in the different use cases.

Write Pandas Dataframe to CSV Without Index

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

You can ignore the index column by using the parameter index=False as shown below.

Snippet

csv_data = df.to_csv(index=False)
print(csv_data)

You can see that the index column is not available in the CSV object.

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,,,

If you would like to hide the index column while exporting the dataframe to CSV file, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_Without_index.csv',index=False)

This is how you can handle the index column while converting the dataframe to CSV.

Next, let’s learn about handling header.

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.

You can ignore the header by using the parameter header=False as shown below.

Snippet

csv_data = df.to_csv(header=False)
print(csv_data)

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,,,

If you would like to export the dataframe to a CSV file without the header row, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_Without_Headers.csv',header=False)

This is how you can ignore the headers.

Next, you’ll see about including the headers.

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 can do this by using the parameter header=True.

..By default also the header is True. So even if you ignore this parameter, then the headers will be included in the CSV object.

Snippet

csv_data = df.to_csv(header=True)

print(csv_data)

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

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,,,

If you would like to include handle the headers when converting the dataframe to the CSV file itself, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_With_Headers.csv',header=True)

This is how you can handle headers when converting dataframe to CSV objects.

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.

You can customise the columns by using the columns parameter and passing the list of columns to be exported as the value of this parameter, as shown below.

Snippet

csv_data = df.to_csv(columns=['product_name','Unit_Price'])
print(csv_data)

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

If you want to export specific columns from the dataframe to the CSV file, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_With_specific_Columns.csv',columns=['product_name','Unit_Price'])

This is how you can export specific columns of the dataframe to CSV.

Next, you’ll learn about appending.

Write Pandas Dataframe to CSV Using Tab Separator

In this section, you’ll learn how to write pandas dataframe to CSV using Tab separator. When using the tab separator, values will be delimited using the tab space rather than the default delimiter ,. This can be useful when you want to present the CSV data in an easily readable form.

You can control the delimiter character by using the sep parameter and the \t as the separator value. Use the below snippet to export CSV with tab separator.

Snippet

csv_data = df.to_csv(sep='\t') # Use ; to seperate data

print(csv_data)

You can see that the dataframe is converted to CSV with tab separation.

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           

If you want to export the dataframe to a CSV file with the tab separation, then you can use the below snippet.

df.to_csv('c:/temp/sample_dataframe_Tab_separator.csv',sep='\t') # Use ; to seperate data

This is how you can handle the delimiter while exporting the dataframe to the CSV object.

Next, you’ll learn about the index column names.

Write Pandas Dataframe to Multiple CSV

In this section, you’ll learn how to write pandas dataframe to multiple CSV objects.

You need to use the np.array_split() from the NumPy to split the dataframe into n times before writing it into CSV. Then you have to enumerate the split array using the for loop and write the chunks to CSV.

For example, the below example

  1. Splits the dataframe into five chunks.
  2. When enumerated using the for loop, the object chunk will have each dataframe.
  3. Then, you can use the to_csv() method on that chunk to convert the split dataframe into a CSV object.

Snippet

import numpy as np

for idx, chunk in enumerate(np.array_split(df, 5)):

    csv_data = chunk.to_csv()

    print(csv_data)

You can see that the dataframe is split into 5 and printed as different CSV objects below.

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

    ,product_name,Unit_Price,No_Of_Units,Available_Quantity,Available_Since_Date
    2,Monitor,5000.235,10,10,08/21/2021

    ,product_name,Unit_Price,No_Of_Units,Available_Quantity,Available_Since_Date
    3,CPU,10000.55,20,Not Available,09/18/2021

    ,product_name,Unit_Price,No_Of_Units,Available_Quantity,Available_Since_Date
    4,Speakers,250.5,8,,01/05/2021

    ,product_name,Unit_Price,No_Of_Units,Available_Quantity,Available_Since_Date
    5,,350.0,,,

If you want to split and write a dataframe to multiple CSV file objects, then you can use the below snippet. It’ll create 5 CSV files with the Index attached to the file name by using placeholder variable {idx} used in the fString.

import numpy as np

for idx, chunk in enumerate(np.array_split(df, 5)):
    chunk.to_csv(f'c:/temp/csvfile_{idx}.csv')

This is how you can write a dataframe to multiple CSV objects.

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.

Conclusion

To summarize, you’ve learned how to export a dataframe to a CSV object or a CSV file using the to_csv() method. You’ve also learned about the different optional parameters that can be used in to_csv() method to handle different things in the CSV file.

If you have any questions, comment below.

You May Also Like

Leave a Comment