Put a dictonary in a single cell of excel in python (pref w/o pandas)-CodePudding

I have a dictonary which i want to put in an single excel cell.

import datetime
my_dict = {
    'key1':         'MOCK-12345-67890:09876', 
    'key2':         'MOCK-abcdef-ghijklmnop',
    'key3':         datetime.datetime.now(), # datetime format
    # ...
    'key15':    15029.62946216,              # float
    'key16':    [ 'MOCK-1', 'MOCK-2' ],      # list
    'info': {                                # dictonary
        'key_sub_1': 3934823,
        # ...
        'key_sub_25': 'MOCK-A'
        },
    }

Desired output: A2 = {'key1': 'MOCK-12345-67890:09876', 'key2': 'MOCK-abcdef-ghijklmnop', 'key3': datetime.datetime(2021, 11, 30, 9, 25, 14, 548815), 'key15': 15029.62946216, 'key16': ['MOCK-1', 'MOCK-2'], 'info': {'key_sub_1': 3934823, 'key_sub_25': 'MOCK-A'}}

Desired Output in Excel

My try (using pandas):

import csv
import pandas as pd
import datetime

my_dict = {
    'key1':         'MOCK-12345-67890:09876', 
    'key2':         'MOCK-abcdef-ghijklmnop',
    'key3':         datetime.datetime.now(), # datetime format
    # ...
    'key15':    15029.62946216,              # float
    'key16':    [ 'MOCK-1', 'MOCK-2' ],      # list
    'info': {                                # dictonary
        'key_sub_1': 3934823,
        # ...
        'key_sub_25': 'MOCK-A'
        },
    }

data = {
    'Col1': str(my_dict),
    'Col2':'something wonderful'
    }

df = pd.DataFrame(data, index = [0])
writer = pd.ExcelWriter('delme1.xlsx', engine='openpyxl')
df.to_excel( writer, sheet_name = 'Sheet 1', index=False)
writer.save()

Now, i love the pandas library but not as much as the actual animal. And while the above solution works, i wanted to know if there is a non-pandas way of doing the same UNLESS this is the best (in efficiency).

In short

Is pandas way the best for this?
If not what how does the non-pandas way look like.

In case i missed something simple- I am not a beginner in python, but that all of my humble glory.

EDIT: Why in one single cell?

I know its very easy to lay out the dict in a tabular format in excel, but that is not what i am looking for.

The dict format tends to be non-constant making maintenance of the column names difficult.
The dict content is just for the sake of records which may not be much used but is need to be there, so its sufficient for to just keep it as is into a single cell - saves unnecessary effort.

CodePudding user response：

As you can see in your code, pandas uses 'openpyxl' as an engine. So it is quite a way-around method. You can just use openpyxl directly as below.

import openpyxl as xl
import datetime

my_dict = {
    'key1':         'MOCK-12345-67890:09876', 
    'key2':         'MOCK-abcdef-ghijklmnop',
    'key3':         datetime.datetime.now(), # datetime format
    # ...
    'key15':    15029.62946216,              # float
    'key16':    [ 'MOCK-1', 'MOCK-2' ],      # list
    'info': {                                # dictonary
        'key_sub_1': 3934823,
        # ...
        'key_sub_25': 'MOCK-A'
        },
    }


wb = xl.Workbook() # create a new workbook
ws = wb.worksheets[0] # select the first worksheet

# write values to cells
ws["A1"].value = str(my_dict)
ws["B1"].value = "something wonderful"
# or you can try this
# ws.cell(row=1, column=1).value = str(my_dict)
# ws.cell(row=1, column=2).value = "something wonderful"

wb.save("delme1.xlsx") # save the workbook
wb.close() # close the workbook