asisgn same values to column with column value after that in pandas-CodePudding

I have a dataframe df:

df = pd.DataFrame({'col_1':[1,1,1,-1,2,2,2,-1],
                   'col_2':[2,2,2,-1,3,3,3,-1],
                   'col_3':[0,0,0,10,0, 0,0,20 ]})

I wanted to change the col_3 such that, it uses the value of col_3 with the value of -1 (in col_1 and col_2) and removes the rows with -1. my output should look like this,

df = pd.DataFrame({'col_1':[1,1,1,2,2,2],
                  'col_2':[2,2,2,3,3,3],
                  'col_3':[10,10,10,20,20,20 ]})

CodePudding user response：

you can chain boolean conditions and use assign with .bfill

s = df['col_1'].eq(-1) & df['col_2'].eq(-1)

df1 = df.assign(col_3=df.loc[s,'col_3']).bfill().loc[~s]

print(df1)

   col_1  col_2  col_3
0      1      2   10.0
1      1      2   10.0
2      1      2   10.0
4      2      3   20.0
5      2      3   20.0
6      2      3   20.0

CodePudding user response：

I would use boolean indexing and bfill:

m = df['col_1'].ne(-1)
# NB. using only col_1 as condition as the example doesn't show
# what should happen if only one of col_1 col_2 is -1.
# to generalize use all/any:
# m = df[['col_1', 'col_2']].ne(-1).any(1)

out = (df
 .assign(col_3=df['col_3'].mask(m).bfill())
 .loc[m]
)

output:

   col_1  col_2  col_3
0      1      2   10.0
1      1      2   10.0
2      1      2   10.0
4      2      3   20.0
5      2      3   20.0
6      2      3   20.0