Nested dictionary with lists to MultiIndex Pandas DataFrame

Question

I have tried every possible solution I have found for nested dictionaries, but cannot get anything to work, as my dictionary is a combination of lists and dictionaries:

I get this result from Oandapyv20:

{
    "positions": [
      {
        "instrument": "USD_TRY",
        "long": {
          "units": "19028",
          "averagePrice": "3.96627",
          "pl": "2619.1369",
          "resettablePL": "2619.1369",
          "financing": "-212.5055",
          "guaranteedExecutionFees": "0.0000",
          "tradeIDs": [
            "173664",
            "173783",
            "173785",
            "173787",
            "176966",
          ],
          "unrealizedPL": "-267.6793"
        },
        "short": {
          "units": "0",
          "pl": "0.0000",
          "resettablePL": "0.0000",
          "financing": "0.0000",
          "guaranteedExecutionFees": "0.0000",
          "unrealizedPL": "0.0000"
        },
        "pl": "2619.1369",
        "resettablePL": "2619.1369",
        "financing": "-212.5055",
        "commission": "0.0000",
        "guaranteedExecutionFees": "0.0000",
        "unrealizedPL": "-267.6793",
        "marginUsed": "951.4000"
      },
      {
        "instrument": "USD_MXN",
        "long": {
          "units": "7750",
          "averagePrice": "19.37866",
          "pl": "122.5599",
          "resettablePL": "122.5599",
          "financing": "-48.8715",
          "guaranteedExecutionFees": "0.0000",
          "tradeIDs": [
            "212492",
            "212494",
            "212496",
          ],
          "unrealizedPL": "-41.5788"
        },
        "short": {
          "units": "0",
          "pl": "0.0000",
          "resettablePL": "0.0000",
          "financing": "0.0000",
          "guaranteedExecutionFees": "0.0000",
          "unrealizedPL": "0.0000"
        },
        "pl": "122.5599",
        "resettablePL": "122.5599",
        "financing": "-48.8715",
        "commission": "0.0000",
        "guaranteedExecutionFees": "0.0000",
        "unrealizedPL": "-41.5788",
        "marginUsed": "387.5000"
      },
      {
        "instrument": "USD_NOK",
        "long": {
          "units": "0",
          "pl": "0.0000",
          "resettablePL": "0.0000",
          "financing": "0.0000",
          "guaranteedExecutionFees": "0.0000",
          "unrealizedPL": "0.0000"
        },
        "short": {
          "units": "-13200",
          "averagePrice": "7.65519",
          "pl": "4906.3941",
          "resettablePL": "4906.3941",
          "financing": "-90.9699",
          "guaranteedExecutionFees": "0.0000",
          "tradeIDs": [
            "214255",
            "214257",
            "214259",
            "214281"
          ],
          "unrealizedPL": "-390.0560"
        },
        "pl": "4906.3941",
        "resettablePL": "4906.3941",
        "financing": "-90.9699",
        "commission": "0.0000",
        "guaranteedExecutionFees": "0.0000",
        "unrealizedPL": "-390.0560",
        "marginUsed": "132.0000"
      }
    ],
    "lastTransactionID": "228573"
  }
}

How can I turn this into a Pandas DataFrame?

For example this gives an error:

 df = pd.DataFrame.from_dict(x,orient='index')
 TypeError: Expected list, got str

And this one:

reform = {(level1_key, level2_key, level3_key): values
    for level1_key, level2_dict in x.items()
    for level2_key, level3_dict in level2_dict.items()
    for level3_key, values      in level3_dict.items()}

AttributeError: 'list' object has no attribute 'items'

Can the above be entered into a DataFrame without resorting to a desperate attempt including for-loops and try & except?

Thanks in advance

BENY · Accepted Answer · 2018-02-08 15:13:13Z

1

You just need to apply pd.Series couple of time

df=pd.DataFrame(d)
s=df.positions.apply(pd.Series)

v=s.short.apply(pd.Series)
t=s.long.apply(pd.Series)
Yourdf=pd.concat([df,v,s,t],1).drop(['short','positions','long'],1)

answered Feb 8, 2018 at 15:13

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested dictionary with lists to MultiIndex Pandas DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related