Merge, sum and removing duplicates with pandas
<p>I have two different data frames with different sizes just like this:</p>

<pre><code>df_web = (['Event Category', 'ID', 'Total Events', 
           'Unique Events', 'Event Value', 'Avg. Value'])

df_app = (['Event Category', 'ID', 'Total Events',
           'Unique Events', 'Event Value', 'Avg. Value']
</code></pre>

<p>I'm using pandas to try to merge them in a 'df_final', but I want to sum the values of 'Total Events' which have the same 'ID' , and in the end I would like to have a 'df_final' without duplicates in the ID.</p>

<p>I tried: </p>

<pre><code>df_final_analysis = df_web.groupby(['Event Category', 'ID', 'Total Events', 
                                   'Unique Events', 'Event Value', 'Avg. Value'],
                                    as_index=False)['Total Events'].sum()
</code></pre>

<p>But it doesnt give me the result that I want. </p>

<p>For example:</p>

<p>df_web</p>

<pre><code>  Video          A        10

  Video          B         5

  Video          C         1

  Video          F         1

  Video          G         1

  Video          H         1
</code></pre>

<p>For df_app:</p>

<pre><code>  Video         A       15


  Video         D        3


  Video         C        1
</code></pre>

<p>For the df_final_analysis I want:</p>

<pre><code>  Video         A       25

  Video         B        5

  Video         D        3

  Video         C        2

  Video         F        1

  Video         G        1

  Video         H        1
</code></pre>

<p>Is there a elegant way to do this?</p>

<p>Modified solution from your code using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow">pd.concat</a>:</p>

<pre><code>In [46]: df
Out[46]: 
           0  1   2
    0  Video  A  10
    1  Video  B   5
    2  Video  C   1
    3  Video  F   1
    4  Video  G   1
    5  Video  H   1

In [47]: df1
Out[47]: 
           0  1   2
    0  Video  A  15
    1  Video  D   3
    2  Video  C   1

In[59]:pd.concat([df,df1]).groupby([0,1],as_index=False)[2].sum()
Out[59]: 
       0  1   2
0  Video  A  25
1  Video  B   5
2  Video  C   2
3  Video  D   3
4  Video  F   1
5  Video  G   1
6  Video  H   1
</code></pre>

<p>using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html" rel="nofollow">pd.merge</a>:</p>

<pre><code>In [60]: pd.merge(df,df1,how='outer').groupby([0,1],as_index=False)[2].sum()
Out[60]: 
       0  1     2
0  Video  A  25.0
1  Video  B   5.0
2  Video  C   1.0
3  Video  D   3.0
4  Video  F   1.0
5  Video  G   1.0
6  Video  H   1.0
</code></pre>

python pandas