Comparing features in two series (pandas or numpy) including wildcards

user_12246875 0
Viewed 40

I have a table containing columns of Type and rows of Features. I'd like to test a sample column: df_sample and see how many Features match.

I can do this when comparing 1s and 0s using the code below. I'd like to be able to incorporate a wildcard match in df i.e. matching a 0 or 1 should add to the PercentageMatch

Grateful for any advice on doing this more efficiently too - would numpy be better?

dmain = {'Type1': {'F1': 0, 'F2': 1, 'F3': 0},
            'Type2': {'F1': 1, 'F2': 0, 'F3': 0},
            'Type3': {'F1': 0, 'F2': 0, 'F3': 1}
           }
dsample = {'P': {'F1': 0, 'F2': 1, 'F3': 0}}

df = pd.DataFrame(data=dmain)
df_sample = pd.DataFrame(data=dsample)
df.sort_index(inplace=True)
df_sample.sort_index(inplace=True)

print(df)
print(df_sample)

   Type1  Type2  Type3
F1      0      1      0
F2      1      0      0
F3      0      0      1
    P
F1  0
F2  1
F3  0

def get_percent_match(df, df_sample):
  df_res = df.where(df.values==df_sample.values).notna()
  df_res = df_res.append((df_res.sum()/df_res.count()).rename('PercentageMatch'))
  print(df_res.loc['PercentageMatch', :])
  
get_percent_match(df, df_sample)

Type1    1.000000
Type2    0.333333
Type3    0.333333
Name: PercentageMatch, dtype: float64

With a wildcard -1 in dmain

dmain2 = {'Type1': {'F1': 0, 'F2': 1, 'F3': 0},
            'Type2': {'F1': 1, 'F2': -1, 'F3': -1},
            'Type3': {'F1': 0, 'F2': -1, 'F3': 1}
           }
df2 = pd.DataFrame(data=dmain2)
df2.sort_index(inplace=True)

The output from get_percent_match(df2, df_sample) should be:

Type1    1.000000
Type2    1.000000
Type3    0.666667
Name: PercentageMatch, dtype: float64
pandas numpy python
1 Answers

Use DataFrame.mask for replace wildcard by df_sample['P'], then compare values by DataFrame.eq and for percentage use mean:

s = df.eq(df_sample['P'], axis=0).mean()
print (s)

Type1    1.000000
Type2    0.333333
Type3    0.333333
dtype: float64

df22 = df2.mask(df2 == -1, df_sample['P'], axis=0)
print(df22)
    Type1  Type2  Type3
F1      0      1      0
F2      1      1      1
F3      0      0      1

s = df22.eq(df_sample['P'], axis=0).mean()
print (s)

Type1    1.000000
Type2    0.666667
Type3    0.666667
dtype: float64

Solution in numpy is possible, but necessary match index values of both DataFrames for compare:

data = df2.to_numpy()
p = df_sample['P'].to_numpy()

a = np.where(data == -1, p, data)
print(a)
[[0 1 0]
 [1 1 0]
 [0 1 1]]

out = (a == p).mean(axis=1)
print (out)
[1.         0.66666667 0.66666667]

s = pd.Series(out, index=df2.columns)
print (s)
Type1    1.000000
Type2    0.666667
Type3    0.666667
dtype: float64
user_29010020
Related