Pandas - Change alignment of dataframe

Question

I've created a dataframe:

In [1]: import pandas as pd 

In [2]: import numpy as np

In [3]: df = pd.DataFrame({ 'Student_ID':['001','002','003','004','005'],
                'Amy'   : ['Amy',np.nan,np.nan,np.nan,'Amy'],
                'Brian' : [np.nan,'Brian',np.nan,np.nan,np.nan],
                'Cat'   : [np.nan,np.nan,np.nan,'Cat',np.nan]},columns=['Student_ID','Amy','Brian','Cat']) 


In  [4]:df
Out [4]:
    Student_ID  Amy Brian   Cat
0          001  Amy   NaN   NaN
1          002  NaN Brian   NaN
2          003  NaN   NaN   NaN
3          004  NaN   NaN   Cat
4          005  Amy   NaN   NaN

And next I'd like to return to a dataframe with two columns only, Student_ID and Name. How to convert to below in precise codes?

In [5]: df
Out[5]: 
  Student_ID    Name    
0        001     Amy    
1        002   Brian    
2        003     NaN    
3        004     Cat
4        005     Amy

BENY · Accepted Answer · 2018-10-09 15:42:35Z

4

You can using dot

df.iloc[:,1:].notna().dot(df.columns[1:])
Out[78]: 
0      Amy
1    Brian
2         
3      Cat
4      Amy
dtype: object
#df['name']=df.iloc[:,1:].notna().dot(df.columns[1:])

Or bfill

df.iloc[:,1:].bfill(1).iloc[:,0]
Out[82]: 
0      Amy
1    Brian
2      NaN
3      Cat
4      Amy

answered Oct 9, 2018 at 15:42

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

unutbu · Accepted Answer · 2018-10-09 16:42:28Z

4

You could use groupby/first, since first selects the first non-NaN item in each group:

In [146]: df.set_index('Student_ID').unstack().groupby(level='Student_ID').first().rename('Name').reset_index()
Out[146]: 
  Student_ID   Name
0        001    Amy
1        002  Brian
2        003    NaN
3        004    Cat
4        005    Amy

edited Oct 9, 2018 at 16:42

answered Oct 9, 2018 at 15:58

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Comments

ALollz · Accepted Answer · 2018-10-09 15:49:34Z

2

Using .lookup

df['Name'] = df.lookup(df.index, df.iloc[:, 1::].notnull().idxmax(1))

  Student_ID  Amy  Brian  Cat   Name
0        001  Amy    NaN  NaN    Amy
1        002  NaN  Brian  NaN  Brian
2        003  NaN    NaN  NaN    NaN
3        004  NaN    NaN  Cat    Cat
4        005  Amy    NaN  NaN    Amy

answered Oct 9, 2018 at 15:49

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

Collectives™ on Stack Overflow

Pandas - Change alignment of dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related