How to properly make an assignment to a slice of a multiindexed dataframe in pandas?

Question

I am running Pandas 0.20.3 with Python 3.5.3 on macOS.

I have a multiindexed dataframe similar to the following df:

import pandas as pd
import numpy as np

refs = ['A', 'B']
dates = pd.date_range(start='2018-01-01', end='2018-12-31')

df = pd.DataFrame({'ref': np.repeat(refs, len(dates)),
                  'date': np.tile(dates, len(refs)),
                  'value': np.random.randn(len(dates) * len(refs))})
df.set_index(["ref", "date"], inplace=True)

I want to modify the dataframe and set some values to 0. Say where ref is equal to 'A' and where date is before 2018-01-15.

I am using the following:

df.loc["A"].loc[df.loc["A"].index < pd.to_datetime('2018-01-15')] = 0

I do not get any SettingWithCopyWarningand the dataframe is modified correctly on my mac. However when I run this code on a Windows environment with the same pandas version, the dataframe is not modified.

Hence my question: Is the above code incorrect? If not, how to properly make the assignment I need?

score 1 · Accepted Answer · answered Jul 10 '18 at 12:17

I think need chain 2 boolean masks with select values of levels of MultiIndex by get_level_values:

m1 = df.index.get_level_values(0) == 'A'
m2 = df.index.get_level_values(1) < '2018-01-15'

df.loc[m1 & m2, 'value'] = 0

print (df.head(20))
                   value
ref date                
A   2018-01-01  0.000000
    2018-01-02  0.000000
    2018-01-03  0.000000
    2018-01-04  0.000000
    2018-01-05  0.000000
    2018-01-06  0.000000
    2018-01-07  0.000000
    2018-01-08  0.000000
    2018-01-09  0.000000
    2018-01-10  0.000000
    2018-01-11  0.000000
    2018-01-12  0.000000
    2018-01-13  0.000000
    2018-01-14  0.000000
    2018-01-15 -0.701757
    2018-01-16 -0.160638
    2018-01-17 -0.226917
    2018-01-18 -0.431952
    2018-01-19 -0.339794
    2018-01-20 -0.050133

How to properly make an assignment to a slice of a multiindexed dataframe in pandas?

1 Answers1