Python: Memory efficient, quick lookup in python for 100 million pairs of data?

user_14722297 0
Viewed 358

This is my first time asking a question on here, so apologies if I am doing something wrong.

I am looking to create some sort of dataframe/dict/list where I can check if the ID in one column has seen a specific value in another column before.

For example for one pandas dataframe like this (90 million rows):

ID  Another_ID
1   10
1   20
2   50
3   10
3   20
4   30

And another like this(10 million rows):

ID  Another_ID
1   30
2   30
2   50
2   20
4   30
5   70

I want to end up with a third column that is like this:

ID  Another_ID seen_before
1   30         0
2   30         0
2   50         1
2   20         0
4   30         1
5   20         0

I am looking for a memory efficient but quick way to do this, any ideas? Thanks!

pandas dataframe numpy dictionary python
3 Answers

Note: how is important on merge. see the comments in the code as well. np.where is quite efficient but I have never worked with 100 million rows. Request to OP to let us know how it goes.

Code:

import pandas as pd
import numpy as np

left = pd.DataFrame(data = {'ID':[1, 1, 2, 3, 3, 4], 'Another_ID': [10, 20, 50, 10, 20, 30]})
right = pd.DataFrame(data = {'ID':[1 , 2 , 2 , 2 , 4 , 5], 'Another_ID': [30 , 30 , 50 , 20 , 30 , 70]})
print(df1, '\n', df2)
res = pd.merge(left, right, how='right', on='ID')

 # Another_ID_x showed up as float despite dtype as int on both right and left
res.fillna(value=0, inplace=True)  # required for astype to work in next step
res['Another_ID_x'] = res['Another_ID_x'].astype(int)

res['Another_ID_x'] = np.where(res.Another_ID_x == res.Another_ID_y, 1, 0 )
res.rename(columns={'Another_ID_x': 'seen_before'}, inplace=True)
res.drop_duplicates(inplace=True)
print(res)

Output:

    Another_ID
ID
1           10
1           20
2           50
3           10
3           20
4           30
     Another_ID
ID
1           30
2           30
2           50
2           20
4           30
5           70
   ID  seen_before  Another_ID_y
0   1            0            30
2   2            0            30
3   2            1            50
4   2            0            20
5   4            1            30
6   5            0            70
user_21239970

Merge is a good idea, here, you want to merge on both columns:

df1['seen_before'] = 1

df2.merge(df1, on=['ID', 'Another_ID'], how='left')

Output:

   ID  Another_ID  seen_before
0   1          30          NaN
1   2          30          NaN
2   2          50          1.0
3   2          20          NaN
4   4          30          1.0
5   5          70          NaN

Note: this assumes that df1 has no duplicates. If you are not sure about this, replace df1 with df1.drop_duplicates() in merge.

user_42384080

Update:

Thanks to everybody for all the replies on my first post!

@Quang Hong's solution worked amazing in this case as there were so many rows.

Total time it took on my laptop was 36.6s
user_147222970
Related