Search code examples
pythonpandasstackmulti-index

sort dataframe using MULTIINDEX

I am a newbie and in a huge need of help of changeing my dataframe with use of multi-indexing,

How my dataframe looks like, Notice Reading it from a csv file.

enter image description here

How I want it to look,

enter image description here

Please someone help me, I have been with this for long time. I have tried the stack function but it doesn't work well at all. Please help me..

What i have managed to do is to:

import pandas as pd

path = "C:\Desktop\data.csv" df=pd.read_csv(path, sep=';')

thank you in advance!

Solution

  • The function you are looking for is called .stack(). Docs here: https://pandas.pydata.org/pandas-docs/stable/reshaping.html#reshaping-by-stacking-and-unstacking

    An example:

    import pandas as pd

dict_ = {
    "date": [2017,2016,2015,2014],
    "total members": [150,140,100,150],
    "lions": [20,10,5,50]
}

df = pd.DataFrame.from_dict(dict_)
table = df.set_index("date").stack() # sets date as index
table.to_frame(name='Amount').to_excel('output.xlsx') # names the column: Amount and outputs to an xlsx file

    Prints:

    date               
2017  lions             20
      total members    150
2016  lions             10
      total members    140
2015  lions              5
      total members    100
2014  lions             50
      total members    150
dtype: int64

    A comment on your original post

    In the future, you can easily share your dataframe here with:

    df.head().to_dict() # .head() return first 5 rows

    This sample outputs:

    {'date': {0: 2017, 1: 2016, 2: 2015, 3: 2014},
 'lions': {0: 20, 1: 10, 2: 5, 3: 50},
 'total members': {0: 150, 1: 140, 2: 100, 3: 150}}