python - 匹配来自同一数据框的最近日期
问题描述
我正在处理有关大联盟棒球比赛上座率的数据。
我正在尝试在我的数据框中创建一个新列,该列返回指定对手球队比赛的最近日期(但不能在给定日期之后)。
例如,对于包含洛杉矶天使队比赛数据的行:
Game_Num Date Team Win Attendance Net Wins
23 2010-04-05 LAA 1 43504 12
我想找到洛杉矶道奇队 ('LAD') 比赛的最近日期,并将其附加到新列中。
我的最终目标是创建另一个列,显示对手球队的净胜场在比赛中的影响,这样我就可以看到另一支球队是否有一个好的赛季,如果它影响门票销售。
这是我到目前为止所尝试的:
for index, row in bbattend.iterrows():
if row['Team'] == 'LAA':
basedate = row['Date']
tempdf = bbattend.loc[(bbattend['Team'] == 'LAD') & (bbattend['Date'] < basedate)]
tempdf['Datediff'] = abs(basedate-tempdf['Date']).days
mindiff = tempdf['Datediff'].min()
bbattend['CloseRivalDate'] = tempdf[tempdf['Date']==mindiff]['Date']
bbattend['RivalNetWins'] = tempdf[tempdf['Date']==mindiff]['Net_Wins']
bbattend['RivalWinPer'] = tempdf[tempdf['Date']==mindiff]['Win_Per']
这是我从中得到的错误:
---------------------------------------------------------------------------
AttributeError Traceback (most recent call last)
<ipython-input-128-f2be88528772> in <module>
3 basedate = row['Date']
4 tempdf = bbattend.loc[(bbattend['Team'] == 'LAD') & (bbattend['Date'] < basedate)]
----> 5 tempdf['Datediff'] = abs(basedate-tempdf['Date']).days
6 mindiff = tempdf['Datediff'].min()
7 bbattend['CloseRivalDate'] = tempdf[tempdf['Date']==mindiff]['Date']
~/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in __getattr__(self, name)
5065 if self._info_axis._can_hold_identifiers_and_holds_name(name):
5066 return self[name]
-> 5067 return object.__getattribute__(self, name)
5068
5069 def __setattr__(self, name, value):
AttributeError: 'Series' object has no attribute 'days'
这是我的数据框代码,以防万一它有帮助:
import requests
import pandas as pd
import numpy as np
from datetime import datetime
import re
Teams = ['LAA', 'LAD', 'NYY', 'NYM', 'CHC', 'CHW', 'OAK', 'SFG']
Years = []
for year in range(2010,2020):
Years.append(str(year))
list_of_df = list()
for team in Teams:
for year in Years:
url = 'https://www.baseball-reference.com/teams/' + team + '/' + year +'-schedule-scores.shtml'
dfname = team + '_' + year
html = requests.get(url).content
df_list = pd.read_html(html)
df = df_list[-1]
#Formatting data table
df.rename(columns={"Gm#": "GM_Num", "Unnamed: 4": "Home", "Tm": "Team", "D/N": "Night"}, inplace = True)
df['Home'] = df['Home'].apply(lambda x: 0 if x == '@' else 1)
df['Game_Win'] = df['W/L'].astype(str).str[0]
df['Game_Win'] = df['Game_Win'].apply(lambda x: 0 if x == 'L' else 1)
df['Night'] = df['Night'].apply(lambda x: 1 if x == 'N' else 0)
df['Streak'] = df['Streak'].apply(lambda x: -1*len(x) if '-' in x else len(x))
df.drop('Unnamed: 2', axis=1, inplace = True)
df.drop('Orig. Scheduled', axis=1, inplace = True)
df.drop('Win', axis=1, inplace = True)
df.drop('Loss', axis=1, inplace = True)
df.drop('Save', axis=1, inplace = True)
#Drop rows that do not have data
df = df[df['GM_Num'].str.isdigit()]
WL = df["W-L"].str.split("-", n = 1, expand = True)
df["Wins"] = WL[0].astype(dtype=np.int64)
df["Losses"] = WL[1].astype(dtype=np.int64)
df['Net_Wins'] = df['Wins'] - df['Losses']
df['Win_Per'] = df['Wins']/(df['Wins']+df['Losses'])
DayDate = df['Date'].str.split(", ", n = 1, expand = True)
df['DayOfWeek'] = DayDate[0]
df['Date'] = DayDate[1] + ', ' + year
df['Date'] = [re.sub("\s\(\d+\)", "", str(x)) for x in df['Date']]
df['Date'] = pd.to_datetime(df['Date'], format='%b %d, %Y')
list_of_df.append(df)
bbattend = pd.concat(list_of_df)
bbattend
我知道这绝对不是最有效的方法,但它得到了我想要的结果。
解决方案
这是我最终使用的最终代码:它基于@foglerit 的回答
#Create game_id which will be used to delete duplicates later
bbattend['game_id'] = bbattend['Team'] + bbattend['Date'].astype(str)
#Create year variable for matching
bbattend['Year'] = bbattend.Date.dt.year
# Create merged table
# Will match all dates of games of team with dates within same year of teams from same-market team
merged = bbattend.merge(
bbattend[["Date", "Year", "Team", "Net_Wins", "Win_Per"]],
how="inner",
left_on=["Year", "Same_Mkt_Team"],
right_on=["Year", "Team"],
suffixes=('', '_Same_Mkt_Team')
)
merged["date_diff"] = (merged.Date - merged.Date_Same_Mkt_Team).dt.days
#Only keep the dates of same-market team that occurred before the date of home team's game
merged = merged[merged['date_diff'] > 0]
#Sort by date_diff so closest dates appear first
merged.sort_values(by='date_diff', inplace = True)
#Only keep first game_id which will include the data of the same-market team for the closest date before the game
merged.drop_duplicates(subset =['game_id'], keep = 'first', inplace = True)
merged
我添加了 date_diff 必须为正的条件,因为我想要在比赛前发生的同一市场球队的比赛日期。
然后我按 date_diff 对数据帧进行排序并删除了 game_id 的重复项,以便最终数据帧只有最小的 date_diff。
推荐阅读
- javascript - 平滑扩展 Div 而不滞后
- firebase - 当我运行我的 ionic 3 时,问题 [object object] 显示在 html 页面中
- java - 如何找到成员所属的对象
- php - 如何使用我的标准 Laravel 项目正确设置 MDBootstrap
- python - 更新后Tensorflow极度缓慢
- javascript - D3-桑基未绘制
- uber-api - Uber API 随机超时
- excel - 应更新文件,但保存副本
- image-processing - 我已经使用 VGG16 构建了一个图像搜索。完成搜索需要 4 分钟。我可以使用哪些技术来缩短这段时间?
- github - 如何在本地恢复丢失的更改