Can I shift specific values in one data column to another column while keeping the other values unchanged?

user_15369326 0
Viewed 38

Here is an example dataset that I have:

C1      C2
 1       1
NaN      1
 2       0
NaN      0
NaN      1
 1       1
 2       2
 2       2
NaN      1

I want to take all the values that have "1" in them in the Column "C2" and shift them to replace the adjacent values in column "C1".

So the output should look like:

C1      C2
 1       1
 1       1
 2       0
 NaN     0
 1       1
 1       1
 2       2
 2       2
 1       1

Alternatively, I could create a new column with these values replaced. Main point is, that I need all the "1s" in C2 TO replace the NaN values in C1.

I can't do find all NaN and replace with 1, because there are some NaN values that should stay in C1.

Is there a way to do this?

Thanks for the help in advance.

pandas dataframe python
3 Answers

You can try with fillna on C1 along with series.wherewhich would help retain only 1s from C2

df['C1'] = df['C1'].fillna(df['C2'].where(df['C2'].eq(1)))

    C1  C2
0  1.0   1
1  1.0   1
2  2.0   0
3  NaN   0
4  1.0   1
5  1.0   1
6  2.0   2
7  2.0   2
8  1.0   1
user_98406370

You could use the DF.mask() api to apply values from one column to another in rows where some condition is true (e.g. ==1).

value = 1
source_col = 1
target_col = 0

condition = df[source_col] == value

df[target_col] = df[target_col].mask(condition,
                                     df[source_col])
user_153324480

You can use bfill/ffill with limit=1:

df['C1'] = df['C1'].fillna(df.loc[df.C2.eq(1), 'C1']
                             .ffill(limit=1).bfill(limit=1)
                          )

Output:

    C1  C2
0  1.0   1
1  1.0   1
2  2.0   0
3  NaN   0
4  1.0   1
5  1.0   1
6  2.0   2
7  2.0   2
8  1.0   1
user_42384080
Related