Which tool should I use for combining this large dataset? This is my first foray into data science and I've hit a snag before even getting to the analysis.

I have 40 CSV files; each one contains 2 columns - a time and value column.

I would like to consolidate this into one table by doing an outer join of the time column for all files, so that the final file would contain 1 time column and 40 value columns. I attempted this using pandas merge method, but my local machine ran out of memory before it could complete. I made sure there was nothing fundamentally wrong with the code by simply combining 8 of the 40 files and got the desired result.

At that point I decided to spin up a more powerful cloud compute instance on AWS; I chose one with absurdly high RAM so I'm working with 190 GB rather than 8. It got further, but got the same memory error around the 30th file. I should also mention that each file as quite a few rows - around 180K.

At that point I decided that I must be going about this the wrong way. I don't think pandas is meant as a tool for combining such large datasets. In my last job I used SQL pretty extensively and something like that seems more equipped. My next idea was to try to do it in AWS Athena, which is a SQL like service that can integrate with the csv files in S3. I assume there is no standard solution to this issue, but I just want to see if I'm way off base or going in the right direction.

Thanks!!
I would suggest trying to do it in batches. The underlying issue could well still be memory related in some way, as the merge method makes copies of its input and so is not memory efficient at all.

As an example, you could read in 10 files, create the desired output, as you have done already. Repeat this for files 10-20, then 20-30 then 30-40. The finally four the four files that you have created. It is a bit of an annoyance, but sometimes these little workarounds get the job done.



[EDIT]

Another option might be to use the more involved memory management during reading, option via the chunksize argument of pd.read_csv(). This will read parts of the file into memory in chunks, as the name suggests. If you do this in a loop, it should put an upper limit on the memory usage. For example (untested):

chunksize = 50e6        # 50 Mb

for single_file in list_of_file_paths:
    for i, chunk in enumerate(pd.read_csv(single_file, chunksize=chunksize):
        if i == 0:
            result = chunk
        else:
            result = pd.merge((result, chunk), copy=False, how= ...)


You may need to do something with the indivdual chunks before merging them.

Additionally, note that I set the copy argument in merge to False, which might help - the documentation is a little vague as to how it saves memory.