Pandas: overlay a column into row with a blank one

These two lines of code should solve the problem for you.

df.manager2 = df.manager2.bfill().ffill()
df.loc[df.manager1.isnull(), 'manager2'] = np.NaN

Below are a few scenarios I tried and the code is the same. See if this is what you want.

import pandas as pd
import numpy as np
c=['party_num','dcc3','manager1','manager2']

Scenario 1:

row 1: manager1 = NaN, manager2 = value

Results: assign manager2 value to row 2

print ('\nScenario 1')
print ('row 1: manager 1: NaN, manager 2: value; pick row2 manager 1 value')
d  = [['L21635789','SBAS01030',np.NaN,'A22810282'],
     ['L21635789','SBAS02030','A22677981',np.NaN],
     ['L21635789','SBAS03030',np.NaN,'A21721880']]

df = pd.DataFrame(data=d,columns=c)
print (df)
df.manager2 = df.manager2.bfill().ffill()
df.loc[df.manager1.isnull(), 'manager2'] = np.NaN
print ()
print (df)

Output for Scenario 1:

Scenario 1
row 1: manager 1: NaN, manager 2: value; pick row2 manager 1 value
   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030        NaN  A22810282
1  L21635789  SBAS02030  A22677981        NaN
2  L21635789  SBAS03030        NaN  A21721880

   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030        NaN        NaN
1  L21635789  SBAS02030  A22677981  A21721880
2  L21635789  SBAS03030        NaN        NaN

Scenario 2:

row 1: manager1 = value, manager2 = NaN

Results: assign manager2 value to row 1

print ('\nScenario 2')
print ('row 1: manager 1: value, manager 2: NaN; pick row2 manager 2 value')

d = [['L21635789','SBAS01030','A22677981',np.NaN],
     ['L21635789','SBAS02030',np.NaN,'A22810282'],
     ['L21635789','SBAS03030',np.NaN,'A21721880']]

df = pd.DataFrame(data=d,columns=c)
print (df)
df.manager2 = df.manager2.bfill().ffill()
df.loc[df.manager1.isnull(), 'manager2'] = np.NaN
print ()
print (df)

Output for Scenario 2:

Scenario 2
row 1: manager 1: value, manager 2: NaN; pick row2 manager 2 value
   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030  A22677981        NaN
1  L21635789  SBAS02030        NaN  A22810282
2  L21635789  SBAS03030        NaN  A21721880

   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030  A22677981  A22810282
1  L21635789  SBAS02030        NaN        NaN
2  L21635789  SBAS03030        NaN        NaN

Scenario 3:

row 1: manager1 = NaN, manager2 = NaN

row 2: manager1 = value; manager2 = NaN; row 3: manager2 = value

Results: assign manager3 value to row 2

print ('\nScenario 3')
print ('row 1: manager 1: NaN, manager 2: NaN; pick row2 manager 1 & row 3 manager 2')

d = [['L21635789','SBAS01030',np.NaN,np.NaN],
     ['L21635789','SBAS02030','A22677981',np.NaN],
     ['L21635789','SBAS03030',np.NaN,'A21721880']]

df = pd.DataFrame(data=d,columns=c)
print (df)
df.manager2 = df.manager2.bfill().ffill()
df.loc[df.manager1.isnull(), 'manager2'] = np.NaN
print ()
print (df)

Output for Scenario 3:

Scenario 3
row 1: manager 1: NaN, manager 2: NaN; pick row2 manager 1 & row 3 manager 2
   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030        NaN        NaN
1  L21635789  SBAS02030  A22677981        NaN
2  L21635789  SBAS03030        NaN  A21721880

   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030        NaN        NaN
1  L21635789  SBAS02030  A22677981  A21721880
2  L21635789  SBAS03030        NaN        NaN

Scenario 4:

row 1: manager1 = value, manager2 = NaN

row 3: manager1 = value, manager2 = value

Results: ignore rows 1 and 2 as row3 has values for both manager1 and manager2

print ('\nScenario 4')
print ('row 1: manager 1: NaN, manager 2: value; row3 has both manager 1 & manager 2')

d = [['L21635789','SBAS01030',np.NaN,'A21721880'],
     ['L21635789','SBAS02030',np.NaN,np.NaN],
     ['L21635789','SBAS03030','A22677981','A21721882']]

df = pd.DataFrame(data=d,columns=c)
print (df)
df.manager2 = df.manager2.bfill().ffill()
df.loc[df.manager1.isnull(), 'manager2'] = np.NaN
print ()
print (df)

Output for Scenario 4:

Scenario 4
row 1: manager 1: NaN, manager 2: value; row3 has both manager 1 & manager 2
   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030        NaN  A21721880
1  L21635789  SBAS02030        NaN        NaN
2  L21635789  SBAS03030  A22677981  A21721882

   party_num       dcc3   manager1   manager2
0  L21635789  SBAS01030        NaN        NaN
1  L21635789  SBAS02030        NaN        NaN
2  L21635789  SBAS03030  A22677981  A21721882

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top