CODEWITHSUNDEEP
pythonperformancepandas
3pitt
3pitt

Reputation: 941

Replace double for loop pandas DataFrame operation with apply

I need to incorporate some text analysis information into an existing dataframe. Rather than using dummy variables to denote the presence of the top phrase or two (for instance), I want to use the actual scores.

Here's some sample data that's representative. I calculate a fixed number of phrase-scores for each code, but the frequency of codes in the original data varies.

import pandas as pd

scores=pd.DataFrame(columns=['code','phrase','score'],
    data=[['01A','stove',0.673],
    ['01A','hot',0.401],
    ['XR3','service',0.437],
    ['XR3','stove',0.408],
    ['0132','replace',0.655],
    ['0132','recommend',0.472]])

df=pd.DataFrame(columns=['CODE','YR_OPEN','COST'],
    data=[['01A',2004,173.23],['01A',2008,82.18],
    ['01A',2012,939.32],['01A',2010,213.21],
    ['01A',2016,173.39],['01A',2013,183.46],
    ['XR3',2017,998.61],['XR3',2012,38.99],
    ['XR3',2017,923.71],['XR3',2004,832.23],
    ['0132',2004,823.12],['0132',2017,832.12],
    ['0132',2002,887.51],['0132',2002,92.35],
    ['0132',2013,21.03],['0132',2008,9472.94],
    ['0132',2012,341.93],['0132',2008,881.36]])

# Here's what the output should look like:
# CODE   YR_OPEN   COST    Phrase_stove    Phrase_hot ...
# 01A    2004      173.23  0.673           0.401
# 01A    2008      82.18   0.673           0.401
# ...
# XR3    2017      998.61  0.408           0
# ...

I can achieve this with a double for loop, but I believe this is pretty undesirable if only from a performance standpoint.

# initially, create new columns filled with 0
# old approach:

# for phrase in scores['phrase'].unique():
#     cname='Phrase_'+phrase
#     df[cname]=0

# new approach:
def new_cols(r):
    cname='Phrase_'+r['phrase']
    df[cname]=0
scores.drop_duplicates(subset='phrase', keep='last').apply(new_cols,axis=1)

for i,r in df.iterrows():
    score_subset=scores[scores['code']==r['CODE']]
    #del score_subset['index']
    for i2,r2 in score_subset.iterrows():
        cname='Phrase_'+r2['phrase']
        df.loc[i,cname]=r2['score']

#print(df)

The approach below does work, but I still can't figure out how to get rid of the second for loop

def curr_attempt(row):
    score_subset=scores[scores['code']==row['CODE']]
    #ideally: another apply?
    for i,r in score_subset.iterrows():
        cname='Phrase_'+r['phrase']
        df.loc[i,cname]=r['score']

df.apply(curr_attempt,axis=1)

Here is my current best attempt, which raises TypeError: ("apply() got multiple values for argument 'axis'", 'occurred at index 0'). The rowIndex idea is taken from another SO user (see getting the index of a row in a pandas apply function).

def row_index(row):
    return row.name

def attempt_helper(row,ind):
    cname='Phrase_'+row['phrase']
    df.loc[ind,cname]=row['score']

def curr_attempt(row):
    score_subset=scores[scores['code']==row['CODE']]
    score_subset.apply(attempt_helper,row['rowIndex'],axis=1)

df['rowIndex']=df.apply(row_index,axis=1)
df.apply(curr_attempt,axis=1)
print(df)

Upvotes: 0

Views: 551

Answers (1)

Ben
Ben

Reputation: 836

If all you want is to join the corresponding values from your two DataFrames, you can pivot the scores DataFrame and join to df.

scores = scores.pivot(index='code', columns='phrase').fillna(0)
scores.columns = scores.columns.droplevel()
scores.columns = ['Phrase_{}'.format(i) for i in scores.columns]

output = pd.merge(df, scores, left_on='CODE', right_index=True)
output[['CODE', 'YR_OPEN', 'COST', 'Phrase_hot', 'Phrase_stove']].sample(5)

    CODE    YR_OPEN COST    Phrase_hot  Phrase_stove
6   XR3     2017    998.61  0.000       0.408
1   01A     2008    82.18   0.401       0.673
11  0132    2017    832.12  0.000       0.000
5   01A     2013    183.46  0.401       0.673
2   01A     2012    939.32  0.401       0.673

Upvotes: 1

Related Questions