[FIXED] How to add or replace numeric score from other dataframe to a dataframe in pandas0

Issue

I'd like to add or replace numeric score from other dataframe to a dataframe.

For example, a dataframe has credit rating columns written and want to add or replace numeric score like belows.

I can build it over and over again through pd.merge, but is there an easier or faster way?

import pandas as pd


df = pd.DataFrame( { 'name' : ['ABC'],
                    'Moodys' :['Aa3'],
                    'SNP' : ['AA'],
                    'Fitch' : ['A']})

credit_socre = {10 : ['AAA', 'Aaa'],
                 9 : ['AA+', 'Aa1'],
                 8 : ['AA', 'Aa2'],
                 7 : ['AA-', 'Aa3'],
                 6 : ['A+', 'A1'],
                 5 : ['A', 'A2'],
                 4 : ['A-', 'A3'],
                 3 : ['BBB+', 'Baa1'],
                 2 : ['BBB', 'Baa2'],
                 1 : ['BBB-', 'Baa3'],
                -1 : ['BB+', 'Ba1'],
                -2 : ['BB', 'Ba2'],
                -3 : ['BB-', 'Ba3'],
                -4 : ['B+', 'B1'],
                -5 : ['B', 'B2'],
                -6 : ['B-', 'B3']}



credit_score2 = pd.DataFrame(credit_socre).transpose().reset_index().rename(columns={'index': 'score', 0 : 'SNP', 1: 'Moodys'})
credit_score2['Fitch'] = credit_score2['SNP']


# >> df
#   name Moodys SNP Fitch
# 0  ABC    Aa3  AA     A


df2 = df.merge(credit_score2, on=['Moodys'], how='left')
df3 = df2.merge(credit_score2, left_on=['Fitch_x'], right_on=['Fitch'], how='left')
df4 = df3.merge(credit_score2, left_on=['SNP_x'], right_on=['SNP'], how='left')

# What I want :
# >> df
#   name Moodys SNP Fitch  Moodys_score   Fitch_score   SNP_score   
# 0  ABC    Aa3  AA     A             7             5           8

```

Solution

Code

Create a mapper using credit_score and use replace.

after replacing, concat.

m = {i : key for key, val in credit_score.items() for i in val}
pd.concat([df, df.loc[:, 'Moodys':].replace(m).add_suffix('_score')], axis=1)

output:

    name    Moodys  SNP Fitch   Moodys_score    SNP_score   Fitch_score
0   ABC     Aa3     AA  A       7               8           5

& your example code have typo. It needs to be fixed. (credit_socre -> credit_score)

Answered By - Panda Kim