title: Pandas.pivot replace some existing values with NaN
body: <p>I have to read some table from a SQL Server and merge their values in a single data structure for a machine learning project. I'm using Pandas and in particular <code>pd.read_sql_query</code> for read the values and <code>pd.merge</code> to fuse them.
  One table has at least 80 milion rows and if I try to read it entirely it occupies all my memory storage (it's not so much, only 20gb but I've got a small ssd), so I decided to divide it in chunk of 100000 rows:</p>
  <pre><code>df_bilanci = pd.read_sql_query('SELECT [IdBilancioRiclassificato], [IdBilancio], [Importo] FROM dbo.Bilanci', conn, chunksize=100000)
  </code></pre>
  <p>One single chunk will be like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th></th>
  <th>IdBilancioRiclassificato</th>
  <th>IdBilancio</th>
  <th>Importo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>10.0</td>
  <td>7001.0</td>
  <td>0.0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>11.0</td>
  <td>7001.0</td>
  <td>502643.0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>12.0</td>
  <td>7001.0</td>
  <td>-4550.0</td>
  </tr>
  <tr>
  <td>3</td>
  <td>10.0</td>
  <td>7002.0</td>
  <td>654654.0</td>
  </tr>
  <tr>
  <td>4</td>
  <td>11.0</td>
  <td>7002.0</td>
  <td>0.0</td>
  </tr>
  <tr>
  <td>5</td>
  <td>12.0</td>
  <td>7002.0</td>
  <td>0.0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'm interested to have the values of IdBilancioRiclassificato as columns (there are a total of 97 unique values for this column, so they have to be 97 columns), so I used <code>pd.pivot</code> on every chunk and then <code>pd.concat</code> plus merge to put together all the data:</p>
  <pre><code>for chunk in df_bilanci:
  
      chunk.reset_index()
      chunk_pivoted = pd.pivot(data=chunk, 
                              index='IdBilancio', 
                              columns='IdBilancioRiclassificato',  
                              values='Importo'
                              )    
  
      df_aziende_bil = pd.concat([df_aziende_bil, pd.merge(left=df_aziende_anagrafe, right=chunk_pivoted, left_on='ID', right_index=True)])
  </code></pre>
  <p>At this point however, the <code>chunk_pivoted</code> dataframe has some values replaced with NaN values but if I look in the table the values exist.</p>
  <p>The result expected is a table like this one:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>IdBilancio</th>
  <th>10.0</th>
  <th>11.0</th>
  <th>12.0</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>7001.0</td>
  <td>0.0</td>
  <td>502643.0</td>
  <td>-4550.0</td>
  </tr>
  <tr>
  <td>7002.0</td>
  <td>654654.0</td>
  <td>0.0</td>
  <td>0.0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>but i've got something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>IdBilancio</th>
  <th>10.0</th>
  <th>11.0</th>
  <th>12.0</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>7001.0</td>
  <td>0.0</td>
  <td>NaN</td>
  <td>-4550.0</td>
  </tr>
  <tr>
  <td>7002.0</td>
  <td>654654.0</td>
  <td>NaN</td>
  <td>NaN</td>
  </tr>
  </tbody>
  </table>
  </div>