Merging two big Dataframes causing memory error

Question

enter image description here - Table image I am trying to merge two really big dataframes which gives me memory error. here is the SQL code for which I am trying to convert it to pandas.

SELECT a.period, a.houseid, a.custid, a.productid, b.local_time
FROM table_a
JOIN table_b
  ON a.period = b.period
  AND a.productid = b.productid
  AND b.local_time BETWEEN a.start_time AND a.end_time

Table_a and Table_b contain rows in millions. Trying to join tables with key and also when localtime from table_b is in between range of start and end time from table a.

DF1:

period  houseid custid prodid localtime     
20181001    1   aa  2   01/10/2018 19:04    
20181001    1   zz  9   01/10/2018 15:57    
20181001    1   zz  178 01/10/2018 13:01    
20181001    1   zz  231 02/10/2018 02:51

DF2:

PERIOD    prodid   Name Product_info    START_TIME  END_TIME

20181001    2   Xab GHI 01/10/2018 19:00    01/10/2018 19:29
20181001    2   Xab QQQ 01/10/2018 19:30    01/10/2018 19:59
20181001    2   Xab asd 01/10/2018 20:00    01/10/2018 20:29
20181001    9   S2  Angele  01/10/2018 14:00    01/10/2018 14:59
20181001    9   S2  Road    01/10/2018 15:00    01/10/2018 15:59
20181001    9   S2  Flash   01/10/2018 16:00    01/10/2018 16:59
20181001    9   S2  Simpson 01/10/2018 17:00    01/10/2018 17:29
20181001    178 T3  Chase   01/10/2018 13:00    01/10/2018 13:59
20181001    178 T3  Chase   01/10/2018 14:00    01/10/2018 14:59
20181001    178 T3  Elaine  01/10/2018 15:00    01/10/2018 15:59

result in DF1:

period  houseid custid   prodid    localtime Product_info Name

20181001    1   aa  2   01/10/2018 19:04    GHI     Xab
20181001    1   zz  9   01/10/2018 15:57    Road    S2
20181001    1   zz  178 01/10/2018 13:01    Chase   T3
20181001    1   zz  231 02/10/2018 02:51    None    None

Please help me. Thanks

Hello, Thanks for your reply. After this merge, the table contains 10,57,43,701 rows. — pri
The most straightforward solution is, of course, to take a smaller chunk of the period between start and end. Taking a slice with the bare minimum number of columns required for the merge should help too. You can slowly append them back after that as long as you retain the unique key. — kerwei
In any case, could it be that your period values aren't unique enough? E.g. if they're month labels ('Jan', 'Feb', etc..) you're going to end up with an explosive output. It probably makes more sense to match on, say 'Jan2019', instead of 'Jan' against all 'Jan's from 2000 to 2019 in both directions — kerwei
Thanks for the answer. Could you please show me code to merge this two dataframes. As I know the merging with on= with period, but bit confused how I can use local_time between the range from start to end time. Could you please show me the code if possible. — pri

kerwei kerwei · Accepted Answer · 2019-02-15T09:36:18

Okay, here's my solution. Hope it's good enough for your case. This is all I can offer at my current skill level. The other way is to loop through one table, and apply a conditional check between START_TIME and END_TIME but I decided to go with this approach as you said that you have millions of rows in the table.

The number of steps here depends on the bin of your START_TIME in DF2. I needed 2 steps in my solution because I first join by a half-hourly START_TIME, and then repeated it for the hourly START_TIME.

import pandas as pd
import datetime


df1 = pd.read_excel('my_sample_data.xls')
df2 = pd.read_excel('my_sample_data2.xls')

# Construct a new index column for df1 - based on half-hourly START_TIME
df1['localtime'] = pd.to_timedelta(df1.loc[:, 'localtime'])
df1['START_TIME'] = df1.loc[:,'localtime'].apply(lambda x: x.floor('1800s'))
df1['START_TIME'] = df1.loc[:,'START_TIME'].apply(lambda x: x + datetime.datetime(1970,1,1))

# Drop unneeded colums
df2 = df2.loc[:,['START_TIME', 'prodid', 'Product_info', 'Name']]
df2.set_index(['prodid', 'START_TIME'], inplace=True)

df = df1.join(df2, on=['prodid', 'START_TIME'])
# Good portion
df_done = df.loc[df['Name'].isnull() == False]
# Bad portion
df_nan = df.loc[df['Name'].isnull() == True, ['period', 'houseid', 'custid', 'prodid', 'localtime']]

# Some ranges in DF2 come with hourly frequencies. Repeat the same process above for this case
df_nan['START_TIME'] = df_nan.loc[:,'localtime'].apply(lambda x: x.floor('3600s'))
df_nan['START_TIME'] = df_nan.loc[:,'START_TIME'].apply(lambda x: x + datetime.datetime(1970,1,1))
df_nan = df_nan.join(df2, on=['prodid', 'START_TIME'])

df = pd.concat([df_done, df_nan])
df['localtime'] = df.loc[:, 'localtime'].apply(lambda x: x + datetime.datetime(1970,1,1))

>>>df
     period  houseid custid  prodid           localtime          START_TIME Product_info Name
0  20181001        1     aa       2 2018-01-10 19:04:00 2018-01-10 19:00:00          GHI  Xab
2  20181001        1     zz     178 2018-01-10 13:01:00 2018-01-10 13:00:00        Chase   T3
1  20181001        1     zz       9 2018-01-10 15:57:00 2018-01-10 15:00:00         Road   S2
3  20181001        1     zz     231 2018-02-10 02:51:00 2018-02-10 02:00:00          NaN  NaN

Merging two big Dataframes causing memory error

1 Answers