Transpose and append data in python-CodePudding

I have a dataframe that looks like this:

And I'd like to transpose and append it in python such that it looks like this:

CodePudding user response：

import pandas as pd

#Data
col1 = ['Student1', 'Age1', 'Student2', 'Age2']
col2 = ['Ian', '23', 'John', '34']
df = pd.DataFrame({'v1':col1, 'v2':col2})

#Split column names using regex based on string versus integer
df_out = df['v1'].str.split('([A-Za-z] )', expand=True)[[1, 2]]

#Add data values to new dataframe
df_out['value'] = df['v2']

#Make pivot table based on the integer split from the column name
df_out = df_out.pivot(index=2, columns=1)

#Fix indexes and column names
df_out = df_out.reset_index(drop=True)
df_out.columns = [i[1] for i in df_out.columns]