Replacing a value in a pandas multiindex dataframe

Question

I'd like to change a value in my pandas dataframe, and I think I have misunderstood how the indexing works.

import pandas as pd idx = pd.IndexSlice df.loc[idx[(0, 2006.0, '01019_13055_01073_01009_01055')],idx[('moment_25','P517')]]

I get the output

Out[376]: 
moment_25  P517    0.665873
Name: (0, 2006.0, 01019_13055_01073_01009_01055), dtype: float64

I'd like to change the value 0.665873 in df to 1. I've tried

df.ix[idx[(0, 2006.0,'01019_13055_01073_01009_01055')],idx[('moment_25','P517')]]=1

but I get the error

Exception: cannot handle a non-unique multi-index!

I've tried to duplicate the problem with a sample dataframe to no avail.

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(3, 8), index=['A', 'B', 'C'], columns=index)
df.loc[idx['A'],idx[('baz','one')]]

Out[391]: -0.17935592549360641

I think the issue is that I'm getting a series for my output when I'm using my actual data, but I get a float when I use the practice data. Why am I getting that series instead of the float 0.665873?

Post a sample of your dataframe? – lanery Mar 03 '17 at 16:25 — lanery, Mar 03 '17 at 16:25

score 1 · Answer 1 · edited May 23 '17 at 12:09

use set_value to change values in dataframes. Example below:

import pandas as pd
import numpy as np
dfp = pd.DataFrame({'A' : [np.NaN,np.NaN,3,4,5,5,3,1,5,np.NaN], 
                    'B' : [1,0,3,5,0,0,np.NaN,9,0,0], 
                    'C' : ['Pharmacy of IDAHO','Access medicare arkansas','NJ Pharmacy','Idaho Rx','CA Herbals','Florida Pharma','AK RX','Ohio Drugs','PA Rx','USA Pharma'], 
                    'D' : [123456,123456,1234567,12345678,12345,12345,12345678,123456789,1234567,np.NaN],
                    'E' : ['Assign','Unassign','Assign','Ugly','Appreciate','Undo','Assign','Unicycle','Assign','Unicorn',]})
print(dfp)


  A    B                         C            D           E
0  NaN  1.0         Pharmacy of IDAHO     123456.0      Assign
1  NaN  0.0  Access medicare arkansas     123456.0    Unassign
2  3.0  3.0               NJ Pharmacy    1234567.0      Assign
3  4.0  5.0                  Idaho Rx   12345678.0        Ugly
4  5.0  0.0                CA Herbals      12345.0  Appreciate
5  5.0  0.0            Florida Pharma      12345.0        Undo
6  3.0  NaN                     AK RX   12345678.0      Assign
7  1.0  9.0                Ohio Drugs  123456789.0    Unicycle
8  5.0  0.0                     PA Rx    1234567.0      Assign
9  NaN  0.0                USA Pharma          NaN     Unicorn
   #                      ^^Check HEERE^^

the change and output:

dfp.set_value(9, 'C', 10)
print(dfp)

     A    B                         C            D           E
0  NaN  1.0         Pharmacy of IDAHO     123456.0      Assign
1  NaN  0.0  Access medicare arkansas     123456.0    Unassign
2  3.0  3.0               NJ Pharmacy    1234567.0      Assign
3  4.0  5.0                  Idaho Rx   12345678.0        Ugly
4  5.0  0.0                CA Herbals      12345.0  Appreciate
5  5.0  0.0            Florida Pharma      12345.0        Undo
6  3.0  NaN                     AK RX   12345678.0      Assign
7  1.0  9.0                Ohio Drugs  123456789.0    Unicycle
8  5.0  0.0                     PA Rx    1234567.0      Assign
9  NaN  0.0                        10          NaN     Unicorn
#                             ^^The CHANGE^^

If you are specifically asking about indexing, check here

Method using link above:

dfp.ix[0, 'C'] = 'x'
#                         vv Check Below vv
     A    B                         C            D           E
0  NaN  1.0                         x     123456.0      Assign
1  NaN  0.0  Access medicare arkansas     123456.0    Unassign
2  3.0  3.0               NJ Pharmacy    1234567.0      Assign
3  4.0  5.0                  Idaho Rx   12345678.0        Ugly
4  5.0  0.0                CA Herbals      12345.0  Appreciate
5  5.0  0.0            Florida Pharma      12345.0        Undo
6  3.0  NaN                     AK RX   12345678.0      Assign
7  1.0  9.0                Ohio Drugs  123456789.0    Unicycle
8  5.0  0.0                     PA Rx    1234567.0      Assign
9  NaN  0.0                        10          NaN     Unicorn

Thanks for the response, but I'm not sure this answers my question. How is your second suggestion dfp.ix[0, 'C'] = 'x' different from the code above that gives me the non-unique index error? — Reid, Mar 03 '17 at 21:50

Replacing a value in a pandas multiindex dataframe

1 Answers1