如何将含列表的金融数据JSON字典转换为pd.DataFrame？

Hey there! Let's break down how to turn your nested financial data into a clean pandas DataFrame. The tricky part here is the nested mid dictionary inside each candle entry—we need to flatten that out first so all metrics (open, high, low, close) become their own columns.

Step-by-Step Solution #

First, make sure you have pandas imported:

import pandas as pd

Let's start with your raw data (I'll assign it to a variable for clarity):

data = {
    u'candles': [
        {u'complete': True, u'mid': {u'c': u'1.19228', u'h': u'1.19784', u'l': u'1.18972', u'o': u'1.19581'}, u'time': u'2018-05-06T21:00:00.000000000Z', u'volume': 119139},
        {u'complete': False, u'mid': {u'c': u'1.18706', u'h': u'1.19388', u'l': u'1.18614', u'o': u'1.19239'}, u'time': u'2018-05-07T21:00:00.000000000Z', u'volume': 83259}
    ], 
    u'granularity': u'D', 
    u'instrument': u'EUR_USD'
}

Next, extract the candles list (this is the core data we need) and flatten the nested mid entries:

# Extract the candle list
candles_list = data['candles']

# Flatten each candle by merging the 'mid' dict into the main candle dict
flattened_candles = []
for candle in candles_list:
    # Create a copy to avoid altering the original data
    flat_candle = candle.copy()
    # Pop the 'mid' dict and merge its key-value pairs into the flat candle
    mid_data = flat_candle.pop('mid')
    flat_candle.update(mid_data)
    flattened_candles.append(flat_candle)

Now convert the flattened list to a DataFrame:

df = pd.DataFrame(flattened_candles)

Clean Up Data Types #

Right now, the price values (c, h, l, o) are strings, and the time is a string too. Let's convert them to proper numeric and datetime types:

# Convert price columns to float
df[['c', 'h', 'l', 'o']] = df[['c', 'h', 'l', 'o']].astype(float)

# Convert time column to datetime
df['time'] = pd.to_datetime(df['time'])

Final Result #

If you print df, you'll get a clean table like this:

Bonus: Add Metadata (Optional) #

If you want to include the granularity and instrument values as columns (e.g., for filtering later), you can add them to each flattened candle:

flattened_candles = []
for candle in candles_list:
    flat_candle = candle.copy()
    mid_data = flat_candle.pop('mid')
    flat_candle.update(mid_data)
    # Add metadata from the top-level dict
    flat_candle['granularity'] = data['granularity']
    flat_candle['instrument'] = data['instrument']
    flattened_candles.append(flat_candle)

df = pd.DataFrame(flattened_candles)

That's it! This approach ensures all nested data is properly expanded into a tabular format that's easy to work with for analysis.

内容的提问来源于stack exchange，提问作者dared