What's the fastest way to merge multiple csv files by column?

Question

I have about 50 CSV files with 60,000 rows in each, and a varying number of columns. I want to merge all the CSV files by column. I've tried doing this in MATLAB by transposing each csv file and re-saving to disk, and then using the command line to concatenate them. This took my computer over a week and the final result needs to transposed once again! I have to do this again, and I'm looking for a solution that won't take another week. Any help would be appreciated.

Answer 1

[...] transposing each csv file and re-saving to disk, and then using the command line to concatenate them [...]

Sounds like Transpose-Cat-Transpose. Use paste for joining files horizontally.

paste -d ',' a.csv b.csv c.csv ... > result.csv

Answer 2

The Python csv module can be set up so that each record is a dictionary with the column names as keys. You should that way be able to read in all the files as dictionaries, and write them to an out-file that has all columns.

Python is easy to use, so this should be fairly trivial for a programmer of any language.

If your csv-files doesn't have column headings, this will be quite a lot of manual work, though, so then it's perhaps not the best solution.

Since these files are fairly big, it's best not to read all of them into memory once. I'd recommend that you first open them only to collect all column names into a list, and use that list to create the output file. Then you can concatenate each input file to the output file without having to have all of the files in memory.