title: How to read all columns of an excel file into a pandas data frame until a condition is met?
body: <p>I try to read an excel file into a pandas data frame. But I only need the columns till a marker called 'End'. This is just a string in a cell in an otherwise empty column, in the excel file. The number of columns before this marker can change in number, so I can't set fixed columns.</p>
  <p>What I tried so far was to read the whole excel file into a data frame. Now I'm trying to drop all columns after one column contains the 'End' string or select all columns before the 'End' marker.</p>
  <p>How can I achieve this?</p>
  <p>Maybe something like this?</p>
  <p><code>df = df.iloc[:, : df != 'End']</code></p>
  <p>I tried to find the column containing 'End' like this:</p>
  <p><code>end = [col for col in df.columns if 'End' in col]</code></p>
  <p>But the list stays empty.</p>
  <p>P.S. Some cells have a background color but contain no content nor formula. Somehow pandas reads them as just empty cells and I can't fill them with na values for some reason. I tried <code>pd.read_excel(***,  na_values='^\s+$')</code> and <code>df.fillna('', inplace=True)</code> in various variations with no success. Maybe you have an idea how to fix this as well.</p>
  <p>EDIT:</p>
  <p>The 'End' is located in the first cell of the column. After import into a data frame it reads:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Unnamed 13</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>End</td>
  </tr>
  <tr>
  <td>NaN</td>
  </tr>
  <tr>
  <td>*empty</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>With *empty meaning that this cell in the data frame is just blank, and I can't transform into nan. In the Excel file this column has a background color. Maybe that's why pandas gets confused. These empty cell show up wherever the Excel has an empty cell with background color.</p>
  <p><a href="https://i.stack.imgur.com/qwqsT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qwqsT.png" alt="Debug View" /></a></p>