To query the df by the MultiIndex values, for example where (A > 1.7) and (B < 666): In [536]: result_df = df.loc[(df.index.get_level_values(‘A’) > 1.7) & (df.index.get_level_values(‘B’) < 666)] In [537]: result_df Out[537]: C A B 3.3 222 43 333 59 5.5 333 56 Hence, to get for example the ‘A’ index values, if still … Read more
There is potentially a better way, more pythonic way to flatten MultiIndex columns. 1. Use map and join with string column headers: grouped.columns = grouped.columns.map(‘|’.join).str.strip(‘|’) print(grouped) Output: code colour size|sum size|average size|size size|idxmax \ 0 one black 862 53.875000 16 14 1 one white 554 46.166667 12 18 2 three black 842 49.529412 17 90 … Read more
pd.Grouper allows you to specify a “groupby instruction for a target object”. In particular, you can use it to group by dates even if df.index is not a DatetimeIndex: df.groupby(pd.Grouper(freq=’2D’, level=-1)) The level=-1 tells pd.Grouper to look for the dates in the last level of the MultiIndex. Moreover, you can use this in conjunction with … Read more
It’s actually pretty simple (FWIW, I originally thought to do it your way): df[‘bar’, ‘three’] = [0, 1, 2] df = df.sort_index(axis=1) print(df) bar baz one two three one two A -0.212901 0.503615 0 -1.660945 0.446778 B -0.803926 -0.417570 1 -0.336827 0.989343 C 3.400885 -0.214245 2 0.895745 1.011671
When sorting by a MultiIndex you need to contain the tuple describing the column inside a list*: In [11]: df.sort_values([(‘Group1’, ‘C’)], ascending=False) Out[11]: Group1 Group2 A B C A B C 2 5 6 9 1 0 0 1 1 0 3 2 5 7 3 7 0 2 0 3 5 * so as … Read more
There is a get_level_values method that you can use in conjunction with boolean indexing to get the the intended result. In [13]: df = pd.DataFrame(np.random.random((4,4))) df.columns = pd.MultiIndex.from_product([[1,2],[‘A’,’B’]]) print df 1 2 A B A B 0 0.543980 0.628078 0.756941 0.698824 1 0.633005 0.089604 0.198510 0.783556 2 0.662391 0.541182 0.544060 0.059381 3 0.841242 0.634603 0.815334 … Read more
One way is to use the get_level_values Index method: In [11]: df Out[11]: 0 A B 1 4 1 2 5 2 3 6 3 In [12]: df.iloc[df.index.get_level_values(‘A’) == 1] Out[12]: 0 A B 1 4 1 In 0.13 you’ll be able to use xs with drop_level argument: df.xs(1, level=”A”, drop_level=False) # axis=1 if columns … Read more
A pandas MultiIndex consists of a list of tuples. So the most natural approach would be to reshape your input dict so that its keys are tuples corresponding to the multi-index values you require. Then you can just construct your dataframe using pd.DataFrame.from_dict, using the option orient=”index”: user_dict = {12: {‘Category 1’: {‘att_1’: 1, ‘att_2’: … Read more
The reset_index() is a pandas DataFrame method that will transfer index values into the DataFrame as columns. The default setting for the parameter is drop=False (which will keep the index values as columns). All you have to do call .reset_index() after the name of the DataFrame: df = df.reset_index()
MultiIndex / Advanced Indexing Note This post will be structured in the following manner: The questions put forth in the OP will be addressed, one by one For each question, one or more methods applicable to solving this problem and getting the expected result will be demonstrated. Notes (much like this one) will be included … Read more