current_df = pd.DataFrame(
    [
        ['honda crv', 9000, 9100, 9200],
        ['mazda cx5', 9300, 10000, 10100],
        ['mazda cx5', 29300, 310000, 510100],
    ],
    columns=['car', 'john', 'peter', 'kate']
)

How do I transform this into dataframe with multiple index ['car', 'salesman']?

From this

Current dataframe

To this

Desired dataframe

Solution (by simon)

pd.DataFrame(current_df.set_index('car').stack())

CodePudding user response：

pd.DataFrame( current_df.set_index('car').stack() )

CodePudding user response：

Building on Simons answer (which should be accepted), to also get the correct headers add the following.

current_df = (
    pd
    .DataFrame(current_df.set_index("car").stack())
    .rename(columns={0: "price"})
    .rename_axis(("car", "salesman"))
)
print(current_df)

                     price
car       salesman        
honda crv john        9000
          peter       9100
          kate        9200
mazda cx5 john        9300
          peter      10000
          kate       10100
          john       29300
          peter     310000
          kate      510100