python - Created extra columns from list

Question

I've created a list which contains unique strings from two different pandas columns, A and B. For example:

A          B
aaa      asd
sad      aaa
vas      aaa
wow      kid
asd      vas

list_a=df.A.unique().tolist()
list_b=df.B.unique().tolist()

list_ab=list(set(list_a+list_b))

list_ab contains therefore aaa, sad, vas, wow, asd, kid.

I am manually assigning values for each element in list_ab, so I am creating a new list with these values:

list_values=[7.5, 2.0, 1.5, 3.6, 7.4, 8.2]

I need to create a new column in the dataset where I assign the average of the values of the elements in A and B; so

    A          B    A_val       B_val     Value
    aaa      asd     7.5          7.4      7.45
    sad      aaa     2.0          7.5      4.75
    vas      aaa     1.5          7.5      4.5
    wow      kid     3.6          8.2      5.9
    asd      vas     7.4          1.5      4.45

I don't know how to create the two columns A_val and B_val in order to have the corresponding values for A and B elements. Should I merge, join?

I hope you can help me with this.

question from:https://stackoverflow.com/questions/65911651/created-extra-columns-from-list

Answer 1

res = {list_ab[i]: list_values[i] for i in range(len(list_ab))}#Create dictionary


#Map dictionary values to create new columns 
df=df.assign(A_val=df['A'].map(res),B_val=df['B'].map(res),Value=df.filter(like='_val').mean(1))



    A    B    A_val  B_val  Value
0  aaa  asd    1.5    7.4   4.45
1  sad  aaa    8.2    1.5   4.85
2  vas  aaa    3.6    1.5   2.55
3  wow  kid    2.0    7.5   4.75
4  asd  vas    7.4    3.6   5.50