title: Optimizing pandas computation
body: <p>I have 22 million rows of house property sale data in a database table called sale_transactions. I am performing a job where I read information from this table, perform some calculations, and use the results to create entries to a new table. The process looks like this:</p>
  
  <pre><code>for index, row in zipcodes.iterrows(): # ~100k zipcodes
      sql_string = """SELECT * from sale_transactions WHERE zipcode = '{ZIPCODE}' """
      sql_query = sql_string.format(ZIPCODE=row['zipcode'])         
      df = pd.read_sql(sql_query, _engine)
      area_stat = create_area_stats(df) # function does calculations
      area_stat.save() # saves a Django model
  </code></pre>
  
  <p>At the moment each iteration of this loop takes about 20 seconds on my macbook pro (16GB RAM), which means that the code is going to take weeks to finish. The expensive part is the <code>read_sql</code> line.</p>
  
  <p>How can I optimize this? I can't read the whole sale_transactions table into memory, it is about 5 GB, hence using the sql query each time to capture the relevant rows with the WHERE clause. </p>
  
  <p>Most answers about optimizing pandas talk about reading with chunking, but in this case I need to perform the WHERE on all the data combined, since I am performing calculations in the <code>create_area_stats</code> function like number of sales over a ten year period. I don't have easy access to a machine with loads of RAM, unless I start going to town with EC2, which I worry will be expensive and quite a lot of hassle.</p>
  
  <p>Suggestions would be greatly appreciated.</p>