Search code examples
pythonpandasmulti-index

Modify the last row of each group in pandas MultiIndex DataFrame


I have a sample multi index dataframe below (actual much larger). I want to set the last row of each level = 0 index in the column 'col' to 100. Dataframe like this

mux = pd.MultiIndex.from_arrays([
list('aaabbbcccddd'),
list('tuvwacdjpkqz')],
names=['level 0', 'level 1'])
df = pd.DataFrame({'col': np.arange(len(mux))}, mux)
df

So the 2,5,8 and 11 wold all be replaced with 100. I have tried these solution but I dont know how to set the value properly. I can get the boolean series and the dataframe of the last rows but cant seem to figure out how to set them to the new value. What I have tried below

df.groupby(level=0).tail(1).col = 100
df.groupby(level=0).tail(1).col
df.iloc[-1, df.columns.get_loc('col')] = 100

The last line of code was from a previous post but it only sets the last row of df, not at the index level


Solution

  • You were close, use loc with the index of your result and assign:

    df.loc[df.groupby(level=0).tail(1).index, 'col'] = 100
    df
    
                     col
    level 0 level 1     
    a       t          0
            u          1
            v        100
    b       w          3
            a          4
            c        100
    c       d          6
            j          7
            p        100
    d       k          9
            q         10
            z        100
    

    This is your groupby result:

    df.groupby(level=0).tail(1)
     
                     col
    level 0 level 1     
    a       v          2
    b       c          5
    c       p          8
    d       z         11
    

    Get the indices of the rows you want to change with .index:

    df.groupby(level=0).tail(1).index
    
    MultiIndex([('a', 'v'),
                ('b', 'c'),
                ('c', 'p'),
                ('d', 'z')],
               names=['level 0', 'level 1'])
    

    You have index labels, so you will need loc (not iloc) for the final assignment step.