How To Combine Multiple Rows Into One Based on Shared Value in Pandas
<p>I have a dataframe that generically looks like this: </p>

<pre><code>df = pd.DataFrame({'Country': ['USA', 'USA', 'Canada', 'Canada'], 'GDP':          [45000, 68000, 34000, 46000], 'Education': [5, 3, 7, 9]})
</code></pre>

<p>Giving: </p>

<pre><code>    Country  Education    GDP
0     USA          5    45000
1     USA          3    68000
2  Canada          7    34000
3  Canada          9    46000
</code></pre>

<p>I'd like to have all the values for each country listed on the same row, so it reads:</p>

<pre><code>Country    Education    Education    GDP        GDP
USA         5            3           45000      68000
</code></pre>

<p>How does one accomplish this?  </p>

<p>And yes, some of the columns do have the same name.</p>

<p>Thank you.</p>

<p>Original DataFrame:</p>

<pre><code>In [150]: df
Out[150]: 
  Country  Education    GDP
0     USA          5  45000
1     USA          3  68000
2  Canada          7  34000
3  Canada          9  46000
</code></pre>

<p>Given that <a href="http://pandas.pydata.org/pandas-docs/stable/groupby.html" rel="nofollow">each country</a> will have exactly two values for the same attribute:</p>

<pre><code>In [151]: df1 = df.groupby('Country').nth(0).reset_index()

In [152]: df1
Out[152]: 
  Country  Education    GDP
0  Canada          7  34000
1     USA          5  45000

In [153]: df2 = df.groupby('Country').nth(1).reset_index()

In [154]: df2
Out[154]: 
  Country  Education    GDP
0  Canada          9  46000
1     USA          3  68000
</code></pre>

<p><a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow">Concat</a> the two data frames and <a href="http://pandas.pydata.org/pandas-docs/version/0.18.0/generated/pandas.DataFrame.drop.html" rel="nofollow">drop</a> duplicate column from any one:    </p>

<pre><code>In [155]: pd.concat([df1, df2.drop('Country', 1)], axis=1)
Out[155]: 
  Country  Education    GDP  Education    GDP
0  Canada          7  34000          9  46000
1     USA          5  45000          3  68000
</code></pre>

<p>Rearrange the columns, if needed:</p>

<pre><code>In [165]: df3 = pd.concat([df1, df2.drop('Country', 1)], axis=1)

In [166]: df3 = df3[['Country', 'Education', 'GDP']]

In [167]: df3
Out[167]: 
  Country  Education  Education    GDP    GDP
0  Canada          7          9  34000  46000
1     USA          5          3  45000  68000
</code></pre>

python pandas