Table in desired format

July 7, 2022

I am trying to reshape the table in accordance with my needs. Below you can see a small example of my data.

import pandas as pd
import numpy as np
pd.set_option('max_columns', None)

data = {
        'type_sale': ['open','closed'],
        'group_1': [20,10],
        'group_2': [20,10],
        'group_3': [20,10],
        'group_4': [20,10],
        'group_5': [20,10],
        'group_6': [20,10],
        'group_7': [20,10],
        'group_8': [20,10],
        'group_9': [20,10],
        'group_10': [20,10],
        }


df = pd.DataFrame(data, columns = ['type_sale',
                                   'group_1',
                                   'group_2',
                                   'group_3',
                                   'group_4',
                                   'group_5',
                                   'group_6',
                                   'group_7',
                                   'group_8',
                                   'group_9',
                                   'group_10',
                                   ])

Now I would like to change the table in the format described below

I tried with this line of code but I can’t get the desired format shown in the pic above.

df1=df.melt(id_vars=["type_sale"],var_name="groups")

So can anybody help me to solve this problem?

>Solution :

Simplest in my opinion, set_index and transpose:

out = df.set_index('type_sale').T

or:

out = df.set_index('type_sale').rename_axis(None).T

output:

          open  closed
group_1     20      10
group_2     20      10
group_3     20      10
group_4     20      10
group_5     20      10
group_6     20      10
group_7     20      10
group_8     20      10
group_9     20      10
group_10    20      10