Key error in data-frame handling
<p>I have a dataframe <code>stockData</code>.  A part example looks like:</p>

<pre><code>Name: BBG.XCSE.CARLB.S_LAST_ADJ    BBG.XCSE.CARLB.S_FX  .....
date
2015-09-11    0.1340                           490.763
2015-09-14    0.1340                           484.263
2015-09-15    0.1340                           484.755
2015-09-16    0.1340                           507.703
2015-09-17    0.1340                           514.104  .....
</code></pre>

<p>each column has a data type , dtype: float64</p>

<p>I am looping a static data dataframe which contans every name in my universe  and I iterate through this, then iterating through each day for each name (in this example the name is BBG.XCSE.CARLB.S but there are hundreds of names in reality) taking the column 'name_LAST_ADJ' and multiplying by the column 'name_FX'.<br>
the code that I am using looks like:</p>

<pre><code>for i, row in staticData.iterrows():

        unique_id = i

        #Create new column for the current name that will take the result of the following calculation
        stockData[unique_id+"_LAST_ADJ_EUR"] = np.nan

        #Perform calculation - this is where I get the KeyError when there is no data in the name_ADJ_LAST column.
        stockData[unique_id+"_LAST_ADJ_EUR"] = stockData[unique_id+"_FX"]*stockData[unique_id+"_LAST_ADJ"]


    return stockData
</code></pre>

<p>However sometimes the data does not exist (because there is no history for the name) and I receive a key error because the columns for the name are not in the data-frame.</p>

<p>With the above code I am trying to create an additional column called name_LAST_ADJ_EUR and when there is data it should look like:</p>

<pre><code>Name: BBG.XCSE.CARLB.S_LAST_ADJ    BBG.XCSE.CARLB.S_FX     BBG.XCSE.CARLB.S_LAST_ADJ_EUR
    date
    2015-09-11    0.1340                       490.763              65.762242
    2015-09-14    0.1340                       484.263              64.891242
    2015-09-15    0.1340                       484.755              64.95717
    2015-09-16    0.1340                       507.703              68.032202
    2015-09-17    0.1340                       514.104              68.889936
</code></pre>

<p>and when there is data no data in the name_LAST_ADJ column is there a way generate an NaN output for he column so it looks like:</p>

<pre><code>Name:      BBG.XCSE.CARLB.S_LAST_ADJ_EUR
    date
    2015-09-11    NaN    
    2015-09-14    NaN       
    2015-09-15    NaN       
    2015-09-16    NaN         
    2015-09-17    NaN        
</code></pre>

<p>I have tried using the following:</p>

<pre><code>stockData[unique_id+"_LAST_ADJ_EUR"] = np.where((stockData[unique_id+"_LAST_ADJ"] == np.nan),stockData[unique_id+"_LAST_ADJ_EUR"]='NaN',stockData[unique_id+"_LAST_ADJ_EUR"] = stockData[unique_id+"_FX"] * stockData[unique_id+"_LAST_ADJ"])
</code></pre>

<p>which would be fine if there was a column but when there is no column to reference it throws the KeyError exception.</p>

<p>Any help much appreciated</p>

<p>I'd start by parsing your columns into a multiindex</p>

<pre><code>tups = df.columns.to_series() \
         .str.extract(r'(.*)_(LAST_ADJ|FX)', expand=False) \
         .apply(tuple, 1).tolist()

df.columns = pd.MultiIndex.from_tuples(tups).swaplevel(0, 1)

df
</code></pre>

<p><a href="http://i.stack.imgur.com/1dSlS.png" rel="nofollow"><img src="http://i.stack.imgur.com/1dSlS.png" alt="enter image description here"></a></p>

<p>Then multiplication becomes simple</p>

<pre><code>df.LAST_ADJ * df.FX
</code></pre>

<p><a href="http://i.stack.imgur.com/OMDls.png" rel="nofollow"><img src="http://i.stack.imgur.com/OMDls.png" alt="enter image description here"></a></p>

<p>Tricky part for me is inserting it back with <code>'EUR'</code>.  I did this</p>

<pre><code>pd.concat([df, pd.concat([df.LAST_ADJ.mul(df.FX)], axis=1, keys=['EUR'])], axis=1)
</code></pre>

<p><a href="http://i.stack.imgur.com/5SiHG.png" rel="nofollow"><img src="http://i.stack.imgur.com/5SiHG.png" alt="enter image description here"></a></p>

python pandas