Merge CSV files with respect to schema of any one of those

Question

I have multiple csv files (around 100s) which has same headers in each of those. However, the sequence of header in every file is different. How shall I merge them in one csv where right data is placed in right column

Ex:

csv has headers : {header1, header2, header3, ... header10}
csv has headers : {header2, header5, header10, ... header9}

I want to merge them such that resultant.csv looks like {header1, header2, header3, ... header10} but has data rightly mapped into each column. Like header1 data from 1.csv is correctly mapped with header1 data from 2.csv and so on.

Read data using pandas then append them is what you need https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.append.html — Phung Duy Phong, Feb 25 '20 at 02:35
load all the csv to dataframes, put them all in a list, df = pd.concat(csv_list, sort = True) — trigonom, Feb 25 '20 at 23:09
@trigonom. Yours also worked. My only question is, is this efficient way? — Neil, Feb 26 '20 at 00:12
no idea, take all the methods that worked and check run time https://stackoverflow.com/questions/1557571/how-do-i-get-time-of-a-python-programs-execution — trigonom, Feb 26 '20 at 07:14

score 4 · Answer 1 · answered Feb 26 '20 at 07:47

It's not Python but it could solve your goal. Using Miller (https://github.com/johnkerl/miller), a great command line utility, the command is

mlr --csv unsparsify ./*.csv >output.csv

If you want also to reorder the fields

mlr --csv unsparsify ./*.csv then reorder -f header1, header2, header3, ... header10 >output.csv

Merge CSV files with respect to schema of any one of those

1 Answers1