Merge DataFrames with the all combinations of Pks

user_4544413 0
Viewed 1578

I'm working with two dataframes one for user data:

USER_ID_hash    SESSION_ID_hash PURCHASEID_hash SEX_ID
1118263 02445d553744423d4f0ea545b5d0b8e0    47de469df74e5e42a24bfd211fb658c6        m
2669790 1c7af4a49d445da0f6a4b985623e9d78    9a347a9e4ab981bb5e9643d19830ff8e        m
1535911 839938507de5eb1a81596499f22cb777    b7dd06ea7277f19c24d322b6257cd73f        f
680107  a99a661aedc1597ce13dc85f96fcb23d    fdad39b2f01378c59dc53cf5454606fe        m
895968  7c233e047f46653273aec2407e46e034    952ab03e8abaa8f9942ea0aaca79bca4        f
1963366 4349579a3398f3155363d67bb5742ca1    225977cc2924ea37d49f190e12aa8511        m
1520880 3964e87932aa3b61f2cc1a58345c2b4c    2a8964196164ca227d2ac7fe14b3f303        f
30698   7afc4e216672636f61dfbad40d9816c6    6bd8061cf66e8d98f6c41c56fd2a86ec        m
1370217 0c7eb402632ace5f62fc81eb6139e448    f8e104ef090e8504c7b57b4dce663af3        m
1171336 bad370d216ccd40302da3fb505ba2501    0cdd2301182eb1097b478dc07380c198        m

And another one with prodcut details: 

    COUPON_ID_hash  DISPEND_MONTH   DISPFROM_MONTH  VALIDFROM_MONTH VALIDEND_MONTH
70678   34a80aff2d963d28013110789cfdfdb9    11  11      
91899   f4ecdf6a1ccd5c4ae83c7a83a9f68454    1   1   1.0 2.0
37526   f0ab02ced0e91e097abc1b3c89a9d9b0    9   9   9.0 2.0
62462   e97a601bc242db11579f7eada35205fd    10  10  11.0    4.0
127695  771941be288930b649c7bb214efe2b07    3   3   3.0 9.0
136313  2faa0e168770fb01a21f3ddf1b23aeab    4   4       
156974  c340a7ab46f19c1ba3b57b513f16bead    6   6   6.0 9.0
158450  d84eb4dfc6d0b7bf0e8c50c06d14e4fe    6   6       
9992    88419bf4acc9f7e5c3529fc3bde29da6    7   7   7.0 8.0
73509   530aa1bf00c00f02255eeea81559b932    11  11

I want to merge them based on a combination of their PKs, which are: USER_ID_hash and COUPON_ID_hash and create with this a new feature that will include all possible cases for this two PKs.

For example, for the first User_id I will have ten rows, one for each Coupon_id and the same for the other 9 User_ids.

Is there a function to make this join efficiently? F1,F2...FN are the features of the input dataframes.

02445d553744423d4f0ea545b5d0b8e0-34a80aff2d963d28013110789cfdfdb9 F1  F2  FN
02445d553744423d4f0ea545b5d0b8e0-f4ecdf6a1ccd5c4ae83c7a83a9f68454 F1  F2  FN
02445d553744423d4f0ea545b5d0b8e0-f0ab02ced0e91e097abc1b3c89a9d9b0 F1  F2  FN
pandas merge join python
1 Answers

It looks like you want the cartesian product of both dataframes. On approach would be to add a key which is repeated for all values in both dataframes, and merge them using this key.

The reason behind this is that as stated in the documentation, when key combinations appear more than once in both dataframes pd.merge does the cartesian product:

It is worth spending some time understanding the result of the many-to-many join case. In SQL / standard relational algebra, if a key combination appears more than once in both tables, the resulting table will have the Cartesian product of the associated data.

Note that all types of merge will yield the same result in this case, here left is chosen simply so that results are sorted with the same order of appearance as in df1:

cp = df1.assign(key=0).merge(df2.assign(key=0), how='left', on = 'key')
cp[['USER_ID_hash','COUPON_ID_hash']]

       USER_ID_hash         COUPON_ID_hash
0        1118263  f4ecdf6a1ccd5c4ae83c7a83a9f68454
1        1118263  34a80aff2d963d28013110789cfdfdb9
2        1118263  f0ab02ced0e91e097abc1b3c89a9d9b0
3        1118263  e97a601bc242db11579f7eada35205fd
4        1118263  771941be288930b649c7bb214efe2b07
5        1118263  2faa0e168770fb01a21f3ddf1b23aeab
6        1118263  c340a7ab46f19c1ba3b57b513f16bead
7        1118263  d84eb4dfc6d0b7bf0e8c50c06d14e4fe
8        1118263  88419bf4acc9f7e5c3529fc3bde29da6
9        1118263  530aa1bf00c00f02255eeea81559b932
10       2669790  f4ecdf6a1ccd5c4ae83c7a83a9f68454
11       2669790  34a80aff2d963d28013110789cfdfdb9
12       2669790  f0ab02ced0e91e097abc1b3c89a9d9b0
user_96986840
Related