2
$\begingroup$

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!!

Improve this question
$\endgroup$
5
  • 1
    $\begingroup$ Have you verified that the data values are unique in every file? Having them non-unique will cause a huge growth in the join size. $\endgroup$
    – Paul
    Jun 19, 2018 at 15:14
  • 1
    $\begingroup$ Do you have complete records for each time value? ie do the 40 CSVs have all the same time values? In which case you can sort and paste them together. Otherwise, how sparse is your 180k x 40 matrix going to be? Worst case scenario is 180k x 180k rows x 40 columns, which is almost big data [edit, I miscalced]. $\endgroup$
    – Spacedman
    Jun 19, 2018 at 15:15
  • 1
    $\begingroup$ I do utilize the drop_duplicates method before joining $\endgroup$
    – David Masters
    Jun 19, 2018 at 15:15
  • 1
    $\begingroup$ They do not all have the exact same time values, but there is a big overlap. You're right - comparatively speaking its not really "big data", but I thought perhaps this particular objective was not meant for the pandas library given its limitations. However, I could certainly be wrong about that $\endgroup$
    – David Masters
    Jun 19, 2018 at 15:17
  • 1
    $\begingroup$ How many Mb are the first 30 files in total? Just wondering at which rough memory boundary it is crashing. $\endgroup$
    – n1k31t4
    Jun 19, 2018 at 16:49

2 Answers 2

Reset to default
2
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • 1
    $\begingroup$ I'm going to try this and report back, but from a high-level view, I can't grasp why this would save memory. Through each iteration of the file, I am resetting the value of the main dataframe to the newly merged result. I'm not keeping each past dataframe in memory individually $\endgroup$
    – David Masters
    Jun 19, 2018 at 15:19
  • 1
    $\begingroup$ @DavidMasters - I added another approach that might be of use. See my edit. $\endgroup$
    – n1k31t4
    Jun 19, 2018 at 16:56
1
$\begingroup$

Use Pentaho Data Integrator. Download from Sourceforge, unzip, and click on spoon.bat (or spoon.sh). Find a Getting Started among the plenty on the net, invest a few minutes to learn how to read a CSV file (once you know, it takes three clicks) and ask the question about joins on SOF, tag pentaho.

Improve this answer
$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.