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
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.