how to merge data frames with same format in python

Question

Looked at other similar questions, but none has the use case I have. I have multiple files with the same format and no header

file1
id, value 
1, 100
2, 150 
...

file2
10, 500
11, 510
....

I would like to "merge" them to have

id, value
1, 100
2, 150
...
10, 500
11, 510
...

tried merge, append, concat but couldn't achieve the end result I am looking for.

df2 = pd.DataFrame(columns=['id','value'])
df2.columns = ['id','value']
for file_name in os.listdir(work_dir):
    df1 = pd.read_csv(work_dir+'/'+file_name, header=None)
    df1.columns = ['id','value']
    df2 = pd.merge(df2,df1, on ='id')

Or any other suggestion to load multiple files in to a data frame appreciated. I do have another data frame coming from a db will be merged same way so merge is a question too.

That is not a `merge` but an `append` (very important semantics in data analysis world). Also, your post suggests the first file does have headers and all else doesn't? — Parfait, Jun 21 '18 at 20:27

score 0 · Accepted Answer · answered Jun 21 '18 at 20:31

0

You are looking for append. Try this:

df1.append(df2)

answered Jun 21 '18 at 20:31

Zachary Oldham

838
1
5
21

score 0 · Answer 2 · answered Jun 21 '18 at 20:32

0

concat should also work for you. https://pandas.pydata.org/pandas-docs/stable/merging.html

You should also name each column upon import...

pd.read_csv(work_dir+'/'+file_name, header=None, names=['id', 'value'])

See Concatenate rows of two dataframes in pandas

answered Jun 21 '18 at 20:32

Erik Z

412
3
11

concat is giving me multiple columns, instead of two – demircioglu Jun 21 '18 at 21:50
You might need to also add ‘axis=1’ in the method call – Erik Z Jun 23 '18 at 03:12

how to merge data frames with same format in python

2 Answers2