I have a data frame (2 columns: Text and HD/TTL) in the format below:
Text HD/TTL
ABC HD
DEF
GHI HD
JKL
MNO
PQR HD
I want it transformed into a new data frame (with 2 columns: HD and Text) as:
HD Text
HD ABC\nDEF
HD GHI\nJKL\nMNO
HD PQR\n
where \n is the new line between the text. How can I go about it?
df
###
Text HD/TTL
0 ABC HD
1 DEF NaN
2 GHI HD
3 JKL NaN
4 MNO NaN
5 PQR HD
g = df['HD/TTL'].notnull().cumsum()
v = df.groupby(g).apply(lambda x: x['Text'].str.cat(sep='\\n'))
output = pd.DataFrame({'HD': df.groupby(g)['HD/TTL'].first().values, 'Text': v}).reset_index(drop=True)
output
###
HD Text
0 HD ABC\nDEF
1 HD GHI\nJKL\nMNO
2 HD PQR