Merge multiple lines into 1 line

Question

Data for pd.read_csv():

Name     Job  Place  Age
John    None  None  None
None  Doctor  None  None
None    None    UK  None
None    None  None    50
Alex    None  None  None
None    Engr  None  None
None    None    US  None
None    None  None    45

Single line information is contained in the diagonal. Is there a way to convert and collapse a diagonal into a string? The resulting data file will contain 2 lines.

Tried df.ffill()/ df.bfill()and df.drop_duplicates(), but it will not work.

Answer 1

You can use:

#change string None to NaN
df = df.replace({'None':np.nan})
#multiindex
df.index = [df.index, df.Name.notnull().cumsum() - 1]
#remove nan by stack
df = df.stack().reset_index(name='val')
#pivoting
df = df.pivot(index='Name', columns='level_2', values='val')
print (df)
level_2 Age     Job  Name Place
Name                           
0        50  Doctor  John    UK
1        45    Engr  Alex    US