How to change format in DataFrame

0
votes

I have the following DataFrame:

Month ID a b c
Jan 1 0.1 0.3 0.5
Jan 2 0.02 0.5 0.1
Jan 3 0.1 0.4 0.7
Feb 1 0.2 0.5 0.5
Feb 2 0.3 0.1 0.3
Feb 3 0.1 0.2 0.05

I want to transpose data to this format:

Month a_1 a_2 a_3 b_1 b_2 b_3 c_1 c_2 c_3
Jan 0.1 0.02 0.1 0.3 0.5 0.4 0.5 0.1 0.7
Feb 0.2 0.3 0.1 0.5 0.1 0.2 0.5 0.3 0.05

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

2
pythonpandasdataframe

2 Answers

0
votes

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
-1
votes
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/