title: Problem with aggregation, column creation and connection in Python Pandas?
body: <p>I have DataFrame1 like below:</p>
  <pre><code>df1 = pd.DataFrame({&quot;ID&quot;: [&quot;ID1&quot;, &quot;ID2&quot;, &quot;ID2&quot;, &quot;ID2&quot;, &quot;ID3&quot;], &quot;number&quot;: [
                     11, 1, 30, 4, 5], &quot;number2&quot;: [55, 23, 12, 66, 78]})
  
  df2 = pd.DataFrame({&quot;ID&quot;: [&quot;ID1&quot;, &quot;ID2&quot;, &quot;ID3&quot;, &quot;ID4&quot;,
                             &quot;ID5&quot;], &quot;item&quot;: [&quot;aa&quot;, &quot;bb&quot;, &quot;cc&quot;, &quot;dd&quot;, &quot;ee&quot;]})
  </code></pre>
  <p>And I would like to:</p>
  <ol>
  <li>Make aggregation, when I sum values from &quot;number&quot; column for each ID from &quot;ID&quot; column</li>
  <li>Create new column &quot;new&quot; where will be the highest value from &quot;number2&quot; column for each ID from &quot;ID&quot; column (for instance, for ID1 the biggest val. from number2 column is 55, for ID2 66, for ID3 78)</li>
  <li>Connect df1 with df2 based on &quot;ID&quot;, nevertheless I do not want to have any NaN, so I ned to take df1 and add only matching columns so ID1, ID2 and ID3.</li>
  </ol>
  <p>To be more precision below I load table wthich I need to have as a result:</p>
  <p><a href="https://i.stack.imgur.com/g6jbb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g6jbb.png" alt="enter image description here" /></a></p>