I have multiple pandas dataframes, and hope to write them as one CSV file. What is the most straightforward way?
For example, from following four dataframes,
how can I create below CSV?
Note The dataframes all have the same dimensions.
-
Do the dataframes all have the same shape?unutbu– unutbu2015-06-14 13:10:59 +00:00Commented Jun 14, 2015 at 13:10
-
@unutbu Yes. All the dataframes have the same shape.Light Yagmi– Light Yagmi2015-06-14 13:12:42 +00:00Commented Jun 14, 2015 at 13:12
Add a comment
|
2 Answers
A very straightforward way would be to concat pairs horizontally, concat the results vertically, and write it all out using to_csv:
import pandas as pd
pd.concat([
pd.concat([df1, df2], axis=1),
pd.concat([df3, df4], axis=1)]).to_csv('foo.csv')
A possibly more memory-conserving way would be to write it piecemeal:
with open('foo.csv', 'w') as f:
pd.concat([df1, df2], axis=1).to_csv(f)
with open('foo.csv', 'a') as f:
pd.concat([df3, df4], axis=1).to_csv(f, header=False)
Omitting headers=False would repeat the headers.
4 Comments
Light Yagmi
Thank you. Is it possible to keep headers visible for df3 and df4? In my question, the destination CSV has four rows and third one is the headers of df3 and df4.
Ami Tavory
@MichaelTamillow Thanks!
S.EB
@AmiTavory how to write each dataframe in diferent sheets of the same csv file
Ami Tavory
@S.EB That's mainly a question of how to read multiple sheets. It's fairly doable, but a separate question. Could you open one on it? Plenty will help you, and if you link here, I'll be happy to look at it too.
For anyone that needs to append multiple dataframes to same csv vertically, you can use this:
with open('all_dfs.csv','a') as f:
for df in list_of_dfs:
df.to_csv(f)
f.write("\n")
1 Comment
DtechNet
Why would you not just concat all the dataframes together using pd.concat([df1, df2, df3]) then simply write the new dataframe to a csv with pd.to_csv? They asked for the most straightforward. Let the Pandas engine handle opening and closing connections etc.,...As I write this I see the accepted answer below. Good suggestion anyway - it would work though prone to issues with memory and possibly formatting issues depending on the consistency of column naming and data types in all the df's.