Merge two MultiIndex levels into one in Pandas

Question

I have a Pandas data frame which is MultiIndexed. The second level contains a year ([2014,2015]) and the third contains the month number ([1, 2, .., 12]). I would like to merge these two into a single level like - [1/2014, 2/2014 ..., 6/2015]. How could this be done?

I'm new to Pandas. Searched a lot but could not find any similar question/solution.

Edit: I found a way to avoid having to do this altogether with the answer to this question. I should have been creating my data frame that way. This seems to be the way to go for indexing by DateTime.

Answer 1

Consider the pd.MultiIndex and pd.DataFrame, mux and df

mux = pd.MultiIndex.from_product([list('ab'), [2014, 2015], range(1, 3)])

df = pd.DataFrame(dict(A=1), mux)

print(df)

          A
a 2014 1  1
       2  1
  2015 1  1
       2  1
b 2014 1  1
       2  1
  2015 1  1
       2  1

---

We want to reassign to the index a list if lists that represent the index we want.

• I want the 1st level the same

  df.index.get_level_values(0)
  

• I want the new 2nd level to be a string concatenation of the current 2nd and 3rd levels but reverse the order

  df.index.map('{0[2]}/{0[1]}'.format)
  

---

df.index = [df.index.get_level_values(0), df.index.map('{0[2]}/{0[1]}'.format)]

print(df)

          A
a 1/2014  1
  2/2014  1
  1/2015  1
  2/2015  1
b 1/2014  1
  2/2014  1
  1/2015  1
  2/2015  1

Answer 2

You can use a list comprehension to restructure your index. For example, if you have a 3 levels index and you want to combine the second and the third levels:

lst = [(i, f'{k}/{j}') for i, j, k in df.index]
df.index = pd.MultiIndex.from_tuples(lst)