<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="from collections import OrderedDict
import random
import pandas as pd


pd.__version__"><pre class="notranslate"><span class="pl-k">from</span> <span class="pl-s1">collections</span> <span class="pl-k">import</span> <span class="pl-v">OrderedDict</span>
<span class="pl-k">import</span> <span class="pl-s1">random</span>
<span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>


<span class="pl-s1">pd</span>.<span class="pl-s1">__version__</span></pre></div>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="'0.20.3'"><pre class="notranslate"><code class="notranslate">'0.20.3'
</code></pre></div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df = pd.DataFrame.from_dict(OrderedDict(
    [
        ((lv1, lv2, lv3), {'KG': random.randint(0, 100)})
        for lv1 in ['A', 'B']
        for lv2 in ['M', 'N', 'O']
        for lv3 in ['X', 'Y', 'Z']
    ]
))
df     "><pre class="notranslate"><span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>.<span class="pl-en">from_dict</span>(<span class="pl-v">OrderedDict</span>(
    [
        ((<span class="pl-s1">lv1</span>, <span class="pl-s1">lv2</span>, <span class="pl-s1">lv3</span>), {<span class="pl-s">'KG'</span>: <span class="pl-s1">random</span>.<span class="pl-en">randint</span>(<span class="pl-c1">0</span>, <span class="pl-c1">100</span>)})
        <span class="pl-k">for</span> <span class="pl-s1">lv1</span> <span class="pl-c1">in</span> [<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>]
        <span class="pl-k">for</span> <span class="pl-s1">lv2</span> <span class="pl-c1">in</span> [<span class="pl-s">'M'</span>, <span class="pl-s">'N'</span>, <span class="pl-s">'O'</span>]
        <span class="pl-k">for</span> <span class="pl-s1">lv3</span> <span class="pl-c1">in</span> [<span class="pl-s">'X'</span>, <span class="pl-s">'Y'</span>, <span class="pl-s">'Z'</span>]
    ]
))
<span class="pl-s1">df</span>     </pre></div>
<div dir="auto">
<table border="1" role="table">
  <thead>
    <tr>
      <th></th>
      <th colspan="9">A</th>
      <th colspan="9">B</th>
    </tr>
    <tr>
      <th></th>
      <th colspan="3">M</th>
      <th colspan="3">N</th>
      <th colspan="3">O</th>
      <th colspan="3">M</th>
      <th colspan="3">N</th>
      <th colspan="3">O</th>
    </tr>
    <tr>
      <th></th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>KG</th>
      <td>16</td>
      <td>71</td>
      <td>27</td>
      <td>8</td>
      <td>83</td>
      <td>0</td>
      <td>100</td>
      <td>72</td>
      <td>100</td>
      <td>42</td>
      <td>26</td>
      <td>21</td>
      <td>91</td>
      <td>5</td>
      <td>36</td>
      <td>98</td>
      <td>96</td>
      <td>52</td>
    </tr>
  </tbody>
</table>
</div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df.loc[:, idx[:, :, 'X']]"><pre class="notranslate"><span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[:, <span class="pl-s1">idx</span>[:, :, <span class="pl-s">'X'</span>]]</pre></div>
<div dir="auto">
<table border="1" role="table">
  <thead>
    <tr>
      <th></th>
      <th colspan="3">A</th>
      <th colspan="3">B</th>
    </tr>
    <tr>
      <th></th>
      <th>M</th>
      <th>N</th>
      <th>O</th>
      <th>M</th>
      <th>N</th>
      <th>O</th>
    </tr>
    <tr>
      <th></th>
      <th>X</th>
      <th>X</th>
      <th>X</th>
      <th>X</th>
      <th>X</th>
      <th>X</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>KG</th>
      <td>44</td>
      <td>83</td>
      <td>-28</td>
      <td>5</td>
      <td>-31</td>
      <td>44</td>
    </tr>
  </tbody>
</table>
</div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df.xs('Y', level=2, axis=1) - df.xs('Z', level=2, axis=1)"><pre class="notranslate"><span class="pl-s1">df</span>.<span class="pl-en">xs</span>(<span class="pl-s">'Y'</span>, <span class="pl-s1">level</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>) <span class="pl-c1">-</span> <span class="pl-s1">df</span>.<span class="pl-en">xs</span>(<span class="pl-s">'Z'</span>, <span class="pl-s1">level</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>)</pre></div>
<div dir="auto">
<table border="1" role="table">
  <thead>
    <tr>
      <th></th>
      <th colspan="3">A</th>
      <th colspan="3">B</th>
    </tr>
    <tr>
      <th></th>
      <th>M</th>
      <th>N</th>
      <th>O</th>
      <th>M</th>
      <th>N</th>
      <th>O</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>KG</th>
      <td>44</td>
      <td>83</td>
      <td>-28</td>
      <td>5</td>
      <td>-31</td>
      <td>44</td>
    </tr>
  </tbody>
</table>
</div>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="idx = pd.IndexSlice
df.loc[:, idx[:, :, 'X']] = (df.xs('Y', level=2, axis=1) - df.xs('Z', level=2, axis=1))
df"><pre class="notranslate"><span class="pl-s1">idx</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">IndexSlice</span>
<span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[:, <span class="pl-s1">idx</span>[:, :, <span class="pl-s">'X'</span>]] <span class="pl-c1">=</span> (<span class="pl-s1">df</span>.<span class="pl-en">xs</span>(<span class="pl-s">'Y'</span>, <span class="pl-s1">level</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>) <span class="pl-c1">-</span> <span class="pl-s1">df</span>.<span class="pl-en">xs</span>(<span class="pl-s">'Z'</span>, <span class="pl-s1">level</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>))
<span class="pl-s1">df</span></pre></div>
<div dir="auto">
<table border="1" role="table">
  <thead>
    <tr>
      <th></th>
      <th colspan="9">A</th>
      <th colspan="9">B</th>
    </tr>
    <tr>
      <th></th>
      <th colspan="3">M</th>
      <th colspan="3">N</th>
      <th colspan="3">O</th>
      <th colspan="3">M</th>
      <th colspan="3">N</th>
      <th colspan="3">O</th>
    </tr>
    <tr>
      <th></th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>KG</th>
      <td>NaN</td>
      <td>71</td>
      <td>27</td>
      <td>NaN</td>
      <td>83</td>
      <td>0</td>
      <td>NaN</td>
      <td>72</td>
      <td>100</td>
      <td>NaN</td>
      <td>26</td>
      <td>21</td>
      <td>NaN</td>
      <td>5</td>
      <td>36</td>
      <td>NaN</td>
      <td>96</td>
      <td>52</td>
    </tr>
  </tbody>
</table>
</div>
<p dir="auto">This was unexpected. I realize that the indexes do not match, and hence this behavior, but I expect Pandas to throw an Exception, rather than silently do this</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="idx = pd.IndexSlice
df.loc[:, idx[:, :, 'X']] = (df.xs('Y', level=2, axis=1) - df.xs('Z', level=2, axis=1)).values
df"><pre class="notranslate"><span class="pl-s1">idx</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">IndexSlice</span>
<span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[:, <span class="pl-s1">idx</span>[:, :, <span class="pl-s">'X'</span>]] <span class="pl-c1">=</span> (<span class="pl-s1">df</span>.<span class="pl-en">xs</span>(<span class="pl-s">'Y'</span>, <span class="pl-s1">level</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>) <span class="pl-c1">-</span> <span class="pl-s1">df</span>.<span class="pl-en">xs</span>(<span class="pl-s">'Z'</span>, <span class="pl-s1">level</span><span class="pl-c1">=</span><span class="pl-c1">2</span>, <span class="pl-s1">axis</span><span class="pl-c1">=</span><span class="pl-c1">1</span>)).<span class="pl-s1">values</span>
<span class="pl-s1">df</span></pre></div>
<div dir="auto">
<table border="1" role="table">
  <thead>
    <tr>
      <th></th>
      <th colspan="9">A</th>
      <th colspan="9">B</th>
    </tr>
    <tr>
      <th></th>
      <th colspan="3">M</th>
      <th colspan="3">N</th>
      <th colspan="3">O</th>
      <th colspan="3">M</th>
      <th colspan="3">N</th>
      <th colspan="3">O</th>
    </tr>
    <tr>
      <th></th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
      <th>X</th>
      <th>Y</th>
      <th>Z</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>KG</th>
      <td>44</td>
      <td>71</td>
      <td>27</td>
      <td>83</td>
      <td>83</td>
      <td>0</td>
      <td>-28</td>
      <td>72</td>
      <td>100</td>
      <td>5</td>
      <td>26</td>
      <td>21</td>
      <td>-31</td>
      <td>5</td>
      <td>36</td>
      <td>44</td>
      <td>96</td>
      <td>52</td>
    </tr>
  </tbody>
</table>
</div>
<p dir="auto">This was, of course, the behavior I was expecting</p>
<p dir="auto">Best shown with an example.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import numpy as np, pandas as pd
timestamps = map(pd.Timestamp, ['2014-01-01', '2014-02-01'])
categories = ['A', 'B', 'C', 'D']
df = pd.DataFrame(index=pd.MultiIndex.from_product([timestamps, categories], names=['ts', 'cat']),
                  columns=['Col1', 'Col2'])

&gt;&gt;&gt; df
                Col1  Col2
ts         cat            
2014-01-01 A     NaN   NaN
           B     NaN   NaN
           C     NaN   NaN
           D     NaN   NaN
2014-02-01 A     NaN   NaN
           B     NaN   NaN
           C     NaN   NaN
           D     NaN   NaN"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">numpy</span> <span class="pl-k">as</span> <span class="pl-s1">np</span>, <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>
<span class="pl-s1">timestamps</span> <span class="pl-c1">=</span> <span class="pl-en">map</span>(<span class="pl-s1">pd</span>.<span class="pl-v">Timestamp</span>, [<span class="pl-s">'2014-01-01'</span>, <span class="pl-s">'2014-02-01'</span>])
<span class="pl-s1">categories</span> <span class="pl-c1">=</span> [<span class="pl-s">'A'</span>, <span class="pl-s">'B'</span>, <span class="pl-s">'C'</span>, <span class="pl-s">'D'</span>]
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>(<span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-s1">pd</span>.<span class="pl-v">MultiIndex</span>.<span class="pl-en">from_product</span>([<span class="pl-s1">timestamps</span>, <span class="pl-s1">categories</span>], <span class="pl-s1">names</span><span class="pl-c1">=</span>[<span class="pl-s">'ts'</span>, <span class="pl-s">'cat'</span>]),
                  <span class="pl-s1">columns</span><span class="pl-c1">=</span>[<span class="pl-s">'Col1'</span>, <span class="pl-s">'Col2'</span>])

<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">df</span>
                <span class="pl-v">Col1</span>  <span class="pl-v">Col2</span>
<span class="pl-s1">ts</span>         <span class="pl-s1">cat</span>            
<span class="pl-c1">2014</span><span class="pl-c1">-</span><span class="pl-c1">01</span><span class="pl-c1">-</span><span class="pl-c1">01</span> <span class="pl-v">A</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
           <span class="pl-v">B</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
           <span class="pl-v">C</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
           <span class="pl-v">D</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
<span class="pl-c1">2014</span><span class="pl-c1">-</span><span class="pl-c1">02</span><span class="pl-c1">-</span><span class="pl-c1">01</span> <span class="pl-v">A</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
           <span class="pl-v">B</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
           <span class="pl-v">C</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span>
           <span class="pl-v">D</span>     <span class="pl-v">NaN</span>   <span class="pl-v">NaN</span></pre></div>
<p dir="auto">I want to set the values for all categories in a single month. These examples work just fine.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df.loc['2014-01-01', 'Col1'] = 5
df.loc['2014-01-01', 'Col2'] = [1,2,3,4]

&gt;&gt;&gt; df
               Col1 Col2
ts         cat          
2014-01-01 A      5    1
           B      5    2
           C      5    3
           D      5    4
2014-02-01 A    NaN  NaN
           B    NaN  NaN
           C    NaN  NaN
           D    NaN  NaN"><pre class="notranslate"><span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[<span class="pl-s">'2014-01-01'</span>, <span class="pl-s">'Col1'</span>] <span class="pl-c1">=</span> <span class="pl-c1">5</span>
<span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[<span class="pl-s">'2014-01-01'</span>, <span class="pl-s">'Col2'</span>] <span class="pl-c1">=</span> [<span class="pl-c1">1</span>,<span class="pl-c1">2</span>,<span class="pl-c1">3</span>,<span class="pl-c1">4</span>]

<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">df</span>
               <span class="pl-v">Col1</span> <span class="pl-v">Col2</span>
<span class="pl-s1">ts</span>         <span class="pl-s1">cat</span>          
<span class="pl-c1">2014</span><span class="pl-c1">-</span><span class="pl-c1">01</span><span class="pl-c1">-</span><span class="pl-c1">01</span> <span class="pl-v">A</span>      <span class="pl-c1">5</span>    <span class="pl-c1">1</span>
           <span class="pl-v">B</span>      <span class="pl-c1">5</span>    <span class="pl-c1">2</span>
           <span class="pl-v">C</span>      <span class="pl-c1">5</span>    <span class="pl-c1">3</span>
           <span class="pl-v">D</span>      <span class="pl-c1">5</span>    <span class="pl-c1">4</span>
<span class="pl-c1">2014</span><span class="pl-c1">-</span><span class="pl-c1">02</span><span class="pl-c1">-</span><span class="pl-c1">01</span> <span class="pl-v">A</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span>
           <span class="pl-v">B</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span>
           <span class="pl-v">C</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span>
           <span class="pl-v">D</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span></pre></div>
<p dir="auto">These examples don't work.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="df.loc['2014-01-01', 'Col1'] += 1
df.loc['2014-02-01', 'Col2'] = df.loc['2014-01-01', 'Col2']

&gt;&gt;&gt; df
               Col1 Col2
ts         cat          
2014-01-01 A    NaN    1
           B    NaN    2
           C    NaN    3
           D    NaN    4
2014-02-01 A    NaN  NaN
           B    NaN  NaN
           C    NaN  NaN
           D    NaN  NaN"><pre class="notranslate"><span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[<span class="pl-s">'2014-01-01'</span>, <span class="pl-s">'Col1'</span>] <span class="pl-c1">+=</span> <span class="pl-c1">1</span>
<span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[<span class="pl-s">'2014-02-01'</span>, <span class="pl-s">'Col2'</span>] <span class="pl-c1">=</span> <span class="pl-s1">df</span>.<span class="pl-s1">loc</span>[<span class="pl-s">'2014-01-01'</span>, <span class="pl-s">'Col2'</span>]

<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">df</span>
               <span class="pl-v">Col1</span> <span class="pl-v">Col2</span>
<span class="pl-s1">ts</span>         <span class="pl-s1">cat</span>          
<span class="pl-c1">2014</span><span class="pl-c1">-</span><span class="pl-c1">01</span><span class="pl-c1">-</span><span class="pl-c1">01</span> <span class="pl-v">A</span>    <span class="pl-v">NaN</span>    <span class="pl-c1">1</span>
           <span class="pl-v">B</span>    <span class="pl-v">NaN</span>    <span class="pl-c1">2</span>
           <span class="pl-v">C</span>    <span class="pl-v">NaN</span>    <span class="pl-c1">3</span>
           <span class="pl-v">D</span>    <span class="pl-v">NaN</span>    <span class="pl-c1">4</span>
<span class="pl-c1">2014</span><span class="pl-c1">-</span><span class="pl-c1">02</span><span class="pl-c1">-</span><span class="pl-c1">01</span> <span class="pl-v">A</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span>
           <span class="pl-v">B</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span>
           <span class="pl-v">C</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span>
           <span class="pl-v">D</span>    <span class="pl-v">NaN</span>  <span class="pl-v">NaN</span></pre></div>
<p dir="auto">It doesn't seem to be a "setting a value on a copy" issue. Instead, Pandas is writing the NaNs.</p>
<p dir="auto">My current workaround is to unstack each column into a DataFrame with simple indexes. This works, but I have lots of columns to work with. One dataframe is much easier to work with than a pile of dataframes.</p>
<p dir="auto">The computations for each month depend on the values computed in the previous month, hence why it can't be done fully vectorized on an entire column.</p>