Lets say I have pandas data frame like this: What I would like to have is: I tried using apply and join as below but not getting the output as I wish: How can I get output that I want by modifying above code. Thank in advance. Edit: I am looking solution using above code to get the expected output.

Join multiple columns into one column

Lets say I have pandas data frame like this:

import numpy as np
import pandas as pd
my_df = pd.DataFrame(columns=['column 1', 'column 2', 'column 3'], data = [['17.1 g', np.nan, np.nan], [np.nan, '7.0 g', np.nan], [np.nan, '3.7 g', '0.7 g'], [np.nan, np.nan, '1.7 g'], ['1.1 g', np.nan, '1.0 g']])

What I would like to have is:

    column1 column2 column3  column_new
0    17.1 g    NaN    NaN     17.1 g
1     NaN      7.0 g  NaN     7.0 g
2     NaN      3.7 g  0.7 g   3.7 g
3     NaN      NaN    1.7 g   1.7 g
4     1.1 g    NaN    1.0 g   1.1 g

I tried using apply and join as below but not getting the output as I wish:

my_df['column_new'] = my_df[my_df.columns[0:]].apply(lambda x: ' '.join(x.dropna().astype(str)), axis=1)
my_df

    column 1    column 2    column 3    column_new
0   17.1 g       NaN         NaN        17.1 g
1   NaN          7.0 g       NaN        7.0 g
2   NaN          3.7 g       0.7 g      3.7 g 0.7 g
2   NaN          NaN         1.7 g      1.7 g
3   1.1 g        NaN         1.0 g      1.1 g 1.0 g

How can I get output that I want by modifying above code. Thank in advance.

Edit: I am looking solution using above code to get the expected output. Although codes in answers using bfill and row-wise max are nice, they don’t work well on the large data that I have.

Answer

We can using bfill

df['New']=df.bfill(axis=1).iloc[:,0]
df
  column 1 column 2 column 3     New
0   17.1 g      NaN      NaN  17.1 g
1      NaN    7.0 g      NaN   7.0 g
2      NaN    3.7 g    0.7 g   3.7 g
3      NaN      NaN    1.7 g   1.7 g
4    1.1 g      NaN    1.0 g   1.1 g

Advertisement

Answer