1
$\begingroup$

Background

I frequently import data from CSV files. This data often has headers, is a mix of numbers and text, and is a simple 2D 'shape'. I need to clean the data (eg removing rows, trimming strings, standardizing units etc.) before I can work with it.

My strategy is to convert it to a List of Associations which makes cleaning and manipulation easier.

Table to Association

I built a function to convert tables with headers into an association.

ta[table_List] := AssociationThread[First[table], #] & /@ Rest[table];

For example:

smallTable = {{"a1", "a2"}, {1, 2}, {3, "Four"}};
ta[smallTable]

{<|"a1" -> 1, "a2" -> 2|>, <|"a1" -> 3, "a2" -> "Four"|>}

Question: Can this code go any faster?

Let's see how it performs on a larger table

(*make table*)
rows = 100000;
cols = 100;

SeedRandom[100];
data = RandomReal[1, {rows, cols}];
data[[1]][[1]] = "TEXT";

header = WordList[][[1 ;; cols]];
table = Join[{header}, data];
assc = ta[table]; // RepeatedTiming

{1.78668, Null}


My attempts

Here is my attempt at making this faster using MapThread

ta2[table_List] :=
  MapThread[
   AssociationThread,
   {
    ConstantArray[First[table], Length[Rest[table]]],
    Rest[table]
    }
   ];

assc2 = ta2[table]; // RepeatedTiming
assc2 === assc

{1.78114, Null}

True

Almost no improvement


Now trying to see if localising the variable helps...

ta3[table_List] :=
  With[
   {
    values = Rest[table],
    headers = First[table]
    },
   With[
    {
     keys = ConstantArray[headers, Length[values]]
     },
    MapThread[AssociationThread, {keys, values}]
    ]
   ];

assc3 = ta3[table]; // RepeatedTiming
assc3 === assc

{1.72666, Null}

True

Virtually no improvement again


Is it faster making a list of Rules and then mapping Association on it...?

ta4[table_List] :=
  With[
   {
    keys = ConstantArray[First[table], Length[Rest[table]]],
    values = Rest[table]
    },
   Map[Association, MapThread[Rule, {keys, values}, 2], 2]
   ];

assc4 = ta4[table]; // RepeatedTiming
assc4 === assc

{10.0572, Null}

True

...it is not


I have also tried ParallelMap with AssociationThread[First[table], #] & but this is incredibly slow.

$\endgroup$
7
  • 1
    $\begingroup$ I tried Inner[Rule, table[[1]], #, Association]& /@ table[[2;;All]], but this is not faster than your ta[ ]. $\endgroup$ Aug 21, 2023 at 15:23
  • 1
    $\begingroup$ Maybe you could try directly importing the CSV as a Dataset with SemanticImport? I do not know if this will be faster however. Ex: imported = SemanticImport["yourCSV.csv"] // Normal; $\endgroup$
    – ydd
    Aug 21, 2023 at 16:27
  • 2
    $\begingroup$ Also, as another option, you could create a paired list instead of a list of associations and do all your manipulations with that instead. Thread[{First@table, #}] & /@ Rest[table]; // RepeatedTiming is about 05-0.6 s . But there may be a good reason you want a list of associations like you have in assc $\endgroup$
    – ydd
    Aug 21, 2023 at 16:33
  • 1
    $\begingroup$ An alternative: Table[AssociationThread @@ ({table[[1]], table[[i]]}), {i, 2, Length[table]}] $\endgroup$ Aug 21, 2023 at 20:17
  • 1
    $\begingroup$ perhaps try Import["yourCSVfile", "Dataset", "HeaderLines" -> 1]? $\endgroup$
    – kglr
    Aug 21, 2023 at 21:46

1 Answer 1

5
$\begingroup$

I find Association (and Dataset) are too heavy for tabular data. Try TableSet.

TableSet supports Query, JoinAcross, and many other SQL-like functions. Named Slots are used so you get Association-like data manipulation in Query. Also, it uses much less memory than Association.

Hope this helps.

$\endgroup$
3
  • $\begingroup$ (+1) Nice Resource Function, @Edmund! $\endgroup$ Sep 8, 2023 at 17:46
  • $\begingroup$ Thank you Edmund, let me see how I can incorporate this into my existing project where I already make extensive use of Associations $\endgroup$ Sep 10, 2023 at 7:24
  • $\begingroup$ I think I'm missing something obvious but how do you extract the column values using the column name in TableSet? ie analogous to assoc[[All,"ColName"]] $\endgroup$ Sep 14, 2023 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.