0

Could please help me how to make the operation below. I think this is the most particular thing that i need to do in pandas at the momment.

Basicaly i need to merge a 2 dataframes, where in df1 i have a partial string(address_id), and in dataframe 2 i have the same information but concatenate with another (concat_address_id).

I tryed several ways to merge, extract strings, preprocessing strings, check a list thta contains partial string match. However dont find a smart way to do what i need as in sample below, that is merge dataframes based in substrings match.

This is df1:

process     sku    qty  address_id  customer    country
process1    sku1    1   address1    customer5   BR
process1    sku2    1   address2    customer5   BR
process1    sku3    1   address3    customer5   BR
process1    sku4    1   address4    customer5   BR
process1    sku5    1   address5    customer5   BR

This is df2.

concat_address_id   last_login  country_of_login
address1address5    15/10/2020  CN
address6address2    18/02/2020  NL
address3address5    13/05/2019  BR
address6address4    18/06/2020  NL
address5address8    13/05/2019  RU

And this is the expected result.

Expected Result:

process        sku  qty address_id  customer     country    last_login  country_of_login
process1    sku1    1   address1    customer5   BR  15/10/2020  CN
process1    sku2    1   address2    customer5   BR  18/02/2020  NL
process1    sku3    1   address3    customer5   BR  13/05/2019  BR
process1    sku4    1   address4    customer5   BR  18/06/2020  NL
process1    sku5    1   address5    customer5   BR  13/05/2019  RU
Caio Euzébio
  • 182
  • 1
  • 1
  • 10
  • I think you can take a look at this answer https://stackoverflow.com/questions/54756025/how-to-merge-pandas-on-string-contains – Martin Hadid Oct 15 '20 at 13:28

2 Answers2

0

Based on this: How to merge pandas on string contains?

>>> df1
    process   sku address_id   customer country
0  process1  sku1   address1  customer5      BR
1  process1  sku2   address2  customer5      BR
2  process1  sku3   address3  customer5      BR
3  process1  sku4   address4  customer5      BR
4  process1  sku5   address5  customer5      BR
>>> df2
  concat_address_id  last_login   customer country_of_login
0  address1address5  15/10/2020  customer5               CN
1  address6address2   18/2/2020  customer5               NL
2  address3address5  13/05/2019  customer5               BR
3  address6address4  18/06/2020  customer5               NL
4  address5address8  13/05/2019  customer5               RU

>>> check = [(process, sku, address_id, customer, country, cust, last_login, country_li) for i, (process, sku, address_id, customer, country) in df1.iterrows() for j, (concat_addr, last_login, cust, country_li) in df2.iterrows() if address_id in concat_addr]

>>> check
[('process1', 'sku1', 'address1', 'customer5', 'BR', 'customer5', '15/10/2020', 'CN'), ('process1', 'sku2', 'address2', 'customer5', 'BR', 'customer5', '18/2/2020', 'NL'), ('process1', 'sku3', 'address3', 'customer5', 'BR', 'customer5', '13/05/2019', 'BR'), ('process1', 'sku4', 'address4', 'customer5', 'BR', 'customer5', '18/06/2020', 'NL'), ('process1', 'sku5', 'address5', 'customer5', 'BR', 'customer5', '15/10/2020', 'CN'), ('process1', 'sku5', 'address5', 'customer5', 'BR', 'customer5', '13/05/2019', 'BR'), ('process1', 'sku5', 'address5', 'customer5', 'BR', 'customer5', '13/05/2019', 'RU')]


>>> (pd.DataFrame(check, columns=["process", "sku", "address_id", "customer", "country", "customer", "last_login", "country_login"]))
    process   sku address_id   customer country   customer  last_login country_login
0  process1  sku1   address1  customer5      BR  customer5  15/10/2020            CN
1  process1  sku2   address2  customer5      BR  customer5   18/2/2020            NL
2  process1  sku3   address3  customer5      BR  customer5  13/05/2019            BR
3  process1  sku4   address4  customer5      BR  customer5  18/06/2020            NL
4  process1  sku5   address5  customer5      BR  customer5  15/10/2020            CN
5  process1  sku5   address5  customer5      BR  customer5  13/05/2019            BR
6  process1  sku5   address5  customer5      BR  customer5  13/05/2019            RU


I have redundant customer so it can be removed!

Let me know if it helps.

ky_aaaa
  • 290
  • 3
  • 10
  • i tryed this method, but it dont perform in my dataframe with 23 milion of lines. ```def strmerge(strcolumn): for i in df2['column_common']: if strcolumn in i: return df2[df2['column_common'] == i]['column_b'].values[0] break else: pass df1['column_b'] = df1.apply(lambda x: strmerge(x['column_common']),axis=1)```, also i cant type data by data because i working with largest datasets. – Caio Euzébio Oct 15 '20 at 16:43
  • 23 million of lines is important info. Are the `address_id` and `concat_address_id` matched by index in dataframe? Can `address1` info be on df1 at line 10 and `address1address5` on df2 at line7? – ky_aaaa Oct 16 '20 at 08:32
  • Yes, will have same substrings in concat, however it can be in defferent order for some cases because this is customer choices. – Caio Euzébio Oct 16 '20 at 12:39
  • What if `address` from df1 appears more than once in df2? – ky_aaaa Oct 16 '20 at 13:26
  • Yes, it happens. – Caio Euzébio Oct 16 '20 at 13:39
0

This should work too

# Split concat_address_id column with reg expression
df2['address_id_1'] = 'address' + df2['concat_address_id'].str.split('address').str.get(1)
df2['address_id_2'] = 'address' + df2['concat_address_id'].str.split('address').str.get(2)

# Create empty address_id column to merge with df1
df2['address_id'] = ''

# Filter out address id missing from df1
df2.loc[~df2['address_id_1'].isin(list(df1['address_id'])),'address_id'] = df2['address_id_2']

# Set value in address_id column 
df2.loc[df2['address_id_1'].isin(list(df1['address_id'])),'address_id'] = df2['address_id_1']

concat_address_id   last_login  country_of_login    address_id_1    address_id_2    address_id
0   address1address5    15/10/2020  CN                  address1    address5    address1
1   address6address2    18/02/2020  NL                  address6    address2    address2
2   address3address5    13/05/2019  BR                  address3    address5    address3
3   address6address4    18/06/2020  NL                  address6    address4    address4
4   address5address8    13/05/2019  RU                  address5    address8    address5

# Merge df1 and df2
df_final = pd.merge(df1,df2[['address_id', 'last_login', 'country_of_login']],
                    on='address_id',how='left')

    process     sku     address_id  customer    country last_login  country_of_login
0   process1    sku1    address1    customer5   BR      15/10/2020  CN
1   process1    sku2    address2    customer5   BR      18/02/2020  NL
2   process1    sku3    address3    customer5   BR      13/05/2019  BR
3   process1    sku4    address4    customer5   BR      18/06/2020  NL
4   process1    sku5    address5    customer5   BR      13/05/2019  RU
Mike
  • 337
  • 3
  • 9