Combining two columns to single Column-CodePudding

Having a data frame as below:

df1 = pd.DataFrame({'Name':['Raju', 'Divya', 'Geeta', 'Sita', 'Raju','Sita','Ramu'],
                    'Marks1':[80, 90, 'Nan', 88, 'Nan',40,'Nan'],'Marks2':['Nan','Nan', 30, 88,23,40,'Nan']})
df1

Need to combine/merge marks1 and marks2 and need to print output in the following format:

'Name':['Raju', 'Divya', 'Geeta', 'Sita', 'Raju','Sita','Ramu']

'Marks1':[80, 90,30, 88,23,40,'Nan']

CodePudding user response：

Try out with np.where

df1['Marks'] = np.where(df1.Marks1.eq('Nan'), df1.Marks2, df1.Marks1)

df1
Out[134]: 
    Name Marks1 Marks2 Marks
0   Raju     80    Nan    80
1  Divya     90    Nan    90
2  Geeta    Nan     30    30
3   Sita     88     88    88
4   Raju    Nan     23    23
5   Sita     40     40    40
6   Ramu    Nan    Nan   Nan

CodePudding user response：

You can set Names as index, bfill, slice and reset_index:

df1 = (df1.set_index('Name')
          .replace('Nan', pd.NA)
          .bfill(axis=1)['Marks1']
          .reset_index()
       )

Alternatively (and assuming string Nan), use boolean indexing and pop:

df1.loc[df1['Marks1'].eq('Nan'), 'Marks1'] = df1.pop('Marks2')
# if real NaN
# df1.loc[df1['Marks1'].isna(), 'Marks1'] = df1.pop('Marks2')

output:

    Name Marks1
0   Raju     80
1  Divya     90
2  Geeta     30
3   Sita     88
4   Raju     23
5   Sita     40
6   Ramu   <NA>

CodePudding user response：

You can use .loc[] for this:

df1.loc[df1['Marks1'] == 'Nan', 'Marks1'] = df1.pop('Marks2')

Now for the output:

print("'Name':", df1.Name.to_list())
print("'Marks1':", df1.Marks1.to_list())

Output:

'Name': ['Raju', 'Divya', 'Geeta', 'Sita', 'Raju', 'Sita', 'Ramu']
'Marks1': [80, 90, 30, 88, 23, 40, 'Nan']