The to_csv() function provides many parameters with reasonable defaults that you will more often than not need to override to suit your particular use case.
Pandas DataFrame to csv
To convert a pandas dataframe to csv file, use the df.to_csv() function. Pandas DataFrame to_csv() is a built-in function that converts DataFrame to a CSV file. Pass the file object to write the CSV data into the file. Otherwise, the CSV data is returned in the string format.
In Pandas, we often deal with DataFrame, and the to_csv() function comes in handy when we need to export Pandas DataFrame to CSV.
Syntax
dataframe.to_csv(
self,
path_or_buf=None,
sep=",",
na_rep="",
float_format=None,
columns=None,
header=True,
index=True,
index_label=None,
mode="w",
encoding=None,
compression="infer",
quoting=None,
quotechar='"',
line_terminator=None,
chunksize=None,
date_format=None,
doublequote=True,
escapechar=None,
decimal=".",
)
Parameters
- path_or_buf: The File object to write the CSV data. If this path argument is not provided, the CSV data is returned as a string, and we can print the string to see what our CSV data looks like.
- sep: It is the delimiter for CSV data. It should be the string of length 1, and the default is a comma.
- na_rep: It is a string representing null or missing values; the default is an empty string.
- columns: It is the sequence to specify the columns to include in the CSV output.
- header: The allowed values are boolean or the list of strings; the default is True. The column names are not written in the output if it is False. If the list of strings it’s used to write the column names. The length of the list of strings should be the same as the number of columns written in the CSV file.
- index: If it is True, the index is included in the CSV data. The index value is not written in the CSV output if it is set to False.
- index_label: It is used to specify the column name for the index.
Return Value
Pandas DataFrame.to_csv() function returns the resulting CSV format as a string If path_or_buf is None. Otherwise returns None.
Steps for Pandas DataFrame to CSV
To convert Python DataFrame to CSV,
- Create the Lists.
- Create a Dictionary from Lists.
- Create a DataFrame from Dictionary.
- Convert DataFrame to CSV using to_csv() method.
If we don’t provide the export file’s path, it will return the CSV format as a string, and we can print it in the console.
Step 1: Create lists that consist of Data
import pandas as pd # list of series, episodes, actors series = ['Stranger Things', 'Money Heist', 'House of Cards']
Step 2: Create a Dictionary from the List
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'Houce of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
Step 3: Create a DataFrame from Dictionary
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'Houce of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
# Creating Dataframe
df = pd.DataFrame(dict)
This is one example that demonstrates how to create a DataFrame. You can use any way to create a DataFrame and not be forced to use only this approach. You can create DataFrame from many Pandas Data Structure.
Step 4: Convert DataFrame to CSV
We have created Pandas DataFrame. Now, we need to convert DataFrame to CSV.
See the following code.
# app.py
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'House of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
# Creating Dataframe
df = pd.DataFrame(dict)
# Convert DataFrame to CSV
data = df.to_csv()
print(data)
Output
python3 app.py ,series,episodes,actors 0,Stranger Things,25,Millie 1,Money Heist,40,Alvaro 2,House of Cards,45,Kevin
We first defined three lists in the above code, created a dictionary with each list as a value, and assigned a new key.
Then created a Pandas DataFrame using that dictionary and converted the DataFrame to CSV using the df.to_csv() function and returned the CSV format as a string.
Export Pandas DataFrame to CSV file
Provide a path parameter, which tells the to_csv() function to write the CSV data in the File object and export the CSV file.
See the following code.
# app.py
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'House of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
# Creating Dataframe
df = pd.DataFrame(dict)
# Export DataFrame to CSV
df.to_csv('data.csv')
Output
# data.csv ,series,episodes,actors 0,Stranger Things,25,Millie 1,Money Heist,40,Alvaro 2,House of Cards,45,Kevin
In the above code, you can see that the index of each row is also there, and almost all the time, we don’t need that.
When storing a DataFrame object into a CSV file using the to_csv method, you probably won’t need to store the preceding indices of each row of the DataFrame object.
See the following code.
# app.py
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'House of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
# Creating Dataframe
df = pd.DataFrame(dict)
# Convert DataFrame to CSV
df.to_csv('data.csv', encoding='utf-8', index=False)
In the last line of code, we have passed three more parameters to the to_csv() function in which we have defined character encoding and index, which we have set False.
In the exported file; we won’t see any index starting from 0, 1, etc. In the output file, there are just a header and content.
Run the above code and see the output.
# data.csv series,episodes,actors Stranger Things,25,Millie Money Heist,40,Alvaro House of Cards,45,Kevin
How to export CSV without headers in Python
Export the CSV file from DataFrame in which there are no headers. Instead, the file starts with the contents.
See the following code.
# app.py
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'House of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
# Creating DataFrame
df = pd.DataFrame(dict)
# Convert DataFrame to CSV
df.to_csv('data.csv', encoding='utf-8', index=False, header=False)
Output
# data.csv Stranger Things,25,Millie Money Heist,40,Alvaro House of Cards,45,Kevin
Selecting only a few columns for CSV Output
To select a specific column in your final CSV output file, you can pass the columns parameter to the to_csv() function like this.
df.to_csv('data.csv', columns=['series', 'actors'], index=False)
Output
# data.csv series,actors Stranger Things,Millie Money Heist,Alvaro Houce of Cards,Kevin
Setting Index Column Name in the CSV
Set the index column name by passing the index_label parameter.
# app.py
df.to_csv('data.csv', index_label='Sr. No')
Output
# data.csv Sr. No,series,episodes,actors 0,Stranger Things,25,Millie 1,Money Heist,40,Alvaro 2,Houce of Cards,45,Kevin
Setting Custom Column Names in the CSV
Set the custom column name in the final output.
# app.py
import pandas as pd
# list of series, episodes, actors
series = ['Stranger Things', 'Money Heist', 'Houce of Cards']
episodes = [25, 40, 45]
actors = ['Millie', 'Alvaro', 'Kevin']
# Creating Dictionary
dict = {'series': series, 'episodes': episodes, 'actors': actors}
# Creating Dataframe
df = pd.DataFrame(dict)
# Convert DataFrame to CSV
df.to_csv('data.csv', index=False, header=['Shows','Parts', 'Characters'])
Output
# data.csv Shows,Parts,Characters Stranger Things,25,Millie Money Heist,40,Alvaro Houce of Cards,45,Kevin
Conclusion
To write the pandas DataFrame to CSV file, you will need DataFrame.to_csv() function. For instance, you might want to use a different separator, change the datetime format, or drop the index when writing to_csv() file has arguments you can pass to address these requirements.
That’s it.
