CODEWITHSUNDEEP
pythonjsonpandas
rolldepollo
rolldepollo

Reputation: 87

Export a dictionary of dataframes to JSON with Datetime index

I have a dictionary of dataframes each one with a Datetime Index, as follows:

z = {'a': df1, 'b': df2, 'c': df3}

print(df1.head(3))

                   Sales  Pre-tax_Income  ...  Profit_Margin  Gross_Margin
2013-02-28  1.909350e+07   -2.557250e+06  ...      -0.220741     -0.133933
2013-05-31  6.909194e+06   -3.371473e+06  ...      -0.220507     -0.487969
2013-08-31  9.655660e+06    1.447535e+05  ...      -0.126062      0.014992

I need to store the data in a JSON file with the following structure:

{
    "a": {
       "2013-02-28": { "Sales": 1.909350e+07, ... "Gross Margin": -0.133933},
       "2013-05-31": { "Sales": 6.909194e+06, ... "Gross Margin": -0.487969},
     (...)
    "b": {...}

I've tried a piece of code from another post:

# convert dataframes into dictionaries
data_dict = {
    key: z[key].to_dict(orient='records')
    for key in z.keys()}

# write to disk
with open('file.json', 'w') as fp:
    json.dump(data_dict, fp, indent=4, sort_keys=True)

and what I get is a JSON with the structure I want but without the Index:

{
    "a": [
       {"Sales": 1.909350e+07, (...)
        "Gross Margin": -0.133933}
  (...)
 }

How can I get a correct JSON file structure and keep my Datetime Index from my dataframes?

Upvotes: 2

Views: 520

Answers (1)

Aramakus
Aramakus

Reputation: 1920

You need to change this 

data_dict = {
    key: z[key].to_dict(orient='records')
    for key in z.keys()}

to this

data_dict = {
    key: {k : v for k, v in 
    zip(z[key].index.values, z[key].to_dict(orient='records'))}
    for key in z.keys()}

Upvotes: 1

Related Questions