Pandas: Multiplying a value extracted from a DataFrame to column values in another DataFrame-CodePudding

What I have:

I have two DataFrames. The first one df1 contains cartesian coordinates and some values related to some elements. It looks like this:

           X           Y   Zn  Pb    Ag  Cu  Mo   Cr  Ni  Co   Ba
0  431434.79  3305001.94   87   7  0.02  42   2   57  38  14  393
1  432522.60  3298058.43   70   6  0.02  56   2   27  29  20  404
2  438045.35  3291987.05   75   5  0.02  69   2   44  23  17  417
3  436260.76  3294412.90   70   6  0.02  54   1   20  19  12  377
4  439294.48  3297653.81  168  14  0.02  27   2   31  18  14  277
5  441406.54  3298650.88   87  11  0.02  37   2   25  28  14  797
6  431189.97  3315353.72  113  11  0.02  40   1  110  36  13  361
7  428527.24  3315392.79  133   9  0.02  45   2   73  42  16  414
8  430881.58  3316951.45  144   8  0.02  58   1   83  39  21  617
9  427968.68  3317058.60  267  11  0.02  37   1  285  43  17  292

Now, I have another DataFrame, df2, that contains some calculations. It looks like this:

    Prediction Rate (%)  Occupied Area (%)  Normalized Density    Weight
Zn                   50                 50                1.00  0.000000
Pb                   50                 50                1.00  0.000000
Ag                   78                 22                3.55  1.266948
Cu                   90                 10                9.00  2.197225
Mo                   79                 21                3.76  1.324419
Cr                   69                 31                2.23  0.802002
Ni                   81                 19                4.26  1.449269
Co                   70                 30                2.33  0.845868
Ba                   79                 21                3.76  1.324419

As evident, the index of df2 is the same as some columns of df1.

What I want:

Now I want to multiply Weight of each element from df2 to the corresponding column of each element from df1. For example, the weight of Zn is 0. I want to multiply 0 to all of the values of Zn in df1. It would be basically a column of zeros. And I want to iterate that for all the columns.

What I have done so far:

I have extracted the Weights column from df2. Then I tried to create a new DataFrame by multiplication.

# list of each element
elements = ['Zn', 'Pb', 'Ag', 'Cu', 'Mo', 'Cr', 'Ni', 'Co', 'Ba']

# extracting "Weight" column from df2
weights_extracted = df1["Weight"]

# creating a new dataframe and multiplying values of each element with their corresponding weight
new_df = pd.DataFrame()
for i,element in enumerate(elements):
    df2[element] = df2[element] * weights_extracted.loc[element,:]
            
new_df = new_df.append(raw_data,False)

Problem:

I get this error:

Too many indexers

How can I solve this issue?

CodePudding user response：

In the for loop 'i' var is not necessary, the var that you are using is 'element'.

Saying “thanks” is appreciated, but it doesn’t answer the question. Instead, vote up the answers that helped you the most! If these answers were helpful to you, please consider saying thank you in a more constructive way – by contributing your own answers to questions your peers have asked here.

CodePudding user response：

I would solve this using numpy instead of pandas just for the readability. So I would recommend this

elements = ['Zn', 'Pb', 'Ag', 'Cu', 'Mo', 'Cr', 'Ni', 'Co', 'Ba']
df1_matrix = df1[elements].to_numpy()
df2_matrix = df2['Weight'].to_numpy()
multiplied = df1_matrix * df2_matrix
new_df = pd.DataFrame(multiplied,columns=elements)

This makes the problem into a relatively simple matrix problem instead of dealing with indexes and for loops.

CodePudding user response：

You can use the index of df2 to select the required columns in df1, then multiply those columns with the corresponding Weight values from df2

df1.update(df1[df2.index].mul(df2['Weight']))

print(df1)

           X           Y   Zn   Pb        Ag          Cu        Mo          Cr         Ni         Co           Ba
0  431434.79  3305001.94  0.0  0.0  0.025339   92.283450  2.648838   45.714114  55.072222  11.842152   520.496667
1  432522.60  3298058.43  0.0  0.0  0.025339  123.044600  2.648838   21.654054  42.028801  16.917360   535.065276
2  438045.35  3291987.05  0.0  0.0  0.025339  151.608525  2.648838   35.288088  33.333187  14.379756   552.282723
3  436260.76  3294412.90  0.0  0.0  0.025339  118.650150  1.324419   16.040040  27.536111  10.150416   499.305963
4  439294.48  3297653.81  0.0  0.0  0.025339   59.325075  2.648838   24.862062  26.086842  11.842152   366.864063
5  441406.54  3298650.88  0.0  0.0  0.025339   81.297325  2.648838   20.050050  40.579532  11.842152  1055.561943
6  431189.97  3315353.72  0.0  0.0  0.025339   87.889000  1.324419   88.220220  52.173684  10.996284   478.115259
7  428527.24  3315392.79  0.0  0.0  0.025339   98.875125  2.648838   58.546146  60.869298  13.533888   548.309466
8  430881.58  3316951.45  0.0  0.0  0.025339  127.439050  1.324419   66.566166  56.521491  17.763228   817.166523
9  427968.68  3317058.60  0.0  0.0  0.025339   81.297325  1.324419  228.570570  62.318567  14.379756   386.730348