Pandas combine/coalesce multiple columns into 1

Question

I want to combine 2 columns into 1 in pandas, when I searched on google, the only options I got were:merge,concatenate, join. Neither of those solve the issue I'm having here.

Goal:

from base_dataframe:

Name Mark_x Mark_y Mark_z
A    90     nan    nan
B    nan    85     nan
C    nan    nan    70
D    30     nan    nan

to desired_dataframe:

Name Mark
A    90
B    85
C    70
D    30

What I have is the above dataframe and only 1 of the Mark columns will ever be not null(prerequisite). Stuck on google search for 1 hour now and don't know what function I should look into that will help solve this. If you know any function that could be related to this problem, please let me know so I can look into it, thanks.

`df.set_index('Name').stack().reset_index(level=1, drop=True)` — It_is_Chris, Apr 02 '20 at 20:19
https://stackoverflow.com/questions/38152389/coalesce-values-from-2-columns-into-a-single-column-in-a-pandas-dataframe — ChrisG, Apr 02 '20 at 20:21
https://stackoverflow.com/questions/43177685/how-to-implement-sql-coalesce-in-pandas/43177739 — ChrisG, Apr 02 '20 at 20:26

Arne · Accepted Answer · 2020-04-02T21:10:24.133

0

Assuming there is always only one value per row across those three columns, as in your example, you could use df.sum(), which skips any NaN by default:

desired_dataframe = pd.DataFrame(base_dataframe['Name'])
desired_dataframe['Mark'] = base_dataframe.iloc[:, 1:4].sum(axis=1)

In case of potentially more values per row, it would perhaps be safer to use e.g. df.max() instead, which works in the same way.

edited Apr 02 '20 at 21:10

answered Apr 02 '20 at 20:44

Arne

9,990
2
18
28

if it works, it might be the simplest solution. Thanks. – M. Albert Apr 02 '20 at 21:16
1

Just make sure the missing values are coded as `np.NaN`. – Arne Apr 02 '20 at 22:05

Pandas combine/coalesce multiple columns into 1

Goal:

1 Answers1