python - 将 DF 列值转换为列（如数据透视表）

Question

我正在为每个玩家（行）抓取 api 数据并将不同值的计数汇总到字典“建筑”：“计数”中。我希望能够进一步分析它。一个简单的解决方案是将不同的唯一“建筑物”（行内的字典键）作为数据框列，然后对它们执行相当于索引/匹配/匹配的操作。该脚本当前获取数据，我可以提取唯一键，但我不知道如何将它们放入 DF 列，然后如何进行索引/匹配/匹配。甚至在运行脚本的“计数”部分之前可能会有更好的方法。

您应该能够运行该脚本，无需凭据即可针对 API 进行 GET。如果您看到带有建筑物计数的 ranklist DF 列，您将看到我所引用的内容。

感谢您的任何指导！

import requests
import pandas as pd
from datetime import datetime
from datetime import date
from datetime import timedelta
import operator
from time import sleep

ranklist = pd.DataFrame()
for i in range(430):
    baserank_url = 'https://www.simcompanies.com/api/v3/encyclopedia/ranking/' + str(i) + '/'
    r = requests.get(baserank_url)
    rank_json = r.json()
    df = pd.DataFrame.from_dict(rank_json)
    df=df.filter(['company','id','rank','value'])
    ranklist = ranklist.append(df)

ranklist.to_csv(r'rank_dataframe.csv',index=False)
print('Ranking list started succesfully!')

levellist=[]
bcolist=[]
today= date.today()
for row in ranklist.itertuples():
    attempt = 0
    while True:
        if attempt == 6:
            break
        try:
            print(str(row.rank + 1) +'  ' + str(attempt))
            account_url = 'https://www.simcompanies.com/api/v2/players/' + str(row.id) + '/'
            r = requests.get(account_url)
            account_json = r.json()
            playerid = account_json.get("player").get("id")
            playerlevel = account_json.get("player").get("level")
            datestart = datetime.strptime(account_json.get("player").get("dateJoined")[:10],'%Y-%m-%d').date()
            yearsactive = round((today - datestart)/ timedelta(days=365.2425),2)
            buildings = account_json.get("buildings")
            certificates = account_json.get("certificates")
            bnames = [d['name'] for d in buildings]
            bnames = [n.replace('Park','Recreation').replace('Lake','Recreation').replace('Castle','Recreation') for n in bnames]
            cnames = [d['name'] for d in certificates]
            sptr = 'Yes' if 'Supporter' in cnames else 'No'
            dictOfElems = dict()
            for elem in bnames:
                if elem in dictOfElems:
                    dictOfElems[elem] += 1
                else:
                    dictOfElems[elem] = 1
            blist = {key:value for key, value in dictOfElems.items()}
            blist = dict(sorted(blist.items(),key=operator.itemgetter(1),reverse=True))
            bcolist.append([blist.keys()])
            levellist.append([playerid, playerlevel,sptr, datestart,yearsactive,blist])
    
        except:
            sleep(20)
            attempt +=1
            continue
        break

#get unique building values
bcodf= pd.DataFrame(bcolist,columns=['buildings'])
bcouni = list(set([a for b in bcodf.buildings.tolist() for a in b]))
print(bcouni)
leveldf = pd.DataFrame(levellist,columns=['id','level','sptr','datestart','yearsactive','blist'])
#clist = list(set([a for b in leveldf.cnames.tolist() for a in b]))

#print(leveldf[blist])
#bul = leveldf[blist].keys()
#buniq = list(set([a for b in leveldf.bul.tolist() for a in b]))
#print(bul)
ranklist = ranklist.merge(leveldf, on='id', how='left')
ranklist['rank'] +=1

ranklist.to_csv(r'rank_dataframe.csv',index=False)

标签： pythondataframepivot