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.
Table of Contents
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_name | Unit_Price | No_Of_Units | Available_Quantity | Available_Since_Date | |
---|---|---|---|---|---|
0 | Keyboard | 500.000 | 5 | 5 | 11/5/2021 |
1 | Mouse | 200.000 | 5 | 6 | 4/23/2021 |
2 | Monitor | 5000.235 | 10 | 10 | 08/21/2021 |
3 | CPU | 10000.550 | 20 | Not Available | 09/18/2021 |
4 | Speakers | 250.500 | 8 | NaT | 01/05/2021 |
5 | NaT | 350.000 | NaT | NaT | NaT |
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.