如何获取数据帧中作为字典的列的值

Question

我有一个列中有字典的dataframe，但是我需要获取值并用信息更新dataframe。这是我的数据帧：

    $type   bays    carParkDetailsUrl   id  name
0   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800491   CarParks_800491 Barkingside Stn (LUL)
1   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800468   CarParks_800468 Buckhurst Hill Stn (LUL)
2   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800475   CarParks_800475 Fairlop Stn (LUL)
3   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800444   CarParks_800444 Greenford Stn (LUL)
4   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800477   CarParks_800477 Hainault Stn (LUL)
5   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800481   CarParks_800481 Leytonstone Stn (LUL)
6   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800456   CarParks_800456 Perivale Stn (LUL)
7   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800459   CarParks_800459 Ruislip Gardens Stn (LUL)
8   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800462   CarParks_800462 South Ruislip Stn (LUL)
9   Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800489   CarParks_800489 South Woodford Stn (LUL)
10  Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800493   CarParks_800493 Theydon Bois Stn (LUL)
11  Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800496   CarParks_800496 Wanstead Stn (LUL)
12  Tfl.Api.Presentation.Entities.CarParkOccupancy...   [{'$type': 'Tfl.Api.Presentation.Entities.Bay,...   Place\CarParks_800480   CarParks_800480 Hornchurch Stn (LUL)

我需要得到黄色的值，并保存在数据帧中，以使所有的信息在一个数据帧。 我已经试过了：

要从API获取信息：

r = rq.get('https://api.tfl.gov.uk/Occupancy/CarPark?app_id=2f7e332e&app_key=68180443ed4baffb6640824d8aa7db5c')
r = r.text
df12 = pd.read_json(r)
df12

要使用dict（$type和bays）从列中获取信息，请执行以下操作：

dfs = pd.DataFrame(columns = ["$type", "bays", "id", "name"])
items = []
for i, row in enumerate(items["results"]):
    "$type" = row["$type"]
    bays = row["bays"]
    id = row["id"]
    name = row["name"]
    dfs.loc[i] = ["$type", "bays", "id", "name"]

dfs.head(20)

我有个错误： 列表索引必须是整数或片，而不是str

Answer 1

奇怪的df:D

df12['bays'][0][0]['$type']在bays列中为您提供第一个条目..可以对其他列执行类似的操作

df12['bays'][0][0]['bayCount']  > 2
df12['bays'][0][1]['bayCount']  > 45

Answer 2

您的“间隔”列包含一个列表，因此首先，您必须拆分它：

def split(x, index): 
    try:
        return x[index]
    except: 
        return None
df12['bays1'] = df12.bays.apply(lambda x:split(x,0))
df12['bays2'] = df12.bays.apply(lambda x:split(x,1))

然后，一旦你真的有了一个包含字典值的列，你就可以把它转换成一个数据帧。此数据帧应将字典键作为列，并将其值作为数据。你知道吗

def values(x): 
    try:
        return ';'.join('{}'.format(val) for  val in x.values())
    except: 
        return None
v = df12['bays1'].apply(lambda x:values(x))
dfs = v.str.split(';', expand=True)
dfs.columns = df12['bays1'][0].keys()

我希望这有帮助。你知道吗

Answer 3

如果每个单元格中都有一个简单的字典，则要获取可以使用的值：

bfs['bays'] = bfs['bays'].map(lambda kv: kv.value)

但它看起来像是在一个列表中，所以如果它只是每个单元格中一个字典的列表，那么您可以使用：

bfs['bays'] = bfs['bays'].map(lambda kv: kv[0].value)