Pandas ValueError: cannot handle a non-unique multi-index when trying to re-index

Question

I have a problem working with stock data when trying to fill in the dates for all instruments since the newer intstruments doesn't have the full time data from the start that the older ones are having.

I want to fill out the earlier dates with NaN values for the instruments that haven't gotten the full time series data.

This is what the data looks like after being imported into a dataframe named as dfo for dataframeoriginal:

    Date        Open        Close     High      Low     Volume      Instr_ID
0   2017-01-02  100.0000    101.0833  102.0000  99.6667 179586.0    2
1   2017-01-03  101.3333    99.7500   101.8333  99.7500 256812.0    2
2   2017-01-04  99.8333     100.1667  100.1667  99.4167 204432.0    2
3   2017-01-05  100.1667    99.9167   100.2500  99.5833  74802.0    2
4   2017-01-06  99.9167     99.9167   99.9167   99.9167      0.0    2
... ... ... ... ... ... ... ...
1182530 2020-04-07  13.2200 13.0000 13.9980 12.7200 5519.0  1932
1182531 2020-04-08  12.9000 13.1000 13.5000 12.9000 8266.0  1932
1182532 2020-04-09  13.1000 13.1000 13.1000 13.1000 0.0     1932
1182533 2020-04-14  14.0000 13.4980 14.0000 12.9000 7363.0  1932
1182534 2020-04-15  13.8000 13.8000 14.0000 13.2000 2638.0  1932

I had success on a partial export of this data setting a multiindex being the "instr_ID" and the "Date" and then creating a new multiindex with:

df1 = dfo.set_index(["Instr_ID","Date"])

                        Open        Close       High        Low     Volume
Instr_ID    Date                    
2           2017-01-02  100.0000    101.0833    102.0000    99.6667 179586.0
            2017-01-03  101.3333    99.7500     101.8333    99.7500 256812.0
            2017-01-04  99.8333     100.1667    100.1667    99.4167 204432.0
            2017-01-05  100.1667    99.9167     100.2500    99.5833 74802.0
            2017-01-06  99.9167     99.9167     99.9167     99.9167 0.0
... ... ... ... ... ... ...
1932        2020-04-07  13.2200     13.0000     13.9980     12.7200 5519.0
            2020-04-08  12.9000     13.1000     13.5000     12.9000 8266.0
            2020-04-09  13.1000     13.1000     13.1000     13.1000 0.0
            2020-04-14  14.0000     13.4980     14.0000     12.9000 7363.0
            2020-04-15  13.8000     13.8000     14.0000     13.2000 2638.0

1182535 rows × 5 columns




start, end = df.index.levels[1].min(), df.index.levels[1].max()
date_range = pd.date_range(start, end)
new_index = pd.MultiIndex.from_product([df1.index.levels[0], date_range])
df1.reindex(index = new_index)

This works fine when trying on a partial export of the data and would fill out the dates back to 2017 even for the instrument id 1932 that only got data from april 2020. But when doing the same on the full data it will raise the following error when trying to reindex with the new_index:

ValueError: cannot handle a non-unique multi-index!

Any advice on what I could do to achieve the end goal of getting all dates for all instruments would be greatly appreciated!

user_13942277 · Answer

First time I answer a question on stackoverflow : I've had the same issue but I think I figured out what the problem was. You probably have duplicate indexes in your multi-index. In your case it probably means that you have duplicate Instr_ID or that you sometimes have duplicate dates in your data (I think the later : it would make sense that more recent instruments give more detailed data). Verify that none of your instruments give multiple measurements for a single day. If there are some that do than you just have to extend your date to a datetime or add a third level in your MultiIndex to accomodate for am/pm. Tips for checking that : check the length of your index vs the length of df.index.unique()

Pandas ValueError: cannot handle a non-unique multi-index when trying to re-index

1 Answers