Question: I have an df which has 3 columns: Region, Country and AREA_CODE. Region Country AREA_CODE AREA_SUB_CODE_1 AREA_SUB_CODE_2 =========================================================================== AMER US A1 A1_US_1 A1_US_2 AMER CANADA A1 A1_CA_1 A1_CA_2 AMER US B1 B1_US_1 B1_US_2 AMER US A1 A1_US_1 A1_US_2  Is there a way to get output list of both the AREA_SUB_CODE_1 and AREA_SUB_CODE_2 as a list under each of the previous column value. something like the below? { "AREA_SUB_CODE_1": { "AMER": { "US": { "A1": ["A1_US_1"], "B1": ["B1_US_1"] }, "CANADA": { "A1": ["A1_CA_1"], } } }, "AREA_SUB_CODE_2": { "AMER": { "US": { "A1": { "A1_US_1": ["A1_US_2"] }, "B1": { "B1_US_1": ["B1_US_2"] }, "CANADA": { "A1": { "A1_CA_1": ["A1_CA_2"], } } } }, }  So far i have tried to groupby on 3 columns it works which is, for (k1, k2), v in df.groupby(['Region', 'Country'])['AREA_CODE']: tTmp.setdefault(k1, {})[k2] = sorted(v.unique())  But when i try to groupby 4 columns, it is throwing error  too many values to unpack (expected 2)  for (k1, k2), v in df.groupby(['Region', 'Country', 'AREA_CODE'])['AREA_SUB_CODE_1']: tTmp.setdefault(k1, {})[k2] = sorted(v.unique())  How to apply groupby for 4 columns and 5 columns? Or any other way to achieve this?

Answer: I think we can achieve this with the following recursive function: f = lambda s: ({k: f(s[k]) for k in s.index.levels[0]} if s.index.nlevels > 1 else {k: s.loc[[k]].unique().tolist() for k in s.index.unique()})  Here, s is expected to be a pandas.Series with hierarchical indexing. At each indexing level, we map the keys to the corresponding depth of the resulting dictionary. At the last level, we extract unique values into a list. The double square brackets in s.loc[[k]] ensure the output is a series, the following unique method returns a numpy.ndarray with unique values of the series, and tolist converts the array into a Python list. If we know there's exactly one unique value at the final level, we can simplify the function: f = lambda s: {k: f(s[k]) for k in s.index.levels[0]} \ if s.index.nlevels > 1 \ else s.to_dict()  In this case, we skip creating a list at the end. But if needed, we can insert additional mapping like s.map(lambda x: [x]).to_dict(). Before applying any of the function above, we have to transform the data into a properly indexed series: inner = ['Region', 'Country', 'AREA_CODE'] values = df.melt(inner).set_index(['variable', *inner]).squeeze()  Here, 'variable' is the default name for the new column with the rest of column names excluding the inner list after melting. The final answer is f(values) Let's see the example: df = pd.DataFrame({ 'Region': ['AMER', 'AMER', 'AMER', 'AMER'], 'Country': ['US', 'CANADA', 'US', 'US'], 'AREA_CODE': ['A1', 'A1', 'B1', 'A1'], 'AREA_SUB_CODE_1': ['A1_US_1x', 'A1_CA_1', 'B1_US_1', 'A1_US_1y'], 'AREA_SUB_CODE_2': ['A1_US_2', 'A1_CA_2', 'B1_US_2', 'A1_US_2']}) f = lambda s: ({k: f(s[k]) for k in s.index.levels[0]} if s.index.nlevels > 1 else {k: s.loc[[k]].unique().tolist() for k in s.index.unique()}) inner = ['Region', 'Country', 'AREA_CODE'] values = df.melt(inner, var_name='sub_code').set_index(['sub_code', *inner]).squeeze() answer = f(values)  Note, that in this example, we have 2 different values for the key set ('AREA_SUB_CODE_1', 'AMER', 'US', 'A1') and 2 equal ones for the key set ('AREA_SUB_CODE_2', 'AMER', 'US', 'A1'), so the second case will end up as a list with one value in the final answer: {'AREA_SUB_CODE_1': {'AMER': {'CANADA': {'A1': ['A1_CA_1']}, 'US': {'A1': ['A1_US_1x', 'A1_US_1y'], 'B1': ['B1_US_1']}}}, 'AREA_SUB_CODE_2': {'AMER': {'CANADA': {'A1': ['A1_CA_2']}, 'US': {'A1': ['A1_US_2'], 'B1': ['B1_US_2']}}}}  If we drop the last record in the example data, then we can use the alternative function with s.to_dict() at the end.