How to do convert Pandas DataFrame to CSV file

Convert-Pandas

In this post, we will explore How to convert Pandas DataFrame to CSV files. The Pandas library to_csv() method is used to write a dataframe to CSV.

Python to_CSV() method

The Python pandas to_CSV() method is used to write a dataframe to CSV. The only file name is the minimum parameter required for this method.

DataFrame.to_csv(file_path_or_buf=None, sep=',', na_rep='', float_format=None, columns=None, header=True, index=True)

Parameters

  • File_path: The name of the file or full path along with the file name.
  • sep: The default separator is a comma(,).can be any separator as per need.
  • columns: The columns to write if we need to create a subset.
  • header :if we want to rename column names ,we can pass name as a list.
  • Index : whenever create a CSV file by default Pandas include indexes.When data loaded from CSV file to dataframe it create a new ‘Unnamed ‘ columns for indexes.So it is recommended to Index = false always.

1. Convert Dataframe to CSV with indexes

In this example, we are creating a CSV from the dataframe by just passing the filename. It will create a file in the current working directory.

The real problem arises when we load data in the dataframe by using the read_CSV() method, it creates new ‘Unnamed ‘ indexes columns. So it is recommended to use Index = false always.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[100,100, 100,100],
    'Subject': ['Math', 'Math', 'Music', 'Physic']
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV

CSVfile = dfobj.to_csv('studata.csv')

#create a dataframe from csv
df = pd.read_csv('studata.csv')
print(df)

Output

   Unnamed: 0   Name  Marks Subject
0           0   Jack    100    Math
1           1   Rack    100    Math
2           2    Max    100   Music
3           3  David    100  Physic

2. Convert Dataframe to CSV with no index

The to_csv() method creates new ‘Unnamed ‘ indexes columns.To avoid this need to set index=False as done in the below example.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[100,100, 100,100],
    'Subject': ['Math', 'Math', 'Music', 'Physic']
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV

CSVfile = dfobj.to_csv('studata.csv',index=False)

df = pd.read_csv('studata.csv')
print(df)

Output

    Name  Marks Subject
0   Jack    100    Math
1   Rack    100    Math
2    Max    100   Music
3  David    100  Physic

3. Convert without header and indexes

To convert a dataframe without head and indexes we need to set this parameter as index=False,header=False.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[100,100, 100,100],
    'Subject': ['Math', 'Math', 'Music', 'Physic']
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV

CSVfile = dfobj.to_csv('studata.csv',index=False,header=False)

df = pd.read_csv('studata.csv')
print(df)

Output

   Jack  100    Math
0   Rack  100    Math
1    Max  100   Music
2  David  100  Physic

4. Convert Dataframe to CSV with Custom header

While creating a CSV from Python pandas dataframe if we need to rename the columns, We can pass the column name as a list to the header parameter of the to_csv() method.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[100,100, 100,100],
    'Subject': ['Math', 'Math', 'Music', 'Physic']
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV

CSVfile = dfobj.to_csv('studata.csv',index=False,header=['Stu_Name','Score','Subj'])

df = pd.read_csv('studata.csv')
print(df)

Output

  Stu_Name  Score    Subj
0     Jack    100    Math
1     Rack    100    Math
2      Max    100   Music
3    David    100  Physic

5. Convert a Dataframe by using a delimiter

We can use the sep =” parameter of to_csv() method to create a CSV file separated by a delimiter. In this example, we are separating each element by using the tab(\t) delimiter.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[100,100, 100,100],
    'Subject': ['Math', 'Math', 'Music', 'Physic']
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV

CSVfile = dfobj.to_csv('studata.csv',index=False,sep='\t')

df = pd.read_csv('studata.csv') 
print(df)

Output

  Name\tMarks\tSubject
0      Jack\t100\tMath
1      Rack\t100\tMath
2      Max\t100\tMusic
3   David\t100\tPhysic

6. Convert DataFrame to CSV missing values

If some values are missing in the dataframe, to handle while creating a CSV file we have to set the na_rep=’NAN’ parameter of the CSV file.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[100,100, 100,100],
    'Subject': ['Math', 'Math', 'Music', pd.NA]
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV

CSVfile = dfobj.to_csv('studata.csv', index=False, na_rep='NAN')

df = pd.read_csv('studata.csv') 
print(df)

Output

    Name  Marks Subject
0   Jack    100    Math
1   Rack    100    Math
2    Max    100   Music
3  David    100     NAN

7. Convert dataframe to CSV with float format

In this example, we handle the float values by using the float_format parameter of the to_csv() method.

Program Example

import pandas as pd
 
Student_dict = {
    'Name': ['Jack', 'Rack', 'Max', 'David'],
    'Marks':[00099.50,00098.05, 00097.01,000.98],
    'Subject': ['Math', 'Math', 'Music', 'Pyh']
}
 
dfobj = pd.DataFrame(Student_dict)

#convert dataframe to CSV



CSVfile = dfobj.to_csv('studata.csv',float_format='%g', index=False)

df = pd.read_csv('studata.csv') 
print(df)

Output

   Name  Marks Subject
0   Jack  99.50    Math
1   Rack  98.05    Math
2    Max  97.01   Music
3  David   0.98     Pyh

Summary

In this post, we have explored the 7 different ways of How to convert Pandas DataFrame to CSV files.

Related Posts