CODEWITHSUNDEEP
pythonpandascsv
Baobab1988
Baobab1988

Reputation: 715

Merge rows from multiple CSV files into one CSV file and keep same number of columns

I have 3 CSV files (separated by ',') with no headers and need to concat them into one file:

file1.csv

United Kingdom     John

file2.csv 

France  Pierre

file3.csv 

Italy   Marco

expected result:

United Kingdom    John
France            Pierre
Italy             Marco

my code:

import pandas as pd

df = pd.read_csv('path/to/file1.csv', sep=',')
df1 = pd.read_csv('path/to/file2.csv', sep=',')
df2 = pd.read_csv('path/to/file3.csv', sep=',')

df_combined = pd.concat([df,df1,df2])

df_combined.to_csv('path/to/output.csv')

the above gives me data merged but it added rows from my CSV files as new columns and rows, instead to add only new rows to existing two columns:

United Kingdom     John
                          France     Pierre
                                               Italy    Marco

Could someone please help with this? Thank you in advance!

Upvotes: 0

Views: 2428

Answers (2)

Narendra Prasath
Narendra Prasath

Reputation: 1531

Read csv as follows

df = pd.read_csv('path/to/file1.csv', sep=',', header=None)
df1 = pd.read_csv('path/to/file2.csv', sep=',', header=None)
df2 = pd.read_csv('path/to/file3.csv', sep=',', header=None)

You can concatenate as bellow 

df.reset_index(inplace=True, drop=True)
df1.reset_index(inplace=True, drop=True)
df2.reset_index(inplace=True, drop=True)
pd.concat([df,df1,df2], axis=0)

output as expected

enter image description here

Upvotes: 1

Alex
Alex

Reputation: 69

Pandas usually infer the column name from the first row when reading CSV file. One thing you can do here is to check each data frame's header, which you should expect to see the sample data is treated as header.

In order to override this default behaviour, you can use names field to explicitly specify column names, like df1=pd.read_csv("file1.csv", names=['country','name']). Then pandas would be able to merge columns accordingly.

Upvotes: 1

Related Questions