CODEWITHSUNDEEP
pythonexcel
Alex Man
Alex Man

Reputation: 477

How to write a Multi-key dict into excel in Python?

Is there any way to write a multi key dict in python into an excel file where keys are row index and column header respectively? Here is an example:

d={('A', 0): 1.0, ('A', 1): 1.0, ('A', 2): 1.0, ('A', 3):1.0, ('B', 0): 1.0, ('B', 1): 1.0, ('B', 2): 1.0, ('B', 3): 1.0}

I am looking for an output like below:

enter image description here

Upvotes: 0

Views: 156

Answers (1)

Aman
Aman

Reputation: 194

I don't think that there is special function for this kind of dictionary. But with openpyxl it can be achieved. Key of your dictionary contains row title and column title respectively. Value of your dictionary is value.

with openpyxl, code will look something like this (assumed that you imported required libs):

new_row_no=1 # It will keep info of last row

for key, value in d.items():
    column_no=int(key[1])+2 # Here ('A', 0), 0 is the column no
    row_no=0

    #Update column title here
    sh.cell(row=1, column=column_no, value=key[1])

    # Do some stuff here to decide which row to insert new value
    # Check if row title exists or not
    # If exists, find that row(for example which cell of column A contains 'A' as row title)
    row_no=int(proper_row_no_finder(sh_to_search=sh, value_to_search=key[0]))

    # If row title is new, it means that it is not used, then do extra stuff
    if row_no==0:

        #if not exists, it means that you have to start working with new row
        new_row_no+=1
        row_no=new_row_no
        sh.cell(row=row_no, column=1, value=key[0]) # Add just row title as it is new
        

    # Now you have both row and column number, you can work with that as you wish
    sh.cell(row=row_no, column=column_no, value=value)

wb.save('result.xlsx')

here is the helper function:

def proper_row_no_finder(**kwargs):
    sh_to_search=kwargs['sh_to_search']
    value_to_search=kwargs['value_to_search']
    xrow=0
    for col in sh_to_search.iter_cols(min_row=1, max_col=1):
        for xvalue in col:
            if xvalue.value==value_to_search:
                xrow=xvalue.row # Take the row number, you need it to insert new value
            else:
                xrow=0
    return xrow

Upvotes: 1

Related Questions