Is there a way of dinamically find partial matching numbers between columns in pandas dataframes?

user_11165920 0
Viewed 58

Im looking for a way of comparing partial numeric values between columns from different dataframes, this columns are filled with something like social security numbers (they can´t and won´t repeat), so something like a dynamic isin() with be ideal. This are representations of very large dataframes that I import from csv files.

{import numpy as np
import pandas as pd

df1 = pd.DataFrame({"S_number": ["271600", "860078", "342964", "763261", "215446", "205303", "973637", "814452", "399304", "404205"]})

df2 = pd.DataFrame({"Id_number": ["14452", "9930", "1544", "5303", "973637", "4205", "0271600", "342964", "763", "60078"]})

print(df1)
print(df2)

df2['Id_number_length']= df2['Id_number'].str.len()

df2.groupby('Id_number_length').count()

count_list = df2.groupby('Id_number_length')[['Id_number_length']].count()
print('count_list:\n', count_list)

df1 ['S_number'] = pd.to_numeric(df1['S_number'], downcast = 'integer')
df2['Id_number'] = pd.to_numeric(df2['Id_number'], downcast = 'integer')

inner_join = pd.merge(df1,  df2,  left_on =['S_number'], right_on = ['Id_number'] ,  how ='inner') 
print('MATCH!:\n', inner_join)

outer_join = pd.merge(df1,  df2,  left_on =['S_number'], right_on = ['Id_number'] ,  how ='outer', indicator = True)
anti_join = outer_join[~(outer_join._merge == 'both')].drop('_merge', axis = 1)

print('UNMATCHED:\n', anti_join)

}

What I need to get is something as the following as a result of the inner join or whatever method: {

df3 = pd.DataFrame({"S_number": ["271600", "860078", "342964", "763261", "215446", "205303", "973637", "814452", "399304", "404205"],
"Id_number": [ "027160", "60078","342964","763", "1544", "5303", "973637", "14452", "9930", "4205",]})

print('MATCH!:\n', df3)

} I thought that something like this (very crude) pseudocode would work. Using count_list to strip parts of the numbers of df1 to fully match df2 instead of partially matching (notice that in df2 the missing or added numbers are always at the begining or the end)

{

for i in count_list: 
    if i ==6:
        try inner join
        except empty output
    elif i ==5:
        try 
            df1.loc[:,'S_number'] = df_ib_c.loc[:,'S_number'].str[1:]
            inner join with df2
        except empty output
        try 
            df1.loc[:,'S_number'] = df_ib_c.loc[:,'S_number'].str[:-1]
            inner join with df2
    elif i == 4:
        same as above...

} But the lengths in count_list are variable so this for is an inefficient way. Any help with this will be very appreciated, I´ve been stuck with this for days. Thanks in advance.

pandas dataframe python
2 Answers

You can 'explode' each line of df1 into up to 45 lines. For example, SSN 123456789 can be map to [1,2,3...9,12,23,34,45..89,...12345678,23456789,123456789]. While this look bad, from algorithm standpoint it is O(1) for each row and therefore O(N) in total. Using this new column as key, a simple 'merge on' can combine the 2 DFs easily - which is usually O(NlogN).

user_81092830

Here is an example of what I should do. I hope I've understood. Feel free to ask if it's not clear.

import pandas as pd
import joblib
from joblib import Parallel,delayed
# Building the base
df1 = pd.DataFrame({"S_number": ["271600", "860078", "342964", "763261", "215446", "205303", "973637", "814452", "399304", "404205"]})
df2 = pd.DataFrame({"Id_number": ["14452", "9930", "1544", "5303", "973637", "4205", "0271600", "342964", "763", "60078"]})

# Initiate empty list for indexes
IDX = []

# Using un function to paralleliza it if database is big
def func(x,y):
    if all(c in df2.Id_number[y] for c in df1.S_number[x]):
        return(x,y)

# using the max of processors
number_of_cpu = joblib.cpu_count()
# Prpeparing a delayed function to be parallelized
delayed_funcs = (delayed(func)(x,y) for x in range(len(df1)) for y in range(len(df2)))
# fiting it with processes and not threads
parallel_pool = Parallel(n_jobs=number_of_cpu,prefer="processes")
# Fillig the IDX List
IDX.append(parallel_pool(delayed_funcs))
# Droping the None
IDX = list(filter(None, IDX[0]))
# Making df3 with the tuples of indexes
df3 = pd.DataFrame(IDX)
# Making it readable
df3['df1'] = df1.S_number[df3[0]].to_list()
df3['df2'] = df2.Id_number[df3[1]].to_list()
df3

OUTPUT :

enter image description here

user_115344930
Related