0

I want to sum all values greater than zero in column 2 of the dataframe. This sum should next be assigned to all the rows that were summed in a new column (3). See the example below:

Dataframe:
     Column 1 Column 2
0       15        0.2
1       11        0
2       13        0.4
3       19        0.4
4       14        0.4
5       2         0 
6       1         0
7       15        0.4
8       3         0

Required result:
     Column 1 Column 2 Column 3
0       15        0.2    0.2
1       11        0      0
2       13        0.4    1.2
3       19        0.4    1.2
4       14        0.4    1.2
5       2         0      0 
6       1         0      0
7       15        0.4    0.4 
8       3         0      0

Next another column (4) should be added which contains the lowest value for each of these blocks out of column 1. The rows with a zero in column 2 can just pas on the value of column 1:

     Column 1 Column 2 Column 3
0       15        0.2    0.2
1       11        0      0
2       13        0.4    1.2
3       19        0.4    1.2
4       14        0.4    1.2
5       2         0      0 
6       1         0      0
7       15        0.4    0.4 
8       3         0      0

Required result:
     Column 1 Column 2 Column 3 Column 4
0       15        0.2    0.2       15
1       11        0      0         11
2       13        0.4    1.2       13 
3       19        0.4    1.2       13 
4       14        0.4    1.2       13 
5       2         0      0         2
6       1         0      0         1
7       15        0.4    0.4       15
8       3         0      0         3

Who can give me the most elegant solution? Thanking you all in advance!

RENS
  • 3
  • 2

1 Answers1

0

Let's try

m = df['Column 2'].eq(0)

df['Column 3'] = (df['Column 2'].mask(m, 0)              # convert value with True to 0
                  .groupby(m.cumsum()).transform('sum')  # sum value in each group
                  .mask(m, 0))                           # convert value with True to 0

df['Column 4'] = (df['Column 1'].mask(m, float('inf'))
                  .groupby(m.cumsum()).transform('min')
                  .mask(m, df['Column 1']))

print(df)

   Column 1  Column 2  Column 3  Column 4
0        15       0.2       0.2      15.0
1        11       0.0       0.0      11.0
2        13       0.4       1.2      13.0
3        19       0.4       1.2      13.0
4        14       0.4       1.2      13.0
5         2       0.0       0.0       2.0
6         1       0.0       0.0       1.0
7        15       0.4       0.4      15.0
8         3       0.0       0.0       3.0
Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
  • @RENS Can you tell me how many `=` in my code? – Ynjxsjmh Oct 26 '22 at 06:53
  • This works perfectly! I just forgot to mention one property in my question, that you can maybe help me with: If the value of column 3 is smaller than 1 it should just yield a certain value (12 for example). It works already fine for the case that column 3 is bigger than 1. Thank you so much! – RENS Oct 26 '22 at 07:38
  • @RENS Check the doc of `Series.clip` – Ynjxsjmh Oct 26 '22 at 07:41
  • @RENS Just think that clip is not the way, check https://stackoverflow.com/questions/21608228/conditional-replace-pandas instead. – Ynjxsjmh Oct 26 '22 at 07:49
  • I solved it with a for loop but this might not be the most efficient solution: for x in range(len(df['Column 1'])): if 0 – RENS Oct 26 '22 at 08:14
  • @RENS Check the link I post, there are multiple ways to do in one line. – Ynjxsjmh Oct 26 '22 at 08:16