Pandas query- select tuples by unique column value and aggregate

Question

I have two columns in a Pandas df that I would like to operate on. First, I would like to remove non-numeric values such as "High" from the column "score" and cast the remaining values as int (all data was input as strings). Next I would like to sum "score" based on unique "measure_id". How can I perform these two functions?

The df is:

nationwide_measures = pd.read_sql_query("""select state,
          measure_id,
          measure_name,
          score
from timely_and_effective_care___hospital;""", conn)

My failed attempt is:

 nationwide_measures1 = nationwide_measures.to_numeric(nationwide_measures{:,'score'}, errors='coerce')

what do you want the "score" to be after removing "high"? Do you want to remove the entire row? — Bharath M Shetty
– Bharath M Shetty, Commented Jul 17, 2017 at 15:42
Yes, if the score value is non-numeric- the tuple should be ignored. — zsad512
– zsad512, Commented Jul 17, 2017 at 15:43

Bharath M Shetty · Accepted Answer · 2017-07-17 16:48:12Z

0

You can select all the nationwide_measure rows where the score value is numeric and I hope they are in the string format so convert them to int and then use groupby to aggregate the scores based on measure_id.

nationwide_measures1 = nationwide_measures[nationwide_measures['score'].str.isalpha() != True]
nationwide_measures1['score'] = pd.to_numeric(nationwide_measures1['score'])
score_sum = nationwide_measures1.groupby('measure_id')['score'].sum()

Hope this helps Update: If you want sum,mean,min,max,std you can use .agg i.e

import numpy as np
score_sum = nationwide_measures1.groupby('measure_id')['score'].agg([pd.np.sum,pd.np.min, pd.np.max, pd.np.mean, pd.np.std])

edited Jul 17, 2017 at 16:48

answered Jul 17, 2017 at 15:47

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

zsad512 Over a year ago

The only problem is that there are other string values that need to be removed as well. I need to amend your suggestion with something like "!= .isalpha()" Any thoughts?

zsad512 Over a year ago

"AttributeError: 'StringMethods' object has no attribute 'isaplha'"

zsad512 Over a year ago

That ran without a stack trace but the dataframe is still the same size.

zsad512 Over a year ago

This answer is not correct. Upon closer inspection, the score_sum values were a concatenation of the actual string values, not their sum. Your answer does not convert the 'score' values to int, which is what I need to do before I can apply the min,max,avg,stdev functions on the df

zsad512 Over a year ago

Yes, next I would like to compute the min, max, average, and stdev for each measure- can you help with that?

|

zsad512 · Accepted Answer · 2017-07-17 16:11:07Z

0

The answer to deleting tuples with non-numeric score values is:

nationwide_measures1 = nationwide_measures[nationwide_measures['score'].astype(str).str.isdigit()]

I found it here: Pandas select only numeric or integer field from dataframe

answered Jul 17, 2017 at 16:11

zsad512

8813 gold badges16 silver badges41 bronze badges

Collectives™ on Stack Overflow

Pandas query- select tuples by unique column value and aggregate

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related