title: Efficiently update values in a pandas dataframe with mixed dtype columns
body: <p>I have a large pandas DataFrame with shape (700,000, 5,000) containing columns of mixed dtypes (mostly int8, some float64, and a couple of datetime64[ns]). For each row in the dataframe I want to set the value of certain columns to zero if another column is also equal to zero.</p>
  
  <p>If I iterate over the dataframe and set the values using iloc, it is super slow. I've tried both iterrows and itertuples e.g.</p>
  
  <p><strong>1. iterrows</strong></p>
  
  <pre><code>ix_1 = 3
  ix_to_change = [20, 24, 51]  # Actually it is almost 5000 columns to change
  for i, row in df.iterrows():
      if not row[ix_1]:
          df.iloc[i, ix_to_change] = 0
  </code></pre>
  
  <p><strong>2. itertuples:</strong></p>
  
  <pre><code>ix_1 = 3
  ix_to_change = [20, 24, 51]  # Actually it is almost 5000 columns to change
  for row in df.itertuples():
      if not row[ix_1 + 1]:
          df.iloc[row[0], ix_to_change] = 0
  </code></pre>
  
  <p>I have also tried using pandas indexing, but it is also very slow (though better than iterrows or itertuples).</p>
  
  <p><strong>3. pandas loc &amp; iloc</strong></p>
  
  <pre><code>df.loc[df.iloc[:, ix_1]==0, df.columns[ix_to_change]] = 0
  </code></pre>
  
  <p>I've then tried dropping down to the underlying numpy array which works fine in terms of performance, but I run into problems with the dtypes.</p>
  
  <p>It quickly iterates through the underlying array, but the new dataframe has all 'object' dtypes. If I try to set the dtypes per column (as in this example) it fails on the datetime columns - possibly because they contain NaT items.</p>
  
  <p><strong>4. numpy</strong></p>
  
  <pre><code>X = df.values
  for i, x in enumerate(X):
      if not x[ix_1]:
          X[i].put(ix_to_change, 0)
  original_dtypes = df.dtypes
  df = pd.DataFrame(data=X, index=df.index, columns=df.columns)
  for col, col_dtype in original_dtypes.items():
      df[c] = df[c].astype(col_dtype)
  </code></pre>
  
  <p>Is there a better way for me to make the update in first place?</p>
  
  <p>Or if not, how should I go about keeping my dtypes the same (the datetime columns are not in the list of columns to change in case that is relevant)?</p>
  
  <p>Or maybe there's a better way for me to update the original dataframe with my updated numpy array where I only update the changed columns (all of which are int8)?</p>
  
  <h3>Update</h3>
  
  <p>As requested in the comments, here is a minimal example illustrating how int8 dtypes become object dtypes after dropping into numpy. To be clear, this is only an issue for method 4 above (which is the only non-slow method I have so far - if I can fix this dtype issue):</p>
  
  <pre><code>import pandas as pd
  
  df = pd.DataFrame({'int8_col':[10,11,12], 'float64_col':[1.5, 2.5, 3.5]})
  df['int8_col'] = df['int8_col'].astype('int8')
  df['datetime64_col'] = pd.to_datetime(['2018-01-01', '2018-01-02', '2018-01-03'])
  
  &gt;&gt;&gt; df.dtypes
  float64_col              float64
  int8_col                    int8
  datetime64_col    datetime64[ns]
  dtype: object
  
  X = df.values
  # At this point in real life I modify the int8 column(s) only in X
  
  new_df = pd.DataFrame(data=X, index=df.index, columns=df.columns)
  
  &gt;&gt;&gt; new_df.dtypes
  float64_col       object
  int8_col          object
  datetime64_col    object
  dtype: object
  </code></pre>