title: Pandas append() and remove duplicates messes up the index
body: <p>I am <strong>scraping</strong> some data from a basketball site and the plan is to automatically update it when new data is added.</p>
  <p>I get the data</p>
  <pre><code>stats = pd.read_html('URL', header=[0, 1])
  player_stats = stats[4]
  
  player_stats.to_csv('stats.csv')
  </code></pre>
  <p>Append it</p>
  <pre><code>with open('stats.csv', 'a') as f:
      player_stats.to_csv(f, header=False)
  </code></pre>
  <p>Remove duplicates (method 1)</p>
  <pre><code>old_data = pd.read_csv('stats.csv')
  data = old_data.drop_duplicates(subset='Unnamed: 1_level_0', keep='last')
  data.to_csv('stats.csv')
  </code></pre>
  <p>Remove duplicates (method 2)</p>
  <pre><code>old_data = pd.read_csv('stats.csv')
  bool_series = data[&quot;Unnamed: 1_level_0&quot;].duplicated(keep='last') 
  bool_series 
  data = data[~bool_series] 
  data.to_csv('stats.csv')
  </code></pre>
  <p>The problem I face is that after the <a href="https://i.imgur.com/o9fKwpx.png" rel="nofollow noreferrer">original data</a> is appended with the <a href="https://i.imgur.com/mrVCMTV.png" rel="nofollow noreferrer">new data</a> the remove duplicate method <a href="https://i.imgur.com/kGu4ZJz.png" rel="nofollow noreferrer">messes up the structure</a> of the file making future appending and removing duplicates impossible as duplicates <a href="https://i.imgur.com/4k1JJtB.png" rel="nofollow noreferrer">are not recognized</a> as such anymore...</p>
  <p>Why is the new index added and how do I fix that?</p>