Pandas Groupby ratios - percent of columns with a specific value

Question

I need some help with a transformation. I would like to extend my code to support a new aggregation method -> pct_value.

[The actual question follows this example used to illustrate what I am starting with]

Let me start with what I have.

My current code will take data representing a CSV, transform it into a list of dicts, where each dict result represents a unique combination (groupby) of columns. The dict contains (1) a calculated metric, (2) the metric name, (3) the columns and their values in that groupby combo.

here is an example:

I have a csv of data in the following structure which I load to a Dataframe:

data = [[id, col1, col2, col3], [1234, True, foo, 3.4], [1235, True, bar, 3.4], [234, False, bar, 1.0]]

df = pd.DataFrame(data[1:], columns=data[0])
df = df.replace({"True": True, "False": False})

I then perform the following transformation with these arguments

metric_name -> this is the arbitrary name of the calculated metric
aggregation_method -> method used to calculate the metric across a Group of data
column_to_aggregate -> Column used to calculate the metric based on the aggregation_method
tag_names -> List of other columns in the DataFrame on which to apply the metric

code:

results = []

if aggregation_method != "count":
   df[column_to_aggregate] = df[column_to_aggregate].apply(
                    lambda x: pd.to_numeric(x, errors="coerce")
                )

groupby_result = (df.groupby(tag_names[column_to_aggregate].aggregate(aggregation_method).fillna(0).to_dict())

for bucket, result in groupby_result.items():
    tags = list(zip(tag_names, bucket))
    results.append({"name": metric_name, "value": result, "tags": tags})

I get the following data structure

Inputs: 
* metric_name = Count of IDs
* aggregation_method = count
* column_to_aggregate = id
* tag_names = [col1, col3]

results in:

 [{'name': Count of IDs,
  'tags': [('col1', True),
           ('col3', 3.4)],
  'value': 2.0},
 {'name': Count of IDs,
  'tags': [('col1', 'False'),
           ('col3', 1.0)],
  'value': 1}]

Inputs: 
* metric_name = Total X
* aggregation_method = sum
* column_to_aggregate = col3
* tag_names = [col1, col2]

results in:

 [{'name': Total X,
  'tags': [('col1', True),
           ('col2', foo)],
  'value': 3.4},
 {'name': Total X,
  'tags': [('col1', True),
           ('col2', bar)],
  'value': 3.4}
 {'name': Total X,
  'tags': [('col1', False),
           ('col2', bar)],
  'value': 1.0}]

Actual Question:

I would like to extend this to support a percent of a columns with a specific value

Example:

metric: Percent of Foo
column: col2
method: pct_value
value: foo

I have control of the data and the caller, so I can make new arguments (eg, value). This is data-driven code, and all of the metrics, tag_names, aggregaton_method, etc are in a yaml file, so I can pass anything in. The caller will know what the data means, so someone will be looking for this specific result where it makes sense based on the input data.

So I am looking for output as follows:

 [{'name': Percent of Foo,
  'tags': [('col1', True),
           ('col3', 3.4)],
  'value': 0.5},
 {'name': Percent of Foo,
  'tags': [('col1', 'False'),
           ('col3', 1.0)],
  'value': 0.0}]

Can someone provide help with a good way of calculating these percentages per groupby and getting the same ending data structure

user_4451439 · Answer

I was able to do it as follows

thoughts?

if aggregation_method == "ratio":
   expected_val = metric['value']
   has_value = "has_expected_value_" + column_to_aggregate
   df[has_value] = np.where(df[column_to_aggregate] == expected_val, True, False)

  groupby_result = (
    df.groupby(tag_names)[has_value].aggregate(lambda x: x.sum() / x.count()).fillna(0).to_dict()
  )

And if groupby_result only has one item in tag_names, I needed to tuplize the dict key

for bucket, result in groupby_result.items():
  if isinstance(bucket, str):
    bucket = (bucket,)

  tags = list(zip(tag_names, bucket))
  results.append({"name": metric_name, "value": result, "tags": tags})

Pandas Groupby ratios - percent of columns with a specific value

1 Answers