Search code examples
pythonpandasmulti-index

Merge two MultiIndex levels into one in Pandas


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.


Solution

  • 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