Set Multi-Index DataFrame values for each inner DataFrame

Question

I have a (very) large multi-indexed dataframe with a single boolean column. for example:

bool_arr = np.random.randn(30)<0
df = pd.concat(3*[pd.DataFrame(np.random.randn(10, 3), columns=['A','B','C'])],
           keys=np.array(['one', 'two', 'three']))
df['bool'] = bool_arr
df.index.rename(['Ind1', 'Ind2'], inplace=True)

I'm trying to set the boolean column to False on the 2 first & 2 last indices of each inner dataframe, but only if the 3rd (or 3rd to last) isn't True. Meaning, I want the first and last 3 boolean entries to be the same.

I can do this by iterating over each index-level, extracting the inner dataframes one by one and resetting the relevant values, then plugging the new Series back to a copy of the original dataframe. But this is very wasteful in both time & memory.
Is there a faster way of doing this?
(I should add that in my example all inner dataframes are of the same length, but that's not necessarily the case for me)

Ben.T · Accepted Answer · 2020-04-27 11:46:02Z

1

you can groupby.transform the 'bool' column to get the third value with nth, then get the intersection with the index of the first two elements with head (last 2 elements tail) per group as well. Then, you can loc the union of index to be set to False:

# used per group action several times
gr = df.groupby(level=0)

# get the third value per group
s1 = gr['bool'].transform('nth',2)
# intersection of index with False at 3rd position per group 
# and index of first 2 rows per group
index_head = df.index[~s1].intersection(gr.head(2).index)

# get the last third value per group
s2 = gr['bool'].transform('nth', -3) #note -3 and not -2
# same idea but with tail
index_tail = df.index[~s2].intersection(gr.tail(2).index)

# loc the union of all the index to change
df.loc[index_head.union(index_tail), 'bool'] = False

edited Apr 27, 2020 at 11:46

answered Apr 25, 2020 at 21:11

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

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

1 Comment

Jon Nir Over a year ago

Thanks! I'm guessing that for index_tail you meant to write gr.tail().

Collectives™ on Stack Overflow

Set Multi-Index DataFrame values for each inner DataFrame

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related