How to merge multi csv files line by line python

Question

I would expect the code to merge the output csv files line by line. It will write the first lines one after another, then the second lines one after another.

import numpy as np, pandas as pd, os, glob
path = (r'E:\csvfile')
all_files = glob.glob(path + "/*.csv")
li = []

for filename in all_files:
    df = pd.read_csv(filename, index_col=False, header=0)
    li.append(df)

frame = pd.concat(li,axis=0,names=None)
frame.to_csv (r'E:\csvfile\exportC.csv', mode = 'w', index=False)

I tried the shorter code with different parameters. >

import pandas as pd, glob
df = pd.concat(map(pd.read_csv, glob.glob(r'E:\csvfile/*.csv')),axis=0)
df.to_csv (r'E:\csvfile\exportC.csv',mode = 'w', index=False)

file1.csv

0, 10,12
0,11,12
1,15,12

file2.csv

0, 2, 1
1,22, 1
3, 11, 1

file3.csv

0, 4, 6
9, 14, 13
5, 6, 2

The expected output.

0, 10,12
0, 2, 1
0, 4, 6
0,11,12
1,22, 1
9, 14, 13
1,15,12
3, 11, 1
5, 6, 2

Thank you from now.

I suspect that there is a mistake in the expected output. Shouldn't the second line be 0, 2, 1? — Sheldon
– Sheldon, Commented Apr 9, 2020 at 20:41

Sheldon · Accepted Answer · 2020-04-09 20:54:03Z

1

You can first concatenate your three individual dataframes df1, df2 and df3 and then use the sort_index pandas method to reorganize your dataframe based on the index number:

import pandas as pd
df1=pd.read_csv(file1.csv)
df2=pd.read_csv(file2.csv)
df3=pd.read_csv(file3.csv)
df=pd.concat([df1, df2, df3], axis=0).sort_index()

This will return:

   0   1   2
0  0  10  12
0  0   2   1
0  0   4   6
1  0  11  12
1  1  22   1
1  9  14  13
2  1  15  12
2  3  11   1
2  5   6   2

answered Apr 9, 2020 at 20:54

Sheldon

4,7804 gold badges28 silver badges53 bronze badges

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

1 Comment

yhybln Over a year ago

Thank you very much for your reply. It solved my problem but it does not write lines sequentially, it writes in a mixed way within itself. Is there a solution for this?

David Smolinski · Accepted Answer · 2020-04-09 21:12:14Z

0

You could make a dataframe for each CSV, and write a function that loops and appends rows. If the CSVs are big, you can read them in chunks.

while i < df_length:
  df = df.append(df1.iloc[[i],:]).append(df2.iloc[i,:]).append(df3.iloc[i,:])
  i += 1

answered Apr 9, 2020 at 21:12

David Smolinski

5243 silver badges13 bronze badges

1 Comment

yhybln Over a year ago

Thank you very much for your reply. It solved my problem but it does not write lines sequentially, it writes in a mixed way within itself. Is there a solution for this?

Collectives™ on Stack Overflow

How to merge multi csv files line by line python

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related