Koalas: join/merge problems

Created on 16 Feb 2021 · 9Comments · Source: databricks/koalas

I'm trying to merge 2 kdf that actually are subsets of the same kdf.
The main kdf contains all the log events of the all _transport_order_number_ :
E.g.,: transport_order_number== 696530708053

kdf=ks.DataFrame({'transport_order_number': {11059585: ('696530708053'),  36538499: '696530708053',  41914814: '696530708053',  58878846: '696530708053',  83502171: '696530708053',  87335732: '696530708053',  89651819: '696530708053'},
 'event_description': {11059585: 'PIEZA EN RUTA AL DESTINATARIO',  36538499: 'TRANSFERENCIA RUTA (OTBCS)',  41914814: 'RECEPCION TRANS. PIEZA',  58878846: 'RETIRO DESDE PDT',  83502171: 'RECEPCION TRANS. CONT.',  87335732: 'RECEPCIONADA',  89651819: 'PIEZA ENTREGADA A DESTINATARIO'},
 'event_date': {11059585: ('2020-12-15 09:05:12.743000'),  36538499: ('2020-12-15 06:42:22.477000'),  41914814: ('2020-12-15 06:42:34.083000'),  58878846: ('2020-12-14 13:41:00'),  83502171: ('2020-12-15 06:42:00'),  87335732: ('2020-12-14 14:41:00'),
  89651819: ('2020-12-15 12:53:00')}})

so I made two subset using ks.loc[] to get just the 'RECEPCIONADA' AND 'RETIRO DESDE PDT ' events with:

recepcion=kdf.loc[kdf['event_description']=='RECEPCIONADA']

retiro=kdf.loc[kdf['event_description']=='RETIRO DESDE PDT']

Then when I try to marge this 2 subset (recepcion & retiro) using 'how=outer' I got only NaN values ...

ks.merge(fecha_recepcion, fecha_retiro, on='transport_order_number', how='outer', suffixes=('_recepcion','_retiro'))

Using Pandas I got the expected results

Pandas DF:

pd.merge(kdf1, kdf2, on='transport_order_number', how='outer')

bug

Source

FJLD

Most helpful comment

@FJLD The fix has been merged. It will be available in the next release. Thanks again for the report!

ueshin on 22 Feb 2021

👍2 😄1

All 9 comments

In order to avoid this problem, I had to recall the data 2 times to create the subsets.

A_kdf=ks.DataFrame({'transport_order_number': {11059585: ('696530708053'), 36538499: '696530708053', 41914814: '696530708053', 58878846: '696530708053', 83502171: '696530708053', 87335732: '696530708053', 89651819: '696530708053'}, 'event_description': {11059585: 'PIEZA EN RUTA AL DESTINATARIO', 36538499: 'TRANSFERENCIA RUTA (OTBCS)', 41914814: 'RECEPCION TRANS. PIEZA', 58878846: 'RETIRO DESDE PDT', 83502171: 'RECEPCION TRANS. CONT.', 87335732: 'RECEPCIONADA', 89651819: 'PIEZA ENTREGADA A DESTINATARIO'}, 'event_date': {11059585: ('2020-12-15 09:05:12.743000'), 36538499: ('2020-12-15 06:42:22.477000'), 41914814: ('2020-12-15 06:42:34.083000'), 58878846: ('2020-12-14 13:41:00'), 83502171: ('2020-12-15 06:42:00'), 87335732: ('2020-12-14 14:41:00'), 89651819: ('2020-12-15 12:53:00')}})

B_kdf=ks.DataFrame({'transport_order_number': {11059585: ('696530708053'), 36538499: '696530708053', 41914814: '696530708053', 58878846: '696530708053', 83502171: '696530708053', 87335732: '696530708053', 89651819: '696530708053'}, 'event_description': {11059585: 'PIEZA EN RUTA AL DESTINATARIO', 36538499: 'TRANSFERENCIA RUTA (OTBCS)', 41914814: 'RECEPCION TRANS. PIEZA', 58878846: 'RETIRO DESDE PDT', 83502171: 'RECEPCION TRANS. CONT.', 87335732: 'RECEPCIONADA', 89651819: 'PIEZA ENTREGADA A DESTINATARIO'}, 'event_date': {11059585: ('2020-12-15 09:05:12.743000'), 36538499: ('2020-12-15 06:42:22.477000'), 41914814: ('2020-12-15 06:42:34.083000'), 58878846: ('2020-12-14 13:41:00'), 83502171: ('2020-12-15 06:42:00'), 87335732: ('2020-12-14 14:41:00'), 89651819: ('2020-12-15 12:53:00')}})

A_kdf_retiro=A_kdf.loc[A_kdf['event_description']=='RETIRO DESDE PDT']
B_kdf_recepcion=B_kdf.loc[B_kdf['event_description']=='RECEPCIONADA']

B_kdf_recepcion.merge(A_kdf_retiro, on='transport_order_number', how='outer')

FJLD on 17 Feb 2021

Sorry, could you clarify what the problem is and what the expected result is?

I tried what you described:

>>> kdf=ks.DataFrame({'transport_order_number': {11059585: ('696530708053'),  36538499: '696530708053',  41914814: '696530708053',  58878846: '696530708053',  83502171: '696530708053',  87335732: '696530708053',  89651819: '696530708053'},
...  'event_description': {11059585: 'PIEZA EN RUTA AL DESTINATARIO',  36538499: 'TRANSFERENCIA RUTA (OTBCS)',  41914814: 'RECEPCION TRANS. PIEZA',  58878846: 'RETIRO DESDE PDT',  83502171: 'RECEPCION TRANS. CONT.',  87335732: 'RECEPCIONADA',  89651819: 'PIEZA ENTREGADA A DESTINATARIO'},
...  'event_date': {11059585: ('2020-12-15 09:05:12.743000'),  36538499: ('2020-12-15 06:42:22.477000'),  41914814: ('2020-12-15 06:42:34.083000'),  58878846: ('2020-12-14 13:41:00'),  83502171: ('2020-12-15 06:42:00'),  87335732: ('2020-12-14 14:41:00'),
...   89651819: ('2020-12-15 12:53:00')}})
>>> kdf
         transport_order_number               event_description                  event_date
11059585           696530708053   PIEZA EN RUTA AL DESTINATARIO  2020-12-15 09:05:12.743000
36538499           696530708053      TRANSFERENCIA RUTA (OTBCS)  2020-12-15 06:42:22.477000
41914814           696530708053          RECEPCION TRANS. PIEZA  2020-12-15 06:42:34.083000
58878846           696530708053                RETIRO DESDE PDT         2020-12-14 13:41:00
83502171           696530708053          RECEPCION TRANS. CONT.         2020-12-15 06:42:00
87335732           696530708053                    RECEPCIONADA         2020-12-14 14:41:00
89651819           696530708053  PIEZA ENTREGADA A DESTINATARIO         2020-12-15 12:53:00

>>> recepcion=kdf.loc[kdf['event_description']=='RECEPCIONADA']
>>> recepcion
         transport_order_number event_description           event_date
87335732           696530708053      RECEPCIONADA  2020-12-14 14:41:00

>>> retiro=kdf.loc[kdf['event_description']=='RETIRO DESDE PDT']
>>> retiro
         transport_order_number event_description           event_date
58878846           696530708053  RETIRO DESDE PDT  2020-12-14 13:41:00

>>> ks.merge(recepcion, retiro, on='transport_order_number', how='outer', suffixes=('_recepcion','_retiro'))
  transport_order_number event_description_recepcion event_date_recepcion event_description_retiro    event_date_retiro
0           696530708053                RECEPCIONADA  2020-12-14 14:41:00             RECEPCIONADA  2020-12-14 14:41:00

or from the pandas' example:

>>> df1 = ks.DataFrame({'transport_order_number': ['696530708053', '696530708055'], 'event_description': ['RECEPCIONADA', 'RECEPCIONADA'], 'event_date': ['2020-01-02', '2020-01-02']})
>>> df1
  transport_order_number event_description  event_date
0           696530708053      RECEPCIONADA  2020-01-02
1           696530708055      RECEPCIONADA  2020-01-02
>>> df2 = ks.DataFrame({'transport_order_number': ['696530708053', '696530708056'], 'event_description': ['RETIRO DESDE PDT', 'RETIRO DESDE PDT'], 'event_date': ['2020-01-03', '2020-01-03']})
>>> df2
  transport_order_number event_description  event_date
0           696530708053  RETIRO DESDE PDT  2020-01-03
1           696530708056  RETIRO DESDE PDT  2020-01-03

>>> ks.merge(df1, df2, on='transport_order_number', how='outer').sort_values('transport_order_number')
  transport_order_number event_description_x event_date_x event_description_y event_date_y
2           696530708053        RECEPCIONADA   2020-01-02    RETIRO DESDE PDT   2020-01-03
0           696530708055        RECEPCIONADA   2020-01-02                None         None
1           696530708056                None         None    RETIRO DESDE PDT   2020-01-03

This seems the same as the pandas' example result?

ueshin on 19 Feb 2021

Ho @ueshin,
Don't you see a litle strange that the ks.merge return the same event ( RECEPCIONADA - RECEPCIONADA ) on both sides, when it shouldn't be possible 'cause df1 and df2 were filtered for different "events" (RECEPCIONADA - RETIRO DESDE PDT, respectively).