Language features
This reference is structured as a series of examples.
The intended audience is primarily:
researchers
software developers
that already have some understanding of how the ehrQL works.
Info
Please refer to the introduction and tutorial documentation sections
if you need more explanation of the underlying concepts behind ehrQL .
How the examples work
Each individual example demonstrates a specific ehrQL feature in isolation.
Every example here consists of:
Headings and subheadings that summarise the feature being demonstrated.
A small example data input table containing entirely fictitious variables and values.
The table has a single-letter name referred to throughout the example
e
for event-level table
p
for patient-level table.
The columns of input tables use a name constructed from a single letter with a number
to create an identifier — for example, i1
.
The single letter in the identifier refers to the column's data type:
a b
column contains Boolean values
a c
column contains electronic health record codes
(the codes used in this reference are fictitious, for example: abc
)
a d
column contains dates
an i
column contains integers
an s
column contains strings
Both table and column names are written with code formatting throughout this reference.
An ehrQL query that extracts some data from the example table.
Like the table names, ehrQL queries are displayed here with code formatting.
The resulting output from the ehrQL query,
displayed as another table,
to demonstrate the query's effect
1 Filtering an event frame
1.1 Including rows
1.1.1 Where with column
This example makes use of an event-level table named e
containing the following data:
e . where ( e . b1 ) . i1 . sum_for_patient ()
returns the following patient series:
1.1.2 Where with expr
This example makes use of an event-level table named e
containing the following data:
e . where (( e . i1 + e . i2 ) < 413 ) . i1 . sum_for_patient ()
returns the following patient series:
1.1.3 Where with constant true
This example makes use of an event-level table named e
containing the following data:
e . where ( True ) . count_for_patient ()
returns the following patient series:
1.1.4 Where with constant false
This example makes use of an event-level table named e
containing the following data:
e . where ( False ) . count_for_patient ()
returns the following patient series:
1.1.5 Chain multiple wheres
This example makes use of an event-level table named e
containing the following data:
e . where ( e . i1 >= 2 ) . where ( e . b1 ) . i1 . sum_for_patient ()
returns the following patient series:
1.2 Excluding rows
1.2.1 Except where with column
This example makes use of an event-level table named e
containing the following data:
e . except_where ( e . b1 ) . i1 . sum_for_patient ()
returns the following patient series:
1.2.2 Except where with expr
This example makes use of an event-level table named e
containing the following data:
e . except_where (( e . i1 + e . i2 ) < 413 ) . i1 . sum_for_patient ()
returns the following patient series:
1.2.3 Except where with constant true
This example makes use of an event-level table named e
containing the following data:
e . except_where ( True ) . count_for_patient ()
returns the following patient series:
1.2.4 Except where with constant false
This example makes use of an event-level table named e
containing the following data:
e . except_where ( False ) . count_for_patient ()
returns the following patient series:
2 Picking one row for each patient from an event frame
2.1 Picking the first or last row for each patient
2.1.1 Sort by column pick first
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 ) . first_for_patient () . i1
returns the following patient series:
2.1.2 Sort by column pick last
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 ) . last_for_patient () . i1
returns the following patient series:
2.2 Sort by more than one column and pick the first or last row for each patient
2.2.1 Sort by multiple columns pick first
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 , e . i2 ) . first_for_patient () . i2
returns the following patient series:
2.2.2 Sort by multiple columns pick last
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 , e . i2 ) . last_for_patient () . i2
returns the following patient series:
2.3 Picking the first or last row for each patient where a column contains NULLs
2.3.1 Sort by column with nulls and pick first
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 ) . first_for_patient () . i1
returns the following patient series:
2.3.2 Sort by column with nulls and pick last
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 ) . last_for_patient () . i1
returns the following patient series:
2.4 Mixing the order of sort_by
and where
operations
2.4.1 Sort by before where
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 ) . where ( e . i1 > 102 ) . first_for_patient () . i1
returns the following patient series:
2.4.2 Sort by interleaved with where
This example makes use of an event-level table named e
containing the following data:
e . sort_by ( e . i1 ) . where ( e . i2 > 1 ) . sort_by ( e . i2 ) . first_for_patient () . i1
returns the following patient series:
2.5 Pointless sort operations that we should nevertheless handle without error
2.5.1 Sort by patient series
This example makes use of an event-level table named e
and a patient-level table named p
containing the following data:
e . sort_by (
# Patient series
p . i1 ,
# Literal constant
0 ,
)
. first_for_patient ()
. i1
returns the following patient series:
3 Aggregating event and patient frames
3.1 Determining whether a row exists for each patient
3.1.1 Exists for patient on event frame
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
returns the following patient series:
3.1.2 Exists for patient on patient frame
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
returns the following patient series:
3.2 Counting the rows for each patient
3.2.1 Count for patient on event frame
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
returns the following patient series:
3.2.2 Count for patient on patient frame
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
returns the following patient series:
4 Aggregating event series
4.1 Minimum and maximum aggregations
4.1.1 Minimum for patient
This example makes use of an event-level table named e
containing the following data:
e . i1 . minimum_for_patient ()
returns the following patient series:
4.1.2 Maximum for patient
This example makes use of an event-level table named e
containing the following data:
e . i1 . maximum_for_patient ()
returns the following patient series:
4.2 Sum aggregation
4.2.1 Sum for patient
This example makes use of an event-level table named e
containing the following data:
returns the following patient series:
4.3 Mean aggregation
4.3.1 Mean for patient integer
This example makes use of an event-level table named e
containing the following data:
returns the following patient series:
4.3.2 Mean for patient float
This example makes use of an event-level table named e
containing the following data:
returns the following patient series:
4.4 Count distinct aggregation
4.4.1 Count distinct for patient integer
This example makes use of an event-level table named e
containing the following data:
e . i1 . count_distinct_for_patient ()
returns the following patient series:
4.4.2 Count distinct for patient float
This example makes use of an event-level table named e
containing the following data:
e . f1 . count_distinct_for_patient ()
returns the following patient series:
4.4.3 Count distinct for patient string
This example makes use of an event-level table named e
containing the following data:
e . s1 . count_distinct_for_patient ()
returns the following patient series:
4.4.4 Count distinct for patient date
This example makes use of an event-level table named e
containing the following data:
e . s1 . count_distinct_for_patient ()
returns the following patient series:
5 Combining series
5.1 Combining two patient series
5.1.1 Patient series and patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
5.2 Combining a patient series with a value
5.2.1 Patient series and value
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
5.2.2 Value and patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
5.3 Combining two event series
5.3.1 Event series and event series
This example makes use of an event-level table named e
containing the following data:
( e . i1 + e . i2 ) . sum_for_patient ()
returns the following patient series:
5.3.2 Event series and sorted event series
The sort order of the underlying event series does not affect their combination.
This example makes use of an event-level table named e
containing the following data:
( e . i1 + e . sort_by ( e . s1 ) . i2 ) . minimum_for_patient ()
returns the following patient series:
5.4 Combining an event series with a patient series
5.4.1 Event series and patient series
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
( e . i1 + p . i1 ) . sum_for_patient ()
returns the following patient series:
5.4.2 Patient series and event series
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
( p . i1 + e . i1 ) . sum_for_patient ()
returns the following patient series:
5.5 Combining an event series with a value
5.5.1 Event series and value
This example makes use of an event-level table named e
containing the following data:
( e . i1 + 1 ) . sum_for_patient ()
returns the following patient series:
5.5.2 Value and event series
This example makes use of an event-level table named e
containing the following data:
( 1 + e . i1 ) . sum_for_patient ()
returns the following patient series:
6 Operations on all series
6.1 Testing for equality
6.1.1 Equals
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.1.2 Not equals
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.1.3 Is null
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.1.4 Is not null
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.2 Testing for containment
6.2.1 Is in
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.2.2 Is not in
This example makes use of a patient-level table named p
containing the following data:
p . i1 . is_not_in ([ 101 , 301 ])
returns the following patient series:
6.2.3 Is in empty list
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.2.4 Is not in empty list
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.3 Testing for containment in another series
6.3.1 Is in series
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
returns the following patient series:
6.3.2 Is not in series
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
returns the following patient series:
6.4 Map from one set of values to another
6.4.1 Map values
This example makes use of a patient-level table named p
containing the following data:
p . i1 . map_values ({ 101 : "a" , 201 : "b" , 301 : "a" }, default = "c" )
returns the following patient series:
6.5 Replace missing values
6.5.1 When null then integer column
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.5.2 When null then boolean column
This example makes use of a patient-level table named p
containing the following data:
p . i1 . is_in ([ 101 , 201 ]) . when_null_then ( False )
returns the following patient series:
6.6 Minimum and maximum aggregations across Patient series
6.6.1 Maximum of two integer patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.2 Minimum of two integer patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.3 Minimum of two integer patient series and a value
This example makes use of a patient-level table named p
containing the following data:
minimum_of ( p . i1 , p . i2 , 150 )
returns the following patient series:
6.6.4 Maximum of two integer patient series and a value
This example makes use of a patient-level table named p
containing the following data:
maximum_of ( p . i1 , p . i2 , 150 )
returns the following patient series:
6.6.5 Minimum of two date patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.6 Maximum of two date patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.7 Minimum of two date patient series and datetime a value
This example makes use of a patient-level table named p
containing the following data:
minimum_of ( p . d1 , p . d2 , date ( 2015 , 5 , 5 ))
returns the following patient series:
6.6.8 Maximum of two date patient series and datetime a value
This example makes use of a patient-level table named p
containing the following data:
maximum_of ( p . d1 , p . d2 , date ( 2015 , 5 , 5 ))
returns the following patient series:
6.6.9 Minimum of two date patient series and string a value
This example makes use of a patient-level table named p
containing the following data:
minimum_of ( p . d1 , p . d2 , "2015-05-05" )
returns the following patient series:
6.6.10 Maximum of two date patient series and string a value
This example makes use of a patient-level table named p
containing the following data:
maximum_of ( p . d1 , p . d2 , "2015-05-05" )
returns the following patient series:
6.6.11 Maximum of two float patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.12 Minimum of two float patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.13 Minimum of two float patient series and a value
This example makes use of a patient-level table named p
containing the following data:
minimum_of ( p . f1 , p . f2 , 1.5 )
returns the following patient series:
6.6.14 Maximum of two float patient series and a value
This example makes use of a patient-level table named p
containing the following data:
maximum_of ( p . f1 , p . f2 , 1.5 )
returns the following patient series:
6.6.15 Maximum of two string patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.16 Minimum of two string patient series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.6.17 Minimum of two string patient series and a value
This example makes use of a patient-level table named p
containing the following data:
minimum_of ( p . s1 , p . s2 , "e" )
returns the following patient series:
6.6.18 Maximum of two string patient series and a value
This example makes use of a patient-level table named p
containing the following data:
maximum_of ( p . s1 , p . s2 , "e" )
returns the following patient series:
6.6.19 Maximum of two integers all a values
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
6.7 Minimum and maximum aggregations across Event series
6.7.1 Maximum of two integer event series
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . i1 , e . i2 ) . maximum_for_patient ()
returns the following patient series:
6.7.2 Minimum of two integer event series
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . i1 , e . i2 ) . minimum_for_patient ()
returns the following patient series:
6.7.3 Minimum of two integer event series and a value
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . i1 , e . i2 , 150 ) . minimum_for_patient ()
returns the following patient series:
6.7.4 Maximum of two integer event series and a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . i1 , e . i2 , 150 ) . maximum_for_patient ()
returns the following patient series:
6.7.5 Minimum of two date event series
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . d1 , e . d2 ) . minimum_for_patient ()
returns the following patient series:
6.7.6 Maximum of two date event series
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . d1 , e . d2 ) . maximum_for_patient ()
returns the following patient series:
6.7.7 Minimum of two date event series and datetime a value
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . d1 , e . d2 , date ( 2015 , 5 , 5 )) . minimum_for_patient ()
returns the following patient series:
6.7.8 Maximum of two date event series and datetime a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . d1 , e . d2 , date ( 2015 , 5 , 5 )) . maximum_for_patient ()
returns the following patient series:
6.7.9 Minimum of two date event series and string a value
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . d1 , e . d2 , "2015-05-05" ) . minimum_for_patient ()
returns the following patient series:
6.7.10 Maximum of two date event series and string a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . d1 , e . d2 , "2015-05-05" ) . maximum_for_patient ()
returns the following patient series:
6.7.11 Maximum of two float event series
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . f1 , e . f2 ) . maximum_for_patient ()
returns the following patient series:
6.7.12 Minimum of two float event series
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . f1 , e . f2 ) . minimum_for_patient ()
returns the following patient series:
6.7.13 Minimum of two float event series and float a value
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . f1 , e . f2 , 1.5 ) . minimum_for_patient ()
returns the following patient series:
6.7.14 Maximum of two float event series and float a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . f1 , e . f2 , 1.5 ) . maximum_for_patient ()
returns the following patient series:
6.7.15 Minimum of two float event series and integer a value
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . f1 , e . f2 , 2 ) . minimum_for_patient ()
returns the following patient series:
6.7.16 Maximum of two float event series and integer a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . f1 , e . f2 , 2 ) . maximum_for_patient ()
returns the following patient series:
6.7.17 Maximum of two string event series
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . s1 , e . s2 ) . maximum_for_patient ()
returns the following patient series:
6.7.18 Minimum of two string event series
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . s1 , e . s2 ) . minimum_for_patient ()
returns the following patient series:
6.7.19 Minimum of two string event series and a value
This example makes use of an event-level table named e
containing the following data:
minimum_of ( e . s1 , e . s2 , "e" ) . minimum_for_patient ()
returns the following patient series:
6.7.20 Maximum of two string event series and a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . s1 , e . s2 , "e" ) . maximum_for_patient ()
returns the following patient series:
6.7.21 Maximum of nested aggregate
This example makes use of an event-level table named e
containing the following data:
maximum_of (
e . s1 . count_distinct_for_patient (),
e . s2 . count_distinct_for_patient (),
)
returns the following patient series:
6.7.22 Maximum of nested aggregate and column and a value
This example makes use of an event-level table named e
containing the following data:
maximum_of ( e . s1 . count_distinct_for_patient (), e . i1 , 1 ) . maximum_for_patient ()
returns the following patient series:
7 Operations on boolean series
7.1 Logical operations
7.1.1 Not
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
7.1.2 And
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
7.1.3 Or
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8 Operations on integer series
8.1 Arithmetic operations without division
8.1.1 Negate
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.1.2 Add
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.1.3 Subtract
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.1.4 Multiply
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.1.5 Multiply with constant
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.2 Comparison operations
8.2.1 Less than
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.2.2 Less than or equal to
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.2.3 Greater than
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
8.2.4 Greater than or equal to
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
9 Operations on all series containing codes
9.1 Testing for containment using codes
9.1.1 Is in
This example makes use of a patient-level table named p
containing the following data:
p . c1 . is_in ([ SNOMEDCTCode ( "123000" ), SNOMEDCTCode ( "789000" )])
returns the following patient series:
9.1.2 Is not in
This example makes use of a patient-level table named p
containing the following data:
p . c1 . is_not_in ([ SNOMEDCTCode ( "123000" ), SNOMEDCTCode ( "789000" )])
returns the following patient series:
9.1.3 Is in codelist csv
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
9.2 Test mapping codes to categories using a categorised codelist
9.2.1 Map codes to categories
This example makes use of a patient-level table named p
containing the following data:
p . c1 . to_category ( codelist )
returns the following patient series:
10 Operations on all series containing multi code strings
10.1 Testing for containment using codes
10.1.1 Contains code prefix
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
10.1.2 Contains code
This example makes use of a patient-level table named p
containing the following data:
p . m1 . contains ( ICD10Code ( "M069" ))
returns the following patient series:
10.1.3 Contains any of codelist
This example makes use of a patient-level table named p
containing the following data:
p . m1 . contains_any_of ([ ICD10Code ( "M069" ), "A429" ])
returns the following patient series:
11 Logical case expressions
11.1 Logical case expressions
11.1.1 Case with expression
This example makes use of a patient-level table named p
containing the following data:
case (
when ( p . i1 < 8 ) . then ( p . i1 ),
when ( p . i1 > 8 ) . then ( 100 ),
)
returns the following patient series:
11.1.2 Case with default
This example makes use of a patient-level table named p
containing the following data:
case (
when ( p . i1 < 8 ) . then ( p . i1 ),
when ( p . i1 > 8 ) . then ( 100 ),
otherwise = 0 ,
)
returns the following patient series:
11.1.3 Case with boolean column
This example makes use of a patient-level table named p
containing the following data:
case (
when ( p . b1 ) . then ( p . i1 ),
when ( p . i1 > 8 ) . then ( 100 ),
)
returns the following patient series:
11.1.4 Case with explicit null
This example makes use of a patient-level table named p
containing the following data:
case (
when ( p . i1 < 8 ) . then ( None ),
when ( p . i1 > 8 ) . then ( 100 ),
otherwise = 200 ,
)
returns the following patient series:
11.2 Case expressions with single condition
11.2.1 When with expression
This example makes use of a patient-level table named p
containing the following data:
when ( p . i1 < 8 ) . then ( p . i1 ) . otherwise ( 100 )
returns the following patient series:
11.2.2 When with boolean column
This example makes use of a patient-level table named p
containing the following data:
when ( p . b1 ) . then ( p . i1 ) . otherwise ( 100 )
returns the following patient series:
12 Operations on all series containing dates
12.1 Operations which apply to all series containing dates
12.1.1 Get year
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.2 Get month
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.3 Get day
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.4 To first of year
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.5 To first of month
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.6 Add days
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.7 Subtract days
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.8 Add months
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.9 Add years
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.10 Add date to duration
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.11 Difference between dates in years
This example makes use of a patient-level table named p
containing the following data:
( date ( 2021 , 2 , 28 ) - p . d1 ) . years
returns the following patient series:
12.1.12 Difference between dates in months
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.13 Difference between dates in days
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.1.14 Reversed date differences
This example makes use of a patient-level table named p
containing the following data:
( p . d1 - "1980-01-20" ) . years
returns the following patient series:
12.1.15 Add days to static date
This example makes use of a patient-level table named p
containing the following data:
date ( 2000 , 1 , 1 ) + days ( p . i1 )
returns the following patient series:
12.1.16 Add months to static date
This example makes use of a patient-level table named p
containing the following data:
date ( 2000 , 1 , 1 ) + months ( p . i1 )
returns the following patient series:
12.1.17 Add years to static date
This example makes use of a patient-level table named p
containing the following data:
date ( 2000 , 1 , 1 ) + years ( p . i1 )
returns the following patient series:
12.2 Comparisons involving dates
12.2.1 Is before
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_before ( date ( 2000 , 1 , 1 ))
returns the following patient series:
12.2.2 Is on or before
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_on_or_before ( date ( 2000 , 1 , 1 ))
returns the following patient series:
12.2.3 Is after
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_after ( date ( 2000 , 1 , 1 ))
returns the following patient series:
12.2.4 Is on or after
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_on_or_after ( date ( 2000 , 1 , 1 ))
returns the following patient series:
12.2.5 Is in
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_in ([ date ( 2010 , 1 , 1 ), date ( 1900 , 1 , 1 )])
returns the following patient series:
12.2.6 Is not in
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_not_in ([ date ( 2010 , 1 , 1 ), date ( 1900 , 1 , 1 )])
returns the following patient series:
12.2.7 Is between but not on
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_between_but_not_on ( date ( 2010 , 1 , 2 ), date ( 2010 , 1 , 4 ))
returns the following patient series:
12.2.8 Is on or between
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_on_or_between ( date ( 2010 , 1 , 2 ), date ( 2010 , 1 , 4 ))
returns the following patient series:
12.2.9 Is during
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.2.10 Is on or between backwards
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_on_or_between ( date ( 2010 , 1 , 4 ), date ( 2010 , 1 , 2 ))
returns the following patient series:
12.3 Types usable in comparisons involving dates
12.3.1 Accepts python date object
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_before ( datetime . date ( 2000 , 1 , 20 ))
returns the following patient series:
This example makes use of a patient-level table named p
containing the following data:
p . d1 . is_before ( "2000-01-20" )
returns the following patient series:
12.3.3 Accepts another date series
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
12.4 Aggregations which apply to all series containing dates
12.4.1 Count episodes
This example makes use of an event-level table named e
containing the following data:
e . d1 . count_episodes_for_patient ( days ( 3 ))
returns the following patient series:
13 Operations on all series containing strings
13.1 Testing whether one string contains another string
13.1.1 Contains fixed value
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
13.1.2 Contains fixed value with special characters
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
13.1.3 Contains value from column
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
13.1.4 Contains value from column with special characters
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series:
14 Defining the dataset population
14.1 Defining a population
define_population
is used to limit the population from which data is extracted.
14.1.1 Population with single table
Extract a column from a patient table after limiting the population by another column.
This example makes use of a patient-level table named p
containing the following data:
p . i1
define_population ( ~ p . b1 )
returns the following patient series:
14.1.2 Population with multiple tables
Limit the patient population by a column in one table, and return values from another
table.
This example makes use of a patient-level table named p
and an event-level table named e
containing the following data:
e . exists_for_patient ()
define_population ( p . i1 > 0 )
returns the following patient series:
14.1.3 Case with case expression
Limit the patient population by a case expression.
This example makes use of a patient-level table named p
containing the following data:
p . i1
define_population (
case (
when ( p . i1 <= 8 ) . then ( True ),
when ( p . i1 > 8 ) . then ( False ),
)
)
returns the following patient series:
15 Defining a table using inline data
15.1 Defining a table using inline data
15.1.1 Table from rows
This example makes use of a patient-level table named p
containing the following data:
returns the following patient series: