A Functional Dataset Toolkit

Alan Calvitti PhD
Veterans Administration, San Diego
acalvitti@ucsd.edu

Background

Illustrate methods with real data

• We study clinical workflow, computer use, doctor-patient communication, IT redesign*

*opportunities for WL

Methods developed over multiple studies & IT initatives

* Quick and Deuce studies highly ranked by NIH, VA HSRD

Collaborative, cloud-based, NoSQL workflow

• Leverage web filesystem // Google Drive: web linked data vs SQL

• Open // Colleagues use: R, Python // formats: CSV, coeditable gsheets

• Agile // Combine human + machine readable metadata

Volume * Variety * Velocity * Veracity

• Here not Big Data: ~10GB scale data

• Variety/lack of structure is key bottleneck // numeric + text + time series + metadata // UI events, eyetracking, human-coded video, clinical data

Mathematica 10 innovations for data

Dataset, Association, Merge, RightComposition, Operator form, named slots..

• Functional // avoid helper functions // clean, merge, stats, viz inline by Query composition 
• Rapid development // exploratory code on small samples - then scale up

• Flexible query patterns // recursive trie* - linear time complexity

• Compact code // easier to read, maintain, adapt than V9

Indexing // leverage your filesys, web-linked data

Import Google gsheet

gsheetToCSV[path_String]:= "https://docs.google.com/spreadsheets/d/"<> (Association[Import[path,"JSON"]]//Lookup[#,"doc_id",Lookup[#,"resource_id"]]&) <>"/export?format=csv" ;

importGsheetCSV[url_]:= StringReplace[url,pre:"https://docs.google.com/spreadsheets/d/"~~x__~~"/edit#gid=0":> pre <>x<>"/export?format=csv"]// Import[#,"CSV"]& ;

Example index manager

quick[visitDataIndex]= quick[dataIndex]["VisitData"/*keyGroupBy[Most]/* Query[All, KeyMap[Last]]][All,metaHandler][keyValueMap[Prepend[#2,"dir"->#1]&]/* KeyMap[StringRiffle[#,""]&/*camelHumpAcronym]]//Normal//Dataset;

quick[visitDataIndex][All,{"dir","metaTime"/*Values/*Union,"data"/*Keys}]
(Dataset[({{"EHCMAC1", { "EMR","HumanCodedMouseActivity","Coder1" }, { "MoraeSyncTime" }, { "Q001","Q002","Q003","Q004","Q005","Q006","Q007","Q008","Q009","Q010","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q024",(Subscript[⋯, 71]) }}, {"EHCMAC2", { "EMR","HumanCodedMouseActivity","Coder2" }, { "MoraeSyncTime" }, { "Q001","Q002","Q003","Q005","Q006","Q007","Q008","Q009","Q010","Q012","Q013","Q014","Q015","Q017","Q019","Q020","Q021","Q023","Q024","Q026","Q028",(Subscript[⋯, 16]) }}, {"EHCMAC3", { "EMR","HumanCodedMouseActivity","Coder3" }, { "MoraeSyncTime" }, { "Q012","Q044","Q057" }}, {"EHCMAEC1", { "EMR","HumanCodedMouseActivity_EPICTEST","Coder1" }, { "MoraeSyncTime" }, { "Q115","Q116","Q117","Q118","Q119","Q120","Q121" }}, {"EHCTT", { "EMR","HumanCodedTabTransition" }, { "MoraeSyncTime" }, { "Q011","Q023","Q025","Q027","Q037","Q039","Q042","Q048","Q051","Q059","Q064","Q065","Q077","Q078","Q079","Q080","Q091" }}, {"EHSA", { "EMR","HumanSegmentedActivity" }, { "MoraeSyncTime" }, { "Q030" }}, {"EMMP", { "EMR","MoraeMousePath" }, { "MoraeSyncTime" }, { "Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q024","Q026","Q028","Q029","Q030","Q031","Q032","Q033","Q034","Q035","Q036","Q038",(Subscript[⋯, 77]) }}, {"EMSE", { "EMR","MoraeSystemEvents" }, { "MoraeSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q023","Q024","Q025","Q026",(Subscript[⋯, 98]) }}, {"EOES", { "EMR","OrderEntrySegments" }, Failed, {  }}, {"ES1H", { "Eyetracking","Smi10Hz" }, { "SmiSyncTime" }, { "Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q022","Q023","Q024","Q025","Q026","Q027",(Subscript[⋯, 96]) }}, {"HCB", { "HumanCodedBehaviors" }, { "MoraeSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q023","Q024","Q025","Q026",(Subscript[⋯, 65]) }}, {"HCC", { "HumanCodedCues" }, { "MoraeSyncTime" }, { "Q004","Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q023","Q025","Q026",(Subscript[⋯, 77]) }}, {"HCMOT", { "HumanCodedMultitasking","OffTopic" }, { "MoraeSyncTime" }, { "Q007","Q009","Q012","Q013","Q014","Q015","Q016","Q018","Q019","Q021","Q024","Q028","Q032","Q035","Q036","Q038","Q039","Q040","Q041","Q042","Q043",(Subscript[⋯, 10]) }}, {"HCNVC1", { "HumanCodedNonVerbal","Coder1" }, { "MoraeSyncTime" }, { "Q001","Q002","Q003","Q004","Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021",(Subscript[⋯, 86]) }}, {"HCNVC2", { "HumanCodedNonVerbal","Coder2" }, { "MoraeSyncTime" }, { "Q012","Q019","Q028","Q032","Q036","Q044","Q047","Q057","Q066","Q083" }}, {"KA1H", { "Kinect","Audio10Hz" }, { "KinectSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q022","Q023","Q024","Q025",(Subscript[⋯, 100]) }}, {"KB", { "Kinect","Body" }, { "KinectSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q022","Q023","Q024","Q025",(Subscript[⋯, 100]) }}, {"KH", { "Kinect","Head" }, { "KinectSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q022","Q023","Q024","Q025","Q026",(Subscript[⋯, 99]) }}, {"VHSC", { "Vocalization","HumanSegmented","Companion" }, { "MoraeSyncTime" }, { "Q046","Q070","Q078" }}, {"VHSD", { "Vocalization","HumanSegmented","Doctor" }, { "MoraeSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q023","Q024","Q025","Q026",(Subscript[⋯, 62]) }}, {"VHSP", { "Vocalization","HumanSegmented","Patient" }, { "MoraeSyncTime" }, { "Q005","Q006","Q007","Q008","Q009","Q010","Q011","Q012","Q013","Q014","Q015","Q016","Q017","Q018","Q019","Q020","Q021","Q023","Q024","Q025","Q026",(Subscript[⋯, 62]) }}, {"VMCA", { "Vocalization","MicroConeAudio" }, { "MicroconeSyncTime" }, { "Q065","Q066","Q067","Q068","Q069","Q070","Q075","Q076","Q077","Q078","Q079","Q080","Q081","Q082","Q083","Q084","Q085","Q086","Q087","Q088","Q089",(Subscript[⋯, 36]) }}, {3 levels | 22rows, , , }}), Dataset[Association[(_String -> _List)..]]])

Indexing beyond SQL, leverage web data, filesys

Adapt key-value stores to shapes that match data design

• Nesting: multiple patients per doctors per med specialty, 
• Multiple sites & study environments (schema variation)

keyGroupBy[f_][expr_]:=Association/@GroupBy[Normal@expr,Keys/*f];

quick[inex][Catenate/*Union][GroupBy[quick@visitClinician]][keyGroupBy[quick@specialty]][Normal,Framed,Panel]//n

AssociationMap variations

AssociationMap has 2 modes

• Association constructor

Range[3] // AssociationMap[f]

• Map on Values only  (inconvenient having to insert First[#]-> ...)

Range[3] // AssociationMap[f] // AssociationMap[First[#]-> Framed[Last[#]]&]

keyValueApply = Key x Value interaction; keyValueMap

keyValueApply[f_][k_-> v_]:= k-> f[k,v];

keyValueApply[f_, g_][k_-> v_]:= f[k,v]-> g[k, v];

keyValueDiagonal[f_, g_][k_-> v_]:= f[k]-> g[ v];

keyValueMap[f_]:= AssociationMap[keyValueApply[f]];

keyValueMap[f_,g_]:= AssociationMap[keyValueApply[f,g]];

Key pivoting

Reshape relational tables to key-value index

http://mathematica.stackexchange.com/questions/87360/dataset-vs-an-association-of-associations/87678

keyPop[key_]:= Query[GroupBy[key],First/*KeyDrop[key]];

keyPush[key_]:= Query[Normal/* Map[Last[#]~Prepend~(key-> First[#])&]];

example:

ds = {<|"id"->1,"a1"->1,"a2"->2|>,<|"id"->2,"a1"->3,"a2"->4|>}

ds // keyPop["id"]

ds // keyPop["id"]// keyPush["id"]

Recursive trie indexer

trie =Query[GroupBy@First,If[First@#=={},{},trie@#]&,Rest];

FileNames["*.*","~/Google Drive",Infinity]//Map[FileNameSplit]//trie

• indexes a list of files in a filesystem (folders filtered out, hence *.*) by nested Associations. 

Preliminary timing studies indicate time complexity is linear in input (leaf count).

flatFiles = FileNames["*gsheet","~/Google Drive/VASD/Projects/Quick/Data2",Infinity]//Dataset

(Need to Normal as, again, default Dataset formatting would hide essentially the entire result)

flatFiles[All,StringSplit[#,"/"]&] [prefixTrie]//Normal

Tally & Normalization utilities for Associations

associationTally

associationTally= Query[Tally /* SortBy[Last]/* Reverse /* Map[Apply@Rule]/* Association];

normalizeBy

normalizeBy[f_]:= Query[Query[{#&,f}]/* (First[#]/Last[#]&)];

Statistics: Quantile normalization

quantileAssociation[p_List][x_] := AssociationThread[p->Quantile[x,p]];

quantileIntervals[p_List] := Query[quantileAssociation[p]/* Normal /* (Partition[#,2,1]&)/* Query[Association/*Map[Interval],Transpose/*Apply[Rule],Apply[List]]];

toQuantile[qtl_Association][x_]:= qtl//Query[Select[IntervalMemberQ[#,x]&]/* Keys/*First];

quantileMap[p_List][x_]:= x // Query[All,toQuantile[quantileIntervals[p][x]]];

Block[{Print},
quick[dataIndex] ["SurveyData",First,importHandler][primaryKeyUp["PatientID"]][Transpose][All,Select[NumberQ]/* quantileColorMap[{0,1/10,9/10,1}]][Transpose][All,(1;;7)/*Values/* Row][keyGroupBy[quick@visitClinician]][All,Values/*Column/*Panel][keyGroupBy[quick@specialty]][All,Framed]//Normal
]

Basic functional graphics

NASA TLX workload survey dashboard

quick[survey] [keyGroupBy[quick@visitClinician]][All,Transpose][All,nasaTLIKeys,MinimalBy[#&]][All,All,Normal/* Map[Style[Underscript[First[#],Style[Last[#],Lighter@Gray]],FontSize-> (2+Last[#])]&]/* (Row[#," "]&)]//n//Dataset

Fancy functional graphics

merge clickstream activity channels

keystrokesProcessQuery = Query[ Query[Tally, Replace[#Details,quick[keystrokesGroupList]]&]/* Query[SortBy[Last]/* Row,Style[First[#],FontSize-> (8+ Last[#]/2)]&]] /*Query[Framed /* (Rotate[#, Pi/2]&)];

quick[kmw0]["Q026",
{Query[ "Mouse",All,{"Sequence"-> mouseProcessQuery /*mouseGraphicsQuery}],
 Query["Keystrokes",All,{"Sequence"->keystrokesProcessQuery}],
 Query["Wheel",All,{"Sequence"->wheelProcessQuery}]}][Catenate/*SortBy["SeqNo"]][Row/*(Panel[#,Background->Lighter@Gray]&),"Sequence"]

Timeline plot layout

merge clickstream activity channels

shift= chron["PageRank"][GroupBy[#Year&]/*Keys/* diffuse[0.35]/* Interpolation];

itemGr := {{Darker@Blue,Disk[{#Year,0},0.25]},
{Thickness[0.001],Opacity[0.5],BezierCurve[{{#Year,0},{#Year,2},{shift[#Year],1+3/4},{shift[#Year],1+3/2},{shift[#Year],3}}]},
Text[Column[{Style[ToLowerCase@#Author,Darker@Blue,FontSize->14,Bold],Style[ToLowerCase@#Contribution,Small]},Spacings->0],{shift[#Year],4},{-1,1/2},{1,1},BaseStyle-> None]}&;

font1 = FontFamily->"Andale Mono";

{chron["PageRank"][GroupBy[#Year&]][Values,MapIndexed[Translate[#1,{0,6 (First[#2]-1)}]&],itemGr]//Normal ,
Inset[Style["a century to page rank 1906-1988",font1,Large,Opacity[0.3]],{1960,14}],
Inset[Style["here\nyou\nx\n",Red,font1,Opacity[0.3]],{2015,0.8}],
Inset[Style[statigrafixLogo,Opacity[0.3]],{2015,11}]}// Graphics[{#},Axes->{True,False},PlotRange->{{1900,2020},All},TicksStyle-> Directive@{FontSize->15,Darker@Blue},ImageSize->1100,Background-> RGBColor[0.98,0.98,0.99],ImagePadding-> 25]&

Future needs

Touch Remote, Dataset browser

My daily workflow is often taken up writing and then deleting exploratory queries about the data. For example, compare Keys at various branches or levels or identify data type or shape information.

A “2nd screen” touch remote might be effective in navigating through the data by opening and closing branches and levels and displaying skeleton view w/ data types, depth etc (these layers should be exposed as options). 

Shown, Apple Logic Remote iPad

Functional interoperability with Cloud apps (Google, Smartsheet, ...)

eg read/write individual cells or ranges

Transparent encrypted data storage

Currently  Encryption-at-rest is required for EHR content w/ PHI, eg clinician progress notes.

Per VA Handbook 6500, Federal Information Processing Standards (FIPS) 140-2 certified encryption must be used to encrypt data in transit and at rest if Personally Identifiable Information (PII), Protected Health Information (PHI) or VA sensitive information is involved or additional mitigating controls must be documented in an approved System Security Plan (SSP). Additionally, the technology must be implemented within the VA production network (not in a Demilitarized Zone (DMZ), ...
	-- http://www.va.gov/TRM/ToolPage.asp?tid=7514&tab=2 // One-VA Technical Reference Model v15.6
	
	Veterans Affairs (VA) users must ensure VA sensitive data is protected properly in accordance with VA Handbook 6500 and the Federal Information Security Management Act (FISMA). Per VA Handbook 6500, FIPS 140-2 certified encryption must be used to protect and encrypt data in transit and at rest if Personally Identifiable Information/Protected Health Information/VA (PII/PHI/VA) sensitive information is involved...

Cloud versions of this product are not permitted without a waiver from the Architecture and Engineering Review Board (AERB) and ... http://www.va.gov/TRM/ToolPage.asp?tid=7864&tab=2&minYear=2015 // Aidin

Thanks // WRI tech support, mathematica.stackexchange