Making or Predefining Structure of Nested Dictiona

Question

Input: I have Excel file containing 3 columns and format of excel file is like as follow:

A   C   D
A   C   E
A   F   G
B   H   J
B   H   K
A   F   I
B   L   M
B   L   N
A   F   O

I wish to make dictionary from the above input in below format: Output:

dictionary= {'A':{'C':['D','E'],'F':['G','I','O']},'B':{'H':['J','K'],'L':['M','N']}}

Logic: For each distinct column-1 value, need to make nested dictionary & in that nested part, for each distinct column-2 value, need to make list of correspondingly column-3 values.

Answer 1

You can do it like so with pandas:

import pandas as pd

df = pd.read_excel('excel_file', header=None)
d = {}
for b in df.groupby([0,1])[2].apply(list).to_frame().iterrows():
    if b[0][0] not in d:
        d[b[0][0]] = {b[0][1]: b[1].tolist()[0]}
    else:
        d[b[0][0]][b[0][1]] = b[1].tolist()[0]
print d

Output:

{'A': {'C': ['D', 'E'], 'F': ['G', 'I', 'O']}, 'B': {'H': ['J', 'K'], 'L': ['M', 'N']}}

Answer 2

@Edchum @MYGz
Thanks!! But without using pandas, i ended by doing something like this.

from xlrd import open_workbook
from nested_dict import nested_dict

book = open_workbook(input_file_location) # location of excel file 
sheet_3=book.sheets()[2] #sheet_3 in which i have data
data_sheet_3  = [sheet_3.row_values(i) for i in xrange(sheet_3.nrows)] # getting data of sheet-3
# specifying 2-level of nesting
#format of dictionary: {'Key1':{'Key2':['Value1','value2']},'Key3':{'Key4':['Value3','value4']}} 
dictionary=nested_dict(2,list) 
for row_no in xrange(sheet_3.nrows):
        col_1=data_sheet_3[row_no][0]
        col_2=data_sheet_3[row_no][1]
        col_3=data_sheet_3[row_no][2]
        dictionary[col_1][col_2].append(col_3)

print dictionary

nested_dict(2,list) is the main important point in the code which allowed me to specify two levels of nesting.

If you find anything better or alternative of Pre-defining the structure of nested dictionary in python, please share with example.