Replace values from column series into respective row valued 1

Question

I have a csv table with 3842 rows and 36columns similar to:

Time,chlo,coord1,coord2,coord3 

2003,0.52, NaN, NaN, 1.0

2003,0.56, NaN, 1.0, NaN

2003,0.58, 1.0, NaN, NaN

I need a code that will automatically replace 1.0 with the values from the left column CHLO, respectively, within each row. At the end, the column CHLO should disappear.

The final result would be similar to:

Time,coord1,coord2,coord3 

2003, NaN, NaN, 0.52

2003, NaN, 0.56, NaN

2003, 0.58, NaN, NaN

I am a beginner, I have learnt some basics of python and managed to write code for sorting the data to a certain level. But to do the above, I have no idea. I need this to organize data for a research project.

I read explanations about array, iterate, dict but I could not get to what I need. I would be extremely grateful if someone could give me a hint!

jezrael · Accepted Answer · 2019-07-22 09:05:00Z

2

Use DataFrame.mask for replace by condition, DataFrame.pop is for extract column chlo.

If first column is not index:

df.iloc[:, 2:] = df.iloc[:, 2:].mask(df == 1, df.pop('chlo'), axis=0)
print (df)
   Time  coord1  coord2  coord3
0  2003     NaN     NaN    0.52
1  2003     NaN    0.56     NaN
2  2003     1.0     NaN     NaN

If first column is index:

df = df.mask(df == 1, df.pop('chlo'), axis=0)
print (df)

      coord1  coord2  coord3
Time                        
2003     NaN     NaN    0.52
2003     NaN    0.56     NaN
2003    0.58     NaN     NaN

answered Jul 22, 2019 at 9:05

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Andurush Over a year ago

This was wonderful help! I solved the problem. Is there anything that I should do when the received answer solved my question?

Collectives™ on Stack Overflow

Replace values from column series into respective row valued 1

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related