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>I think you can use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.concat.html" rel="nofollow"><code>concat</code></a>:</p>

<pre><code>df_pivoted = countryKPI.pivot_table(index='country', 
                              columns='indicator', 
                              values='value', 
                              fill_value=0)
print (df_pivoted)    
indicator  x  z
country        
Austria    7  7
Germany    8  9

print (pd.concat([customers.set_index('nationality'), df_pivoted], axis=1))
        customer  value  x  z
Austria   second      8  7  7
Germany    first      7  8  9                       


print (pd.concat([customers.set_index('nationality'), df_pivoted], axis=1)
         .reset_index()
         .rename(columns={'index':'nationality'})
         [['customer','nationality','value','x','z']])

  customer nationality  value  x  z
0   second     Austria      8  7  7
1    first     Germany      7  8  9
</code></pre>

<p>EDIT by comments:</p>

<p>Problem is <code>dtypes</code> of columns <code>customers.nationality</code> and <code>countryKPI.country</code> is <code>category</code> and if some categories are missing, it raise error:</p>

<blockquote>
  <p>ValueError: incompatible categories in categorical concat </p>
</blockquote>

<p>Solution find common categories by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Index.union.html" rel="nofollow"><code>union</code></a> and then <a href="http://pandas.pydata.org/pandas-docs/stable/categorical.html#setting-categories" rel="nofollow">set_categories</a>:</p>

<pre><code>import pandas as pd
import numpy as np

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]})

customers.nationality = customers.nationality.astype('category')
countryKPI.country = countryKPI.country.astype('category')

print (countryKPI.country.cat.categories)
Index(['Austria', 'Germany'], dtype='object')

print (customers.nationality.cat.categories)
Index(['Austria', 'Slovakia'], dtype='object')

all_categories =countryKPI.country.cat.categories.union(customers.nationality.cat.categories)
print (all_categories)
Index(['Austria', 'Germany', 'Slovakia'], dtype='object')

customers.nationality = customers.nationality.cat.set_categories(all_categories)
countryKPI.country = countryKPI.country.cat.set_categories(all_categories)
</code></pre>



<pre><code>df_pivoted = countryKPI.pivot_table(index='country', 
                              columns='indicator', 
                              values='value', 
                              fill_value=0)
print (df_pivoted)    
indicator  x  z
country        
Austria    7  7
Germany    8  9
Slovakia   0  0        

print (pd.concat([customers.set_index('nationality'), df_pivoted], axis=1)
         .reset_index()
         .rename(columns={'index':'nationality'})
         [['customer','nationality','value','x','z']])

  customer nationality  value  x  z
0   second     Austria    8.0  7  7
1      NaN     Germany    NaN  8  9
2    first    Slovakia    7.0  0  0
</code></pre>

<p>If need better performance, instead <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html" rel="nofollow"><code>pivot_table</code></a> use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a>:</p>

<pre><code>df_pivoted1 = countryKPI.groupby(['country','indicator'])
                        .mean()
                        .squeeze()
                        .unstack()
                        .fillna(0)
print (df_pivoted1)
indicator    x    z
country            
Austria    7.0  7.0
Germany    8.0  9.0
Slovakia   0.0  0.0
</code></pre>

<p><strong>Timings</strong>:</p>

<pre><code>In [177]: %timeit countryKPI.pivot_table(index='country', columns='indicator', values='value', fill_value=0)
100 loops, best of 3: 6.24 ms per loop

In [178]: %timeit countryKPI.groupby(['country','indicator']).mean().squeeze().unstack().fillna(0)
100 loops, best of 3: 4.28 ms per loop
</code></pre>

python pandas