CDISCPILOT01 NCA

Author

Mohammed Shas

Import Packages

using DataFramesMeta
using ReadStatTables
using Statistics
using Dates
include("con-fun.jl")

find_next_nominal_time (generic function with 1 method)

Define the path and load the data

path = joinpath(@__DIR__, "..", "Datasets") |> normpath
pc = @chain readstat(joinpath(path, "pc.xpt")) DataFrame convert_blanks_to_missing
ex = @chain readstat(joinpath(path, "ex.xpt")) DataFrame convert_blanks_to_missing
vs = @chain readstat(joinpath(path, "vs.xpt")) DataFrame convert_blanks_to_missing
lb = @chain readstat(joinpath(path, "lb.xpt")) DataFrame convert_blanks_to_missing
adsl = @chain readstat(joinpath(path, "adsl.xpt")) DataFrame convert_blanks_to_missing
first(adsl, 5)

5×54 DataFrame

Row	STUDYID	USUBJID	SUBJID	RFSTDTC	RFENDTC	RFXSTDTC	RFXENDTC	RFICDTC	RFPENDTC	DTHDTC	DTHFL	SITEID	AGE	AGEU	SEX	RACE	ETHNIC	ARMCD	ARM	ACTARMCD	ACTARM	COUNTRY	DMDTC	DMDY	TRT01P	TRT01A	TRTSDTM	TRTSTMF	TRTEDTM	TRTETMF	TRTSDT	TRTEDT	TRTDURD	SCRFDT	EOSDT	EOSSTT	FRVDT	RANDDT	DTHDT	DTHDTF	DTHADY	LDDTHELD	DTHCAUS	DTHDOM	DTHCGR1	LSTALVDT	SAFFL	RACEGR1	AGEGR1	REGION1	LDDTHGR1	DTH30FL	DTHA30FL	DTHB30FL
	String15	String15	String7	String15?	String15?	String15?	String15?	Missing	String31	String15?	String3?	String3	Float64	String7	String3	String	String31	String15	String31	String15	String31	String3	String15	Float64?	String31	String31	DateTime?	String3?	DateTime?	String3?	Date?	Date?	Float64?	Date?	Date?	String15?	Date?	Date?	Date?	Missing	Float64?	Float64?	String31?	String3?	String15?	Date?	String3?	String15	String7	String3	String7?	String3?	Missing	String3?
1	CDISCPILOT01	01-701-1015	1015	2014-01-02	2014-07-02	2014-01-02	2014-07-02	missing	2014-07-02T11:45	missing	missing	701	63.0	YEARS	F	WHITE	HISPANIC OR LATINO	Pbo	Placebo	Pbo	Placebo	USA	2013-12-26	-7.0	Placebo	Placebo	2014-01-02T00:00:00	H	2014-07-02T23:59:59	H	2014-01-02	2014-07-02	182.0	missing	2014-07-02	COMPLETED	missing	2014-01-02	missing	missing	missing	missing	missing	missing	missing	2014-07-02	Y	White	18-64	NA	missing	missing	missing	missing
2	CDISCPILOT01	01-701-1023	1023	2012-08-05	2012-09-02	2012-08-05	2012-09-01	missing	2013-02-18	missing	missing	701	64.0	YEARS	M	WHITE	HISPANIC OR LATINO	Pbo	Placebo	Pbo	Placebo	USA	2012-07-22	-14.0	Placebo	Placebo	2012-08-05T00:00:00	H	2012-09-01T23:59:59	H	2012-08-05	2012-09-01	28.0	missing	2012-09-02	DISCONTINUED	2013-02-18	2012-08-05	missing	missing	missing	missing	missing	missing	missing	2012-09-02	Y	White	18-64	NA	missing	missing	missing	missing
3	CDISCPILOT01	01-701-1028	1028	2013-07-19	2014-01-14	2013-07-19	2014-01-14	missing	2014-01-14T11:10	missing	missing	701	71.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	2013-07-11	-8.0	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	H	2014-01-14T23:59:59	H	2013-07-19	2014-01-14	180.0	missing	2014-01-14	COMPLETED	missing	2013-07-19	missing	missing	missing	missing	missing	missing	missing	2014-01-14	Y	White	>64	NA	missing	missing	missing	missing
4	CDISCPILOT01	01-701-1033	1033	2014-03-18	2014-04-14	2014-03-18	2014-03-31	missing	2014-09-15	missing	missing	701	74.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Lo	Xanomeline Low Dose	Xan_Lo	Xanomeline Low Dose	USA	2014-03-10	-8.0	Xanomeline Low Dose	Xanomeline Low Dose	2014-03-18T00:00:00	H	2014-03-31T23:59:59	H	2014-03-18	2014-03-31	14.0	missing	2014-04-14	DISCONTINUED	2014-09-15	2014-03-18	missing	missing	missing	missing	missing	missing	missing	2014-04-14	Y	White	>64	NA	missing	missing	missing	missing
5	CDISCPILOT01	01-701-1034	1034	2014-07-01	2014-12-30	2014-07-01	2014-12-30	missing	2014-12-30T09:50	missing	missing	701	77.0	YEARS	F	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	2014-06-24	-7.0	Xanomeline High Dose	Xanomeline High Dose	2014-07-01T00:00:00	H	2014-12-30T23:59:59	H	2014-07-01	2014-12-30	183.0	missing	2014-12-30	COMPLETED	missing	2014-07-01	missing	missing	missing	missing	missing	missing	missing	2014-12-30	Y	White	>64	NA	missing	missing	missing	missing

Lookup table

param_lookup = DataFrame(
    PCTESTCD = ["XAN", "DOSE"],
    PARAMCD = ["XAN", "DOSE"],
    PARAM = ["Pharmacokinetic concentration of Xanomeline", "Xanomeline Patch Dose"],
    PARAMN = [1, 2]
)
   
dose_freq_lookup = DataFrame(
    CDISC_VALUE = ["QD", "BID", "TID", "QID"],
    HOURS_BETWEEN_DOSES = [24, 12, 8, 6]
)
adsl_subset = @chain adsl begin
    @select :STUDYID :USUBJID :TRTSDT :TRTSDTM :TRT01P :TRT01A
end
first(adsl_subset, 5)

5×6 DataFrame

Row	STUDYID	USUBJID	TRTSDT	TRTSDTM	TRT01P	TRT01A
	String15	String15	Date?	DateTime?	String31	String31
1	CDISCPILOT01	01-701-1015	2014-01-02	2014-01-02T00:00:00	Placebo	Placebo
2	CDISCPILOT01	01-701-1023	2012-08-05	2012-08-05T00:00:00	Placebo	Placebo
3	CDISCPILOT01	01-701-1028	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose
4	CDISCPILOT01	01-701-1033	2014-03-18	2014-03-18T00:00:00	Xanomeline Low Dose	Xanomeline Low Dose
5	CDISCPILOT01	01-701-1034	2014-07-01	2014-07-01T00:00:00	Xanomeline High Dose	Xanomeline High Dose

PC data preparation

pc_prep = @chain pc begin
    leftjoin(adsl_subset, on = [:STUDYID, :USUBJID])

    @rtransform :ADTM = parse_datetime_column(:PCDTC)
    @rtransform :ADT = Date(:ADTM)
    @rtransform :ATM = Time(:ADTM)
    @rtransform :ADY = Dates.value(:ADT - :TRTSDT) + 1
    
    @rtransform begin
        :EVID = 0
        :DRUG = :PCTEST
        :NFRLT = :PCTPTNUM < 0 ? 0 : :PCTPTNUM 
    end
end
first(pc_prep, 5)

5×31 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT
	String15	String3	String15	Float64	String3	String15	String31	String7	String31	Float64?	String7	String15	String7	Float64	String15	Float64	String31	Float64	String31	Float64	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String15	Real
1	CDISCPILOT01	PC	01-701-1015	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-01-01T23:30:00	-1.0	Pre-dose	-0.5	2014-01-02	2014-01-02T00:00:00	Placebo	Placebo	2014-01-01T23:30:00	2014-01-01	23:30:00	0	0	XANOMELINE	0
2	CDISCPILOT01	PC	01-701-1015	2.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	missing	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-01-02T00:05:00	1.0	5 Min Post-dose	0.08	2014-01-02	2014-01-02T00:00:00	Placebo	Placebo	2014-01-02T00:05:00	2014-01-02	00:05:00	1	0	XANOMELINE	0.08
3	CDISCPILOT01	PC	01-701-1015	3.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	missing	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-01-02T00:30:00	1.0	30 Min Post-dose	0.5	2014-01-02	2014-01-02T00:00:00	Placebo	Placebo	2014-01-02T00:30:00	2014-01-02	00:30:00	1	0	XANOMELINE	0.5
4	CDISCPILOT01	PC	01-701-1015	4.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	missing	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-01-02T01:00:00	1.0	1h Post-dose	1.0	2014-01-02	2014-01-02T00:00:00	Placebo	Placebo	2014-01-02T01:00:00	2014-01-02	01:00:00	1	0	XANOMELINE	1.0
5	CDISCPILOT01	PC	01-701-1015	5.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	missing	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-01-02T01:30:00	1.0	1.5h Post-dose	1.5	2014-01-02	2014-01-02T00:00:00	Placebo	Placebo	2014-01-02T01:30:00	2014-01-02	01:30:00	1	0	XANOMELINE	1.5

EX data preparation

ex_prep = @chain ex begin
    leftjoin(adsl_subset, on = [:STUDYID, :USUBJID])

    @rsubset :EXDOSE > 0

    @rtransform :ASTDTM = parse_datetime_column(:EXSTDTC)
    @rtransform @passmissing :AENDTM = parse_datetime_column(:EXENDTC)
    @rtransform :ASTDT = Date(:ASTDTM)
    @rtransform @passmissing :AENDT = Date(:AENDTM)
    @rtransform :AENDTM = ismissing(:AENDTM) ? :ASTDTM : :AENDTM

    @rtransform begin
        :EVID = 1
        :NFRLT = :VISITDY == 1 ? 0 : 24 * :VISITDY
    end
end
first(ex_prep, 5)

5×27 DataFrame

Row	STUDYID	DOMAIN	USUBJID	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITNUM	VISIT	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	TRTSDT	TRTSDTM	TRT01P	TRT01A	ASTDTM	AENDTM	ASTDT	AENDT	EVID	NFRLT
	String15	String3	String15	Float64	String15	Float64	String3	String7	String3	String15	Float64	String15	Float64	String15	String15?	Float64	Float64?	Date?	DateTime?	String31?	String31?	DateTime	DateTime	Date	Date?	Int64	Real
1	CDISCPILOT01	EX	01-701-1028	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	1	0
2	CDISCPILOT01	EX	01-701-1028	2.0	XANOMELINE	81.0	mg	PATCH	QD	TRANSDERMAL	4.0	WEEK 2	14.0	2013-08-02	2014-01-06	15.0	172.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-08-02T00:00:00	2014-01-06T00:00:00	2013-08-02	2014-01-06	1	336.0
3	CDISCPILOT01	EX	01-701-1028	3.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	12.0	WEEK 24	168.0	2014-01-07	2014-01-14	173.0	180.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2014-01-07T00:00:00	2014-01-14T00:00:00	2014-01-07	2014-01-14	1	4032.0
4	CDISCPILOT01	EX	01-701-1033	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2014-03-18	2014-03-31	1.0	14.0	2014-03-18	2014-03-18T00:00:00	Xanomeline Low Dose	Xanomeline Low Dose	2014-03-18T00:00:00	2014-03-31T00:00:00	2014-03-18	2014-03-31	1	0
5	CDISCPILOT01	EX	01-701-1034	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2014-07-01	2014-07-15	1.0	15.0	2014-07-01	2014-07-01T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2014-07-01T00:00:00	2014-07-15T00:00:00	2014-07-01	2014-07-15	1	0

Expanding dose

ex_exp = @chain ex_prep begin

    leftjoin(dose_freq_lookup, on = [:EXDOSFRQ => :CDISC_VALUE])
    expand_dose_dataframe

    @rtransform :AVISIT = "Day $(:AVISITN)"
    @rtransform :DRUG = :EXTRT

    @rtransform :ADT = Date(:ADTM)
    @rtransform :ATM = Time(:ADTM)
    @rtransform :ASTTM = Time(:ASTDTM)
    @rtransform :AENTM = Time(:AENDTM)
    @rtransform :ADY = Dates.value(:ADT - :TRTSDT) + 1
end     

first(ex_exp, 5)

5×38 DataFrame

Row	STUDYID	DOMAIN	USUBJID	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITNUM	VISIT	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	TRTSDT	TRTSDTM	TRT01P	TRT01A	ASTDTM	AENDTM	ASTDT	AENDT	EVID	NFRLT	HOURS_BETWEEN_DOSES	ADTM	AVISITN	DOSE_INDEX	AVISIT	DRUG	ADT	ATM	ASTTM	AENTM	ADY
	String15	String3	String15	Float64	String15	Float64	String3	String7	String3	String15	Float64	String15	Float64	String15	String15?	Float64	Float64?	Date	DateTime	String31	String31	DateTime	DateTime	Date	Date?	Int64	Float64	Int64	DateTime	Float64	Int64	String	String15	Date	Time	Time	Time	Int64
1	CDISCPILOT01	EX	01-701-1028	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	1	0.0	24	2013-07-19T00:00:00	1.0	1	Day 1.0	XANOMELINE	2013-07-19	00:00:00	00:00:00	00:00:00	1
2	CDISCPILOT01	EX	01-701-1028	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	1	24.0	24	2013-07-20T00:00:00	2.0	2	Day 2.0	XANOMELINE	2013-07-20	00:00:00	00:00:00	00:00:00	2
3	CDISCPILOT01	EX	01-701-1028	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	1	48.0	24	2013-07-21T00:00:00	3.0	3	Day 3.0	XANOMELINE	2013-07-21	00:00:00	00:00:00	00:00:00	3
4	CDISCPILOT01	EX	01-701-1028	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	1	72.0	24	2013-07-22T00:00:00	4.0	4	Day 4.0	XANOMELINE	2013-07-22	00:00:00	00:00:00	00:00:00	4
5	CDISCPILOT01	EX	01-701-1028	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	3.0	BASELINE	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	1	96.0	24	2013-07-23T00:00:00	5.0	5	Day 5.0	XANOMELINE	2013-07-23	00:00:00	00:00:00	00:00:00	5

Filter ex_expanded for valid doses and ADTM

adpc_first_dose = @chain ex_exp begin

    @rsubset :EXDOSE > 0 && !ismissing(:ADTM)

    combine(groupby(_,[:STUDYID, :USUBJID, :DRUG]),:ADTM => (x->minimum(x)) => :FANLDTM)

    leftjoin(pc_prep,_,on = [:STUDYID, :USUBJID, :DRUG], makeunique=true)

    @rsubset !ismissing(:FANLDTM)

    @rtransform :AVISITN = (:NFRLT ÷ 24) + 1
    @rtransform :AVISIT = "Day $(:AVISITN)"
end
first(adpc_first_dose, 5)

5×34 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT
	String15	String3	String15	Float64	String3	String15	String31	String7	String31	Float64?	String7	String15	String7	Float64	String15	Float64	String31	Float64	String31	Float64	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String15	Real	DateTime?	Real	String
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1
2	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0
3	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0
4	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0
5	CDISCPILOT01	PC	01-701-1028	5.0	XAN	XANOMELINE	1.18750587834024	ug/ml	1.18750587834024	1.18751	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:30:00	1.0	1.5h Post-dose	1.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:30:00	2013-07-19	01:30:00	1	0	XANOMELINE	1.5	2013-07-19T00:00:00	1.0	Day 1.0

Find Previous Dose

Find Next Dose

adpc_nom_next = @chain adpc_first_dose begin
    find_previous_dose(ex_exp)
    find_next_dose(ex_exp)
    find_previous_nominal_time(ex_exp)
    find_next_nominal_time(ex_exp)
end
first(adpc_nom_next, 5)

5×44 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next
	String15	String3	String	Float64	String3	String15	String31	String7	String31	Float64?	String7	String15	String7	Float64	String15	Float64	String31	Float64	String31	Float64	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String15	Real	DateTime?	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0
2	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0
3	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0
4	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0
5	CDISCPILOT01	PC	01-701-1028	5.0	XAN	XANOMELINE	1.18750587834024	ug/ml	1.18750587834024	1.18751	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:30:00	1.0	1.5h Post-dose	1.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:30:00	2013-07-19	01:30:00	1	0	XANOMELINE	1.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0

Derive Relative Time Variables

adpc_arrlt = vcat(adpc_nom_next, ex_exp,cols = :union)
adpc_arrlt.USUBJID = String.(adpc_arrlt.USUBJID)
adpc_arrlt.DRUG = String.(adpc_arrlt.DRUG)

adpc_arrlt = @chain adpc_arrlt begin
    @groupby([:USUBJID, :DRUG])
    @transform(
        :maxdate = begin
            ev0_idx = (:EVID .== 0) .&& .!ismissing.(:ADT)
            isempty(:ADT[ev0_idx]) ? missing : maximum(:ADT[ev0_idx])
        end,
        :FANLDTM = minimum(skipmissing(:FANLDTM)),
        :min_NFRLT = minimum(skipmissing(:NFRLT_prev))
    )

    @rsubset(:ADT <= :maxdate || ismissing(:maxdate))

    @rtransform :AFRLT = ismissing(:FANLDTM) || ismissing(:ADTM) ?
                         missing : Dates.value(:ADTM - :FANLDTM) / 3600000

    @rtransform :ARRLT = ismissing(:ADTM_prev) || ismissing(:ADTM) ?
                         missing : Dates.value(:ADTM - :ADTM_prev) / 3600000

    @rtransform :AXRLT = ismissing(:ADTM_next) || ismissing(:ADTM) ?
                         missing : Dates.value(:ADTM - :ADTM_next) / 3600000

    @rtransform :ARRLT = (:EVID == 1) ? 0 : ismissing(:ARRLT) ? :AXRLT : :ARRLT

    @rtransform :PCRFTDTM = (:EVID == 1) ? :ADTM :
                            ismissing(:ADTM_prev) ? :ADTM_next : :ADTM_prev

    @rtransform @passmissing begin
        :FANLDT = Date(:FANLDTM)
        :FANLTM = Time(:FANLDTM)
        :PCRFTDT = Date(:PCRFTDTM)
        :PCRFTTM = Time(:PCRFTDTM)
    end
end
first(adpc_arrlt, 5)

5×74 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Real	Float64?	DateTime	Date	Time	Date	Time
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	-0.5	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00
2	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.0833333	0.0833333	-23.9167	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00
3	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.5	0.5	-23.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00
4	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.0	1.0	-23.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00
5	CDISCPILOT01	PC	01-701-1028	5.0	XAN	XANOMELINE	1.18750587834024	ug/ml	1.18750587834024	1.18751	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:30:00	1.0	1.5h Post-dose	1.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:30:00	2013-07-19	01:30:00	1	0	XANOMELINE	1.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.5	1.5	-22.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00

Derive Nominal Relative Time from Reference Dose (NRRLT)

adpc_nrrlt = @chain adpc_arrlt begin
    @rtransform begin
        :NRRLT = :EVID == 1 ? 0.0 :
                    ismissing(:NFRLT_prev) ? :NFRLT - :min_NFRLT :
                    :NFRLT - :NFRLT_prev

        :NXRLT = :EVID == 1 ? 0.0 :
                    :NFRLT - :NFRLT_next
    end
end
first(adpc_nrrlt, 5)

5×76 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	NXRLT
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Real	Float64?	DateTime	Date	Time	Date	Time	Float64	Float64?
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	-0.5	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0
2	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.0833333	0.0833333	-23.9167	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	-23.92
3	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.5	0.5	-23.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	-23.5
4	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.0	1.0	-23.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	-23.0
5	CDISCPILOT01	PC	01-701-1028	5.0	XAN	XANOMELINE	1.18750587834024	ug/ml	1.18750587834024	1.18751	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:30:00	1.0	1.5h Post-dose	1.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:30:00	2013-07-19	01:30:00	1	0	XANOMELINE	1.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.5	1.5	-22.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.5	-22.5

Derive Analysis Variables

adpc_aval = @chain adpc_nrrlt begin
    @rtransform :PARCAT1 = :PCSPEC

    @rtransform :ATPTN = (:EVID == 1) ? 0 : :PCTPTNUM
    @rtransform :ATPT = (:EVID == 1) ? "Dose" : :PCTPT

    @rtransform :ATPTREF = (:EVID == 1) ? :AVISIT : (ismissing(:AVISIT_prev) ? :AVISIT_next : :AVISIT_prev)

    @rtransform :ABLFL = (:ATPT == "Pre-dose") ? "Y" : missing

    @rtransform :BASETYPE = string(:ATPTREF, " Baseline")

    @rtransform :DOSEA = (:EVID == 1) ? :EXDOSE : (ismissing(:EXDOSE_prev) ? :EXDOSE_next : :EXDOSE_prev)

    @rtransform :DOSEP = (:TRT01P == "Xanomeline High Dose") ? 81 : ((:TRT01P == "Xanomeline Low Dose") ? 54 : missing)
    @rtransform :DOSEU = "mg"

    @rtransform :FRLTU = "h"
    @rtransform :RRLTU = "h"

    @rtransform :PARAMCD = coalesce(:PCTESTCD, "DOSE")
    @rtransform :ALLOQ = :PCLLOQ

    @rtransform :AVAL = 
        (:EVID == 1) ? :EXDOSE :
        ((:PCSTRESC == "<BLQ" && !ismissing(:NFRLT) && :NFRLT == 0) ? 0 :
        ((:PCSTRESC == "<BLQ" && !ismissing(:NFRLT) && :NFRLT > 0) ? 0.5 * :ALLOQ :
        (!ismissing(:PCSTRESN) ? :PCSTRESN : missing)))

    @rtransform :AVALU = (:EVID == 1) ? :EXDOSU : :PCSTRESU

    @rtransform :AVALCAT1 = (!ismissing(:PCSTRESC) && :PCSTRESC == "<BLQ") ? "<BLQ" :
                             (ismissing(:AVAL) ? missing : string(round(:AVAL, sigdigits=3)))

    @rtransform :SRCDOM = :DOMAIN
    @rtransform :SRCVAR = "SEQ"
    @rtransform :SRCSEQ = coalesce(:PCSEQ, :EXSEQ)
end
first(adpc_aval, 5)

5×95 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	NXRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Real	Float64?	DateTime	Date	Time	Date	Time	Float64	Float64?	String7?	Real	Abstract…	String	String?	String	Float64	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	-0.5	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0
2	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.0833333	0.0833333	-23.9167	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	-23.92	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0
3	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.5	0.5	-23.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	-23.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0
4	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.0	1.0	-23.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	-23.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0
5	CDISCPILOT01	PC	01-701-1028	5.0	XAN	XANOMELINE	1.18750587834024	ug/ml	1.18750587834024	1.18751	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:30:00	1.0	1.5h Post-dose	1.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:30:00	2013-07-19	01:30:00	1	0	XANOMELINE	1.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.5	1.5	-22.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.5	-22.5	PLASMA	1.5	1.5h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	1.18751	ug/ml	1.19	PC	SEQ	5.0

Create DTYPE copy records

dtype = @chain adpc_aval begin
    @subset(
        .!ismissing.(:NFRLT) .&& :NFRLT .> 0 .&&
        .!ismissing.(:NXRLT) .&& :NXRLT .== 0 .&&
        .!ismissing.(:EVID) .&& :EVID .== 0 .&&
        .!ismissing.(:AVISIT_next)
    )
    
    @select Not([:PCRFTDT, :PCRFTTM])
    
    @rtransform(
        :ABLFL = "Y",
        :ATPTREF = :AVISIT_next,
        :ARRLT = :AXRLT,
        :NRRLT = :NXRLT,
        :PCRFTDTM = :ADTM_next,
        :DOSEA = :EXDOSE_next,
        :BASETYPE = string(:AVISIT_next, " Baseline"),
        :ATPT = "Pre-dose",
        :ATPTN = -0.5,
        :DTYPE = "COPY"
    )
    
  @rtransform begin
        :PCRFTDT = ismissing(:PCRFTDTM) ? missing : Date(:PCRFTDTM)
        :PCRFTTM = ismissing(:PCRFTDTM) ? missing : Dates.format(:PCRFTDTM, "HH:MM:SS")
    end
end
first(dtype, 5)

5×96 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	NRRLT	NXRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	PCRFTDT	PCRFTTM
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Float64?	Float64?	DateTime?	Date	Time	Float64?	Float64?	String7?	Float64	String	String?	String	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String	Date	String
1	CDISCPILOT01	PC	01-701-1028	12.0	XAN	XANOMELINE	0.0107062734363561	ug/ml	0.0107062734363561	0.0107063	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-20T00:00:00	2.0	24h Post-dose	24.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-20T00:00:00	2013-07-20	00:00:00	2	0	XANOMELINE	24.0	2013-07-19T00:00:00	2.0	Day 2.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	24.0	0.0	0.0	2013-07-20T00:00:00	2013-07-19	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 2.0	Y	Day 2.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.0107063	ug/ml	0.0107	PC	SEQ	12.0	COPY	2013-07-20	00:00:00
2	CDISCPILOT01	PC	01-701-1028	14.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	missing	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-21T00:00:00	3.0	48h Post-dose	48.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-21T00:00:00	2013-07-21	00:00:00	3	0	XANOMELINE	48.0	2013-07-19T00:00:00	3.0	Day 3.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 3.0	2013-07-21T00:00:00	2013-08-01T00:00:00	24.0	48.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	48.0	0.0	0.0	2013-07-21T00:00:00	2013-07-19	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 3.0	Y	Day 3.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.005	ug/ml	<BLQ	PC	SEQ	14.0	COPY	2013-07-21	00:00:00
3	CDISCPILOT01	PC	01-701-1033	12.0	XAN	XANOMELINE	0.0178368122132096	ug/ml	0.0178368122132096	0.0178368	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-03-19T00:00:00	2.0	24h Post-dose	24.0	2014-03-18	2014-03-18T00:00:00	Xanomeline Low Dose	Xanomeline Low Dose	2014-03-19T00:00:00	2014-03-19	00:00:00	2	0	XANOMELINE	24.0	2014-03-18T00:00:00	2.0	Day 2.0	Day 1.0	2014-03-18T00:00:00	2014-03-31T00:00:00	54.0	54.0	Day 2.0	2014-03-19T00:00:00	2014-03-31T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2014-03-20	0.0	24.0	0.0	0.0	2014-03-19T00:00:00	2014-03-18	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 2.0	Y	Day 2.0 Baseline	54.0	54	mg	h	h	XAN	0.01	0.0178368	ug/ml	0.0178	PC	SEQ	12.0	COPY	2014-03-19	00:00:00
4	CDISCPILOT01	PC	01-701-1033	14.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	missing	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-03-20T00:00:00	3.0	48h Post-dose	48.0	2014-03-18	2014-03-18T00:00:00	Xanomeline Low Dose	Xanomeline Low Dose	2014-03-20T00:00:00	2014-03-20	00:00:00	3	0	XANOMELINE	48.0	2014-03-18T00:00:00	3.0	Day 3.0	Day 2.0	2014-03-19T00:00:00	2014-03-31T00:00:00	54.0	54.0	Day 3.0	2014-03-20T00:00:00	2014-03-31T00:00:00	24.0	48.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2014-03-20	0.0	48.0	0.0	0.0	2014-03-20T00:00:00	2014-03-18	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 3.0	Y	Day 3.0 Baseline	54.0	54	mg	h	h	XAN	0.01	0.005	ug/ml	<BLQ	PC	SEQ	14.0	COPY	2014-03-20	00:00:00
5	CDISCPILOT01	PC	01-701-1034	12.0	XAN	XANOMELINE	0.0138327547613956	ug/ml	0.0138327547613956	0.0138328	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2014-07-02T00:00:00	2.0	24h Post-dose	24.0	2014-07-01	2014-07-01T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2014-07-02T00:00:00	2014-07-02	00:00:00	2	0	XANOMELINE	24.0	2014-07-01T00:00:00	2.0	Day 2.0	Day 1.0	2014-07-01T00:00:00	2014-07-15T00:00:00	54.0	54.0	Day 2.0	2014-07-02T00:00:00	2014-07-15T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2014-07-03	0.0	24.0	0.0	0.0	2014-07-02T00:00:00	2014-07-01	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 2.0	Y	Day 2.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.0138328	ug/ml	0.0138	PC	SEQ	12.0	COPY	2014-07-02	00:00:00

Combine original records and DTYPE copy records

adpc_dtype = @chain vcat(adpc_aval, dtype; cols = :union) begin
    sort!([:STUDYID, :USUBJID, :BASETYPE, :ADTM, :NFRLT])

    @rtransform :MRRLT = (!ismissing(:ARRLT) && :ARRLT < 0) ? 0 : :ARRLT

    @rtransform :ANL01FL = "Y"

    @rtransform :ANL02FL = ismissing(:DTYPE) ? "Y" : missing
end
first(adpc_dtype, 5)

5×99 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	NXRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	MRRLT	ANL01FL	ANL02FL
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Real?	Float64?	DateTime?	Date	Time	Date	Any	Float64?	Float64?	String7?	Real	Abstract…	String?	String?	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String?	Real	String	String?
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	-0.5	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0	missing	0	Y	Y
2	CDISCPILOT01	EX	01-701-1028	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	BASELINE	3.0	missing	missing	missing	missing	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-07-19	00:00:00	1	1	XANOMELINE	0.0	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	24	1	00:00:00	00:00:00	2013-07-21	0.0	0.0	0	missing	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	missing	0	Dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	DOSE	missing	54.0	mg	54.0	EX	SEQ	1.0	missing	0	Y	Y
3	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.0833333	0.0833333	-23.9167	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	-23.92	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0	missing	0.0833333	Y	Y
4	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.5	0.5	-23.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	-23.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0	missing	0.5	Y	Y
5	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.0	1.0	-23.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	-23.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0	missing	1.0	Y	Y

Derive BASE and Calculate Change from Baseline

adpc_base = @chain adpc_dtype begin
    groupby([:STUDYID, :USUBJID, :PARAMCD, :PARCAT1, :BASETYPE])
    @transform :BASE = begin
        base_vals = :AVAL[ .!ismissing.(:ABLFL) .& (:ABLFL .== "Y") ]
        isempty(base_vals) ? missing : base_vals[1]
    end
end
first(adpc_base, 5)

5×100 DataFrame

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	NXRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	MRRLT	ANL01FL	ANL02FL	BASE
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Real?	Float64?	DateTime?	Date	Time	Date	Any	Float64?	Float64?	String7?	Real	Abstract…	String?	String?	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String?	Real	String	String?	Float64?
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	-0.5	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0	missing	0	Y	Y	0.0
2	CDISCPILOT01	EX	01-701-1028	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	BASELINE	3.0	missing	missing	missing	missing	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-07-19	00:00:00	1	1	XANOMELINE	0.0	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	24	1	00:00:00	00:00:00	2013-07-21	0.0	0.0	0	missing	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	missing	0	Dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	DOSE	missing	54.0	mg	54.0	EX	SEQ	1.0	missing	0	Y	Y	missing
3	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.0833333	0.0833333	-23.9167	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	-23.92	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0	missing	0.0833333	Y	Y	0.0
4	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.5	0.5	-23.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	-23.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0	missing	0.5	Y	Y	0.0
5	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.0	1.0	-23.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	-23.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0	missing	1.0	Y	Y	0.0

Calculate CHG for post-baseline records

adpc_chg = @chain adpc_base begin
    @rtransform :CHG = (!ismissing(:AVISITN) && :AVISITN > 0 && !ismissing(:AVAL) && !ismissing(:BASE)) ?
                        :AVAL - :BASE : missing
end
first(adpc_chg, 5)

5×101 DataFrame

1 column omitted

Row	STUDYID	DOMAIN	USUBJID	PCSEQ	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	EVID	DRUG	NFRLT	FANLDTM	AVISITN	AVISIT	AVISIT_prev	ADTM_prev	AENDTM_prev	EXDOSE_prev	EXDOSE_next	AVISIT_next	ADTM_next	AENDTM_next	NFRLT_prev	NFRLT_next	EXSEQ	EXTRT	EXDOSE	EXDOSU	EXDOSFRM	EXDOSFRQ	EXROUTE	VISITDY	EXSTDTC	EXENDTC	EXSTDY	EXENDY	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	maxdate	min_NFRLT	AFRLT	ARRLT	AXRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	NXRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	MRRLT	ANL01FL	ANL02FL	BASE	⋯
	String15	String3	String	Float64?	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Int64	String	Real	DateTime	Real	String	String?	DateTime?	DateTime?	Float64?	Float64?	String?	DateTime?	DateTime?	Float64?	Float64?	Float64?	String15?	Float64?	String3?	String7?	String3?	String15?	Float64?	String15?	String15?	Float64?	Float64?	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Date	Float64	Float64	Real?	Float64?	DateTime?	Date	Time	Date	Any	Float64?	Float64?	String7?	Real	Abstract…	String?	String?	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String?	Real	String	String?	Float64?	⋯
1	CDISCPILOT01	PC	01-701-1028	1.0	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	XANOMELINE	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	54.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	missing	0.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	-0.5	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0	missing	0	Y	Y	0.0	⋯
2	CDISCPILOT01	EX	01-701-1028	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	BASELINE	3.0	missing	missing	missing	missing	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-07-19	00:00:00	1	1	XANOMELINE	0.0	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	1.0	XANOMELINE	54.0	mg	PATCH	QD	TRANSDERMAL	1.0	2013-07-19	2013-08-01	1.0	14.0	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	24	1	00:00:00	00:00:00	2013-07-21	0.0	0.0	0	missing	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	0.0	missing	0	Dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	DOSE	missing	54.0	mg	54.0	EX	SEQ	1.0	missing	0	Y	Y	missing	⋯
3	CDISCPILOT01	PC	01-701-1028	2.0	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0	XANOMELINE	0.08	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.0833333	0.0833333	-23.9167	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	-23.92	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0	missing	0.0833333	Y	Y	0.0	⋯
4	CDISCPILOT01	PC	01-701-1028	3.0	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0	XANOMELINE	0.5	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	0.5	0.5	-23.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	-23.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0	missing	0.5	Y	Y	0.0	⋯
5	CDISCPILOT01	PC	01-701-1028	4.0	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	0	XANOMELINE	1.0	2013-07-19T00:00:00	1.0	Day 1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	54.0	54.0	Day 2.0	2013-07-20T00:00:00	2013-08-01T00:00:00	0.0	24.0	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	2013-07-21	0.0	1.0	1.0	-23.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	-23.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0	missing	1.0	Y	Y	0.0	⋯

Add ASEQ

adpc_aseq = @chain adpc_chg begin
           groupby([:STUDYID, :USUBJID])
           @transform :ASEQ = 1:length(:STUDYID)


           @select(Not([:DOMAIN, :PCSEQ, :DRUG, :EVID, :AXRLT, :NXRLT, :VISITDY]))
           @select(Not(r"^orig"))
           @select(Not(r"^min"))
           @select(Not(r"^max"))
           @select(Not(r"^EX"))
           @select(Not(r"next$"))
           @select(Not(r"prev$"))

           leftjoin(param_lookup[:, Not(:PCTESTCD)], on = :PARAMCD) 
       end
first(adpc_aseq, 5)

5×74 DataFrame

Row	STUDYID	USUBJID	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	NFRLT	FANLDTM	AVISITN	AVISIT	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	AFRLT	ARRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	MRRLT	ANL01FL	ANL02FL	BASE	CHG	ASEQ	PARAM	PARAMN
	String15	String	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Real	DateTime	Real	String	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Float64	Real?	DateTime?	Date	Time	Date	Any	Float64?	String7?	Real	Abstract…	String?	String?	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String?	Real	String	String?	Float64?	Float64?	Int64	String?	Int64?
1	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	missing	missing	missing	missing	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0	missing	0	Y	Y	0.0	0.0	1	Pharmacokinetic concentration of Xanomeline	1
2	CDISCPILOT01	01-701-1028	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	BASELINE	3.0	missing	missing	missing	missing	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-07-19	00:00:00	1	0.0	2013-07-19T00:00:00	1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	24	1	00:00:00	00:00:00	0.0	0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	missing	0	Dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	DOSE	missing	54.0	mg	54.0	EX	SEQ	1.0	missing	0	Y	Y	missing	missing	2	Xanomeline Patch Dose	2
3	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0.08	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	0.0833333	0.0833333	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0	missing	0.0833333	Y	Y	0.0	0.101566	3	Pharmacokinetic concentration of Xanomeline	1
4	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0.5	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	0.5	0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0	missing	0.5	Y	Y	0.0	0.546902	4	Pharmacokinetic concentration of Xanomeline	1
5	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	1.0	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	1.0	1.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0	missing	1.0	Y	Y	0.0	0.925465	5	Pharmacokinetic concentration of Xanomeline	1

Derive additional baselines from VS

vs_height = @chain vs begin
    @rsubset :VSTESTCD == "HEIGHT"
    @select :STUDYID :USUBJID :VSSTRESN :VSSTRESU
    rename(:VSSTRESN => :HTBL, :VSSTRESU => :HTBLU)
end

vs_weight = @chain vs begin
    @rsubset :VSTESTCD == "WEIGHT" && :VSBLFL == "Y"
    @select :STUDYID :USUBJID :VSSTRESN :VSSTRESU
    rename(:VSSTRESN => :WTBL, :VSSTRESU => :WTBLU)
end

adpc_baselines = @chain adpc_aseq begin
    leftjoin(vs_height, on = [:STUDYID, :USUBJID])
    leftjoin(vs_weight, on = [:STUDYID, :USUBJID])
    @rtransform begin
        :BMIBL = (!ismissing(:HTBL) && !ismissing(:WTBL) && :HTBL > 0) ?
                 round(:WTBL / ((:HTBL / 100)^2), digits=2) : missing
        :BMIBLU = "kg/m^2"
    end
end
first(adpc_baselines, 5)

5×80 DataFrame

Row	STUDYID	USUBJID	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	NFRLT	FANLDTM	AVISITN	AVISIT	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	AFRLT	ARRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	MRRLT	ANL01FL	ANL02FL	BASE	CHG	ASEQ	PARAM	PARAMN	HTBL	HTBLU	WTBL	WTBLU	BMIBL	BMIBLU
	String15	String	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Real	DateTime	Real	String	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Float64	Real?	DateTime?	Date	Time	Date	Any	Float64?	String7?	Real	Abstract…	String?	String?	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String?	Real	String	String?	Float64?	Float64?	Int64	String?	Int64?	Float64?	String15?	Float64?	String15?	Float64?	String
1	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	missing	missing	missing	missing	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0	missing	0	Y	Y	0.0	0.0	1	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2
2	CDISCPILOT01	01-701-1028	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	BASELINE	3.0	missing	missing	missing	missing	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-07-19	00:00:00	1	0.0	2013-07-19T00:00:00	1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	24	1	00:00:00	00:00:00	0.0	0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	missing	0	Dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	DOSE	missing	54.0	mg	54.0	EX	SEQ	1.0	missing	0	Y	Y	missing	missing	2	Xanomeline Patch Dose	2	177.8	cm	99.34	kg	31.42	kg/m^2
3	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0.08	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	0.0833333	0.0833333	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0	missing	0.0833333	Y	Y	0.0	0.101566	3	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2
4	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0.5	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	0.5	0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0	missing	0.5	Y	Y	0.0	0.546902	4	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2
5	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	1.0	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	1.0	1.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0	missing	1.0	Y	Y	0.0	0.925465	5	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2

Add all ADSL variables

adpc = @chain adpc_baselines begin
    leftjoin(adsl, on = [:STUDYID, :USUBJID], makeunique = true)
end
first(adpc, 5)

5×132 DataFrame

32 columns omitted

Row	STUDYID	USUBJID	PCTESTCD	PCTEST	PCORRES	PCORRESU	PCSTRESC	PCSTRESN	PCSTRESU	PCNAM	PCSPEC	PCLLOQ	VISIT	VISITNUM	PCDTC	PCDY	PCTPT	PCTPTNUM	TRTSDT	TRTSDTM	TRT01P	TRT01A	ADTM	ADT	ATM	ADY	NFRLT	FANLDTM	AVISITN	AVISIT	ASTDTM	AENDTM	ASTDT	AENDT	HOURS_BETWEEN_DOSES	DOSE_INDEX	ASTTM	AENTM	AFRLT	ARRLT	PCRFTDTM	FANLDT	FANLTM	PCRFTDT	PCRFTTM	NRRLT	PARCAT1	ATPTN	ATPT	ATPTREF	ABLFL	BASETYPE	DOSEA	DOSEP	DOSEU	FRLTU	RRLTU	PARAMCD	ALLOQ	AVAL	AVALU	AVALCAT1	SRCDOM	SRCVAR	SRCSEQ	DTYPE	MRRLT	ANL01FL	ANL02FL	BASE	CHG	ASEQ	PARAM	PARAMN	HTBL	HTBLU	WTBL	WTBLU	BMIBL	BMIBLU	SUBJID	RFSTDTC	RFENDTC	RFXSTDTC	RFXENDTC	RFICDTC	RFPENDTC	DTHDTC	DTHFL	SITEID	AGE	AGEU	SEX	RACE	ETHNIC	ARMCD	ARM	ACTARMCD	ACTARM	COUNTRY	⋯
	String15	String	String3?	String15?	String31?	String7?	String31?	Float64?	String7?	String15?	String7?	Float64?	String15	Float64	String31?	Float64?	String31?	Float64?	Date?	DateTime?	String31?	String31?	DateTime	Date	Time	Int64	Real	DateTime	Real	String	DateTime?	DateTime?	Date?	Date?	Int64?	Int64?	Time?	Time?	Float64	Real?	DateTime?	Date	Time	Date	Any	Float64?	String7?	Real	Abstract…	String?	String?	String	Float64?	Int64	String	String	String	Abstract…	Float64?	Real	InlineSt…	String	String3	String	Float64	String?	Real	String	String?	Float64?	Float64?	Int64	String?	Int64?	Float64?	String15?	Float64?	String15?	Float64?	String	String7?	String15?	String15?	String15?	String15?	Missing	String31?	String15?	String3?	String3?	Float64?	String7?	String3?	String?	String31?	String15?	String31?	String15?	String31?	String3?	⋯
1	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	<BLQ	ug/ml	<BLQ	0.0	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-18T23:30:00	-1.0	Pre-dose	-0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-18T23:30:00	2013-07-18	23:30:00	0	0	2013-07-19T00:00:00	1	Day 1	missing	missing	missing	missing	missing	missing	missing	missing	-0.5	-0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	PLASMA	-0.5	Pre-dose	Day 1.0	Y	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0	ug/ml	<BLQ	PC	SEQ	1.0	missing	0	Y	Y	0.0	0.0	1	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2	1028	2013-07-19	2014-01-14	2013-07-19	2014-01-14	missing	2014-01-14T11:10	missing	missing	701	71.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	⋯
2	CDISCPILOT01	01-701-1028	missing	missing	missing	missing	missing	missing	missing	missing	missing	missing	BASELINE	3.0	missing	missing	missing	missing	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:00:00	2013-07-19	00:00:00	1	0.0	2013-07-19T00:00:00	1.0	Day 1.0	2013-07-19T00:00:00	2013-08-01T00:00:00	2013-07-19	2013-08-01	24	1	00:00:00	00:00:00	0.0	0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.0	missing	0	Dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	DOSE	missing	54.0	mg	54.0	EX	SEQ	1.0	missing	0	Y	Y	missing	missing	2	Xanomeline Patch Dose	2	177.8	cm	99.34	kg	31.42	kg/m^2	1028	2013-07-19	2014-01-14	2013-07-19	2014-01-14	missing	2014-01-14T11:10	missing	missing	701	71.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	⋯
3	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.101566224882241	ug/ml	0.101566224882241	0.101566	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:05:00	1.0	5 Min Post-dose	0.08	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:05:00	2013-07-19	00:05:00	1	0.08	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	0.0833333	0.0833333	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.08	PLASMA	0.08	5 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.101566	ug/ml	0.102	PC	SEQ	2.0	missing	0.0833333	Y	Y	0.0	0.101566	3	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2	1028	2013-07-19	2014-01-14	2013-07-19	2014-01-14	missing	2014-01-14T11:10	missing	missing	701	71.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	⋯
4	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.546901773708848	ug/ml	0.546901773708848	0.546902	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T00:30:00	1.0	30 Min Post-dose	0.5	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T00:30:00	2013-07-19	00:30:00	1	0.5	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	0.5	0.5	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	0.5	PLASMA	0.5	30 Min Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.546902	ug/ml	0.547	PC	SEQ	3.0	missing	0.5	Y	Y	0.0	0.546902	4	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2	1028	2013-07-19	2014-01-14	2013-07-19	2014-01-14	missing	2014-01-14T11:10	missing	missing	701	71.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	⋯
5	CDISCPILOT01	01-701-1028	XAN	XANOMELINE	0.925465387145289	ug/ml	0.925465387145289	0.925465	ug/ml	Imaginary Labs	PLASMA	0.01	BASELINE	3.0	2013-07-19T01:00:00	1.0	1h Post-dose	1.0	2013-07-19	2013-07-19T00:00:00	Xanomeline High Dose	Xanomeline High Dose	2013-07-19T01:00:00	2013-07-19	01:00:00	1	1.0	2013-07-19T00:00:00	1.0	Day 1.0	missing	missing	missing	missing	missing	missing	missing	missing	1.0	1.0	2013-07-19T00:00:00	2013-07-19	00:00:00	2013-07-19	00:00:00	1.0	PLASMA	1.0	1h Post-dose	Day 1.0	missing	Day 1.0 Baseline	54.0	81	mg	h	h	XAN	0.01	0.925465	ug/ml	0.925	PC	SEQ	4.0	missing	1.0	Y	Y	0.0	0.925465	5	Pharmacokinetic concentration of Xanomeline	1	177.8	cm	99.34	kg	31.42	kg/m^2	1028	2013-07-19	2014-01-14	2013-07-19	2014-01-14	missing	2014-01-14T11:10	missing	missing	701	71.0	YEARS	M	WHITE	NOT HISPANIC OR LATINO	Xan_Hi	Xanomeline High Dose	Xan_Hi	Xanomeline High Dose	USA	⋯