jojo12 - 9 months ago 46

Python Question

I hope I can find help for my question. I am searching for a solution for the following problem:

I have a dataFrame like:

`Sp Mt Value count`

0 MM1 S1 a **3**

1 MM1 S1 n 2

2 MM1 S3 cb 5

3 MM2 S3 mk **8**

4 MM2 S4 bg **10**

5 MM2 S4 dgd 1

6 MM4 S2 rd 2

7 MM4 S2 cb 2

8 MM4 S2 uyi **7**

My objective is to get the result rows whose count is max between the groups, like :

`0 MM1 S1 a **3**`

1 3 MM2 S3 mk **8**

4 MM2 S4 bg **10**

8 MM4 S2 uyi **7**

Somebody knows how can I do it in pandas or in python?

I didn't give more details for my question. For my problem, I want to group by ['Sp','Mt']. Let take a second example like this :

`Sp Mt Value count`

4 MM2 S4 bg 10

5 MM2 S4 dgd 1

6 MM4 S2 rd 2

7 MM4 S2 cb 8

8 MM4 S2 uyi 8

For the above example, I want to get ALL the rows where count equals max in each group e.g :

`MM4 S4 bg 10`

MM4 S2 cb 8

MM4 S2 uyi 8

Answer Source

```
In [1]: df
Out[1]:
Sp Mt Value count
0 MM1 S1 a 3
1 MM1 S1 n 2
2 MM1 S3 cb 5
3 MM2 S3 mk 8
4 MM2 S4 bg 10
5 MM2 S4 dgd 1
6 MM4 S2 rd 2
7 MM4 S2 cb 2
8 MM4 S2 uyi 7
In [2]: df.groupby(['Mt'], sort=False)['count'].max()
Out[2]:
Mt
S1 3
S3 8
S4 10
S2 7
Name: count
```

To get the indices of the original DF you can do:

```
In [3]: idx = df.groupby(['Mt'])['count'].transform(max) == df['count']
In [4]: df[idx]
Out[4]:
Sp Mt Value count
0 MM1 S1 a 3
3 MM2 S3 mk 8
4 MM2 S4 bg 10
8 MM4 S2 uyi 7
```

Note that if you have multiple max values per group, all will be returned.

**Update**

On a hail mary chance that this is what the OP is requesting:

```
In [5]: df['count_max'] = df.groupby(['Mt'])['count'].transform(max)
In [6]: df
Out[6]:
Sp Mt Value count count_max
0 MM1 S1 a 3 3
1 MM1 S1 n 2 3
2 MM1 S3 cb 5 8
3 MM2 S3 mk 8 8
4 MM2 S4 bg 10 10
5 MM2 S4 dgd 1 10
6 MM4 S2 rd 2 7
7 MM4 S2 cb 2 7
8 MM4 S2 uyi 7 7
```