Skip to content

Latest commit

 

History

History
251 lines (165 loc) · 10.7 KB

README.md

File metadata and controls

251 lines (165 loc) · 10.7 KB
Raw
description icon cover coverY
The steps taken to calculate a Parliamentary Attendance Rating (PAR) Score for ranking members of parliament (MPs) according to their attendance across portfolio committee meetings.
scale-balanced
0

PAR Score Methodology

The data we have

We have the following data for MP attendance at portfolio committee meetings throughout the year:

  • Column A = id — Unique number assigned to each MP (e.g., 1423)
  • Column B = member_name — Surname and initials of an MP (e.g., Hendricks, Mr MGE)
  • Column C = party_name — Political party an MP is representing (e.g., Al Jama-ah)
  • Column D = date_created — Date a portfolio committee meeting took place (e.g., 06/02/2024)
  • Column E = year — Year a portfolio committee meeting took place (e.g., 2024)
  • Column F = attendance — Attendance of an MP at a portfolio committee meeting (e.g., Absent)
  • Column G = committee_meeting — The Portfolio Committee meeting (e.g., International Relations)

What we are trying to achieve

We want to be able to rank MPs based on their attendance across all portfolio committee meetings throughout a year, taking into account the total number of portfolio committees they are subscribed to for that year. To do this, we need to extract the following from the data we have:

  1. No. of times an MP was Absent for meetings throughout a year.
  2. No. of times an MP was Absent with Apologies for meetings throughout a year.
  3. No. of times an MP Arrived Late for meetings throughout a year.
  4. No. of times an MP Arrived Late & Departed Early from meetings throughout a year.
  5. No. of times an MP Departed Early from meetings throughout a year.
  6. No. of times an MP was Present for meetings throughout a year.
  7. No. of Unique Portfolio Committees an MP is subscribed to throughout a year.
  8. Average no. of Unique Portfolio Committees per MP throughout a year.

In addition to extracting the data points above, we assign weightings to the different types of attendance, as follows:

  • 0.0 for Absent
  • 0.25 for Absent with Apologies
  • 0.75 for Arrived Late
  • 0.5 for Arrived Late & Departed Early
  • 0.75 for Departed Early
  • 1.0 for Present

Below are the steps for extracting each of the numbered bullet points above, and calculating a Parliamentary Attendance Rating (PAR) score for each MP in a given year.

NOTE: These steps apply to Google Sheets.

STEP 1 — Extracting the Absent count per MP

Assuming our first row is a header row, we use the following formula in cell H2:

{% hint style="info" %} =COUNTIFS(B:B, B2, E:E, E2, F:F, "Absent") {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column F = attendance

We drag this formula down Column H, and label it absent_count.

STEP 2 — Extracting the Absent with Apologies count per MP

Assuming our first row is a header row, we use the following formula in cell I2:

{% hint style="info" %} =COUNTIFS(B:B, B2, E:E, E2, F:F, "Absent with Apologies") {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column F = attendance

We drag this formula down Column I, and label it absent_with_apologies_count.

STEP 3 — Extracting the Arrived Late count per MP

Assuming our first row is a header row, we use the following formula in cell J2:

{% hint style="info" %} =COUNTIFS(B:B, B2, E:E, E2, F:F, "Arrived Late") {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column F = attendance

We drag this formula down Column J, and label it arrived_late_count.

STEP 4 — Extracting the Arrived Late & Departed Early count per MP

Assuming our first row is a header row, we use the following formula in cell K2:

{% hint style="info" %} =COUNTIFS(B:B, B2, E:E, E2, F:F, "Arrived Late & Departed Early") {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column F = attendance

We drag this formula down Column K, and label it arrived_late_departed_early_count.

STEP 5 — Extracting the Departed Early count per MP

Assuming our first row is a header row, we use the following formula in cell L2:

{% hint style="info" %} =COUNTIFS(B:B, B2, E:E, E2, F:F, "Departed Early") {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column F = attendance

We drag this formula down Column L, and label it departed_early_count.

STEP 6 — Extracting the Present count per MP

Assuming our first row is a header row, we use the following formula in cell M2:

{% hint style="info" %} =COUNTIFS(B:B, B2, E:E, E2, F:F, "Present") {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column F = attendance

We drag this formula down Column M, and label it present_count.

STEP 7 — Calculating a Weighted Attendance Score per MP

With the attendance data extracted, and the weightings assigned earlier, we can calculate a Weighted Attendance Score per MP, for a given year, using the following formula in cell N2:

{% hint style="info" %} =(H20)+(I20.25)+(J20.75)+(K20.5)+(L20.75)+(M21) {% endhint %}

Where:

  • Column H = absent_count
  • Column I = absent_with_apologies_count
  • Column J = arrived_late_count
  • Column K = arrived_late_departed_early_count
  • Column L = departed_early_count
  • Column M = present_count

We drag this formula down Column N, and label it weighted_attendance_score.

STEP 8 — Extracting Unique Portfolio Committees count per MP

To extract the number of unique portfolio committees an MP is subscribed to throughout a year, we first need to create a ‘helper’ column. We do this using the following formula in cell O2:

{% hint style="info" %} =UNIQUE(B2:B & “-” & E2:E) {% endhint %}

Where:

  • Column B = member_name
  • Column E = year

We drag this formula down Column O, and label it helper_column.

This creates a unique combination of member_name and year in Column O. We can use this unique combination to extract the number of unique portfolio committees a member is subscribed to for a given year, using the following formula in cell P2:

{% hint style="info" %} =COUNTA(UNIQUE(FILTER(G2:G, B2:B & “-” & E2:E = O2))) {% endhint %}

Where:

  • Column B = member_name
  • Column E = year
  • Column G = committee_meeting

We drag this formula down Column P, and label it unique_committee_count_per_member_for_year.

STEP 9 — Extracting Average Unique Portfolio Committees per MP

Calculate the average number of portfolio committees per MP for a year using the following formula in cell Q2:

{% hint style="info" %} =AVERAGEIFS(P:P, E:E, E2) {% endhint %}

Where:

  • Column E = year
  • Column P = unique_committee_count_per_member_for_year

We drag this formula down Column Q, and label it average_unique_committee_count_per_member_for_year.

STEP 10 — Calculating a Parliamentary Attendance Rating (PAR) score per MP

Before we can calculate PAR scores, we have to adjust the weighted_attendance_score so that it accounts for the number of unique portfolio committees an MP subscribes to throughout a year. To do this, we use the following formula in cell R2:

{% hint style="info" %} =N2*(1+(P2/Q2)) {% endhint %}

Where:

  • Column N = weighted_attendance_score
  • Column P = unique_committee_count_per_member_for_year
  • Column Q = average_unique_committee_count_per_member_for_year

We drag this formula down Column R, and label it score_adjusted_for_committees_per_member_for_year.

Next, we want normalise the score_adjusted_for_committees_per_member_for_year to a value of one (1), using the maximum value, for each year, from Column R. Assuming we a header row, we use the following formulae in cell S2:

{% hint style="info" %} =MAXIFS(R:R, E:E, E2) {% endhint %}

Where:

  • Column E = year
  • Column R = score_adjusted_for_committees_per_member_for_year

We drag this formula down Column S, and label it max_for_year. We can now normalise the values in Column R (score_adjusted_for_committees_per_member_for_year) using the following formula in cell T2:

{% hint style="info" %} =R2/S2 {% endhint %}

Where:

  • Column R = score_adjusted_for_committees_per_member_for_year
  • Column S = max_for_year

We drag this formula down Column T, and label it par_rating_for_year.

We now have a Parliamentary Attendance Rating (PAR) for each MP, which takes into account their attendance across all portfolio committee meetings, and the number of unique portfolio committees they are subscribed to, through a given year.