title: Alternative to df.iterrows() for joining two Postgres tables and computing features
body: <p>I have a dataframe (<code>game_df</code>) and a Postgres table (<code>team_stats_1970_2017</code>).  </p>
  
  <p><code>game_df</code> consists of a few thousand rows with data like this...</p>
  
  <pre><code>      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
  </code></pre>
  
  <p><code>team_stats_1970_2017</code> would have this corresponding data</p>
  
  <pre><code>   team season_yr  r_per_g pa      ab    b_r  b_h   b2   b3   b_hr
  0  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   
  </code></pre>
  
  <p>As an example, for row 1 of <code>game_df</code>, the code selects the "ARI" and "SFG" data from <code>team_stats_1970_2017</code> in Postgres and creates features from this.  This is then repeated for the rest of the rows within <code>game_df</code>.</p>
  
  <p>I am currently using <code>df.iterrows</code>, but I am noticing it is quite slow as I am only testing a small piece of my data and it is still taking a while.  Would anyone have a better/faster alternative for this?  </p>
  
  <pre><code>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)
  
          ## Calculate result of game 
          if (p2 - p1) &gt; 0:
              result = 1
          else:
              result = 0
  
          features.append(feature)
          results.append(result)
  </code></pre>