Wolfram Cloud Document

A dataset

The following dataset is a small snippet of a large list of datasets imported from an Excel Workbook. Each dataset in the list is imported from a separate worksheet in the Excel workbook. The worksheet contents are all different. But they share a common structure. The top three rows define column headings (the dataset keys), data type codes and unit names. Subsequent rows contain the data values. All data values are stored (and imported) as strings.

Note that the data type codes and unit names (rows 2 and 3 of the Excel worksheet) are excluded from the following dataset.

In[]:=

ds=

Dataset

Out[]=

locaId	isptTop	isptSeat	isptMain	isptNpen	isptNval	isptRep
WS01	1.00	—	—	—	17	N=17 (4,4/5,5,4,3)
WS01	2.00	—	—	—	22	N=22 (3,4/1,7,7,7)
WS01	3.00	—	—	—	—	50 (7,10/50 for 170mm)
WS02	1.00	—	—	—	3	N=3 (1,1/1,0,1,1)
WS02	2.00	—	—	—	—	N=50 (5,5/50 for 226mm)
WS03	1.00	—	—	—	—	50 (3,14/50 for 175mm)
WS04	1.00	—	—	—	19	N=19 (2,3/4,5,5,5)
WS04	2.00	—	—	—	22	N=22 (2,3/5,5,6,6)
WS04	3.00	—	—	—	—	50 (12,10/50 for 160mm)
WS05	1.00	—	—	—	—	50 (25 for 85mm/50 for 80mm)

Get the dataset keys (or column headings)

In[]:=

keys=ds//First//Keys//Normal

Out[]=

{locaId,isptTop,isptSeat,isptMain,isptNpen,isptNval,isptRep}

Define codes that define the type of each column in the dataset

types={"ID","2DP","0DP","0DP","0DP","0DP","X"};

Define a function that converts numeric string values based on the type codes

In[]:=

ClearAll[toType]toType[type_String,""]:=Missing[]toType[type_String,x_Missing]:=Missing[]toType[type_String/;StringEndsQ[type,"DP"],x_String]:=ToExpression[x]toType[type_String/;StringEndsQ[type,"SF"],x_String]:=ToExpression[x]toType[type_String/;StringEndsQ[type,"SCI"],x_String]:=Interpreter["Number"][x]toType[type_String/;StringMatchQ[type,"U"],x_String]:=Interpreter["Number"][x]toType[type_String,x_String]:=x

Build an expression to be used in converting string values to numeric values

In[]:=

expression=Table[keys[[i]]->toType[types[[i]],Slot[keys[[i]]]],{i,Length@keys}]

Out[]=

{locaIdtoType[ID,#locaId],isptToptoType[2DP,#isptTop],isptSeattoType[0DP,#isptSeat],isptMaintoType[0DP,#isptMain],isptNpentoType[0DP,#isptNpen],isptNvaltoType[0DP,#isptNval],isptReptoType[X,#isptRep]}

Use the above expression to convert string values in the dataset to numeric values

Using the variable expression does not work – see below.

ds[All,Association[#,expression]&]

Out[]=

locaId	isptTop	isptSeat	isptMain	isptNpen	isptNval	isptRep
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]
toType["ID",#locaId]	toType["2DP",#isptTop]	toType["0DP",#isptSeat]	toType["0DP",#isptMain]	toType["0DP",#isptNpen]	toType["0DP",#isptNval]	toType["X",#isptRep]

But, when I copy the evaluated form of expression and paste it into the above block of code, I get the desired result – see below.

In[]:=

dsAll,Association#,

&

Out[]=

locaId	isptTop	isptSeat	isptMain	isptNpen	isptNval	isptRep
WS01	1.0	—	—	—	17	N=17 (4,4/5,5,4,3)
WS01	2.0	—	—	—	22	N=22 (3,4/1,7,7,7)
WS01	3.0	—	—	—	—	50 (7,10/50 for 170mm)
WS02	1.0	—	—	—	3	N=3 (1,1/1,0,1,1)
WS02	2.0	—	—	—	—	N=50 (5,5/50 for 226mm)
WS03	1.0	—	—	—	—	50 (3,14/50 for 175mm)
WS04	1.0	—	—	—	19	N=19 (2,3/4,5,5,5)
WS04	2.0	—	—	—	22	N=22 (2,3/5,5,6,6)
WS04	3.0	—	—	—	—	50 (12,10/50 for 160mm)
WS05	1.0	—	—	—	—	50 (25 for 85mm/50 for 80mm)

Unfortunately, copying and pasting the evaluated form of expression is not a viable solution for this use case so I want to figure out a way to achieve the above result programmatically.