|
| 1 | +import pandas as pd |
| 2 | + |
| 3 | + |
| 4 | +def check_nat_val(df: pd.DataFrame, breakdown_col: str = "Breakdown", |
| 5 | + measure_col: str = "Measure", value_col: str = |
| 6 | + "Value_Unsuppressed", nat_val: str = "National") -> bool: |
| 7 | + """ |
| 8 | + Check national value less than or equal to sum of breakdowns. |
| 9 | +
|
| 10 | + This function checks that the national value is less than or equal to the |
| 11 | + sum of each organisation level breakdown. |
| 12 | + This function does not apply to values which are averages. |
| 13 | + This function does not apply to values which are percentages calculated |
| 14 | + from the numerator and denominator. |
| 15 | +
|
| 16 | + Parameters |
| 17 | + ---------- |
| 18 | + df : pandas.DataFrame |
| 19 | + DataFrame of data to check. |
| 20 | + breakdown_col : str, default = "Breakdown" |
| 21 | + Column name for the breakdown level. |
| 22 | + measure_col : str, default = "Measure" |
| 23 | + Column name for measures |
| 24 | + value_col : str, default = "Value_Unsuppressed" |
| 25 | + Column name for values |
| 26 | + nat_val : str, default = "National" |
| 27 | + Value in breakdown column denoting national values |
| 28 | + Returns |
| 29 | + ------- |
| 30 | + bool |
| 31 | + Whether the checks have been passed. |
| 32 | +
|
| 33 | + Examples |
| 34 | + -------- |
| 35 | + >>> check_nat_val( |
| 36 | + ... df = pd.DataFrame({ |
| 37 | + ... "Breakdown" : ['National', 'CCG', 'CCG', 'Provider', 'Provider', |
| 38 | + 'National' ,'CCG', 'CCG', 'Provider', 'Provider','National' ,'CCG', 'CCG', |
| 39 | + 'Provider', 'Provider',], |
| 40 | + ... "Measure" : ['m1', 'm1', 'm1', 'm1', 'm1', 'm2', 'm2', 'm2', 'm2', |
| 41 | + 'm2', 'm3', 'm3', 'm3', 'm3', 'm3',], |
| 42 | + ... "Value_Unsuppressed" : [9, 4, 5, 3, 6, 11, 2, 9, 7, 4, 9, 5, 4, 6, |
| 43 | + 3], |
| 44 | + ... }), |
| 45 | + ... breakdown_col = "Breakdown", |
| 46 | + ... measure_col = "Measure", |
| 47 | + ... value_col = "Value_Unsuppressed", |
| 48 | + ... nat_val = "National", |
| 49 | + ... ) |
| 50 | + True |
| 51 | + >>> check_nat_val( |
| 52 | + ... df = pd.DataFrame({ |
| 53 | + ... "Breakdown" : ['National', 'CCG', 'CCG', 'Provider', 'Provider', |
| 54 | + 'National' ,'CCG', 'CCG', 'Provider', 'Provider','National' ,'CCG', 'CCG', |
| 55 | + 'Provider', 'Provider',], |
| 56 | + ... "Measure" : ['m1', 'm1', 'm1', 'm1', 'm1', 'm2', 'm2', 'm2', 'm2', |
| 57 | + 'm2', 'm3', 'm3', 'm3', 'm3', 'm3',], |
| 58 | + ... "Value_Unsuppressed" : [18, 4, 5, 3, 6, 11, 2, 9, 7, 4, 9, 5, 4, 6, |
| 59 | + 3], |
| 60 | + ... }), |
| 61 | + ... breakdown_col = "Breakdown", |
| 62 | + ... measure_col = "Measure", |
| 63 | + ... value_col = "Value_Unsuppressed", |
| 64 | + ... nat_val = "National", |
| 65 | + ... ) |
| 66 | + False |
| 67 | + """ |
| 68 | + |
| 69 | + if not isinstance(breakdown_col, str) or not isinstance(measure_col, str)\ |
| 70 | + or not isinstance(value_col, str): |
| 71 | + raise ValueError("Please input strings for column indexes.") |
| 72 | + if not isinstance(nat_val, str): |
| 73 | + raise ValueError("Please input strings for value indexes.") |
| 74 | + if breakdown_col not in df.columns or measure_col not in df.columns or\ |
| 75 | + value_col not in df.columns: |
| 76 | + raise KeyError("Check column names correspond to the DataFrame.") |
| 77 | +# aggregate values by measure and breakdown |
| 78 | + grouped = df.groupby([measure_col, breakdown_col]).agg({value_col: sum}) |
| 79 | + .reset_index() |
| 80 | + national = grouped.loc[grouped[breakdown_col] == nat_val].reset_index() |
| 81 | + non_national = grouped.loc[grouped[breakdown_col] != nat_val].reset_index() |
| 82 | +# check values are less than or equal to national value for each measure |
| 83 | + join = pd.merge(non_national, national, left_on=measure_col, |
| 84 | + right_on=measure_col, how='left') |
| 85 | + left = value_col + '_x' |
| 86 | + right = value_col + '_y' |
| 87 | + join['Check'] = join[right] <= join[left] |
| 88 | + result = all(join['Check'] |
| 89 | + return result |
0 commit comments