Easiest Way To Flatten a Dictionary & Convert to Pandas Dataframe

Question

I have a dictionary that looks like this…

{'Bitfinex': {'BTC/USD': [{'buy or sell': 'buy',
                           'date': '1466673272.0',
                           'margin': False,
                           'order number': 841508598,
                           'price': '391.42',
                           'type': 'exchange limit',
                           'volume': '0.01490759'},
                            {'buy or sell': 'buy',
                           'date': '1466673283.0',
                           'margin': False,
                           'order number': 841509160,
                           'price': '150.11',
                           'type': 'exchange limit',
                           'volume': '0.04342146'}]},
 'Poloniex': {'USDT/BTC': [{'buy or sell': 'buy',
                            'date': '2016-06-24 09:14:31',
                            'margin': False,
                            'order number': '5299560256',
                            'price': '1.00112000',
                            'volume': '10.63783387'},
                           {'buy or sell': 'buy',
                            'date': '2016-06-24 09:14:29',
                            'margin': False,
                            'order number': '5299559257',
                            'price': '10.01190000',
                            'volume': '1.00490446'},
                           {'buy or sell': 'buy',
                            'date': '2016-06-24 09:14:26',
                            'margin': False,
                            'order number': '5299553263',
                            'price': '100.08200000',
                            'volume': '0.04170130'}]}}

I need it to become a Pandas dataframe with columns like this:

Exchange | Currency Pair | Buy or Sell | Date | Margin | Order Number | Price | Volume

Is there an easy way to do this without iterating over the keys & values?

Answer 1

"Easy" is highly subjective here. I doubt there is a way to do that without iterating the keys & values, at least without some functional magic and it is questionable if that would be shorter AND more readable/understandable.

If you want to try iterations, you can do that with something like the following, where INPUT is your input dictionary as given by example:

import pandas as pd

...

def convertToDataFrameItems(olddata):
    name_map={
        'buy or sell': 'Buy or Sell',
        'date': 'Date',
        'margin': 'Margin',
        'order number': 'Order Number',
        'price': 'Price',
        'volume': 'Volume'
    }
    for ex, currencies in olddata.items():
        for currency, data in currencies.items():
            for item in data:
                d = dict()
                for oldname,newname in name_map.items():
                    d[newname] = item[oldname]
                d.update({"Exchange":ex,"Currency Pair":currency})
                yield d

pd.DataFrame([x for x in convertToDataFrameItems(INPUT)])

This list of dictionary constructor should create the columns as needed, after the data is mapped into a single set.

Note: Can't test the code since I don't have panda installed