Combine several columns into one column when there is only one value per row-CodePudding

I have this df with only one value per column between y1 and y4

       x        y1        y2          y3           y4   
0  -17.7 -0.785430       NaN         NaN          NaN 
1  -15.0       NaN       NaN         NaN -3820.085000 
2  -12.5       NaN       NaN    2.138833          NaN

I want to combine all y columns in one column y.

Edit: Also, I forgot I need another column to tell me which of the 4 y columns the value belongs to.

The output I need is this:

       x             y       no   
0  -17.7     -0.785430       y1
1  -15.0  -3820.085000       y4
2  -12.5      2.138833       y3

CodePudding user response：

Let us try groupby with first

out = df.groupby(df.columns.str[0],axis=1).first()
Out[60]: 
      x            y
0 -17.7    -0.785430
1 -15.0 -3820.085000
2 -12.5     2.138833

CodePudding user response：

Another possible solution:

df.assign(y = df.iloc[:,1:].sum(axis=1)).dropna(axis=1)

Output:

      x            y
0 -17.7    -0.785430
1 -15.0 -3820.085000
2 -12.5     2.138833