changing column to row with specific columns

Question

i have a json data which can genrate this type of table

but i wand categories data under category name with price range my required out put like this table

Answer 1

Using pd.pivot_table() you can do this:

First we need to make the new 'department' 'Total Pages' and add it to the dataframe.

df_tot = df.groupby(['category', 'date'])[['below_1k', 'below_10k', 'below_100k', 'above_100k']].sum().reset_index()
df_tot.loc[:, 'department'] = 'Total Pages'
df = pd.concat([df, df_tot])

Now we can pivot our dataframe.

df_pivot = df.pivot_table(index=['date', 'department'], columns=['category']).T
df_pivot = df_pivot.swaplevel()

Output:

date                   2022-08-22        ... 2022-09-05            
department                     CD Other  ...      Other Total Pages
category                                 ...                       
Colleges    above_100k       59.0  62.0  ...        NaN         NaN
Exam        above_100k        NaN   NaN  ...        8.0        17.0
StudyAbroad above_100k        1.0   1.0  ...        1.0         2.0
Colleges    below_100k       77.0  85.0  ...        NaN         NaN
Exam        below_100k        NaN   NaN  ...       26.0        63.0
StudyAbroad below_100k        4.0   5.0  ...        4.0         8.0
Colleges    below_10k        28.0  31.0  ...        NaN         NaN
Exam        below_10k         NaN   NaN  ...        9.0        20.0
StudyAbroad below_10k        28.0  24.0  ...       23.0        47.0
Colleges    below_1k          0.0   0.0  ...        NaN         NaN
Exam        below_1k          NaN   NaN  ...        0.0         0.0
StudyAbroad below_1k         26.0  24.0  ...       22.0        43.0