Transplant List Data Charts

Rolling 12 month pre-emptive transplant listed (Graph1)
Each line shows over time (quarters) the proportion of preemptive listingdata items in every unit of a specific region every 4 quarters of listingdata items. Every list of quarters contains the last three quarters of the previous list (e.g., [2016 Q1, 2016 Q2, 2016 Q3, 2016 Q4], [2016 Q2, 2016 Q3, 2016 Q4, 2017 Q1], [2016 Q3, 2016 Q4, 2017 Q1, 2017 Q2]…). Both, unit and region data points in this graph consider 4 quarters at each point. The horizontal axis labels represent the last quarter of the list.

An item is pre-emptive when the adjusted function from txfirst/models.py returns 0. Preemptive records example query: select * from txfirst_listingdata where unit_id = UNIT and (dialysis_started is NULL or dialysis_started = transplant_listed or dialysis_started > transplant_listed) and (quarter_id  >= Q) – replace UNIT for the desired unit, and Q for the specific quarter.

E.g., Shrewsbury and Telford NHS Trust at Q4 2016= [10, 22] the total txfirst_listingdata records where quarter_id >= 7 and quarter_id <= 10 is equal to 22. From those 22 records, only 10 records are preemptive. Then the point at Q4 2016 for Shrewsbury and Telford NHS Trust is 10/22 * 100 = 45.45%.

E.g., Coventry and Warwickshire at Q1 2020

Pre-emptive records:
select count(*) from txfirst_listingdata where unit_id = 9 and (dialysis_started is NULL or dialysis_started = transplant_listed or dialysis_started > transplant_listed) and (quarter_id >= 14 and quarter_id <=17) → 13

All records:
select count(*) from txfirst_listingdata where unit_id = 9 and (quarter_id >= 14 and quarter_id <=17) → 32

13 / 32 * 100 = 40.62%

The above example shows how quarters 14-17 (2019 Q2, 2019 Q3, 2019 Q4, 2020 Q1) are included at point Q1 2020. Now, for point Q3 2020 the quarters are: 2019 Q3, 2019 Q4, 2020 Q1, 2020 Q3.

E.g., Birmingham at Q3 2020

Pre-emptive records:
select count(*) from txfirst_listingdata where unit_id = 12 and (dialysis_started is NULL or dialysis_started = transplant_listed or dialysis_started > transplant_listed) and (quarter_id >= 15 and quarter_id <=19) → 9

All records:
select count(*) from txfirst_listingdata where unit_id = 12 and (quarter_id >= 15 and quarter_id <=19) → 15

9 / 15 * 100 = 60%

Rolling 12 month pre-emptive transplants kidney only (Graph2)
Each line plot shows pre-emptive kidney transplant changes in a region/unit since 2016 until the most recent quarter.

get_rolling_transplant function – after getting the list of records (summarynumbers per region of specific unit for all quarters in ascendant quarter order), the function groups all records by quarter.
Then, it iterates the records by groups of 4 quarters (items) (in the same form as Graph1, [2016 Q1, 2016 Q2, 2016 Q3, 2016 Q4], [2016 Q2, 2016 Q3, 2016 Q4, 2017 Q1], [2016 Q3, 2016 Q4, 2017 Q1, 2017 Q2]…)

Each point in a line graph represents:

Unit graph: unit_interim[0] / unit_interim[1] * 100. Percentage of pre-emptive transplants kidney of the total of all summarynumbers for the unit over 4 quarters. Horizontal axis indicates the last quarter of the group.

E.g., University Hospital of North Midlands over 2016 Q1 – 2016 Q4

select sum(at_other_kidney) from txfirst_summarynumbers left join txfirst_summarynumberrowname on txfirst_summarynumbers.row_id = txfirst_summarynumberrowname.id where unit_id = 2 and quarter_id >= 7 and quarter_id <= 10 and txfirst_summarynumberrowname.name like "%pre-emptive" / select sum(at_other_kidney) from txfirst_summarynumbers left join txfirst_summarynumberrowname on txfirst_summarynumbers.row_id = txfirst_summarynumberrowname.id where unit_id=2 and quarter_id >= 7 and quarter_id <= 10 → 5/28*100 = 17.86%

17.86% is the value at Q4 2016 for University Hospital of North Midlands.

Region graph: preemptive / total * 100. Percentage of pre-emptive transplants kidney of the total of all summarynumbers for the region over 4 quarters. Horizontal axis indicates the last quarter of the group.

E.g., West Midlands region over 2018 Q2 – 2019 Q1

select sum(at_other_kidney) from txfirst_summarynumbers left join txfirst_summarynumberrowname on txfirst_summarynumbers.row_id = txfirst_summarynumberrowname.id left join users_unit on txfirst_summarynumbers.unit_id = users_unit.id where users_unit.region_id = 2 and (quarter_id = 1 or quarter_id >= 11 and quarter_id<= 13) and txfirst_summarynumberrowname.name like "%pre-emptive" / select sum(at_other_kidney) from txfirst_summarynumbers left join txfirst_summarynumberrowname on txfirst_summarynumbers.row_id = txfirst_summarynumberrowname.id left join users_unit on txfirst_summarynumbers.unit_id = users_unit.id where users_unit.region_id = 2 and (quarter_id = 1 or quarter_id >= 11 and quarter_id<= 13) → 12/67 * 100 = 17.91%

17.91% is the value at Q1 2019 for West Midlands.

Proportion of CKD5 patients who have a transplant status (Graph3)
Each line plot shows the trend in percentages of CKD5 patients who have a transplant status over all quarters recorded in the database for a region/unit.

Each point in a line graph represents:

Unit graph: unit_status / unit_totals * 100. Percentage of CKD5 patients who have a transplant status of the total of records (column total in txfirst_summaryckdproportion) per quarter in the unit. Horizontal axis indicates the quarter.

E.g., University Hospital of North Midlands at Q1 2018

select sum(transplant_status) from txfirst_summaryckdproportion left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 2 and txfirst_quarter.quarter = 201801 / select sum(total) from txfirst_summaryckdproportion left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 2 and txfirst_quarter.quarter = 201801 → 515 / 573 * 100 = 89.88 %

89.88% is the value at Q1 2018 for University Hospital of North Midlands.

Region graph: region_status / region_totals * 100. Percentage of CKD5 patients who have a transplant status of the total of records (column total in txfirst_summaryckdproportion) per quarter in the region. Horizontal axis indicates the quarter.

E.g., West Midlands region at Q1 2018

select sum(transplant_status) from txfirst_summaryckdproportion left join txfirst_quarter on quarter_id = txfirst_quarter.id left join users_unit on unit_id = users_unit.id where users_unit.region_id = 2 and txfirst_quarter.quarter = 201801 / select sum(total) from txfirst_summaryckdproportion left join txfirst_quarter on quarter_id = txfirst_quarter.id left join users_unit on unit_id = users_unit.id where users_unit.region_id = 2 and txfirst_quarter.quarter = 201801 → 856/929 * 100 = 92.14%

92.14% is the value at Q1 2018 for West Midlands.

Proportion transplant list suspended (Graph4)
Each line plot shows the trend in percentages of suspended transplants over all quarters recorded in the database for a unit.

E.g., Derriford Hospital at Q3 2019

select suspended from txfirst_summaryproportion where unit_id = 21 and quarter_id = 15 → 25.6%

Reasons why patients have not been listed pre-emptively – Proportions (Graph 5)
Each bar represents the percentage of not listed patients for a specific reason in a unit at a quarter.

E.g., Derriford hospital at Q1 2019

select quarter_id, reason_id, count(reason_id) as count1 from txfirst_listingdata left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 21 and txfirst_quarter.quarter = 201901 group by reason_id order by quarter_id

Result:

Then the bars of Derriford hospital at quarter 201901 (the reason_id is from table txfirst_listingreason) depict the following percentages:

Reason_id = 5 (Medically Complex) proportion: 3/5 * 100 = 60%
Reason_id = 6 (Unsuitable became suitable) proportion: 1/5 * 100 = 20%
Reason_id = 8 (Transferred in) proportion: 1/5 * 100 = 20%

Reasons why patients have not been listed pre-emptively – Absolute numbers (Graph 6)
Each bar represents the amount of patients not listed for a specific reason in a unit at a quarter.

E.g., Salford at Q1 2019

select quarter_id, reason_id, count(reason_id) as count1 from txfirst_listingdata left join txfirst_quarter on quarter_id = txfirst_quarter.id where unit_id = 7 and txfirst_quarter.quarter = 201901 group by reason_id order by quarter_id

Result:

Then the bars of Salford at quarter 201901 (the reason_id is from table txfirst_listingreason) depict the following amounts:

Reason_id = 1 (Ref when eGFR <15) = 4
Reason_id = 2 (Ref within 1 year of predicted ESRF) = 5
Reason_id = 3 (Patient Choice) = 1
Reason_id = 5 (Medically complex) = 2
Reason_id = 7 (Unplanned start) = 2