convert data elements in data frame via long to wide format using Panda

Question

I have my df as below:

df

I want to convert into df2 as below:

df2

I have been trying with panda, but still not able to achieve my targeted aim.

This is my sample code:

df = pd.read_excel('PathV1.xlsx')
df1 = pd.DataFrame(df, columns=['source'])
df1[['target']] = pd.DataFrame(df1['source'].str.split(':').tolist())
df1['index'] = df1.index// 3
df2 = df1[['source', 'target', 'index']].pivot(columns='source', values='target', index='index')

Appreciate any suggestions from anyone.

Many Thanks

Answer 1

First extract the values from your DataFrame that you want to transform:
data = df2.loc[:, ["source", "target"]]

Then flatten these into a single array:
data = data.values.flatten()

Now assemble them into a new DataFrame:
new_df = pd.DataFrame(data).T