How to fill the rightmost column with values in pandas

Question

There is an unknown number of columns, and each row has exactly one value.

However, I cannot tell which column the number is in.

I would like to know how to fill one value from each row into the rightmost column.

The example below consists of three columns, but I don't know how many there actually are.

import pandas as pd
import io

temp = u"""
col1,col2,col3
nan,nan,3
nan,4,nan
1,nan,nan
"""

data = pd.read_csv(io.StringIO(temp), sep=",")

# data
# Out[31]: 
#    col1  col2  col3
# 0   NaN   NaN   3.0
# 1   NaN   4.0   NaN
# 2   1.0   NaN   NaN


What I want:

# data2
#     col3
# 0    3.0
# 1    4.0
# 2    1.0

Answer 1

Since you know you have exactly one value in each row, you can add all the rows.

import pandas as pd
import io

temp = u"""
col1,col2,col3
nan,nan,3
nan,4,nan
1,nan,nan
"""

data = pd.read_csv(io.StringIO(temp), sep=",")

---

data['col4'] = data.sum(axis=1, numeric_only=True)

---

One more way is to :

data['col4'] = data.loc[:,[*data.columns.values]].sum(axis=1)

Output

pandas.DataFrame.sum

Answer 2

If you are sure there is only one value in each row, you can just sum them over the columns. The NaN's will be ignored.

import pandas as pd
import io

temp = u"""
col1,col2,col3
nan,nan,3
nan,4,nan
1,nan,nan
"""

data = pd.read_csv(io.StringIO(temp), sep=",")
print(data.sum(axis=1))

0    3.0
1    4.0
2    1.0
dtype: float64

Answer 3

In Pandas you can use the .bfill() method along the axis=1 to fill in missing values.

you should do something like this:

data2 = data.bfill(axis=1).iloc[:, -1].to_frame()

print(data2)

and this should work.

Answer 4

If all values are NaN except one on each row, you can also use max:

>>> data.max(axis=1)
0    3.0
1    4.0
2    1.0
dtype: float64

For the same expected output:

>>> data.ffill(axis=1)[['col3']]
   col3
0   3.0
1   4.0
2   1.0