Shaping a Pandas DataFrame (multiple columns into 2)-CodePudding

I have a simular dataframe to below and require it to be shaped as per expected output.

df = pd.DataFrame({
                   'col1': ['A', 'A', 'A', 'B', 'B', 'B'],
                   'col2': [1, 3, 5, 7, 9, 11],
                   'col3': [2, 4, 6, 8, 10, 12]
                  })

    col1  col2  col3
0    A     1     2
1    A     3     4
2    A     5     6
3    B     7     8
4    B     9    10
5    B    11    12

Expected Output

df_expected = pd.DataFrame({
                            'A': [1, 2, 3, 4, 5, 6],
                            'B': [7, 8, 9, 10, 11, 12]
                           })

   A   B
0  1   7
1  2   8
2  3   9
3  4  10
4  5  11
5  6  12

So far I have tried pack, unpack & pivot without getting the desired result

Thanks for your help!

CodePudding user response：

pd.DataFrame(df.groupby('col1').agg(list).T.sum().to_dict())

CodePudding user response：

Use Numpy to reshape the data then package back up into a dataframe.

cols = (df['col2'],df['col3'])
data = np.stack(cols,axis=1).reshape(len(cols),len(df))
dft = pd.DataFrame(data, index=df['col1'].unique()).T

print(dft)

Result