Using apply() to add a 'value score' to customer data in pandas. After each row I can't reset the score to zero

user_11222963 0
Viewed 235

I am trying to score customer data based on how complete their address is. The conditions are as follows:

If address 1 & address 2 are both present: score is 10

If other address details are present: a score of 5 for each one.

The problem is the score output is wrong. It's 15 for both customers, but the first one should be 25, second one should be 15.

Here's a mock dataframe: 

import numpy as np
import pandas as pd

df = pd.DataFrame({'address1':['foo','bar'],'address2':['foo','bar'],'city':['foo',np.nan],'country':['hello','bar'],'county':['foo','bar'],'should_be':['25','20']})

df


  address1 address2 city country county should_be
0      foo      foo  foo   hello    foo        25
1      bar      bar  NaN     bar    bar        20

So then I apply the function, which tallies what address info is there/missing, and adds a score in an extra column:


def f(row):

    score = 0

    # if we have address1 and address2
    if (row['address1'] ==True) & (row['address2']==True) ==2:
        score += 10
    # if we have city data 
    if row['city']:
        score += 5
    # if we have country data
    if row['country']:
        score += 5
    # if we have county data                                                                                                                      
    if row['county']:
        score += 5

    return score

    score = 0

df['actual'] = df.apply(f, axis=1)

df

  address1 address2 city country county should_be  actual
0      foo      foo  foo   hello    foo        25      15
1      bar      bar  NaN     bar    bar        20      15

First of all, the way I'm doing this is quite messy. Is there a more efficient way of scoring/checking the data?

Secondly, as you can see, both customers were scored 15, when the first one should have a score of 25 due to all address details being present, and the second one should have 20.

I expected the score to be reset to zero after the below section. It's also not adding up the scores correctly. Where am I going wrong?

return score

score = 0

If anybody can spot where I'm going wrong I'd appreciate it. Also, if you have any advice to clean up or make this code more readable I'm all ears. Thank you.

pandas python
3 Answers

How about this:

In [31]:df_boolean = df.notna()
        df_boolean['add1&add2'] = (df_boolean.loc[:,['address1','address2']].all(1))*2
        df_boolean.drop(['address1','address2'], axis=1, inplace=True)
        df_boolean = df_boolean * 5
        df_boolean['score'] = df_boolean.sum(axis=1)



In [32]: df_boolean
Out[32]: 
  city country county  add1&add2  score
0    5       5      5         10   25.0
1    0       5      5         10   20.0
user_68549290

I am not sure of a more efficient way but just modifying your function works fine:

def f(row):
    score = 0
    # if we have address1 and address2
    if not pd.isnull(row['address1']) and not pd.isnull(row['address2']):
        score += 10
    # if we have city data 
    if not pd.isnull(row['city']):
        score += 5
    # if we have country data
    if not pd.isnull(row['country']):
        score += 5
    # if we have county data                                                                                                                      
    if not pd.isnull(row['county']):
        score += 5
    return score

df['actual'] = df.apply(f, axis=1)

If you want a more concise function this will also work:

def f2(row):
    score = 25
    if pd.isnull(row['address1']) or pd.isnull(row['address2']):
        score -= 10
    score -= row[['city', 'country', 'county']].isnull().sum() * 5
    return score

More efficient:

bool_arr = df.isna().values
score = np.where(np.any(bool_arr[:,0:2], 1), 0, 10)
score += np.sum(~bool_arr[:,2:5], 1) * 5
df['score'] = score
user_87200000

Just to add another method:

d={"address1":10,"address2":10,"city":5,"country":5,"county":5} #ref dict
other_address=['city','country','county'] #other address columns
address = df.columns.difference(other_address) #primary address columns

Concatenating by checking any Nan in the address columns and the other df without address columns:

m=pd.concat([pd.Series(df[address].notna().all(axis=1),name='address1'),
         df[other_address].notna()],axis=1)
print(m)

   address1   city  country  county
0      True   True     True    True
1      True  False     True    True

Then using df.dot() and dict.get()

(m.dot(m.columns+ ',').str[:-1].str.split(',')
    .apply(lambda x: sum([int(d.get(i)) for i in x])))

0    25
1    20
user_98406370
Related