This must be a simple question, but its taking too much time to slice a pandas multi-index dataframe for me. So I seek for help.
I have a dataframe like this: (incomplete)
Product_Category Category_001 Category_002 Category_003 Category_004 \
Warehouse Year
Whse_A 2011 NaN NaN 108.000000 NaN
2012 NaN NaN 70.685714 NaN
2013 10.086957 NaN 58.475138 NaN
2014 18.564516 NaN 71.526316 NaN
2015 7.125000 NaN 73.397260 NaN
2016 9.212121 NaN 65.900391 NaN
Whse_C 2011 17.909091 NaN NaN NaN
2012 36.653374 NaN NaN NaN
2013 29.292553 NaN NaN NaN
2014 27.556098 NaN NaN NaN
2015 28.470356 NaN NaN NaN
2016 20.480734 NaN NaN NaN
2017 NaN NaN NaN NaN
Whse_J 2011 13.000000 NaN NaN NaN
2012 15.282823 NaN 33.446154 NaN
2013 15.574038 NaN 33.181518 NaN
2014 17.537404 NaN 23.289256 NaN
2015 17.950261 NaN 21.353760 NaN
2016 20.335565 NaN 32.150418 NaN
2017 7.250000 NaN NaN NaN
It has two index columns: Warehouse and Year.
It has 33 original columns: Category_001 to Category_33.
df1.index
MultiIndex(levels=[[2011, 2012, 2013, 2014, 2015, 2016, 2017], ['Whse_A', 'Whse_C', 'Whse_J', 'Whse_S']],
codes=[[0, 0, 0, 0, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4], [0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3, 0, 1, 2, 3]],
names=['Year', 'Warehouse'])
I can slice Warehouse 'Whse_A' and year 2011 like this:
df.loc[('Whse_A',2011)]
But I am struggling how to select all Years for 'Whse_A' ?
Related links: https://pandas-docs.github.io/pandas-docs-travis/user_guide/advanced.html
Help is appreciated.
Update
One idea is slicing:
df.loc[('Whse_A',2011):('Whse_A',2017)]
But, can we do it if we don't know the start and end years?
Something like:
df.loc[('Whse_A',:)]
