Copy values from one column to other columns in same row based on specific criteria in Pandas

Question

I have a data frame like below:

import pandas as pd

df = pd.DataFrame({'ID':['M001','M002','M003','M004','M005'],
                      'X001':[0,0,1,0,0],
                      'X002':[0,0,1,1,0],
                      'X003':[0,0,1,0,1],
                      'X004':[1,0,1,0,0],
                      'X005':[1,0,1,1,0]})

print(df)

And it looks like this:

     ID  X001  X002  X003  X004  X005
0  M001     0     0     0     1     1
1  M002     0     0     0     0     0
2  M003     1     1     1     1     1
3  M004     0     1     0     0     1
4  M005     0     0     1     0     0

What I want to do is to copy the value in the ID column into the other columns based where the value is 1 as shown below.

     ID  X001  X002  X003  X004  X005
0  M001     0     0     0  M001  M001
1  M002     0     0     0     0     0
2  M003  M003  M003  M003  M003  M003
3  M004     0  M004     0     0  M004
4  M005     0     0  M005     0     0

What would be the easiest and fastest way to do so on a ~2000 x ~2000 data frame?

sacuL · Accepted Answer · 2018-10-26 23:07:27Z

2

Here is a way, replacing 1 with a null value, Transposing, using fillna, and transposing back:

df.T.replace(1,pd.np.nan).fillna(df['ID']).T

     ID  X001  X002  X003  X004  X005
0  M001     0     0     0  M001  M001
1  M002     0     0     0     0     0
2  M003  M003  M003  M003  M003  M003
3  M004     0  M004     0     0  M004
4  M005     0     0  M005     0     0

answered Oct 26, 2018 at 23:07

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DSM · Accepted Answer · 2018-10-26 23:28:25Z

2

I might use where, for example:

In [218]: df.where(df != 1, df.ID, axis=0)
Out[218]: 
     ID  X001  X002  X003  X004  X005
0  M001     0     0     0  M001  M001
1  M002     0     0     0     0     0
2  M003  M003  M003  M003  M003  M003
3  M004     0  M004     0     0  M004
4  M005     0     0  M005     0     0

There's an np.where equivalent of this which, like usual, is slightly faster but I find it harder to read.

answered Oct 26, 2018 at 23:28

DSM

355k67 gold badges606 silver badges504 bronze badges

Collectives™ on Stack Overflow

Copy values from one column to other columns in same row based on specific criteria in Pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related