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.