Waveform data from Philips DWC relational database

[vijaym01]

Hi,

Has anyone pulled the waveform data from Philips DWC relational database? I am looking for assistance in identifying the tables/columns from Philips DWC database needed for waveform data submission.

Thanks,
Vijay

[tompollard]

@bemoody, do you have any suggestions here?

[clermontg]

This is a great question. I can help with this. We are doing this here at Pitt and have put together a de-identification pipeline going from the original DWC 9 tables to a safe harbor version of them. I could provide the layout of those transformed tables. In a nutshell, we are keeping all fields and are time shifting time fields. Time shifting sequencenumbers is a bit tricky, but just needs to be consistent so that an integral number of days are used (to preserve time of day).

[vijaym01]

Can you please share the layout of the tables and if possible, the ETL scripts?
Thank you!
-Vijay

[vijaym01]

Thank you! If you can send the layout of the transformed tables to my email ***@***.******@***.***> that will be great. Thanks, Vijay From: clermontg ***@***.***> Date: Friday, September 15, 2023 at 9:34 AM To: chorus-ai/data_acq_SOP ***@***.***> Cc: Makkakode, Vijayaraghavan ***@***.***>, Author ***@***.***> Subject: Re: [chorus-ai/data_acq_SOP] Waveform data from Philips DWC relational database (Discussion #8) CAUTION: This email originated outside of this organization. This is a great question. I can help with this. We are doing this here at Pitt and have put together a de-identification pipeline going from the original DWC 9 tables to a safe harbor version of them. I could provide the layout of those transformed tables. In a nutshell, we are keeping all fields and are time shifting time fields. Time shifting sequencenumbers is a bit tricky, but just needs to be consistent so that an integral number of days are used (to preserve time of day). — Reply to this email directly, view it on GitHub<https://urldefense.com/v3/__https:/github.com/chorus-ai/data_acq_SOP/discussions/8*discussioncomment-7016210__;Iw!!NuzbfyPwt6ZyPHQ!skivzt-MJWEDPvsdUeIowrVuhJl44LyqF0BzeP7bDBM7ZOlNUXU5CqQfU0AF-BH6x0EPRzGyiZx8z2M6-aAZ2hjubj9qaxv3Zxo_Iw$>, or unsubscribe<https://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BCQJ2NQR5ZAR4LVFJAM75UDX2R7RDANCNFSM6AAAAAA4UAK4KQ__;!!NuzbfyPwt6ZyPHQ!skivzt-MJWEDPvsdUeIowrVuhJl44LyqF0BzeP7bDBM7ZOlNUXU5CqQfU0AF-BH6x0EPRzGyiZx8z2M6-aAZ2hjubj9qaxvvuNkkIg$>. You are receiving this because you authored the thread.Message ID: ***@***.***> CONFIDENTIALITY NOTICE: This e-mail, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

[clermontg]

Yes: We are export 7 or the DWC files: alert, enumeration, enumerationvalue, numerics, numericvalue, wave, wavesample. We do not export the patient, bedtag, and attributes files because, as you will see below, we replace the Philips patientid with an actual studyID and encountered derived from our anonymization pipeline. I printed the field layout and first data line of each of those seven files. As you can see, the field structure is quite close to the original, except for the patientid that has been linked to an actual patient. So, the ETL is simple if you have a lookup table from the DWC patientID to an actual identifier. Maybe your patient file has a medical record number which allows you to link. I also attached the DWC reference file (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_wave.csv Id,TimeStamp,BasePhysioId,PhysioId,Label,Channel,SamplePeriod,IsSlowWave,IsDerived,Color,LowEdgeFrequency,HighEdgeFrequency,ScaleLower,ScaleUpper,CalibrationScaledLower,CalibrationScaledUpper,CalibrationAbsLower,CalibrationAbsUpper,CalibrationType,UnitLabel,UnitCode,EcgLeadPlacement,PatientId 206700646,2020-11-21 19:01:14.096 -05:00,150016,150036,ABP,0,8,false,false,-2344647,,,1760,2826,1840,2640,65.000,115.000,1,mmHg,266016,-1,4789637A-96A2-423E-B6E3-87E81A58B58F (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_wavesample.csv WaveId,TimeStamp,WaveSamples,UnavailableSamples,InvalidSamples,PacedPulses,Fin_Study_Id,Study_Id,New_SequenceNumber 207119154,2020-11-21 21:01:05.000 -05:00,d008da08e708f008f008e508d008b20890086b08480828080b08f107d907c107a90791077a0765075407490744074407460748074907480744073f07390733072e07290726072407230721071f071c07190715070f0709070307fd06f706f306f006ee06ed06ed06ec06ea06e606e006d806d006d206ea062a0792070e088208da080e0926092a09290929092e09370941094609420933091809f608d008aa088608660848082d081508fc07e407cc07b3079b07860777076d076807670768076807670763075d0755074d0746073f073a073507330730072d072a07260721071c0715070e0708070207fe06fb06fa06f906f806f506f106eb06e506e206ed0618076e07e6076708d40822094e09620969096d0974097d0985098a0986097a09640947092409fe08d908b70898087e0866084f0838081f080508ea07d007b807a407970791078f078f078f078e078907820779076f0767075f075a07550752074f074c074907450741073b0735072e07270721071c07180716071407130712070f070a070407ff06010716074f07b3073108ad08100952097509840987098a098f099709a009a609a40996097b0956092c090109d908b60896087a08600847082e081508fc07e307cb07b607a7079d07980796079607940790078a07820779077007690763075f075d075a07570753074e074a07450740073a0734072e07270722071d071b07190718071707150712070d07070704070e0735078707fb077a08e7083409610974097809770978097c098209850981097509600941091d09f608d008ac088c08700856083e0825080b08f107d607be07aa079c07950792079207920790078c0785077c0773076a0763075d0759075607530751074f074c07480743073e07390733072e07290723071e07190716071307110710070f070d0709070307fd06fa0605072d078007f5077408e1082e095a096d0971097109730976097b097c0978096b09550936091209eb08c408a0087f08620848082e081508fc07e207ca07b307a10794078d078b078b078b07890785077e0776076d0764075c075707530751074f074d074b074807440740073b07360731072b07260720071b07160712070f070d070c070b07090705070007fa06f606fc061b076307d1074e08bf081209430958095c095a0959095d0965096c096c0962094d092e090909e008b7089208710855083c0824080c08f307d907c007a9079607880780077c077b077a07770773076c07630759075007480742073e073a073707340731072c07270721071b0715070e0708070207fb06f406ee06e906e406e006dd06db06d806d506d106cb06c406c006c506e5062e079c0718088308ce08f7080509050901090209090914091e0922091d090c09f108ce08a6087e08590837081a080008e707ce07b4079a0781076a0758074d07480748074b074e0750074f074b0746073f07390732072d07290726072407230721071f071d07190714070f0709070307fc06f706f406f106f006ef06ef06ef06ee06eb06e506dd06d406cf06da0605075b07d1074a08ae08ef080f0917091409110913091a0923092c092d0925091209f508d108aa0884086108420826080b08f107d707bd07a4078d077a076d0766076407660769076a07690765075e0757074f07490745074207410740073e073c07390735073007290722071b0713070d07090706070407020702070007fc06f606ee06e706e506f40625078207ff077f08e8082f09560967096d097109780983098e099409,,,,1009187,ODPCOBE,659296007936 (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_numeric.csv Id,TimeStamp,BasePhysioId,PhysioId,Label,IsAperiodic,UnitLabel,Validity,LowerLimit,UpperLimit,IsAlarmingOff,SubPhysioId,SubLabel,Color,IsManual,MaxValues,Scale 609216720,2020-11-21 22:59:43.037 -05:00,131840,131840,ST,false,mm,0,-2.000,2.000,true,131841,ST-I,-16711936,false,12,1 (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_numericvalue.csv Study_Id,Fin_Study_Id,NumericId,TimeStamp,New_SequenceNumber,IsTrendUploaded,CompoundValueId,Value ODPCOBE,1009187,609472273,2020-11-21 20:15:01.000 -05:00,659293243136,false,3B9D2776-F1AA-4B6E-8F8D-322CED588DBC,1.100 (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_enum.csv Id,TimeStamp,basePhysioId,PhysioId,Label,ValuePhysioId,IsAperiodic,IsManual,Validity,UnitCode,UnitLabel,Color 138505045,2020-11-21 23:11:48.037 -05:00,184321,184321,Annot,148631,false,false,0,262144,Unknwn,-16711936 (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_enumerationvalue.csv Study_Id,Fin_Study_Id,EnumerationId,TimeStamp,CompoundValueId,Value,New_SequenceNumber ODPCOBE,1009187,138851148,2020-11-22 02:22:56.000 -05:00,91A66FCF-B81B-4A0E-93BD-A2BB6C86FB14,Sinus Rhythm,659315315456 (base) ***@***.*** 05012023]$ head -n 2 ODPCOBE_1009187_alert.csv Study_Id,Fin_Study_Id,TimeStamp,AlertId,Source,Code,Label,Severity,Kind,IsSilenced,SubtypeId,AnnounceTime,OnsetTime,EndTime,New_SequenceNumber ODPCOBE,1009187,2020-11-21 19:24:19.000 -05:00,BB6B9B3B-AF3C-4A46-9DE4-EBB830EC6DF8,150036,6704,ABP Change Scale,5,1,false,203312,2020-11-21 19:24:22.000 -05:00,0001-03-22 19:00:00.000 -05:00,2020-11-21 19:24:24.000 -05:00,659290201856 (base) ***@***.*** 05012023]$ From: vijaym01 ***@***.***> Sent: Thursday, September 21, 2023 1:15 PM To: chorus-ai/data_acq_SOP ***@***.***> Cc: Clermont, Gilles ***@***.***>; Comment ***@***.***> Subject: Re: [chorus-ai/data_acq_SOP] Waveform data from Philips DWC relational database (Discussion #8) Can you please share the layout of the tables and if possible, the ETL scripts? Thank you! -Vijay - Reply to this email directly, view it on GitHub<#8 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AL33RKWY3YPV6AFQYBXOMU3X3RY25ANCNFSM6AAAAAA4UAK4KQ>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[vijaym01]

Thank you Gilles for the detailed structure of the files. We currently have all these tables and I am working on de-identification of patient IDs and date shifts. I see that your data is in csv files. Are you going to convert csv files to HDF5 format? If so, do you already have the converter from csv to hdf5?

Thanks,
Vijay

[bemoody]

I think we still need to settle on what that HDF5 format should be. Whether the native DWC data is stored as CSV, BCP, Parquet or something else doesn't make a lot of difference. But the transformation from those formats into HDF5 (or anything else) is not entirely trivial. @clermontg, you did not mention these tables: - PatientMapping - Composite (rev C) - SourceDevice (rev C) I don't know if the Composite and SourceDevice tables will be useful, though our colleagues at Philips have expressed some interest in distinguishing hardware device types. As for PatientMapping, we have found that table invaluable in correcting faulty DST conversion. So I would highly advise that all of these tables should be archived.

[clermontg]

Vijay, We do have a converter to CCDEF version of h5. I am copying Will as he might also have one. Gilles From: vijaym01 ***@***.***> Sent: Wednesday, October 18, 2023 5:13 PM To: chorus-ai/data_acq_SOP ***@***.***> Cc: Clermont, Gilles ***@***.***>; Comment ***@***.***> Subject: Re: [chorus-ai/data_acq_SOP] Waveform data from Philips DWC relational database (Discussion #8) Thank you Gilles for the detailed structure of the files. We currently have all these tables and I am working on de-identification of patient IDs and date shifts. I see that your data is in csv files. Are you going to convert csv files to HDF5 format? If so, do you already have the converter from csv to hdf5? Thanks, Vijay - Reply to this email directly, view it on GitHub<#8 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AL33RKXWNMHAB6GEIY773QLYABA6TAVCNFSM6AAAAAA4UAK4KSVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TGMRQGA4DC>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[clermontg]

Hi Gilles and Vijay- The UVA converter works on csv vitals; it's a relatively simple extension to have it work for waveforms, but if Pitt has one already configured (especially one built for Philips) I would recommend starting there. -Will From: Clermont, Gilles ***@***.***> Sent: Thursday, October 19, 2023 1:58 PM To: chorus-ai/data_acq_SOP ***@***.***>; chorus-ai/data_acq_SOP ***@***.***> Cc: Comment ***@***.***>; Ashe, William B *HS ***@***.***> Subject: RE: [chorus-ai/data_acq_SOP] Waveform data from Philips DWC relational database (Discussion #8) Vijay, We do have a converter to CCDEF version of h5. I am copying Will as he might also have one. Gilles From: vijaym01 ***@***.******@***.***>> Sent: Wednesday, October 18, 2023 5:13 PM To: chorus-ai/data_acq_SOP ***@***.******@***.***>> Cc: Clermont, Gilles ***@***.******@***.***>>; Comment ***@***.******@***.***>> Subject: Re: [chorus-ai/data_acq_SOP] Waveform data from Philips DWC relational database (Discussion #8) Thank you Gilles for the detailed structure of the files. We currently have all these tables and I am working on de-identification of patient IDs and date shifts. I see that your data is in csv files. Are you going to convert csv files to HDF5 format? If so, do you already have the converter from csv to hdf5? Thanks, Vijay - Reply to this email directly, view it on GitHub<#8 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AL33RKXWNMHAB6GEIY773QLYABA6TAVCNFSM6AAAAAA4UAK4KSVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM3TGMRQGA4DC>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>

[bemoody]

The UVA Converter - I assume that is the same as https://github.com/Ostrich-Emulators/PreVent ? I have not used it but I do not believe it has any capability to handle DWC. It has some classes called "DwcReader" and "DWCxReader" but these are for reading WFDB and CSV files, not the DWC native format.