Count how many characters from a column appear in another column (pandas)

user_10944942 0
Viewed 142

I am trying to count how many characters from the first column appear in second one. They may appear in different order and they should not be counted twice.

For example, in this df

df = pd.DataFrame(data=[["AL0","CP1","NM3","PK9","RM2"],["AL0X24",
                            "CXP44",
                            "MLN",
                            "KKRR9",
                            "22MMRRS"]]).T

the result should be:

result = [3,2,2,2,3]
pandas python
3 Answers

Looks like set.intersection after zipping the 2 columns:

[len(set(a).intersection(set(b))) for a,b in zip(df[0],df[1])]
#[3, 2, 2, 2, 3]
user_98406370

Sticking to the dataframe data structure, you could do:

>>> def count_common(s1, s2):
...     return len(set(s1) & set(s2))
...
>>> df["result"] = df.apply(lambda x: count_common(x[0], x[1]), axis=1)
>>> df
     0        1  result
0  AL0   AL0X24       3
1  CP1    CXP44       2
2  NM3      MLN       2
3  PK9    KKRR9       2
4  RM2  22MMRRS       3
user_142759290

The other solutions will fail in the case that you compare names that both have the same multiple character, eg. AAL0 and AAL0X24. The result here should be 4.

from collections import Counter

df = pd.DataFrame(data=[["AL0","CP1","NM3","PK9","RM2", "AAL0"],
                        ["AL0X24", "CXP44", "MLN", "KKRR9", "22MMRRS", "AAL0X24"]]).T

def num_shared_chars(char_counter1, char_counter2):
    shared_chars = set(char_counter1.keys()).intersection(char_counter2.keys())
    return sum([min(char_counter1[k], char_counter2[k]) for k in shared_chars])

df_counter = df.applymap(Counter)
df['shared_chars'] = df_counter.apply(lambda row: num_shared_chars(row[0], row[1]), axis = 'columns')

Result:

      0        1  shared_chars
0   AL0   AL0X24             3
1   CP1    CXP44             2
2   NM3      MLN             2
3   PK9    KKRR9             2
4   RM2  22MMRRS             3
5  AAL0  AAL0X24             4
user_92550870
Related