How to merge csv file line by line python

Question

Hello I am new to Python and I am trying to merge these two files fileA and fileB into one using this piece of code

    a = pandas.read_csv(fileA)
    b = pandas.read_csv(fileB)
    b = b.dropna(axis=1)
    merged = b.merge(a, on="day")
    merged.to_csv(output, index=False)

But the problem is instead of it merging line by line it merges the first line of fileA with all lines of fileB! below you'll find an example

content of fileA

numb,day

one,sat
two,sun  
three,mon

content of fileB

day,month,color,shape

sat,apr,black,circle
sun,may,white,triangle
mon,jun,red,square

expected output

numb,day,month,color,shape

one,sat,apr,black,circle
two,sun,may,white,triangle
three,mon,jun,red,square

what I am actually getting

numb,day,month,color,shape

one,sat,apr,black,circle
one,sat,may,white,triangle
one,sat,mon,jun,red,square
two,sun,apr,black,circle
two,sun,may,white,triangle
two,sun,jun,red,square
.
.
.

So how can I merge the files line by line instead of all of this, or what exactly am I doing wrong here?

I am using Python 3.7

try [`concat`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html) : `df_out = pd.concat([a, b], axis=1)` — Chris Adams, Mar 10 '20 at 14:29
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Chris Adams, Mar 10 '20 at 14:38
Indeed,`concat` is the way to go. You used `merge`, which kinda does a JOIN under the hood, hence the undesired result. — arnaud, Mar 10 '20 at 14:39
No, it’s a pandas method for concatenating DataFrames. a and b are DataFrames after read_csv — Chris Adams, Mar 10 '20 at 15:28
oh I see now I thought I had to change the function I had already. and it worked thank you so much @ChrisA — raghad, Mar 10 '20 at 16:09

score 3 · Accepted Answer · answered Mar 10 '20 at 16:31

3

Use pandas.concat to combine DataFrames:

a = pandas.read_csv(fileA)
b = pandas.read_csv(fileB)
b = b.dropna(axis=1)

merged = pd.concat([a, b], axis=1)

merged.to_csv('output.csv', index=False)

answered Mar 10 '20 at 16:31

Chris Adams

18,389
4
22
39

score 1 · Answer 2 · answered Mar 10 '20 at 16:57

1

You can use pandas.join

a = pandas.read_csv(fileA)
b = pandas.read_csv(fileB)
fileA.join(fileB.set_index('day'), on='day')

answered Mar 10 '20 at 16:57

Zalak Bhalani

979
7
15

How to merge csv file line by line python

2 Answers2