python sum values in df1 if loc in df2 is True

user_15367195 0
Viewed 73

I'm still quite new to Python, and after looking intensively here on SO, I've decided to just ask.

I have a DataFrame, df

df
    NO2  NO2  NO3  DK1  DK2  
0   1.0  3.0  2.0  1.0  1.0
1   1.0  3.0  3.0  3.0  1.0
2   2.0  2.0  2.0  1.0  1.0

Now, what I want to do is sum up all values in row 0 that are equal to the value in column "DK1" (incl. itself) and return it in a new column. Then after doing that for row 0, the same procedure for row 1, then row 2, etc.

Desired output:

df2
    NO2  NO2  NO3  DK1  DK2   Sum 
0   1.0  3.0  2.0  1.0  1.0   3.0
1   1.0  3.0  3.0  3.0  1.0   9.0
2   2.0  2.0  2.0  1.0  1.0   2.0
pandas dataframe python
2 Answers

Compare all values by DF1 column, then multiple this column and last use sum per rows:

df['sum'] = df.eq(df['DK1'], axis=0).mul(df['DK1'], axis=0).sum(axis=1)
print (df)
   NO2  NO2.1  NO3  DK1  DK2  sum
0  1.0    3.0  2.0  1.0  1.0  3.0
1  1.0    3.0  3.0  3.0  1.0  9.0
2  2.0    2.0  2.0  1.0  1.0  2.0

Details:

print (df.eq(df['DK1'], axis=0))
     NO2  NO2.1    NO3   DK1    DK2
0   True  False  False  True   True
1  False   True   True  True  False
2  False  False  False  True   True

print (df.eq(df['DK1'], axis=0).mul(df['DK1'], axis=0))
   NO2  NO2.1  NO3  DK1  DK2
0  1.0    0.0  0.0  1.0  1.0
1  0.0    3.0  3.0  3.0  0.0
2  0.0    0.0  0.0  1.0  1.0
user_29010020

@jezrael, I didn't know how to put this in a comment

  NO1    NO2   ...   DK1    DK2                     sum
0 28.4   28.4  ...   21.0   21.0                                  2121
1 28.2   28.2  ...   25.1   25.1    25,125,125,125,125,125,125,125,125,1
2 28.0   28.0  ...   25.1   25.1    25,125,125,125,125,125,125,125,125,1
3 28.0   28.0  ...   16.0   16.0                                  1616
4 28.0   28.0  ...   16.4   16.4                              16,416,4

Naturally, my actual dataset is not just ones the simple ones that I started out by - these are my actual values, and the result that I get. Does that help?

user_153671950
Related