23

I'm downloading two datasets from two different databases that need to be joined. Each of them separately is around 500MB when I store them as CSV. Separately the fit into the memory but when I load both I sometimes get a memory error. I definitely get into trouble when I try to merge them with pandas.

What is the best way to do an outer join on them so that I don't get a memory error? I don't have any database servers at hand but I can install any kind of open source software on my computer if that helps. Ideally I would still like to solve it in pandas only but not sure if this is possible at all.

To clarify: with merging I mean an outer join. Each table has two row: product and version. I want to check which products and versions are in the left table only, right table only and both tables. That I do with a 

pd.merge(df1,df2,left_on=['product','version'],right_on=['product','version'], how='outer')
Nickpick
  • 6,163
  • 16
  • 65
  • 116
  • What OS are you running? – user2027202827 Jun 10 '16 at 21:10
  • Please specify in more detail what you expect this program to do and on what fields the join is supposed to work. In the best case, you could just merge the two CSV files together (line by line). Also, if you could post the code that results in the memory error, this would help a lot. – Simon Kirsten Jun 10 '16 at 21:10
  • I have added more details to the original question – Nickpick Jun 10 '16 at 21:14

2 Answers2

36

This seems like a task that dask was designed for. Essentially, dask can do pandas operations out-of-core, so you can work with datasets that don't fit into memory. The dask.dataframe API is a subset of the pandas API, so there shouldn't be much of a learning curve. See the Dask DataFrame Overview page for some additional DataFrame specific details.

import dask.dataframe as dd

# Read in the csv files.
df1 = dd.read_csv('file1.csv')
df2 = dd.read_csv('file2.csv')

# Merge the csv files.
df = dd.merge(df1, df2, how='outer', on=['product','version'])

# Write the output.
df.to_csv('file3.csv', index=False)

Assuming that 'product' and 'version' are the only columns, it may be more efficient to replace the merge with:

df = dd.concat([df1, df2]).drop_duplicates()

I'm not entirely sure if that will be better, but apparently merges that aren't done on the index are "slow-ish" in dask, so it could be worth a try.

jpgard
  • 653
  • 7
  • 15
root
  • 32,715
  • 6
  • 74
  • 87
  • Great but what if df1 doesn't fit into memory either? – Nickpick Jun 10 '16 at 21:40
  • 2
    That's the entire point of `dask`. It does manipulations out-of-core, so you can work with data that doesn't fit into memory. It essentially extends the size of convenient datasets from “fits in memory” to “fits on disk”. – root Jun 10 '16 at 21:45
  • is there any way to do pivot tables with large dataframes? Dask doesn't seem to offer that functionality – Nickpick Jun 13 '16 at 09:32
  • 2
    Dask seems to be very buggy. Even simple merge operations give error messages. Column names contain \r at the end etc. Is there any alternative? – Nickpick Jun 15 '16 at 11:54
  • 1
    Dask is under development, it also doesn't support multi-indexes. :( – Diego Queiroz Nov 02 '17 at 19:57
1

I would recommend you to use RDBMS like MySQL for that...

So you would need to load your CSV files into tables first.

After that you can perform your checks:

which products and versions are in the left table only

SELECT a.product, a.version
FROM table_a a
LEFT JOIN table_b b
ON a.product = b.product AND a.version = b.version
WHERE b.product IS NULL;

which products and versions are in the right table only

SELECT b.product, b.version
FROM table_a a
RIGHT JOIN table_b b
ON a.product = b.product AND a.version = b.version
WHERE a.product IS NULL;

in both

SELECT a.product, a.version
FROM table_a a
JOIN table_b b
ON a.product = b.product AND a.version = b.version;

Configure your MySQL Server, so that it uses at least 2GB of RAM

You may also want to use MyISAM engine for your tables, in this case check this

It might work slower compared to Pandas, but you definitely won't have memory issues.

Another possible solutions:

  • increase your RAM
  • use Apache Spark SQL (distributed DataFrame) on multiple cluster nodes - it will be much cheaper though to increase your RAM
Community
  • 1
  • 1
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419