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 Excel worksheet contents are all different. But they share a common structure. The top three rows of each worksheet define column headings (the dataset keys), data type codes and unit names. Subsequent rows, 4 onwards, contain the data values. All data values are stored in Excel (and imported into Mathematica) as strings.

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

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

In[]:=

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

Code “ID” denotes columns containing String values.

Codes ending in “DP” denote columns containing decimal number values.

Code “X” denotes columns containing String values.

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.

In[]:=

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[]:=

numeric=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.

Eric’s solution

In[]:=

keyType=MapThread[(#1->#2)&,{keys,types}]

Out[]=

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

In[]:=

ClearAll[typedVal]typedVal[type_String,""]:=Missing["Empty string",type];typedVal[type_String?(StringEndsQ[{"DP","SF"}]),val_String]:=ToExpression[val];typedVal[type_String?(StringEndsQ[{"SCI","U"}]),val_String]:=Interpreter["Number"][val];typedVal[type_String,val_]:=val

In[]:=

toTypedPair[key_String,val_]:=key->typedVal[key/.keyType,val]

In[]:=

eric=ds[All,Association@*KeyValueMap[toTypedPair]]

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)

A dataset

Get the dataset keys (or column headings)

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

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

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

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

Eric’s solution

Check that the numeric values have been properly converted