Pandas append multiple columns for a single one
<p>How can I use pandas to append multiple KPI values per single customer efficiently?</p>

<p>A join of the <code>pivoted</code> df with the <code>customers</code> df makes some problems because the country is the index of the pivoted data frame and the nationality is not in the index.</p>

<pre><code>countryKPI = pd.DataFrame({'country':['Austria','Germany', 'Germany', 'Austria'],
                           'indicator':['z','x','z','x'],
                           'value':[7,8,9,7]})
customers = pd.DataFrame({'customer':['first','second'],
                           'nationality':['Germany','Austria'],
                           'value':[7,8]})
</code></pre>

<p>See the desired result in pink:
<a href="http://i.stack.imgur.com/VCbYn.jpg" rel="nofollow"><img src="http://i.stack.imgur.com/VCbYn.jpg" alt="enter image description here"></a></p>

<p>You could counter the mismatch in the categories through <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html" rel="nofollow"><code>merge</code></a>:</p>

<pre><code>df = pd.pivot_table(data=countryKPI, index=['country'], columns=['indicator'])
df.index.name = 'nationality'    
customers.merge(df['value'].reset_index(), on='nationality', how='outer')
</code></pre>

<p><a href="http://i.stack.imgur.com/W5lbD.png" rel="nofollow"><img src="http://i.stack.imgur.com/W5lbD.png" alt="Image"></a></p>

<p>Data:</p>

<pre><code>countryKPI = pd.DataFrame({'country':['Austria','Germany', 'Germany', 'Austria'],
                           'indicator':['z','x','z','x'],
                           'value':[7,8,9,7]})
customers = pd.DataFrame({'customer':['first','second'],
                           'nationality':['Slovakia','Austria'],
                           'value':[7,8]})
</code></pre>

<hr>

<p>The problem appears to be that you have got <code>CategoricalIndex</code> in your <code>DF</code> resulting from the <code>pivot</code> operation and when you perform <code>reset_index</code> on that complains you of that error.</p>

<p>Simply do reverse engineering as in check the <code>dtypes</code> of <code>countryKPI</code> and <code>customers</code> Dataframes and wherever there is <code>category</code> mentioned, convert those columns to their <code>string</code> representation via <code>astype(str)</code></p>

<hr>

<p><strong>Reproducing the Error and Countering it:</strong></p>

<p>Assume the <code>DF</code> to be the above mentioned:</p>

<pre><code>countryKPI['indicator'] = countryKPI['indicator'].astype('category')
countryKPI['country'] = countryKPI['country'].astype('category')
customers['nationality'] = customers['nationality'].astype('category')

countryKPI.dtypes
country      category
indicator    category
value           int64
dtype: object

customers.dtypes
customer         object
nationality    category
value             int64
dtype: object
</code></pre>

<p>After <code>pivot</code> operation:</p>

<pre><code>df = pd.pivot_table(data=countryKPI, index=['country'], columns=['indicator'])
df.index
CategoricalIndex(['Austria', 'Germany'], categories=['Austria', 'Germany'], ordered=False, 
                  name='country', dtype='category')
# ^^ See the categorical index
</code></pre>

<p>When you perform <code>reset_index</code> on that:</p>

<pre><code>df.reset_index()
</code></pre>

<blockquote>
  <p>TypeError: cannot insert an item into a CategoricalIndex that is not
  already an existing category</p>
</blockquote>

<p>To counter that error, simply cast the categorical columns to <code>str</code> type.</p>

<pre><code>countryKPI['indicator'] = countryKPI['indicator'].astype('str')
countryKPI['country'] = countryKPI['country'].astype('str')
customers['nationality'] = customers['nationality'].astype('str')
</code></pre>

<p>Now, the <code>reset_index</code> part works and even the <code>merge</code> too.</p>

python pandas