Python Pandas Data Frame to CSV
In the previous tutorial, we learned how to create a CSV file based on Pandas Data frame. There, we used a single data frame based on a list and then created a CSV file by using Pandas DF’s to_csv method.
In this tutorial, we are going to share examples of creating CSV files based on multiple data frames.
For the sake of simplicity, we are using almost similar types of data in the lists.
An example of creating CSV by two data frames
In this example, we have two product lists that we will use to create a CSV file.
See the code and output below and we will explain how it worked.
The code:
import pandas as pd
#First Data Frame's data
product_list1 = [ ['p001', "Wheat"],
['p002', "Rice"],
['p003', "Sugar"]
]
#Second Data Frame's data
product_list2 = [ ["25.99","Out of Stock"],
["8","In Stock"],
["10","In Stock"]
]
#Creating data frame objects
df_1 = pd.DataFrame (product_list1, columns = ['Product ID', 'Product Name'])
df_2 = pd.DataFrame (product_list2, columns = ['Price', 'Status'])
#Concat and Write to CSV
pd.concat([df_1, df_2], axis=1).to_csv('multi_dfs.csv')
Output:
How above code work?
- First of all, we imported the Pandas library
- This is followed by creating two lists with different columns
- Then data frames are created where we specified those lists and column names
- Finally, we used concat method to concatenate both data frames horizontally.
Writing without index column
In the above output images, you may notice an index column “A” with numbers starting at 0,1,..
To avoid this, you may use the index = False attribute in the to_csv method as follows:
Code:
import pandas as pd
#First Data Frame's data
product_list1 = [ ['p001', "Wheat"],
['p002', "Rice"],
['p003', "Sugar"]
]
#Second Data Frame's data
product_list2 = [ ["25.99","Out of Stock"],
["8","In Stock"],
["10","In Stock"]
]
#Creating data frame objects
df_1 = pd.DataFrame (product_list1, columns = ['Product ID', 'Product Name'])
df_2 = pd.DataFrame (product_list2, columns = ['Price', 'Status'])
#Concat and Write to CSV without Index column
pd.concat([df_1, df_2], axis=1).to_csv('multi_dfs.csv', index=False)
Output in Notepad:
An example of writing data frames one after another vertically
You may use the append mode in .to_csv method for writing data vertically. That is, the second DF data goes down after the first DF, and so on.
See the code below and output:
import pandas as pd
#First Data Frame's data
product_list1 = [ ['p001', "Wheat"],
['p002', "Rice"],
['p003', "Sugar"]
]
#Second Data Frame's data
product_list2 = [ ["25.99","Out of Stock"],
["8","In Stock"],
["10","In Stock"]
]
#Creating data frame objecst
df_1 = pd.DataFrame (product_list1, columns = ['Product ID', 'Product Name'])
df_2 = pd.DataFrame (product_list2, columns = ['Price', 'Status'])
#Using mode='a' for the second DF and so on
df_1.to_csv('multi_dfs.csv', index=False)
df_2.to_csv('multi_dfs.csv', mode='a', index=False)
Output:
Second way
In order to write second/other data frames vertically, you may iterate through each data frame with Python open method and append the df.
See an example below where we will write the same data frames as in the above examples but vertically:
import pandas as pd
#First Data Frame's data
product_list1 = [ ['p001', "Wheat"],
['p002', "Rice"],
['p003', "Sugar"]
]
#Second Data Frame's data
product_list2 = [ ["25.99","Out of Stock"],
["8","In Stock"],
["10","In Stock"]
]
#Creating data frame objecst
df_1 = pd.DataFrame (product_list1, columns = ['Product ID', 'Product Name'])
df_2 = pd.DataFrame (product_list2, columns = ['Price', 'Status'])
all_dfs = [df_1,df_2]
#Write to CSV vertically
with open('multi_dfs.csv','a') as f:
for df in all_dfs:
df.to_csv(f, index=False)
Result:
