x is a dataframe:
x
year mar 31, 2024 mar 31, 2023
0 net income 306.000 524.0000
1 net income growth -0.416 -0.0455
2 retained rate NaN NaN
3 pe 419.930 0.0000
It’s row index and column name:
x.index
RangeIndex(start=0, stop=4, step=1)
x.columns
Index(['year', 'mar 31, 2024', 'mar 31, 2023'], dtype='object')
I want the desired new dataframe with below formats:
new_x
year net income net income growth retained rate pe
0 mar 31, 2024 306.0 -0.416 NaN 419.93
1 mar 31, 2023 524.0 -0.0455 NaN 0.0
new_x.index
RangeIndex(start=0, stop=1, step=1)
new_x.columns
['year', 'net income', 'net income growth', 'retained rate', 'pe']
How can get the new dataframe effectively?
x.T
0 1 2 3
year net income net income growth retained rate pe
mar 31, 2024 306.0 -0.416 NaN 419.93
mar 31, 2023 524.0 -0.0455 NaN 0.0
The transpose method can’t work.
You have to set_index, transpose, rename_axis:
out = x.set_index('year').T.rename_axis(index='year', columns=None).reset_index()
Output:
year net income net income growth retained rate pe
0 mar 31, 2024 306.0 -0.4160 NaN 419.93
1 mar 31, 2023 524.0 -0.0455 NaN 0.00
Or rename “year”, then melt+pivot:
out = (x.rename(columns={'year': 'cols'})
.melt('cols', var_name='year')
.pivot(index='year', columns='cols', values='value')
.rename_axis(columns=None).reset_index() # optional
)
Output:
year net income net income growth pe retained rate
0 mar 31, 2023 524.0 -0.0455 0.00 NaN
1 mar 31, 2024 306.0 -0.4160 419.93 NaN