partial update to dataframe with Multi-index index with integer labels

Question

I am using multi index dataframe with mixed index labels i.e. first level contains string labels, 2nd and 3rd level are integer labels and 4th level labels are dates. Dataframe looks like below (master_df)

                                           X1   X2  X3
bucket       Start    Stop       Date           
B1             1       1         1/3/2000   2   2   3
                                 1/4/2000   4   3   3
B1             1       2         1/3/2000   4   2   3
                                 1/4/2000   6   2   2

I want to take out sub_df as master_df.ix['B1',1,2,:], do some operations on sub_df and store it back into master_df at the same location. I am able to take out sub_df using various methods, but when comes to storing it back, all the options I have tried dont seem to be working. I guess this issue is related to having 'Integer' labels(at 2nd and 3rd lavel - start-stop). I have tried below options/methods without any success

    sub_df = master_df.ix['B1'].ix[1].ix[2]

    #do some operations on sub_df

    master_df.xs('B1').xs(1).xs(2).update(sub_df)

    master_df.ix['B1'].ix[1].ix[2].update(sub_df)

    merge(master_df.ix['B1',1,2,:],sub_df)

none of the above operations reflect changes in master_df (i dont get any error messages either.)

Can you suggest proper way to do it?

Update :

sub_df and master_df do not have same index as operations on sub_df expect date index only. sub_df look like below.

          X1   X2  X3
Date           
1/3/2000   2   2   3
1/4/2000   4   3   3

If I try

sub_df = master_df[master_df.index.get_loc(('B1', 1, 2))]

I get following error -

TypeError: unhashable type: 'numpy.ndarray'

Main purpose here is to operate on only small chunk of master_df and store those results back at original location in master_df. I tried using update method, however any other alternative to achive this purpose will do.

could you check if `sub_df` has the same index as `master_df`? because the [doc](http://pandas.pydata.org/pandas-docs/stable/merging.html#merging-together-values-within-series-or-dataframe-columns) mentions something like "two like-indexed (or similarly indexed)" DataFrames or Series for the `update` method. — herrfz, Mar 04 '13 at 12:17

Jeff · Answer 1 · 2013-03-05T00:49:38.077

This doesn't exactly solve your question, but I this might provide some inspiration

Here's a way to set the values directly

In [75]: df
Out[75]: 
  bucket  start  stop                date  x1  x2  x3
0     B1      1     1 2000-10-03 00:00:00   2   2   3
1     B1      1     1 2000-01-04 00:00:00   4   3   3
2     B1      1     2 2000-01-03 00:00:00   4   2   3
3     B1      1     2 2000-01-04 00:00:00   6   2   2

In [76]: df2 = df.set_index(['bucket','start','stop'])

In [77]: df2
Out[77]: 
                                 date  x1  x2  x3
bucket start stop                                
B1     1     1    2000-10-03 00:00:00   2   2   3
             1    2000-01-04 00:00:00   4   3   3
             2    2000-01-03 00:00:00   4   2   3
             2    2000-01-04 00:00:00   6   2   2

In [78]: df2.ix[('B1',1,2)].ix[:,'x1'] = 5

In [79]: df2
Out[79]: 
                                 date  x1  x2  x3
bucket start stop                                
B1     1     1    2000-10-03 00:00:00   2   2   3
             1    2000-01-04 00:00:00   4   3   3
             2    2000-01-03 00:00:00   5   2   3
             2    2000-01-04 00:00:00   5   2   2

Here's another way, where you select out a series that has a multi-index, modify it, then assign it back (only works with series).

In [89]: df2.ix[:,'x1']
Out[89]: 
bucket  start  stop
B1      1      1       2
               1       4
               2       4
               2       6
Name: x1, dtype: int64

In [90]: new_s = df2.ix[:,'x1'].copy()

In [91]: new_s
Out[91]: 
bucket  start  stop
B1      1      1       2
               1       4
               2       4
               2       6
Name: x1, dtype: int64

# can also do a more complicated selctor than the 0th row
In [92]: new_s[0] = 5

In [93]: new_s
Out[93]: 
bucket  start  stop
B1      1      1       5
               1       4
               2       4
               2       6
Name: x1, dtype: int64

In [94]: df2.ix[:,'x1'] = new_s

In [95]: df2
Out[95]: 
                                 date  x1  x2  x3
bucket start stop                                
B1     1     1    2000-10-03 00:00:00   5   2   3
             1    2000-01-04 00:00:00   4   3   3
             2    2000-01-03 00:00:00   4   2   3
             2    2000-01-04 00:00:00   6   2   2

Here's what you can do in 0.11

# this is sessentially saying give me the first 2 rows (equivalent
# to selecting via complicated tuple)
In [107]: df2.iloc[0:2,:]
Out[107]: 
                                 date  x1  x2  x3
bucket start stop                                
B1     1     1    2000-10-03 00:00:00  10   2   3
             1    2000-01-04 00:00:00   4   3   3

In [108]: df2.iloc[0:2,:].loc[:,'x1']
Out[108]: 
bucket  start  stop
B1      1      1       10
               1        4
Name: x1, dtype: int64

In [109]: df2.iloc[0:2,:].loc[:,'x1'] = 5

In [110]: df2
Out[110]: 
                                 date  x1  x2  x3
bucket start stop                                
B1     1     1    2000-10-03 00:00:00   5   2   3
             1    2000-01-04 00:00:00   5   3   3
             2    2000-01-03 00:00:00   4   2   3
             2    2000-01-04 00:00:00   6   2   2

score 1 · Answer 2 · answered Mar 04 '13 at 12:26

1

It's important that sub_df has the same index as master_df.

One way to get the correct index is to use get_loc:

sub_df = df[df.index.get_loc(('B1', 1, 2))]
# operations not changing index
master_df.update(sub_df)

answered Mar 04 '13 at 12:26

Andy Hayden

359,921
101
625
535

score 0 · Answer 3 · answered Mar 05 '13 at 12:35

0

Thanks all for the help. At the end I switched to charater indexing from number indexing at level 2 and 3, and now things are working fine (this also helped in doing propering sorting on levels, which is i suppose made things more unambiguous.)

answered Mar 05 '13 at 12:35

user2131221

21
3

score 0 · Answer 4 · answered Mar 06 '13 at 20:34

For the example you give (selecting ('B1', 1, 2, ...)) you can use xs iso ix. In contrast to ix, xs can return a view on the data when using labels (more details on ix returning view/copy see docs). In the example below, colum X1 of sub_df is changes, which also impacts master_df.

In [48]: master_df
Out[48]: 
                              X1  X2  X3
bucket Start Stop Date                  
B1     1     1    2000-01-03   2   2   3
                  2000-01-04   4   3   3
             2    2000-01-03   4   2   3
                  2000-01-04   6   2   2

In [49]: sub_df = master_df.xs(['B1', 1, 2], copy=False)

In [50]: sub_df
Out[50]: 
            X1  X2  X3
Date                  
2000-01-03   4   2   3
2000-01-04   6   2   2

In [51]: sub_df.X1 -= 4

In [52]: sub_df
Out[52]: 
            X1  X2  X3
Date                  
2000-01-03   0   2   3
2000-01-04   2   2   2

In [53]: master_df
Out[53]: 
                              X1  X2  X3
bucket Start Stop Date                  
B1     1     1    2000-01-03   2   2   3
                  2000-01-04   4   3   3
             2    2000-01-03   0   2   3
                  2000-01-04   2   2   2

Note: copy is no longer a parameter for .xs – ZaxR Jul 21 '18 at 20:06 — ZaxR, Jul 21 '18 at 20:06

score 0 · Answer 5 · edited May 23 '17 at 12:03

0

In 0.13 parameter 'drop_level' has been added to 'xs' so that will solve your problem as all index levels will be present in sub_df:

sub_df = master_df.xs(['B1', 1, 2], level=['bucket','Start','Stop'], drop_level=False)

Now merge will work.

Have not tried but it should work.

ref: "Large data" work flows using pandas

edited May 23 '17 at 12:03

Community

1
1

answered Feb 21 '14 at 15:14

user1429976

19
1

partial update to dataframe with Multi-index index with integer labels

5 Answers5