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

Question

我有一个数据框，其中一列中有一个字典，但我需要获取值并使用信息更新数据框。这是我的数据框:

    $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

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

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()

我希望这会有所帮助。