Reindexing Multiindex dataframe

Question

I have Multiindex dataframe and I want to reindex it. However, I get 'duplicate axis error'.

Product  Date            col1
A        September 2019     5
         October 2019       7
B        September 2019     2
         October 2019       4

How can I achieve output like this?

Product  Date            col1
A        January 2019      0
         February 2019     0
         March 2019        0
         April 2019        0
         May 2019          0
         June 2019         0
         July 2019         0
         August 2019       0
         September 2019    5
         October 2019      7
B        January 2019      0
         February 2019     0
         March 2019        0
         April 2019        0
         May 2019          0
         June 2019         0
         July 2019         0
         August 2019       0
         September 2019    2
         October 2019      4

First I tried this:

nested_df = nested_df.reindex(annual_date_range, level = 1, fill_value = 0)

Secondly,

nested_df = nested_df.reset_index().set_index('Date')
nested_df  = nested_df.reindex(annual_date_range, fill_value = 0)

score 0 · Answer 1 · answered Dec 25 '19 at 09:47

0

You should do the following for each month:

df.loc[('A', 'January 2019'), :] = (0)
df.loc[('B', 'January 2019'), :] = (0)

answered Dec 25 '19 at 09:47

Aleksandr Iurkin

66
8

I thought of doing this, I guess, it is not an optimal solution. Is there any other solution? – JuniorESE Dec 25 '19 at 10:14
`ValueError: cannot reindex from a duplicate axis`, This error is preventing me. – JuniorESE Dec 25 '19 at 10:15
@JuniorESE Could you share your code where the error arises? – Aleksandr Iurkin Dec 25 '19 at 11:02
@JuniorESE to optimize this code you can create two loops. The first for loop through Products, second nested loop through Date. In the body of this loop will be only one line that I mentioned above. – Aleksandr Iurkin Dec 25 '19 at 11:07

score 0 · Answer 2 · answered Dec 25 '19 at 21:22

Let df1 be your first data frame with non-zero values. The approach is to create another data frame df with zero values and merge both data frames to obtain the result.

dates = ['{month}-2019'.format(month=month) for month in range(1,9)]*2
length = int(len(dates)/2)
products = ['A']*length + ['B']*length
Col1 = [0]*len(dates)
df = pd.DataFrame({'Dates': dates, 'Products': products, 'Col1':Col1}).set_index(['Products','Dates'])

Now the MultiIndex is converted to datetime:

df.index.set_levels(pd.to_datetime(df.index.get_level_values(1)[:8]).strftime('%m-%Y'), level=1,inplace=True)

In df1 you have to do the same, i.e. change the datetime multiindex level to the same format:

df1.index.set_levels(pd.to_datetime(df1.index.get_level_values(1)[:2]).strftime('%m-%Y'), level=1,inplace=True)

I did it because otherwise (for example if datetimes are formatted like %B %y) the sorting of the MultiIndex by months goes wrong. Now it is sufficient to merge both data frames:

result = pd.concat([df1,df]).sort_values(['Products','Dates'])

The final move is to change the datetime format:

result.index.set_levels(levels = pd.to_datetime(result.index.get_level_values(1)[:10]).strftime('%B %Y'), level=1, inplace=True)

Reindexing Multiindex dataframe

2 Answers2