XLSX仅写入数组中的最后一个索引

Question

我遇到的问题是，只有player id中的最后一个播放器被写入excel工作表，而我应该得到所有的播放器。我试图改变xlsx方法，但仍然得到相同的结果。是否有不同的方法来处理此问题或使用不同的库

import requests 
import json 
import numpy as np
import statistics
import pandas as pd
import xlsxwriter

player_id = ["stephen-curry-1079","rodney-hood-18629","c-j-mccollum-17121","damian-lillard-13900","enes-kanter-13299",
"andre-iguodala-1334","draymond-green-13976","klay-thompson-13315","andrew-bogut-1494","zach-collins-37866","kevon-looney-18945",
"al-farouq-aminu-1279","evan-turner-1291","seth-curry-16825","shaun-livingston-14007","jonas-jerebko-1106","maurice-harkless-16971",
"jordan-bell-37877","quinn-cook-31771","alfonzo-mckinnie-37808","meyers-leonard-13905","jake-layman-35216"]

for x in player_id:
    url = "https://rotogrinders.com/players/"+x+"?format=json"
    r = requests.get(url)
    cont = r.json()
    player_one_name = cont['player']['name']
    fpts = []
    above_average = []
    games_played = len(cont['stats']['this-season'])
    i = 0
    while i < games_played:
        fpts.append(cont['stats']['this-season'][i]['fpts']['2'])
        i += 1
    fpts = list(map(float,fpts))
    length = len(fpts)
    total = sum(fpts)
    average = total / length 
    player_average = round(average, 2)
    player_variance = round(statistics.variance(fpts), 2)
    player_std = round(statistics.stdev(fpts),2)
    for x in fpts:
        if x >= average:
            above_average.append(x)
    above_average_variance = round(statistics.variance(above_average),2)
    #above_average_average = round(above_average / len(above_average),2)

    player_one = np.random.normal(player_average, player_std, 1000) #avg, std, number of iterations

    data = [player_one_name, player_average, player_variance, player_std, above_average_variance, max_score]
    df = pd.DataFrame([data], columns = ["Name", "Average", "Variance", "STD", "Variance above Avg", "Predicted Score"])
    writer = pd.ExcelWriter('NBAPlayers.xlsx', engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1')
    df.style.set_properties(**{'text-align':'center'})
    pd.set_option('display.max_colwidth',100)
    pd.set_option('display.width', 1000)
    print(df)   
    writer.save()

Answer 1

data和df在每次迭代中都会重新创建，因此当循环完成时，只会保存最后一行

相反，您应该在每次迭代后附加到列表中，然后将数据帧导出到循环的外部：

...
data = []
for x in player_id:
    ...
    data.append([player_one_name, player_average, player_variance, player_std, above_average_variance, max_score])

df = pd.DataFrame(data, columns = ["Name", "Average", "Variance", "STD", "Variance above Avg", "Predicted Score"])
writer = pd.ExcelWriter('NBAPlayers.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
df.style.set_properties(**{'text-align':'center'})
pd.set_option('display.max_colwidth',100)
pd.set_option('display.width', 1000)
print(df)   
writer.save()