0

Let's say I have a JSON or a dictionary. I would like to generate an easy, human-readable Excel report out of it.

{'A1': 
    {'B1': 'XX1',
     'B2': 'XX2',
     'B3': 'XX4',
     'B4': {'C1': 'XX000'},
     'B8': 'XX5'
     }
  ...
}

How do I create a dataframe (more specifically an Excel report) that looks like this:

A1
B1   XX1
B2   XX2
B3   XX4
B4   C1    XX000
B8   XX5

Excel final Output:

Example XLSX Output

Any ideas welcome! Assume that this is a multi-index JSON/dictionary and needs to be dynamically developed.

I have some basic code that returns me metadata containing tree-path, sub-depth, etc. but this is not getting me too far. Any suggestions?

Essentially, I need to create the opposite of this: How to generate n-level hierarchical JSON from pandas DataFrame?

sgerbhctim
  • 3,420
  • 7
  • 38
  • 60

1 Answers1

0

you have a lot of options, using pandas or python csv module, see example below, you should tweak it to get the formatting you want

import pandas as pd

d = {'A1': 
        {'B1': 'XX1',
         'B2': 'XX2',
         'B3': 'XX4',
         'B4': {'C1': 'XX000'},
         'B8': 'XX5'
        }
    }

df = pd.DataFrame(data=d)
df.to_csv('mydata.csv')
Mahmoud Elshahat
  • 1,873
  • 10
  • 24
  • Thanks for your response. I should have been more specific with my question verbiage.. I can turn a dict into a dataframe, this is not the issue. The issue is when we have randomly nested dictionaries scattered throughtout. Your proposition results in column `A1` row `B4` to display a dictionary, whereas I would need that dictionary to be spread open as well. I cant seem to figure out a way to do this with recursion. – sgerbhctim Apr 29 '19 at 19:48
  • A large part of your solution will be completely custom to your situation for how you want the data parsed into rows and columns. For example, it's not intuitively obvious (to us) that you want the `{'C1': 'XX000'}` data on the same row as `B4`. Unless you lay out the specific rules for all the different situations your logic may encounter (such as nested dictionaries 3, 4, 5 or more levels deep), you're not going to get a ready-made solution here. The way it usually works for me is that when I sit down to define what those rules are, the coding logic works itself out and I solve it myself. – PeterT Apr 29 '19 at 20:08