Have a dataframe with 15 columns, and am trying to use groupby to find the maximum value of one of those columns.
This shows what I've been doing and the output. I am getting the maximum value of item_number_revision in each item_number_start I would like to also be able to show all of my other existing columns in the original dataframe:
If you want to update item_number_revision to contain max by group, you can do this:
data1 = df.set_index('item_number_start').assign(item_number_revision=df.groupby('item_number_start')['item_number_revision'].max()).reset_index()
Input:
item_number_start item_number_revision other_column
0 80-0010 1 a
1 80-0011 2 b
2 80-0012 3 c
3 80-0010 4 d
4 80-0011 5 e
5 80-0012 6 f
6 80-0010 7 g
7 80-0011 8 h
8 80-0012 9 i
9 80-0010 10 j
10 80-0011 11 k
11 80-0012 12 l
Output:
item_number_start item_number_revision other_column
0 80-0010 10 a
1 80-0011 11 b
2 80-0012 12 c
3 80-0010 10 d
4 80-0011 11 e
5 80-0012 12 f
6 80-0010 10 g
7 80-0011 11 h
8 80-0012 12 i
9 80-0010 10 j
10 80-0011 11 k
Alternatively, if you want to preserve the original column and add a new column containing the max, you can do this:
data1 = df.set_index('item_number_start').assign(item_number_revision_max=df.groupby('item_number_start')['item_number_revision'].max()).reset_index()
Output:
item_number_start item_number_revision other_column item_number_revision_max
0 80-0010 1 a 10
1 80-0011 2 b 11
2 80-0012 3 c 12
3 80-0010 4 d 10
4 80-0011 5 e 11
5 80-0012 6 f 12
6 80-0010 7 g 10
7 80-0011 8 h 11
8 80-0012 9 i 12
9 80-0010 10 j 10
10 80-0011 11 k 11
11 80-0012 12 l 12
In the examples above, we use set_index() to temporarily make the original DataFrame use an index that matches that of the groupby()...max() Series, then we use assign() to either overwrite the column we took the max of, or add a new column, where each row is assigned the max for its group. In the end, we use reset_index() to restore the column we temporarily set as the index.
UPDATE:
To delete all rows except those with item_number_revision equal to item_number_revision_max, we can do this:
data1 = (
df.join(
df.groupby('item_number_start')['item_number_revision'].max()
.to_frame().set_index('item_number_revision', append=True),
on=['item_number_start', 'item_number_revision'], how='right')
)
Output:
item_number_start item_number_revision other_column
9 80-0010 10 j
10 80-0011 11 k
11 80-0012 12 l