I have the following DataFrame:

I want to transpose data to this format:

Can anyone direct me on how to do this? Thanks!

CodePudding user response：

Use DataFrame.pivot with flatten MultiIndex and then sorting months by sorted CategoricalIndex:

df = df.pivot('Month','ID')
df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
cats = ['Jan', 'Feb', 'Mar', 'Apr','May','Jun', 'Jul', 'Aug','Sep', 'Oct', 'Nov', 'Dec']
df.index = pd.CategoricalIndex(df.index, categories=cats, ordered=True)
df = df.sort_index()

print (df)
       a_1   a_2  a_3  b_1  b_2  b_3  c_1  c_2   c_3
Month                                               
Jan    0.1  0.02  0.1  0.3  0.5  0.4  0.5  0.1  0.70
Feb    0.2  0.30  0.1  0.5  0.1  0.2  0.5  0.3  0.05

Or by key parameter in DataFrame.sort_index:

df = df.pivot('Month','ID')
df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
df = df.sort_index(key=lambda x: pd.to_datetime(x, format='%b'))

print (df)
       a_1   a_2  a_3  b_1  b_2  b_3  c_1  c_2   c_3
Month                                               
Jan    0.1  0.02  0.1  0.3  0.5  0.4  0.5  0.1  0.70
Feb    0.2  0.30  0.1  0.5  0.1  0.2  0.5  0.3  0.05

CodePudding user response：

d1 = {'col1': [1, 2], 'col2': [3, 4]}
df1 = pd.DataFrame(data=d1)
df1
   col1  col2
0     1     3
1     2     4
df1_transposed = df1.T # or df1.transpose()
df1_transposed
      0  1
col1  1  2
col2  3  4

When the dtype is homogeneous in the original DataFrame, we get a transposed DataFrame with the same dtype: for more info and tutorial visit this link: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.transpose.html

https://www.geeksforgeeks.org/python-pandas-dataframe-transpose/