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

Indeed,concat is the way to go. You used merge, which kinda does a JOIN under the hood, hence the undesired result. — arnaud
– arnaud, Commented Mar 10, 2020 at 14:39
oh I see now I thought I had to change the function I had already. and it worked thank you so much @ChrisA — raghad
– raghad, Commented Mar 10, 2020 at 16:09

Chris Adams · Accepted Answer · 2020-03-10 16:31:12Z

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, 2020 at 16:31

Chris Adams

18.7k4 gold badges26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zalak Bhalani · Accepted Answer · 2020-03-10 16:57:55Z

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, 2020 at 16:57

Zalak Bhalani

1,1449 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to merge csv file line by line python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related