我有一個數據框（game_df）和一個Postgres表（team_stats_1970_2017）。game_df 由幾千行包含這樣的數據... season_yr home_team visitor_team home_team_runs visitor_team_runs0 2017 ARI SFG 6 51 2017 ARI SFG 4 82 2017 ARI SFG 8 63 2017 ARI SFG 9 34 2017 ARI CLE 7 35 2017 ARI CLE 11 26 2017 ATL LAD 2 3team_stats_1970_2017 會有這個對應的數據 team season_yr r_per_g pa ab b_r b_h b2 b3 b_hr0 ARI 2017 5.01 6224.0 5525 812 1405 314 39 220 1 ATL 2017 4.52 6216.0 5584 732 1467 289 26 165 2 CLE 2017 5.05 6234.0 5511 818 1449 333 29 212 3 LAD 2017 4.75 6191.0 5408 770 1347 312 20 221 4 SFG 2017 3.94 6137.0 5551 639 1382 290 28 128 例如，對于的第1行game_df，代碼從team_stats_1970_2017Postgres中選擇“ ARI”和“ SFG”數據，并由此創建特征。然后，對其中的其余行重復此操作game_df。我目前正在使用df.iterrows，但是我注意到它的速度很慢，因為我僅測試了我的一小部分數據，并且仍然需要一段時間。有人會為此提供更好/更快的替代方法嗎？features = []results = []for index,row in game_df.iterrows(): import psycopg2 as pg2 connect = pg2.connect(login) cursor=connect.cursor() year, t1, t2, p1, p2 = row p1, p2 = map(int, [p1, p2]) feature1 = cursor.execute("SELECT * FROM team_stats_1970_2017 WHERE team = (%s) and season_yr = (%s)",(t1,year)) feature1 = list(cursor.fetchone()[2::]) feature2 = cursor.execute("SELECT * FROM team_stats_1970_2017 WHERE team = (%s) and season_yr = (%s)",(t2,year)) feature2 = list(cursor.fetchone()[2::]) feature = np.array(feature2) - np.array(feature1)
2 回答
蕪湖不蕪
TA貢獻1796條經驗 獲得超7個贊
如果我理解正確，如果可以將其team_stats_1970_2017作為pandas數據框，則可以應用2個合并：一個在home_team和上season_yr，一個在visitor_team和上season_yr：
merged_df = (game_df.merge(team_stats_1970_2017,
left_on=['home_team', 'season_yr'],
right_on=['team', 'season_yr'])
.merge(team_stats_1970_2017, left_on=['visitor_team', 'season_yr'],
right_on=['team', 'season_yr'],
suffixes=['_home', '_visitor'])
.drop(['team_visitor', 'team_home'], axis=1))
>>> merged_df
season_yr home_team visitor_team home_team_runs visitor_team_runs \
0 2017 ARI SFG 6 5
1 2017 ARI SFG 4 8
2 2017 ARI SFG 8 6
3 2017 ARI SFG 9 3
4 2017 ARI CLE 7 3
5 2017 ARI CLE 11 2
6 2017 ATL LAD 2 3
r_per_g_home pa_home ab_home b_r_home b_h_home ... b3_home \
0 5.01 6224.0 5525 812 1405 ... 39
1 5.01 6224.0 5525 812 1405 ... 39
2 5.01 6224.0 5525 812 1405 ... 39
3 5.01 6224.0 5525 812 1405 ... 39
4 5.01 6224.0 5525 812 1405 ... 39
5 5.01 6224.0 5525 812 1405 ... 39
6 4.52 6216.0 5584 732 1467 ... 26
b_hr_home r_per_g_visitor pa_visitor ab_visitor b_r_visitor \
0 220 3.94 6137.0 5551 639
1 220 3.94 6137.0 5551 639
2 220 3.94 6137.0 5551 639
3 220 3.94 6137.0 5551 639
4 220 5.05 6234.0 5511 818
5 220 5.05 6234.0 5511 818
6 165 4.75 6191.0 5408 770
b_h_visitor b2_visitor b3_visitor b_hr_visitor
0 1382 290 28 128
1 1382 290 28 128
2 1382 290 28 128
3 1382 290 28 128
4 1449 333 29 212
5 1449 333 29 212
6 1347 312 20 221
[7 rows x 21 columns]
然后，您可以使用它merged_df來計算特征。例如（因為它似乎你希望你的特點np.arrays），計算之間的差異pa_home和pa_visitor（這僅僅是一個虛擬的例子）：
>>> (merged_df['pa_home'] - merged_df['pa_visitor']).values
array([ 87., 87., 87., 87., -10., -10., 25.])
添加回答
舉報
0/150
提交
取消