Consider we have the following dataframe
myDF = DataFrame(np.random.randn(4,2), index= [[1,1,2,2],['Mon','Tue','Mon','Tue']])
myDF
0 1
1 Mon -0.910930 1.592856
Tue -0.167228 -0.763317
2 Mon -0.926121 -0.718729
Tue 0.372288 -0.417337
If i want change the values of the first column for all rows in index 1, i try doing this:
myDF.ix[1,:][0] = 99
But that doesn't work and returns the same DataFrame unchanged. What am I missing. Thank you
Recent versions of pandas give a warning when you try something like this. For example, on version 0.13.1, you'd get this:
In [4]: myDF.ix[1,:][0] = 99
SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_index,col_indexer] = value instead
What you have done is called chained assignment, and it fails due to subtleties in the internal workings of numpy, on which pandas depends.
Your situation is more complicated that the one that general warning is addressing because you have a MultiIndex. To select all rows with the label 1 in the outer level and the column label 0, use .loc[1, 0]. (Also see this answer.)
In [5]: myDF.loc[1, 0] = 99
In [6]: myDF
Out[6]:
0 1
1 Mon 99.000000 1.609539
Tue 99.000000 1.464771
2 Mon -0.819186 -1.122967
Tue -0.545171 0.475277
I believe we can have true flexibility by using the following:
index = [idx for idx, vals in enumerate(myDF.index.values) if vals[1] in ['Mon','Wed'] and vals[0] in [2,3,4]]
colums = [0,1]
myDF.iloc[index, columns] = 99
creating the index from the for loop isn't the most efficient way so one can create a dictionary where the keys are multiIndex tuples and values are the indexes.
This way we can specify which values in both levels of index we want to change. .xs() does something similar but you can't change values through that function.
If there is a simpler way, I would be really interested in finding it out..