Create multiple column with a condition

Question

I have a dataset with columns "Rate","value1","value2". I want to generate new columns each time the rate changes. Whenever the rate changes the values will be populated from first row. I have attached an image file for the reference. I want to generate "newvalue1", "newvalue2", "newvalue3"

    Rate  value1  value2
0      2       5       1
1      2       3       6
2      2       5       0
3      2       3       3
4      2       6       6
5      3       3       1
6      3       1       4
7      3       9       7
8      4       6       8
9      4       0       4
10     4       4       2
11     4       6       7
12     5       7       9
13     5       8       0

jezrael · Accepted Answer · 2018-12-20 09:14:58Z

Create Series of differences, compare for not equal 0 and add cumulative sum, then in for loop create new columns with numpy.where

s = df['Rate'].diff().ne(0).cumsum()
for x in s.unique()[:-1]:
    #python 3.6+ 
    df[f'New{x}'] = np.where(s <= x, df['value1'], df['value2'])
    #python bellow
    #df['New{}'.format(x)] = np.where(s <= x, df['value1'], df['value2'])

print (df)
    Rate  value1  value2  New1  New2  New3
0      2       5       1     5     5     5
1      2       3       6     3     3     3
2      2       5       0     5     5     5
3      2       3       3     3     3     3
4      2       6       6     6     6     6
5      3       3       1     1     3     3
6      3       1       4     4     1     1
7      3       9       7     7     9     9
8      4       6       8     8     8     6
9      4       0       4     4     4     0
10     4       4       2     2     2     4
11     4       6       7     7     7     6
12     5       7       9     9     9     9
13     5       8       0     0     0     0

Collectives™ on Stack Overflow

Create multiple column with a condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related