How to deal with multi-index in pandas dataframe?

Question

I have the following dataframe: [1]: https://i.stack.imgur.com/3gvRa.png

which has three sub-levels of indices. The first one is a number of 4 digits, the second is a date, and the last one is an index from the original dataframe.

I want to rename each level by ['District_ID', 'Month'] and to drop the third level. I already tried to drop the last one, and I used:

DF.index = DF.index.droplevel(2)

As a result, the third level is gone but the second one duplicates for all the rows. In addition I want to rename the column indices. How can I accomplish these tasks?

Answer 1

I think you have a mistaken impression of what is going on. Take this simple sample frame

midx = pd.MultiIndex.from_tuples(
    (lev_0, lev_1, lev_2) for lev_0, lev_1 in zip("ab", "xy") for lev_2 in range(2)
)
df = pd.DataFrame({"col": range(4)}, index=midx)

       col
a x 0    0
    1    1
b y 0    2
    1    3

and look at the result of

print(df.index)
df = df.droplevel(2)
print(df.index)

MultiIndex([('a', 'x', 0),
            ('a', 'x', 1),
            ('b', 'y', 0),
            ('b', 'y', 1)],
           )

MultiIndex([('a', 'x'),
            ('a', 'x'),
            ('b', 'y'),
            ('b', 'y')],
           )

This should be exactly what you want? If you print the df after the droplevel it looks as if there's something strange happening with the first level, but this is only for making the print clearer.

As for the renaming:

df.index.names = ["lev_0", "lev_1"]

or

df.index.set_names(["lev_0", "lev_1"], inplace=True)

both lead to

             col
lev_0 lev_1     
a     x        0
      x        1
b     y        2
      y        3

Or if you want to rename the columns (not clear to me what you are looking for), then you could do

df.columns = ["new_col"]

or

df = df.rename(columns={"col": "new_col"})

     new_col
a x        0
  x        1
b y        2
  y        3