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!

Answer 1

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