CODEWITHSUNDEEP
pythonnumpypandasdataframe
Gioelelm
Gioelelm

Reputation: 2775

Pandas, how to filter a df to get unique entries?

I have a dataframe like this:

ID  type value
1   A    8
2   A    5
3   B    11
4   C    12
5   D    1
6   D    22
7   D    13

I want to filter the dataframe so that I have a unique occurrence of "type" attrybute (e.g. A appears only once), and if there are more rows that have the same value for "type" I want to choose the one with higher value. I want to get something like:

ID  type value
1   A    8
3   B    11
4   C    12
6   D    22

How do I do this with pandas?

Upvotes: 10

Views: 22387

Answers (4)

mkln
mkln

Reputation: 14953

one way is to sort the dataframe and then take the first after a groupby.

# first way
sorted = df.sort_values(['type', 'value'], ascending = [True, False])

first = sorted.groupby('type').first().reset_index()

another way does not necessarily take only the first one, so potentially it would keep all IDs corresponding to the same maximum (and not take just 1 of them)

# second way
grouped = df.groupby('type').agg({'value': max}).reset_index()
grouped = grouped.set_index(['type','value'])

second = grouped.join(df.set_index(['type', 'value']))

example:

data

ID  type    value
1   A   8
2   A   5
3   B   11
4   C   12
5   D   1
6   D   22
7   D   13
8   D   22

first method results in

type  ID  value
A   1      8
B   3     11
C   4     12
D   6     22

second method keeps ID=8

            ID
type value    
A    8       1
B    11      3
C    12      4
D    22      6
     22      8

(you can reset_index() again here if you don't like the multiindex)

Upvotes: 8

legale
legale

Reputation: 702

I prefer my way. Because groupby will create new df. You will get unique values. But tecnically this will not filter your df, this will create new one. My way will keep your indexes untouched, you will get the same df but without duplicates.

df = df.sort_values('value', ascending=False)
# this will return unique by column 'type' rows indexes
idx = df['type'].drop_duplicates().index
#this will return filtered df
df.loc[idx,:]

Upvotes: 1

vesszabo
vesszabo

Reputation: 441

df[['type', 'value']].drop_duplicates(subset=['type'])

This works generally, if you would have more columns, you can select the interested columns, in our case we chose all, 'type', 'value'.

Upvotes: 8

Tom Ron
Tom Ron

Reputation: 6181

Use groupby "type" and grab only the first object - df.groupby("type").first()

Upvotes: 2

Related Questions