to_csv() to write DataFrame data to CSV files

We will save data to CSV ( comma separated values ) file. This is a format used for storing data which can easily exported to MS Excel or any other database.

import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
my_data = pd.DataFrame(data=my_dict)
my_data.to_csv('my_file.csv')

Returns None if path of csv file is given and returns the string in CSV format if path is not given.

my_file.csv is stored in the same directory ( script )

Python Pandas DataFrame to creating csv file and using MySQL sample table to csv by using to_csv()

By default we will have index as left most column. We can remove index by using option index=False

my_data.to_csv('my_file.csv',index=False)

Storing Path

We can keep in D drive ( root )

my_data.to_csv('D:\my_file.csv')

Inside data directory

my_data.to_csv('D:\data\my_file.csv')

Separator sep

By default sep="," , This is one char length string used to separate data in a row.

my_data.to_csv('D:\my_file.csv',sep='#',index=False)

na_rep Blank data

How to handle if data is blank, we can use na_rep='*'

my_data.to_csv('D:\my_file.csv',na_rep='*')

index

By default it will add the index to csv file as extra column, we can set the value to False to remove the index column.

my_data.to_csv('my_file.csv',index=False)

columns

We can restric the columns by specifying the column names as a list.

my_data.to_csv('my_file.csv',columns=['NAME','ID'])

header

We can remove the column names and store only the data part. By default it is True and all column headers are used.

my_data.to_csv('my_file.csv',header=False)

Storing part of the data

We can filter the DataFrame and then save the rows in CSV file. For this we will use our test.csv file as we have more rows.
Now let us store only two columns, class and name

import pandas as pd 
my_data=pd.read_csv('test.csv')
df=my_data.loc[:,['class','name']]
my_data = pd.DataFrame(data=df)
my_data.to_csv('my_file.csv',index=False)

We can use various other filters to manage the data and store in CSV file. You can rad more on filters sections.

Data from MySQL table to CSV file

Read on how to connect to MySQL database and then collected the records of student table by using read_sql() to a DataFrame. Finally we will save them in an CSV file using to_csv().

import mysql.connector
import pandas as pd 
my_connect = mysql.connector.connect(
      host="localhost",
      user="root",
      passwd="test",
      database="my_tutorial"
    )
####### end of connection ####
sql="SELECT * FROM student "
my_data = pd.read_sql(sql,my_connect )
my_data.to_csv('D:\my_file.csv')

Using SQLAlchemy

Read more on MySQL with SQLAlchemy connection. Below code will create student.csv file in the same directory, you can add path ( as above ) if you want the file to be created at different location.

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
my_conn=my_conn.connect()
sql="SELECT * FROM student "
my_data = pd.read_sql(sql,my_conn)
my_data.to_csv('student.csv') # Add the path

From SQLite database to csv file.

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("sqlite:///E:\\my_db\\my_db.db") # Update path 
my_conn=my_conn.connect()
sql="SELECT * FROM student "
my_data = pd.read_sql(sql,my_conn)
my_data.to_csv('E:\\my_db\\sqstudent.csv') # Add the path

Questions

How do I use the to_csv() function in Pandas to save a DataFrame to a CSV file?
What are the common parameters of the to_csv() function in Pandas?
Can I specify a custom delimiter while using to_csv() in Pandas?
How can I exclude the index column while saving a DataFrame to CSV using to_csv()?
Is it possible to save only specific columns of a DataFrame using to_csv()?
What is the purpose of the header parameter in the to_csv() function?
How can I handle missing values while using to_csv() in Pandas?
Can I save a DataFrame to a CSV file without quoting the values using to_csv()?
How do I save a DataFrame to a CSV file with a different encoding using to_csv()?
Is it possible to save a DataFrame to a CSV file with a specific date format in the filename using to_csv()?

Data input and output from Pandas DataFrame
Pandas read_csv() read_excel() to_excel() to_string()

Numpy arrays Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: