CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
Niranjan Kumar
Niranjan Kumar

Reputation: 1518

pandas groupBy dataframe with original indexes from dataframe preserved

Input:

import pandas as pd
data = [['Delhi', 'A', 10], ['Delhi', 'B', 12], ['Delhi', 'C', 9], ['Delhi', 'D', 11], ['Mumbai', 'A', 21], ['Mumbai', 'B', 13], ['Mumbai', 'C', 19], ['Mumbai', 'D', 23]]
df = pd.DataFrame(data, columns = ['Name', 'Group', 'Val']) 
df

Out[4]: 
     Name Group  Val
0   Delhi     A   10
1   Delhi     B   12
2   Delhi     C    9
3   Delhi     D   11
4  Mumbai     A   21
5  Mumbai     B   13
6  Mumbai     C   19
7  Mumbai     D   23

I want to group the data, but with the indexes from original df preserved

Grouping code: As expected it resets indexes, but I want the index from df

df.groupby('Name')['Val'].max().reset_index()
Out[8]: 
     Name  Val
0   Delhi   12
1  Mumbai   23

Expected Output:

     Name    Val
1   Delhi    12  
7  Mumbai    23

Upvotes: 1

Views: 2164

Answers (2)

sammywemmy
sammywemmy

Reputation: 28659

Try this :

df.loc[df.groupby('Name').Val.idxmax(),['Name','Val']]

    Name    Val
1   Delhi   12
7   Mumbai  23

Upvotes: 4

Code Pope
Code Pope

Reputation: 5449

To get the original indices, you can first group by transforming. Here the code:

idx = df.groupby(['Name'])['Val'].transform(max) == df['Val']
df[idx]

Output:

    Name    Group   Val
1   Delhi   B       12
7   Mumbai  D       23

Upvotes: 2

Related Questions