Create aggregate columns based on list of headers

user_11918314 0
Viewed 209

I have a dataframe with survey data. It contains several other columns with demographic data (eg age, department etc) and the columns with ratings. Would like to add some columns to the dataframe based on calculations of the ratings columns.

The purpose of adding the columns is to provide a) get a count of Favourable responses b) get the percentage of Favourable responses (no of favourable responses / no of items in that factor) c) get the factor level percentage of Favourable responses (where it will be NaN if there are any items with NaN that belong to the factor) The table below shows an example of how it will be applied to the Coaching Factor Would like to replicate this for the other Factors like Diversity, Leadership, Engagement.

Coach_q1  Coach_q2      Coach_q8    coach_favcount   coach_fav_perc   coach_agg_perc
Favourable   Neutral    Favourable   2                  66.6%          66.6%
Favourable   Favourable NaN          2                  100%           NaN
Favourable   Favourable Unfavourable 2                  66.6%          66.6%  
NaN          NaN        Unfavourable 0                  0%             NaN

I've used the following code and it works, however, am only able to get the fav_count column and the fav_perc column for Coaching. Would like a) get the _agg_perc column and b) apply this to all other Factors.

#Get the Coaching Columns
coaching_agg = df.loc[:, df.columns.str.contains('Coaching_')] 

#Create a column to store the number of favourable responses
df['coaching_fav_count'] = df[coaching_cols == 'Favourable'].notna().sum(axis=1)

#create a column to store the percentage of favourable responses
df['coaching_fav_perc'] = df['coaching_fav'] / len(coaching_agg.columns)

Im guessing the logic behind the for loop would be to a) create a list of the rating columns (see code below) and b) create a function to calculate the counts, percentages of favourable responses, look for presence of NaN at the item level and c) create a for loop to apply the function to the rating columns.

#Create a list made up of rating cols
ratingcollist = ['Coaching_','Communication_','Development_','Diversity_','Engagement_']

ratingcols = df.loc[:, df.columns.str.contains('|'.join(ratingcollist))]

Appreciate any form of help that i can get, thank you!

pandas aggregate for-loop function python
3 Answers

I believe you need processing each value of list separately:

df = pd.DataFrame({'Coach_q1': ['Favourable', 'Favourable', 'Favourable', 'nan'], 
                   'Coach_q2': ['Neutral', 'Favourable', 'Favourable', 'NaN'], 
                   'Coach_q8': ['Favourable', 'nan', 'Unfavourable', 'Unfavourable']})
    
print (df)
     Coach_q1    Coach_q2      Coach_q8
0  Favourable     Neutral    Favourable
1  Favourable  Favourable           nan
2  Favourable  Favourable  Unfavourable
3         nan         NaN  Unfavourable

#replace nan and NaN strings to missing values
df = df.replace(['nan','NaN'], np.nan)

ratingcollist = ['Coach_','Communication_','Development_','Diversity_','Engagement_']

for rat in ratingcollist:
    #filter columns by substrings
    cols = df.filter(like=rat).columns

    #mask for no missing values
    mask = df[cols].notna().all(axis=1)
    
    #create new columns if match
    if len(cols) > 0:
        df[f'{rat.lower()}fav_count'] = (df[cols] == 'Favourable').sum(axis=1)
        df[f'{rat.lower()}fav_perc'] = df[f'{rat.lower()}fav_count'] / df[cols].count(axis=1)
        df.loc[mask, f'{rat.lower()}agg_perc'] = df.loc[mask, f'{rat.lower()}fav_count'] / len(cols)

print (df)

     Coach_q1    Coach_q2      Coach_q8  coach_fav_count  coach_fav_perc  \
0  Favourable     Neutral    Favourable                2        0.666667   
1  Favourable  Favourable           NaN                2        1.000000   
2  Favourable  Favourable  Unfavourable                2        0.666667   
3         NaN         NaN  Unfavourable                0        0.000000   

   coach_agg_perc  
0        0.666667  
1             NaN  
2        0.666667  
3             NaN

If replace nans to word missing output for fav_perc is wrong, second value should by 1, because count exclude misisng values:

df = pd.DataFrame({'Coach_q1': ['Favourable', 'Favourable', 'Favourable', 'nan'], 
                   'Coach_q2': ['Neutral', 'Favourable', 'Favourable', 'NaN'], 
                   'Coach_q8': ['Favourable', 'nan', 'Unfavourable', 'Unfavourable']})
    
print (df)
     Coach_q1    Coach_q2      Coach_q8
0  Favourable     Neutral    Favourable
1  Favourable  Favourable           nan
2  Favourable  Favourable  Unfavourable
3         nan         NaN  Unfavourable

df = df.replace(['nan','NaN'], 'Missing')
print (df)
     Coach_q1    Coach_q2      Coach_q8
0  Favourable     Neutral    Favourable
1  Favourable  Favourable       Missing
2  Favourable  Favourable  Unfavourable
3     Missing     Missing  Unfavourable

#create a list of all the rating columns
ratingcollist = ['Coach_','Diversity', 'Leadership', 'Engagement']


#create a for loop to get all the columns that match the column list keyword
for rat in ratingcollist:
    cols = df.filter(like=rat).columns
    mask = (df[cols] != 'Missing').all(axis=1)
    
#create 3 new columns for each factor, one for count of Favourable responses, 
#one for percentage of Favourable responses, and one for Factor Level percentage of Favourable responses

    if len(cols) > 0:
        df[f'{rat.lower()}fav_count'] = (df[cols] == 'Favourable').sum(axis=1)
        df[f'{rat.lower()}fav_perc'] = df[f'{rat.lower()}fav_count'] / df[cols].count(axis=1)
        df.loc[mask,f'{rat.lower()}agg_perc'] = df.loc[mask, f'{rat.lower()}fav_count'] / len(cols)

print (df)
     Coach_q1    Coach_q2      Coach_q8  coach_fav_count  coach_fav_perc  \
0  Favourable     Neutral    Favourable                2        0.666667   
1  Favourable  Favourable       Missing                2        0.666667   
2  Favourable  Favourable  Unfavourable                2        0.666667   
3     Missing     Missing  Unfavourable                0        0.000000   

   coach_agg_perc  
0        0.666667  
1             NaN  
2        0.666667  
3             NaN

So if want use Missing is necessary change count to sum with compare not equal Missing:

#create a list of all the rating columns
ratingcollist = ['Coach_','Diversity', 'Leadership', 'Engagement']


#create a for loop to get all the columns that match the column list keyword
for rat in ratingcollist:
    cols = df.filter(like=rat).columns
    mask = (df[cols] != 'Missing').all(axis=1)
    
#create 3 new columns for each factor, one for count of Favourable responses, 
#one for percentage of Favourable responses, and one for Factor Level percentage of Favourable responses

    if len(cols) > 0:
        df[f'{rat.lower()}fav_count'] = (df[cols] == 'Favourable').sum(axis=1)
        df[f'{rat.lower()}fav_perc'] = df[f'{rat.lower()}fav_count'] / df[cols].ne('Missing').sum(axis=1)
        df.loc[mask,f'{rat.lower()}agg_perc'] = df.loc[mask, f'{rat.lower()}fav_count'] / len(cols)

print (df)
     Coach_q1    Coach_q2      Coach_q8  coach_fav_count  coach_fav_perc  \
0  Favourable     Neutral    Favourable                2        0.666667   
1  Favourable  Favourable       Missing                2        1.000000   
2  Favourable  Favourable  Unfavourable                2        0.666667   
3     Missing     Missing  Unfavourable                0        0.000000   

   coach_agg_perc  
0        0.666667  
1             NaN  
2        0.666667  
3             NaN
user_29010020

We can try without loops:

columns_split = df.columns.str.split('_')
count = (df.set_axis(pd.MultiIndex.from_tuples(map(tuple, columns_split)), axis=1)
           .stack()
           .eq('Favourable')
           .sum(level=0))

s = columns_split.str[0].to_series().add('_%Fav')

new_df = (df.join(count.add_suffix('_FavCount'))
           .join(count.add_suffix('_%Fav').div(s.value_counts()))
         )

print(new_df)

Output

  Coaching_q1 Coaching_q2 Diversity_q1 Diversity_q2  Coaching_FavCount  \
0  Favourable     Neutral   Favourable   Favourable                1.0   
1  Favourable  Favourable   Favourable    Favourble                2.0   
2         NaN  Favourable          NaN          NaN                1.0   

   Diversity_FavCount  Coaching_%Fav  Diversity_%Fav  
0                 2.0            0.5             1.0  
1                 1.0            1.0             0.5  
2                 0.0            0.5             0.0
user_118842370

Issue has been resolved by recoding the NaN values in the columns to 'Missing' and applying the mask recommended by @jezrael

#create a list of all the rating columns
ratingcollist = ['Coaching_','Diversity', 'Leadership', 'Engagement']


#create a for loop to get all the columns that match the column list keyword
for rat in ratingcollist:
    cols = df.filter(like=rat).columns
    mask = (df[cols] != 'Missing').all(axis=1)
    
#create 3 new columns for each factor, one for count of Favourable responses, one for percentage of Favourable responses, and one for Factor Level percentage of Favourable responses

    if len(cols) > 0:
        df[f'{rat.lower()}fav_count'] = (df[cols] == 'Favourable').sum(axis=1)
        df[f'{rat.lower()}fav_perc'] = df[f'{rat.lower()}fav_count'] / df[cols].count(axis=1)
        df.loc[mask,f'{rat.lower()}agg_perc'] = df.loc[mask, f'{rat.lower()}fav_count'] / len(cols)
user_119183140
Related