Replace column values in pandas multiindexed dataframe

Question

I want to make a conditional replacement based on the first index value in my pandas dataframe. If I have a dataframe such as:

from pandas import *
arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
          ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]

tuples = zip(*arrays)

index = MultiIndex.from_tuples(tuples, names=['first','second'])
data = DataFrame(randn(8,2),index=index,columns=['c1','c2'])

I think I should be able to replace value in column via:

data.ix['bar']['c1'] = -999

But this returns the original dataframe, unchanged. Can anyone explain how this should be done and why my current method does not work?

@AndyHayden - True enough, and I likely would use NaN. I just chose -999 here for the example. — tnknepp
– tnknepp, Commented Dec 11, 2013 at 11:35

DSM · Accepted Answer · 2013-12-10 21:55:14Z

3

You could use .loc:

>>> data.loc["bar", "c1"]
second
one       0.369406
two       0.691445
Name: c1, dtype: float64
>>> data.loc["bar", "c1"] = -999
>>> data
                      c1        c2
first second                      
bar   one    -999.000000  0.302155
      two    -999.000000 -1.260789
baz   one       0.103455 -0.556967
      two      -1.600112  0.491787
foo   one       0.779901 -0.885565
      two      -1.041722 -0.570302
qux   one      -1.152093 -1.767028
      two      -0.364624 -0.302240

[8 rows x 2 columns]

answered Dec 10, 2013 at 21:55

DSM

355k67 gold badges606 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dmvianna · Accepted Answer · 2013-12-10 21:58:45Z

1

You used the wrong notation. Try

data.ix['bar','c1'] = -999

The first element of the index refers to rows, and the second to columns. See the Docs.

answered Dec 10, 2013 at 21:58

dmvianna

15.8k18 gold badges81 silver badges110 bronze badges

1 Comment

tnknepp Over a year ago

Thanks. I favor .ix since it is more general than .loc. Thanks again

behzad.nouri · Accepted Answer · 2013-12-10 21:52:40Z

1

maybe this:

data.c1[ 'bar' ] = -999

or

data[ 'c1' ][ 'bar' ] = -999

my guess is that in here data.ix['bar']['c1'] is returning a copy as opposed to view. see this

edited Dec 10, 2013 at 21:52

answered Dec 10, 2013 at 21:45

behzad.nouri

78.5k18 gold badges130 silver badges127 bronze badges

1 Comment

tnknepp Over a year ago

Interesting, thanks for the reference. I never knew about that.

Collectives™ on Stack Overflow

Replace column values in pandas multiindexed dataframe

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related