Mapping multiple columns and values with dictionary-CodePudding

I have a df with 5 columns and multiple rows, I would like to add data from dictionary (also a df) into an extra column. It works fine with one column but would like to have the values from the other columns; either all in one or in separate columns.

data = [{'1': 'tom', '2': 'frank', '3':'peter', '4': 'michael', '5':'nick'},
        {'1':'nick', '2': 'john', '3': 'frank','4': 'tom', '5': 'peter' },
       {'1':'sam', '2': 'mike', '3': 'tom','4': 'nick', '5': 'adam' }
       ]
 

df2 = pd.DataFrame(data)
 

```print(df2)```

      1      2      3        4      5
0   tom  frank  peter  michael   nick
1  nick   john  frank      tom  peter
2   sam   mike    tom     nick   adam

```rating ={
'tom':'A',
'nick':'B',
'frank':'A',
'john':'F',
'peter':'D',
'michael':'C',
'adam':'D',
'mike':'D', 'sam':'B'}```

```df2['CC'] = df2['1'].map(rating)```


     1      2      3        4      5    CC
0   tom  frank  peter  michael   nick    A
1  nick   john  frank      tom  peter    B
2   sam   mike    tom     nick   adam    D


What I would like to have is the values for each row (column 1-5) in one column. 

     1      2      3        4      5       CC  
0   tom  frank  peter  michael   nick      A, A, D, C, B
1  nick   john  frank      tom  peter      B, F, A, A, D
2   sam   mike    tom     nick   adam      D, D, A, B, D 
 

Any idea?

CodePudding user response：

How about

df2['CC'] = df2.apply(lambda x: [rating['key'] for key in x.keys()], axis=1)

CodePudding user response：


def get_rating(c1,c2,c3,c4,c5):
    collect = []
    result = None
    if c1 in rating:
        col1 = rating[c1]
    else:
        col1 = None
    if c2 in rating:
        col2 = rating[c2]
    else:
        col2 = None
    if c3 in rating:
        col3 = rating[c3]
    else:
        col3 = None
    if c4 in rating:
        col4 = rating[c4]        
    else:
        col4 = None
    if c5 in rating:
        col5 = rating[c5]
    else:
        col5 = None
    collect = [col1,col2,col3,col4,col5]
    collection = ','.join(['' if i is None else i for i in collect])
    return collection

df2['CC']=df2.apply(lambda k: get_rating(k['1'],k['2'],k['3'],k['4'],k['5']),axis=1)

CodePudding user response：

The correct way to resolve this problem is using applymap function like this:

df2.applymap(lambda x: rating[x])

This should apply the conversion that you want for all columns.

CodePudding user response：

Use replace:

df2['CC'] = df2.replace(rating).apply(lambda x: ', '.join(x), axis=1)
print(df2)

# Output
      1      2      3        4      5             CC
0   tom  frank  peter  michael   nick  A, A, D, C, B
1  nick   john  frank      tom  peter  B, F, A, A, D
2   sam   mike    tom     nick   adam  B, D, A, B, D