How to convert DataFrame to CSV for different scenarios

Convert dataframe to CSV in different output formats

We can simply use dataframe.to_csv to convert pandas dataframe to CSV, but we can further customise this and add additional options to save the CSV file in different format such as:

• Converting without Index
• Converting without header
• Converting with new column names
• Converting dataframe with specific columns to csv
• Converting in append mode
• Converting by setting index column name

 

Create pandas DataFrame with example data

DataFrame is a data structure used to store the data in two dimensional format. It is similar to table that stores the data in rows and columns. Rows represents the records/ tuples and columns refers to the attributes.

We can create the DataFrame by using pandas.DataFrame() method.

Syntax:

pandas.DataFrame(input_data,columns,index)

Parameters:

It will take mainly three parameters

1. input_data is represents a list of data
2. columns represent the columns names for the data
3. index represent the row numbers/values

We can also create a DataFrame using dictionary by skipping columns and indices.

Example: Python Program to create a dataframe for market data from a dictionary of food items by specifying the column names.

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])

# display the dataframe
print(dataframe)

Output:

            id            name    cost  quantity
item-1  foo-23  ground-nut oil  567.00         1
item-2  foo-13         almonds  562.56         2
item-3  foo-02           flour   67.00         3
item-4  foo-31         cereals   76.09         2

You can learn more at Pandas dataframe explained with simple examples

 

Scenario-1 : Using dataframe.to_csv without any options

In this method we are going to convert pandas dataframe to csv using to_csv() with out specifying any parameters.

Syntax:

dataframe.to_csv('file.csv') 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.

 

Example: Python program to convert dataframe to csv

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv")

Output:

,id,name,cost,quantity
item-1,foo-23,ground-nut oil,567.0,1
item-2,foo-13,almonds,562.56,2
item-3,foo-02,flour,67.0,3
item-4,foo-31,cereals,76.09,2

 

Scenario-2 : Convert dataframe to csv without Index

In this method we are going to convert pandas dataframe to csv using to_csv() by specifying index parameter.

csv will not read index from the dataframe , if it set to False

Syntax:

dataframe.to_csv('file.csv',index=False) 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.
3. index is the parameter used to set or remove the index. By default index is True

 

Example: Python program to convert dataframe to csv without index

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",index=False)

Output:

id,name,cost,quantity
foo-23,ground-nut oil,567.0,1
foo-13,almonds,562.56,2
foo-02,flour,67.0,3
foo-31,cereals,76.09,2

 

Scenario-3 : Convert dataframe to csv without header

In this method we are going to convert pandas dataframe to csv using to_csv() by specifying header parameter.

csv will not read column names from the dataframe , if it set to False

Syntax:

dataframe.to_csv('file.csv',header=False) 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.
3. header is the parameter used to  remove the column names . By default index is True

 

Example: Python program to convert dataframe to csv without header

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",header=False)

Output:

item-1,foo-23,ground-nut oil,567.0,1 
item-2,foo-13,almonds,562.56,2 
item-3,foo-02,flour,67.0,3 
item-4,foo-31,cereals,76.09,2

 

Scenario-4 : Convert dataframe to csv with new column names

In this method we are going to convert pandas dataframe to csv using to_csv() by specifying header parameter.

We can define new set of column names through a list for csv file.

Syntax:

dataframe.to_csv('file.csv',header=[columns]) 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.
3. header is the parameter used to  get the new column names

 

Example: Python program to convert dataframe to csv with new headers/columns

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",header=['A','B','C','D'])

Output:

,A,B,C,D
item-1,foo-23,ground-nut oil,567.0,1
item-2,foo-13,almonds,562.56,2
item-3,foo-02,flour,67.0,3
item-4,foo-31,cereals,76.09,2

 

Scenario-5 : Convert dataframe with specific columns to csv

In this case, we are going to convert only specific columns from pandas dataframe to csv using columns parameter.

This parameter will take column names in a list and convert to csv that are specified.

Syntax:

dataframe.to_csv('file.csv',columns=[columns]) 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.
3. columns is the parameter used to  get the column names that are included in csv

 

Example: Python program to convert dataframe to csv with some columns/specified columns.

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",columns=['id','name'])

Output:

,id,name
item-1,foo-23,ground-nut oil
item-2,foo-13,almonds
item-3,foo-02,flour
item-4,foo-31,cereals

 

Scenario-6 : Convert dataframe to csv in append mode

Here we are appending the pandas dataframe to csv with mode parameter in append mode - 'a'

Syntax:

dataframe.to_csv('file.csv',mode = 'a') 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.
3. mode with 'a' defines the append mode.

Example: Python program to convert dataframe to csv by appending csv data

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",mode='a')

Output:

,id,name
item-1,foo-23,ground-nut oil
item-2,foo-13,almonds
item-3,foo-02,flour
item-4,foo-31,cereals
,id,name,cost,quantity
item-1,foo-23,ground-nut oil,567.0,1
item-2,foo-13,almonds,562.56,2
item-3,foo-02,flour,67.0,3
item-4,foo-31,cereals,76.09,2

 

Scenario-7 : Convert dataframe to csv by setting index column name

Here we are setting the column name for index through index_label parameter. It will take index column name value and set as index column in the csv file.

Syntax:

dataframe.to_csv('file.csv',index_label='column') 

where,

1. dataframe is the input dataframe
2. file is the file name for  the csv created from the dataframe.
3. index_label takes column name for index

 

Example: Python program to convert dataframe to csv by specifying index label as 'Index col'

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",index_label='Index col')

Output:

Index col,id,name,cost,quantity
item-1,foo-23,ground-nut oil,567.0,1
item-2,foo-13,almonds,562.56,2
item-3,foo-02,flour,67.0,3
item-4,foo-31,cereals,76.09,2

 

Combined Examples

Example 1: In this example we are using index_label, columns ,index and header parameters for demonstration with 2 columns and setting remaining parameters to True.

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",index_label='Index col',columns=['name','cost'],index=True,header=True)

Output

Index col,name,cost
item-1,ground-nut oil,567.0
item-2,almonds,562.56
item-3,flour,67.0
item-4,cereals,76.09

 

Example 2: In this example we are using index_label, columns ,index and header parameters for demonstration by selecting three columns and setting index to False.

# import the module
import pandas

# consider the food data
food_input={'id':['foo-23','foo-13','foo-02','foo-31'],
                  'name':['ground-nut oil','almonds','flour','cereals'],
                  'cost':[567.00,562.56,67.00,76.09],
                  'quantity':[1,2,3,2]}

# pass this food to the dataframe by specifying rows 
dataframe=pandas.DataFrame(food_input,index = ['item-1', 'item-2', 'item-3', 'item-4'])


dataframe.to_csv("converted.csv",index_label='Index col',columns=['name','cost','quantity'],index=False,header=True)

Output

name,cost,quantity
ground-nut oil,567.0,1
almonds,562.56,2
flour,67.0,3
cereals,76.09,2

 

Summary

In this tutorial we discussed how to convert pandas dataframe to csv using to_csv() method. Following scenarios were covered:

• Converting without Index
• Converting without header
• Converting with new column names
• Converting dataframe with specific columns to csv
• Converting in append mode
• Converting by setting index column name

 

References

Pandas - to_csv()