Built-in SQLite queries
EntFsCorr.Queries — Module
EntFsCorr.QueriesExecute pre-built SQLite queries.
Generally any result that is more than a single element should be returned as a DataFrames.DataFrame.
julia> using EntFsCorr.Queries
EntFsCorr.Queries.distinct_cases_with_interpretation — Function
distinct_cases_with_interpretation(db::DBInterface.Connection)Return number of cases with at least one parsed field_b.
Example
julia> distinct_cases_with_interpretation(db)
6092
EntFsCorr.Queries.interpretation_counts — Function
interpretation_counts(db; view=false)Return counts of each combination of FS and final calls.
Provides a DataFrame or creates an SQLite view (if view is set to true or function is called with !) of the number of occurrences of each combination of interpretation, margin_status and overall_status.
Example
julia> interpretation_counts(db)
36×4 DataFrame
Row │ COUNT(*) interpretation margin_status overall_status
│ Int64 String String? String?
─────┼─────────────────────────────────────────────────────────
1 │ 3144 BENIGN missing BENIGN
2 │ 14 BENIGN missing INTERMEDIATE
3 │ 56 BENIGN missing MALIGNANT
4 │ 2222 BENIGN BENIGN BENIGN
5 │ 5 BENIGN BENIGN INTERMEDIATE
6 │ 2782 BENIGN BENIGN MALIGNANT
7 │ 4 BENIGN INTERMEDIATE INTERMEDIATE
8 │ 780 BENIGN MALIGNANT MALIGNANT
9 │ 2 BENIGN SUPERSEDED MALIGNANT
10 │ 96 DEFER missing missing
11 │ 666 DEFER missing BENIGN
⋮ │ ⋮ ⋮ ⋮ ⋮
27 │ 7 MALIGNANT SUPERSEDED MALIGNANT
28 │ 2 OTHER missing missing
29 │ 6 OTHER missing BENIGN
30 │ 4 OTHER missing MALIGNANT
31 │ 1 OTHER BENIGN BENIGN
32 │ 10 OTHER BENIGN MALIGNANT
33 │ 6 OTHER MALIGNANT MALIGNANT
34 │ 1 OTHER OTHER OTHER
35 │ 1 SUPERSEDED BENIGN MALIGNANT
36 │ 1 SUPERSEDED MALIGNANT MALIGNANT
15 rows omitted
EntFsCorr.Queries.concordance_by_staff_type — Function
concordance_by_staff_type(
db::DBInterface.Connection;
concordance_table="concordance_staff",
ingroup=("RP", "RRS", "SXC1", "DXB", "ANB"),
)Overall levels of agreement stratified by head and neck practice
Uses the view created by concordance_by_staff_view! and computes count of each level of concordance over the group of parts assessed by head and neck pathologists versus those assessed by others.
Returns a named tuple (hn=::DataFrame, non_hn=::DataFrame).
Example
julia> concordance_by_staff_type(db)
(hn = 5×3 DataFrame
Row │ concordance nrow proprow
│ String Int64 Float64
─────┼────────────────────────────────────
1 │ agree 6741 0.828336
2 │ defer 1125 0.13824
3 │ major_undercall 209 0.025682
4 │ major_overcall 54 0.00663554
5 │ minor_undercall 9 0.00110592, non_hn = 4×3 DataFrame
Row │ concordance nrow proprow
│ String Int64 Float64
─────┼────────────────────────────────────
1 │ agree 796 0.60717
2 │ defer 476 0.363082
3 │ major_undercall 31 0.0236461
4 │ major_overcall 8 0.00610221)
EntFsCorr.Queries.specimen_by_staff_type — Function
specimen_by_staff_type(
db::DBInterface.Connection;
concordance_table="concordance_specimen",
ingroup=("RP", "RRS", "SXC1", "DXB", "ANB")
)Specimen type volume stratified by head and neck practice
Uses the view created by concordance_by_specimen_view! and computes the number of specimens by type assessed by head and neck pathologists versus those assessed by others.
Returns a named tuple (hn=::DataFrame, non_hn=::DataFrame).
Example
julia> specimen_by_staff_type(db)
(hn = 10×3 DataFrame
Row │ part_type nrow proprow
│ String Int64 Float64
─────┼─────────────────────────────────────────
1 │ ent_biopsy 4426 0.543868
2 │ ent_resection 1445 0.177562
3 │ endocrine_biopsy 915 0.112435
4 │ sinonasal_skull 721 0.0885967
5 │ skin 326 0.040059
6 │ neck_dissection 198 0.0243303
7 │ endocrine_resection 52 0.00638978
8 │ ent_unclassified 44 0.00540673
9 │ other 8 0.000983043
10 │ hematolymphoid 3 0.000368641, non_hn = 10×3 DataFrame
Row │ part_type nrow proprow
│ String Int64 Float64
─────┼────────────────────────────────────────
1 │ ent_biopsy 713 0.54386
2 │ ent_resection 254 0.193745
3 │ skin 112 0.085431
4 │ endocrine_biopsy 109 0.0831426
5 │ sinonasal_skull 53 0.0404272
6 │ neck_dissection 41 0.0312738
7 │ other 12 0.00915332
8 │ endocrine_resection 9 0.00686499
9 │ ent_unclassified 5 0.00381388
10 │ hematolymphoid 3 0.00228833)
EntFsCorr.Queries.concordance_by_specimen_type — Function
concordance_by_specimen_type(
db::DBInterface.Connection;
concordance_table = "concordance_specimen"
)Compute concordance data by specimen type
Returns a GroupedDataFrame by specimen/part type.
Example
julia> concordance_by_specimen_type(db)
GroupedDataFrame with 10 groups based on key: part_type
First Group (5 rows): part_type = "ent_biopsy"
Row │ part_type concordance nrow proprow
│ String String Int64 Float64
─────┼────────────────────────────────────────────────
1 │ ent_biopsy agree 4141 0.805485
2 │ ent_biopsy defer 886 0.17234
3 │ ent_biopsy major_undercall 99 0.019257
4 │ ent_biopsy major_overcall 9 0.00175063
5 │ ent_biopsy minor_undercall 6 0.00116709
⋮
Last Group (2 rows): part_type = "hematolymphoid"
Row │ part_type concordance nrow proprow
│ String String Int64 Float64
─────┼──────────────────────────────────────────────
1 │ hematolymphoid defer 4 0.666667
2 │ hematolymphoid agree 2 0.333333
EntFsCorr.Queries.margin_distance_bins — Function
margin_distance_bins(
db::DBInterface.Connection;
margin_distance_table = "margin_distance",
top_bin = 0.5,
)Compute discordance rate by margin distance
Bins distances into 0.1 cm increments up to top_bin and reports proportion where concordance is "agree".
Example
julia> margin_distance_bins(db)
6×3 DataFrame
Row │ distance_lower_bound concordance_proportion nrow
│ Float64 Float64 Int64
─────┼─────────────────────────────────────────────────────
1 │ 0.0 0.766667 120
2 │ 0.1 0.87037 108
3 │ 0.2 0.904255 94
4 │ 0.3 0.915493 71
5 │ 0.4 1.0 24
6 │ 0.5 0.925373 67
EntFsCorr.Queries.overall_malignancy_risk — Function
overall_malignancy_risk(
db::DBInterface.Connection;
overall_view_name = "overall_fs_vs_final",
exclude_incomplete = false,
)Calculate the risk of malignancy by frozen overall interpretation
Uses the view created by overall_view!.
Risk for benign cases may be improperly elevated due to cases where obvious tumor was present but only the margin was sampled. These would be recorded as benign since all frozen tissue was benign. This can be corrected by setting exclude_incomplete = true.
Example
julia> overall_malignancy_risk(db)
3×3 DataFrame
Row │ overall_interpretation malignant_proportion nrow
│ String Float64 Int64
─────┼─────────────────────────────────────────────────────
1 │ BENIGN 0.0584958 5385
2 │ DEFER 0.371608 1437
3 │ MALIGNANT 0.998428 2544
julia> overall_malignancy_risk(db; exclude_incomplete=true)
3×3 DataFrame
Row │ overall_interpretation malignant_proportion nrow
│ String Float64 Int64
─────┼─────────────────────────────────────────────────────
1 │ BENIGN 0.0176258 4709
2 │ DEFER 0.288805 1063
3 │ MALIGNANT 0.999105 1117
EntFsCorr.Queries.sampling_ratio_by_overall — Function
sampling_ratio_by_overall(
db::DBInterface.Connection;
overall_view_name = "overall_fs_vs_final",
)Proportion of frozen blocks for incompletely submitted cases by overall diagnosis.
Uses the view created by overall_view!.
Only cases with a block ratio less than 1 (i.e. not completely frozen) are included. The final diagnostic category must be outright benign or malignant.
Example
julia> result = sampling_ratio_by_overall(db)
6×4 DataFrame
Row │ overall_interpretation overall_status AVG(block_ratio) COUNT(*)
│ String String Float64 Int64
─────┼────────────────────────────────────────────────────────────────────
1 │ BENIGN BENIGN 0.397311 441
2 │ DEFER BENIGN 0.35526 137
3 │ MALIGNANT BENIGN 0.261538 3
4 │ BENIGN MALIGNANT 0.195013 232
5 │ DEFER MALIGNANT 0.308686 227
6 │ MALIGNANT MALIGNANT 0.279124 1424
EntFsCorr.Queries.average_levels_by_overall — Function
average_levels_by_overall(
db::DBInterface.Connection;
overall_levels_view_name = "overall_levels",
)Compute average and maximum number of levels per part, stratified by overall status
Example
julia> average_levels_by_overall(db)
6×5 DataFrame
Row │ overall_interpretation overall_status avg_lv_per_block max_lv_per_block nrow
│ String String Float64 Float64 Int64
─────┼───────────────────────────────────────────────────────────────────────────────────
1 │ BENIGN BENIGN 1.57036 1.5749 5060
2 │ DEFER BENIGN 1.72268 1.73443 851
3 │ MALIGNANT BENIGN 1.5 1.75 4
4 │ BENIGN MALIGNANT 1.80635 1.82857 315
5 │ DEFER MALIGNANT 1.79213 1.88764 534
6 │ MALIGNANT MALIGNANT 1.58543 1.66339 2540
SQLite view constructors
EntFsCorr.Queries.interpretation_counts! — Function
interpretation_counts!(db) # view=trueShort-hand for interpretation_counts but creating a new SQLite view.
EntFsCorr.Queries.concordance_by_staff_view! — Function
concordance_by_staff_view!(
db::DBInterface.Connection;
concordance_table="concordance",
block_info_table="block_info"m
)Create an SQL view with level of concordance and FS pathologist
EntFsCorr.Queries.concordance_by_specimen_view! — Function
concordance_by_specimen_view!(
db::DBInterface.Connection[,
part_type_classifier];
concordance_table="concordance",
block_info_table="block_info"
)Create an SQL view with level of concordance and specimen origin
Also includes staff to see differences in sites.
part_type_classifier is an optional positional argument pointing to a two-column CSV file that maps each part_type to a category.
EntFsCorr.Queries.margin_distance_view! — Function
margin_distance_view!(
db::DBInterface.Connection;
margin_distance_name = "margin_distance",
concordance_table_name = "concordance",
distance_table_name = "combined_fs_after_overrides",
)Creates a view with lowest distance to margin reported
The view will only include cases called benign on the original frozen and where the overall margin status was either benign or malignant (i.e. no moderate dysplasia at margin).
margin_distance_name is the name of the view to be created.
EntFsCorr.Queries.overall_view! — Function
overall_view!(
db::DBInterface.Connection;
concordance_table_name = "concordance",
interpretations_table_name = "combined_fs_after_overrides",
overall_view_name = "overall_fs_vs_final",
)Create view for overall benign/malignant status
EntFsCorr.Queries.overall_levels_view! — Function
overall_levels_view!(
db::DBInterface.Connection;
# Keyword arguments
concordance_table_name = "concordance",
interpretations_table_name = "combined_fs_after_overrides",
block_table_name = "block_info",
overall_levels_view_name = "overall_levels",
)Create view for number of levels cut by overall status