Python Pandas Data Frame to CSV
In the previous tutorial, we learned how to create a CSV file based on the Pandas Data frame. There, we used a single data frame based on a list and then created a CSV file using Pandas DF’s to_csv method.
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 did the above code work?
- First of all, we imported the Pandas library
- This is followed by creating two lists with different columns
- The data frames are created where we specify those lists and column names
- Finally, we used the 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
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 the 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: