如何使用Pandas和Python创建并向dataframe添加新变量以打印到excel？

Question

我正在尝试创建一个新变量“spg”，即在Python上使用Pandas来创建每个游戏的快照数。这个新变量仅仅是s/gp或射击/游戏。我想将这个新变量添加到我当前的数据框中，以便将其打印到excel。你知道吗

我希望我最后的专栏是这样的： 列=（“名称”、“团队”、“gp”、“g”、“s”、“spg”）

# import modules

from urllib.request import urlopen
from lxml.html import fromstring

import pandas as pd

# connect to url

url = "https://www.hockey-reference.com/leagues/NHL_2020_skaters.html"

# remove HTML comment markup

content = str(urlopen(url).read())

comment = content.replace("-->", "").replace("<!--", "")
tree = fromstring(comment)

# setting up excel columns

columns = ("name", "team", "gp", "g", "s")

df = pd.DataFrame(columns=columns)

# parsing data while using loop

for idx, row in enumerate(tree.xpath('//table[@id="stats"]/tbody/tr[not(@class="thead")]')):
    name = row.xpath('.//td[@data-stat="player"]')[0].text_content()
    team = row.xpath('.//td[@data-stat="team_id"]')[0].text_content()
    gp = row.xpath('.//td[@data-stat="games_played"]')[0].text_content()
    goals = row.xpath('.//td[@data-stat="goals"]')[0].text_content()
    s = row.xpath('.//td[@data-stat="shots"]')[0].text_content()


    # create pandas dataframe to export data to excel.

    df.loc[idx] = (name, team, gp, goals, s) # I would like to add "spg"
    print(df)


# write data to excel
writer = pd.ExcelWriter('NHL skater20.xlsx')
df.to_excel(writer, 'Sheet1')
writer.save()

关于最好的方法有什么建议吗？我非常感谢任何我能得到的帮助或反馈。提前谢谢！你知道吗

Answer 1

简单：在循环之后，添加以下行：

df['spg'] = df['s'] / df['gp']

升级版：（测试和工作）

# import modules

from urllib.request import urlopen
from lxml.html import fromstring

import pandas as pd

# connect to url

url = "https://www.hockey-reference.com/leagues/NHL_2020_skaters.html"

# remove HTML comment markup

content = str(urlopen(url).read())

comment = content.replace(" >", "").replace("<! ", "")
tree = fromstring(comment)

# setting up excel columns

columns = ("name", "team", "gp", "g", "s")

df = pd.DataFrame(columns=columns)

# parsing data while using loop

for idx, row in enumerate(tree.xpath('//table[@id="stats"]/tbody/tr[not(@class="thead")]')):
    name = row.xpath('.//td[@data-stat="player"]')[0].text_content()
    team = row.xpath('.//td[@data-stat="team_id"]')[0].text_content()
    gp = row.xpath('.//td[@data-stat="games_played"]')[0].text_content()
    goals = row.xpath('.//td[@data-stat="goals"]')[0].text_content()
    s = row.xpath('.//td[@data-stat="shots"]')[0].text_content()


    # create pandas dataframe to export data to excel.
    # this is fine for a small dataset, however
    # for a bigger one writing a generator is much more efficient
    df.loc[idx] = (name, team, gp, goals, s) # I would like to add "spg"
    # print(df)  # commented out as unnecessary

# at this point these columns are .text_content(), converting to numbers
df = df.astype({'gp': int, 'g': int, 's': int})
# main part: adding a new column
df['spg'] = df['s'] / df['gp']

# write data to excel
writer = pd.ExcelWriter('NHL skater20.xlsx')
df.to_excel(writer, 'Sheet1')
writer.save()

Answer 2

要将其发送到excel，可以使用以下任一方法：

DataFrame.to_excel('filename.xlsx')
DataFrame.to_csv('filename.csv')