python - 如何将带有列表作为值的嵌套字典转换为 Pandas 数据框

Question

我正在尝试将以下字典转换为数据框:

city_data = {
    'San Francisco': {'x': [1, 2, 3], 'y': [4, 1, 2]},
    'Montreal': {'x': [1, 2, 3], 'y': [2, 4, 5]},
    'New York City': {'x': [1, 2, 3], 'y': [2, 2, 7]},
    'Cincinnati': {'x': [1, 2, 3], 'y': [1, 0, 2]},
    'Toronto': {'x': [1, 2, 3], 'y': [4, 7, 3]},
    'Ottawa': {'x': [1, 2, 3], 'y': [2, 3, 3]}
}

数据框看起来像这样:

city            |  x  |  y
San Francisco   |  1  |  4
San Francisco   |  2  |  1
San Francisco   |  3  |  2
...

使用我在这里找到的解决方案 Unfold a nested dictionary with lists into a pandas DataFrame我试过:

data = city_data

def unroll(data):
    if isinstance(data, dict):
        for key, value in data.items():
            # Recursively unroll the next level and prepend the key to each row.
            for row in unroll(value):
                yield [key] + row
    if isinstance(data, list):
        # This is the bottom of the structure (defines exactly one row).
        yield data

df = pd.DataFrame(list(unroll(nested_dict)))
df.rename(columns=lambda i: 'col{}'.format(i+1))

然而，我最终得到了这样的结果:

Answer 1

几乎是一个骗局，但输入让它有点棘手。使用 unnesting来自@Wen。

df = pd.DataFrame.from_dict(city_data, orient='index')
unnesting(df, ['x', 'y'])

               x  y 
Cincinnati     1  1 
Cincinnati     2  0 
Cincinnati     3  2 
Montreal       1  2 
Montreal       2  4 
Montreal       3  5 
New York City  1  2 
New York City  2  2 
New York City  3  7 
Ottawa         1  2 
Ottawa         2  3 
Ottawa         3  3 
San Francisco  1  4 
San Francisco  2  1 
San Francisco  3  2 
Toronto        1  4 
Toronto        2  7 
Toronto        3  3 

---

链接答案中的 Wen 函数:

def unnesting(df, explode):
    idx = df.index.repeat(df[explode[0]].str.len())
    df1 = pd.concat([
        pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
    df1.index = idx

    return df1.join(df.drop(explode, 1), how='left')