Htykuut

I am trying to merge two huge dataframes (4+ millions each) that have the following structure:

Dataframe A:

     date    Fruit        a    b    c    d

     01      "apple"      0    3    5    1

     03      "apple"      8    2    7    2

     02      "banana"     1    4    3    5

     04      "banana"     3    5    2    6

     03      "pineapple"  2    6    4    6

     05      "pineapple"  3    5    7    9

Dataframe B:

     date   Fruits                         x    y    z 

     01     "apple, pear, strawberry"      a    n    q 

     02     "banana, apple, coconut"       b    m    p 

     03     "pineapple, pear, banana"      c    s    o

     04     "banana, apple, coconut"       d    f    v 

     05     "pineapple, pear, banana"      r    ñ    t  

What I am trying to achieve is a third dataframe with the following structure:

Dataframe C:

     date   Fruit        a    b    c    d    x    y    z

     01     "apple"      0    3    5    1    a    n    q

     03     "apple"      0    3    5    1    0    0    0

     02     "banana"     1    4    3    5    b    m    p

     04     "banana"     1    4    3    5    d    f    v

     03     "pineapple"  2    6    4    6    c    s    o

     05     "pineapple"  2    6    4    6    r    ñ    t

      ...

I had already tried something like:

test = market_test.assetCode.apply(lambda x : news_test.assetCodes.str.find(x)>=0)

But my kernel breaks, I also had tried using a for cycle to expand the fruit column of B dataframe into a 'fruit-b' column, keeping the data from the other B columns and then merging between the date column and the 'fruit-B' columns, but the time of execution is too high.

Is there a way of obtaining dataframe C using dataframe A and B that does not consume a lot of time and memory?

Fruit and Fruits columns type is string.

Use:

print (df_A)



   date      Fruit  a  b  c  d

0     1      apple  0  3  5  1

1     3      apple  8  2  7  2

2     2     banana  1  4  3  5

3     4     banana  3  5  2  6

4     3  pineapple  2  6  4  6

5     5  pineapple  3  5  7  9



print (df_B)



   date                   Fruits  x  y  z

0     1  apple, pear, strawberry  a  n  q

1     2   banana, apple, coconut  b  m  p

2     3  pineapple, pear, banana  c  s  o

3     4   banana, apple, coconut  d  f  v

4     5  pineapple, pear, banana  r  ñ  t

import pandas as pd

import numpy as np



# Split the strings into list.

df_B.Fruits = df_B.Fruits.str.split(', ')



# reindex and repeat on length of list

temp = df_B.reindex(df_B.index.repeat(df_B.Fruits.str.len())).drop('Fruits',1)



temp['Fruit'] = np.concatenate(df_B.Fruits.values)



df_C = df_A.merge(temp, on=['date','Fruit'], how='left').fillna(0)



print (df_C)



   date      Fruit  a  b  c  d  x  y  z

0     1      apple  0  3  5  1  a  n  q

1     3      apple  8  2  7  2  0  0  0

2     2     banana  1  4  3  5  b  m  p

3     4     banana  3  5  2  6  d  f  v

4     3  pineapple  2  6  4  6  c  s  o

5     5  pineapple  3  5  7  9  r  ñ  t