CODEWITHSUNDEEP
pythonpandascsv
ArchivistG
ArchivistG

Reputation: 168

Using pandas to find the max value for specific rows

I've got a csv that looks like this (there are more years):

year,title_field,value
2009,Total Housing Units,39499
2009,Vacant Housing Units,3583
2009,Occupied Housing Units,35916
2008,Total Housing Units,41194
2008,Vacant Housing Units,4483
2008,Occupied Housing Units,36711
2009,Owner Occupied,18057
2009,Renter Occupied,17859
2008,Owner Occupied,17340
2008,Renter Occupied,19371
2009,Median Gross Rent,769
2008,Median Gross Rent,768

I need to find the max value of all Vacant Housing Units.

So far, I've got this:

import pandas as pd

df = pd.read_csv("denton_housing.csv", names=("year", "title_field", "value"))

inds = df.groupby(['title_field'])['value'].transform(max) == df['value']
df = df[inds]
df.reset_index(drop=True, inplace=True)
print(df)

That code gives me this:

   year             title_field  value
0  year             title_field  value
1  2014     Total Housing Units  49109
2  2014  Occupied Housing Units  46295
3  2008    Vacant Housing Units   4483
4  2014          Owner Occupied  21427
5  2014         Renter Occupied  24868
6  2014       Median Gross Rent    905

I only need it to output:

2008    Vacant Housing Units    4483

Upvotes: 0

Views: 184

Answers (2)

BENY
BENY

Reputation: 323276

I think you need idxmax

df.loc[[df.groupby(['title_field'])['value'].idxmax().loc['Vacant Housing Units']]]
Out[92]: 
   year           title_field  value
4  2008  Vacant Housing Units   4483

Upvotes: 1

Allen Qin
Allen Qin

Reputation: 19947

You can first filter the Vacant Housing Units records, sort them and take the max.

df.loc[df.title_field.eq('Vacant Housing Units')].sort_values(by='value').tail(1)
Out[96]: 
   year           title_field  value
4  2008  Vacant Housing Units   4483

Upvotes: 0

Related Questions