CODEWITHSUNDEEP
pythonstringpandasdataframecompare
surya narayan
surya narayan

Reputation: 51

Compare two dataframe columns for matching percentage

I want to compare a data frame of one column with another data frame of multiple columns and return the header of the column having maximum match percentage.

I am not able to find any match functions in pandas. First data frame first column :

cars
----   
swift   
maruti   
wagonor  
hyundai  
jeep

First data frame second column :

bikes
-----
RE
Ninja
Bajaj
pulsar

one column data frame :

words
---------
swift 
RE 
maruti
waganor
hyundai
jeep
bajaj

Desired output :

100% match  header - cars

Upvotes: 5

Views: 2907

Answers (4)

Lawis
Lawis

Reputation: 125

Try to use isin function of pandas DataFrame. Assuming df is your first dataframe and words is a list :

In[1]: (df.isin(words).sum()/df.shape[0])*100
Out[1]:
cars     100.0
bikes     20.0
dtype: float64

You may need to lowercase strings in your df and in the words list to avoid any casing issue.

Upvotes: 3

AlCorreia
AlCorreia

Reputation: 552

Here is a solution with a function that returns a tuple (column_name, match_percentage) for the column with the maximum match percentage. It accepts a pandas dataframe (bikes and cars in your example) and a series (words) as arguments.

def match(df, se):
    max_matches = 0
    max_col = None
    for col in df.columns:
        # Get the number of matches in a column
        n_matches = sum([1 for row in df[col] if row in se.unique()])
        if n_matches > max_matches:
            max_col = col
            max_matches = n_matches
    return max_col, max_matches/df.shape[0]

With your example, you should get the following output.

df = pd.DataFrame()
df['Cars'] = ['swift', 'maruti', 'wagonor', 'hyundai', 'jeep']
df['Bikes'] = ['RE', 'Ninja', 'Bajaj', 'pulsar', '']
se = pd.Series(['swift', 'RE', 'maruti', 'wagonor', 'hyundai', 'jeep', 'bajaj'])

In [1]: match(df, se)
Out[1]: ('Cars', 1.0)

Upvotes: 0

Chris Adams
Chris Adams

Reputation: 18647

Construct a Series using numpy.in1d and ndarray.mean then call the Series.idxmax and max methods:

# Setup
df1 = pd.DataFrame({'cars': {0: 'swift', 1: 'maruti', 2: 'waganor', 3: 'hyundai', 4: 'jeep'}, 'bikes': {0: 'RE', 1: 'Ninja', 2: 'Bajaj', 3: 'pulsar', 4: np.nan}})
df2 = pd.DataFrame({'words': {0: 'swift', 1: 'RE', 2: 'maruti', 3: 'waganor', 4: 'hyundai', 5: 'jeep', 6: 'bajaj'}})

match_rates = pd.Series({col: np.in1d(df1[col], df2['words']).mean() for col in df1})

print('{:.0%} match header - {}'.format(match_rates.max(), match_rates.idxmax()))

[out]

100% match header - cars

Upvotes: 1

PV8
PV8

Reputation: 6270

You can first get the columns into lists:

dfCarsList = df['cars'].tolist()
dfWordsList = df['words'].tolist()
dfBikesList = df['Bikes'].tolist()

And then iterate of the list for comparision:

numberCars = sum(any(m in L for m in dfCarsList) for L in dfWordsList)
numberBikes = sum(any(m in L for m in dfBikesList) for L in dfWordsList)

The higher number you can use than for your output.

Upvotes: 1

Related Questions