Loading Numerical Data

Fundamentals

What Is Numerical Data?

There are many different data formats that fall under the umbrella of "numerical data" (and they need not be entirely numerical in nature). The main distinction between numerical data and generic data is that the desired Wolfram Language form of the data will be represented by reals, integers, rationals, or complex numbers, rather than strings or symbols.

A typical example of numerical data might be a two-column array of floating-point numbers stored as Comma Separated Values (CSV).

Another common form of numerical data is time series data, which has a time stamp associated with measurements of some kind (temperature, speed, pressure, etc). Here is a sample of financial time series data that is included with the Wolfram System.

ReadList["ExampleData/financialtimeseries.csv",Record,5]//TableForm

Functions & Strategies for Loading Data

There are a number of different functions available in the Wolfram Language that are used to load data. The following chart summarizes these functions and some of the advantages and disadvantages of each.

For small files, and when working with new data for the first time, it is often easiest to use (RefLink[Import, paclet:ref/Import]) to load data. (RefLink[Import, paclet:ref/Import]) offers the most user-friendly interface for loading data, and in these cases the extra overhead generally is not a major concern. However, when deploying an application or working with particularly large datasets, working with a function like (RefLink[ReadList, paclet:ref/ReadList]) or (RefLink[BinaryReadList, paclet:ref/BinaryReadList]) can greatly enhance the performance of your data loading.

Limitation for ReadList

As a general rule, (RefLink[ReadList, paclet:ref/ReadList]) will outperform a comparable (RefLink[Import, paclet:ref/Import]) operation in terms of speed and memory overhead. This is due in part to the type restriction used as a second argument in (RefLink[ReadList, paclet:ref/ReadList]), which allows the Wolfram Language to skip the various parsing operations used in (RefLink[Import, paclet:ref/Import]) (which make sure that entries that should be strings are interpreted as strings, and reals and integers are used appropriately for numerical values). This generally leads to (RefLink[Import, paclet:ref/Import]) being more user-friendly, but at the cost of requiring additional time and memory to do the same operation as (RefLink[ReadList, paclet:ref/ReadList]).

If you want to see the difference in memory usage, try applying (RefLink[MemoryConstrained, paclet:ref/MemoryConstrained])[…,4*1024^2] (limiting the operation to 4 MB of RAM):

AbsoluteTiming[ReadList["ExampleData/fish.data",Real,RecordLists->True]//Short]

AbsoluteTiming[Import["ExampleData/fish.data","Table"]//Short]

(RefLink[ReadList, paclet:ref/ReadList]) is simple to use in cases where a large data file has a single type (such as rows and columns of reals or integers) but can be more work when multiple types are used (such as when strings and reals are intermixed, or where a data file with real values has strings in the first row of each column).

(RefLink[Import, paclet:ref/Import]) automatically converts the date strings for time series data

Take[ Import["ExampleData/financialtimeseries.csv"],10]

(RefLink[Head, paclet:ref/Head]) verifies that the second element of each list is interpreted as a (RefLink[Real, paclet:ref/Real]) value:

%[[1,2]]//Head

In contrast, (RefLink[ReadList, paclet:ref/ReadList]) returns each row as a (RefLink[Record, paclet:ref/Record]):

ReadList["ExampleData/financialtimeseries.csv",Record,10]

The (RefLink[FullForm, paclet:ref/FullForm]) indicates the entire row is being handled as a (RefLink[String, paclet:ref/String]):

%[[1]]//FullForm

An alternative specification for (RefLink[ReadList, paclet:ref/ReadList]) might load each row as a list of {(RefLink[String, paclet:ref/String]),(RefLink[Number, paclet:ref/Number])}:

ReadList["ExampleData/financialtimeseries.csv",{Record,Number},10,RecordSeparators->{",","\n","\r","\r\n"}]

(RefLink[FullForm, paclet:ref/FullForm]) indicates the source of the error being issued by (RefLink[ReadList, paclet:ref/ReadList]).

(RefLink[RecordSeparators, paclet:ref/RecordSeparators]) only apply to (RefLink[Record, paclet:ref/Record]) types, not numbers:

ReadList["ExampleData/financialtimeseries.csv",{Record,String},1,RecordSeparators->{",","\n","\r","\r\n"}]//FullForm

To get the desired types in the Wolfram Language, an additional processing step is required.

ReadList["ExampleData/financialtimeseries.csv",{Record,Record},10,RecordSeparators->{",","\n","\r","\r\n"}]

{#[[1]],ToExpression[#[[2]]]}&/@%

%[[1,2]]//Head

For small datasets, it generally is more efficient to use (RefLink[Import, paclet:ref/Import]) for time series data, but in cases where a large amount of information is being read, the processing overhead associated with (RefLink[Import, paclet:ref/Import]) may make it worth the extra effort so that your data can be handled using less memory, and more quickly.

Importing with Converters

Not all (RefLink[Import, paclet:ref/Import]) operations are handled by the Wolfram Language directly, but instead rely on external converter binaries to handle the import operation. In many cases, these binaries have their own limitations on file size or may have a certain amount of overhead associated with them.

(StringTypeLink[XLS, paclet:ref/format/XLS]) files (Microsoft Excel documents), for example, use an XLS converter to process the XLS documents and read them into the Wolfram Language. This process requires additional memory, since not only the Wolfram Language but also the converter needs a copy of the file in memory to process, and then must pass the data over the Wolfram Symbolic Transfer Protocol (WSTP) to the Wolfram Language. It is not uncommon to see very large amounts of memory overhead associated with importing these file formats.

Big Data-Friendly Formats

The following is a table of Big Data-friendly file formats (those formats that involve little formatting, can be directly loaded by the Wolfram Language, and do not require much memory to load).

Speed and Memory Performance

Measuring

Accurate measures of load time and memory overhead are important when optimizing a data-loading operation. The Wolfram Language has a number of functions available for both of these tasks, which can help you determine the best way to load and process your data.

AbsoluteTiming versus Timing

There are two functions in the Wolfram Language that measure the time taken to perform a kernel operation (note that these values do not include the amount of time taken to render in the front end): (RefLink[Timing, paclet:ref/Timing]) and (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming]).

The biggest difference between (RefLink[Timing, paclet:ref/Timing]) and (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming]) is that (RefLink[Timing, paclet:ref/Timing]) measures calculation time in the kernel, whereas (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming]) measures elapsed time in the kernel. This distinction is easiest to see in the following example.

(RefLink[Timing, paclet:ref/Timing]) measures the amount of time spent computing in the kernel, and does not count time waiting.

Timing[Pause[2]]

AbsoluteTiming[Pause[2]]

(RefLink[Timing, paclet:ref/Timing]) also accounts for multithreading, so if an operation only takes two seconds in elapsed time but runs on four threads, then (RefLink[Timing, paclet:ref/Timing]) will return a calculation time of eight seconds.

When reading data in, it can be useful to look at the results of (RefLink[Timing, paclet:ref/Timing]), but in most cases elapsed time (and therefore (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming])) is a more valuable measure of speed, since the actual time taken is more likely to impact the application. The following examples illustrate the differences in (RefLink[Timing, paclet:ref/Timing]) and (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming]) on an example dataset.

(RefLink[ClearSystemCache, paclet:ref/ClearSystemCache]) is used before each evaluation to ensure that the timing values are not influenced by cached results:

ClearSystemCache[];
Timing[Import["ExampleData/fish.data"];]

ClearSystemCache[];
AbsoluteTiming[Import["ExampleData/fish.data"];]

Another way of measuring timing is to manually create time stamps before and after the data loading operation, which you can see gives the same value as (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming]) (which actually has higher resolution than (RefLink[DateList, paclet:ref/DateList]) in this case).

ClearSystemCache[];
start=DateList[];
Import["ExampleData/fish.data"];
end=DateList[];
DateDifference[start,end,"Second"]

Notes on Measuring Timing

When measuring timing, it is often a good idea to put a time limit on a specific operation. (RefLink[TimeConstrained, paclet:ref/TimeConstrained]) can be wrapped around the target function, and a time limit (in seconds) is specified.

TimeConstrained[Import["ExampleData/fish.data"],.1]

Note that (RefLink[TimeConstrained, paclet:ref/TimeConstrained]) uses the same type of elapsed time measure as (RefLink[AbsoluteTiming, paclet:ref/AbsoluteTiming]), so the time limit is not based on computation time.

TimeConstrained[Pause[1.1],1]

MemoryInUse versus MaxMemoryUsed

There are two functions in the Wolfram Language that measure memory overhead in the Wolfram Language kernel (note that these values do not include front end overhead normally): (RefLink[MemoryInUse, paclet:ref/MemoryInUse]) and (RefLink[MaxMemoryUsed, paclet:ref/MaxMemoryUsed]).

As the function names suggest, (RefLink[MemoryInUse, paclet:ref/MemoryInUse]) and (RefLink[MaxMemoryUsed, paclet:ref/MaxMemoryUsed]) measure how much memory is currently being used by the kernel, and what the peak memory use for the current kernel session has been.

Values for (RefLink[MemoryInUse, paclet:ref/MemoryInUse]) and (RefLink[MaxMemoryUsed, paclet:ref/MaxMemoryUsed]) can vary significantly:

MemoryInUse[]/1024^2.

MaxMemoryUsed[]/1024^2.

Notes on Measuring Memory

It is important to note, especially when working with large data files, that the Wolfram Language keeps a history of previous evaluations, which is managed by the environment variable (RefLink[$HistoryLength, paclet:ref/$HistoryLength]).

$HistoryLength

This means that even if a variable is cleared, there is still a copy of the data contained in that variable in the memory. When working with large datasets, it is generally a good idea to set the (RefLink[$HistoryLength, paclet:ref/$HistoryLength]) to a small number or zero to prevent unwanted memory overhead.

When initially working with a large dataset, it can be easy to run evaluations that are taxing on system resources. When first prototyping your application, it can be helpful to wrap evaluations in (RefLink[MemoryConstrained, paclet:ref/MemoryConstrained]) to limit the amount of resources that are available to a particular evaluation.

MemoryConstrained[Range[10^6],10000]

Cautionary Notes on ByteCount

(RefLink[ByteCount, paclet:ref/ByteCount]) results can vary from those returned by (RefLink[MemoryInUse, paclet:ref/MemoryInUse]), as (RefLink[ByteCount, paclet:ref/ByteCount]) counts each expression and subexpression as though it were unique; but in fact, subexpressions can be identical and therefore are shared.
The following example illustrates this.

mem=MemoryInUse[];

x = Table["Sample long string, with a variety of characters, but still something that can be easily shared.",{1000000}];

ByteCount[x]

MemoryInUse[]-mem

These results vary because a 1,000,000-element expression (like x) takes 8,000,000 bytes, independent of what the elements take. (This is a 64-bit machine the author is using, where a pointer is 8 bytes, so an array of 1 million pointers is 8 million bytes.) All those pointers are pointing to the same expression, the (RefLink[String, paclet:ref/String]) whose contents are "Sample long string...".

(RefLink[ByteCount, paclet:ref/ByteCount]) uses a simple method for computing the size of an expression: it simply adds up the size of the components and the size of the containers.

ByteCount[x[[1]]]

144*1000000 + 8*1000000

The 8,000,000 bytes returned, plus 1 million copies of the (RefLink[String, paclet:ref/String]) expression, result in an estimated byte count of 152 million.

Limits

In Wolfram System Version 9 and higher, the only real restriction on how much data you can import is how much memory is available on your system, or more specifically, how much memory your operating system will allow any single process to utilize. That said, there are a number of factors that you should be aware of when loading data, and how they might limit your ability to load larger datasets.

Dimensionality of Data versus File Size

It is important to note that memory is affected by not only what is held within an expression, but also by the dimensionality of that data. For example, the following two datasets were loaded using both (RefLink[ReadList, paclet:ref/ReadList]) and (RefLink[Import, paclet:ref/Import]) on the same machine, each with a fresh kernel, and have the same number of elements, but require very different amounts of memory to load and store the expressions.

This data is composed of (RefLink[Real, paclet:ref/Real]) numbers between 0 and 10, with 4.68 million elements in a single column.

This data is composed of (RefLink[Real, paclet:ref/Real]) numbers between 0 and 10, also with 4.68 million elements, but is arranged in a 23.4k×200 matrix.

You will notice that the packed expressions are all the same size (as are the (RefLink[Import, paclet:ref/Import]) and (RefLink[ReadList, paclet:ref/ReadList]) unpacked sizes for each data file), but there are large differences between the amount of memory required to load and store the expressions based on the dimensionality of the file (expressions that are long in just one dimension will generally require more memory).

Pushing the Limits

The previous section outlines some of the general limits when loading data into the Wolfram Language. In this section, some of the techniques that can be used to push those limits are discussed, and work is done with particularly large datasets.

ReadList versus Import Comparison

The largest performance difference between (RefLink[ReadList, paclet:ref/ReadList]) and (RefLink[Import, paclet:ref/Import]) is the memory overhead required by the loading process, though speed can also vary widely, even on smaller datasets.

AbsoluteTiming[ReadList["ExampleData/fish.data",Real,RecordLists->True]//Short]

AbsoluteTiming[Import["ExampleData/fish.data","Table"]//Short]

(RefLink[Import, paclet:ref/Import]) takes more than twice as long to load the data as (RefLink[ReadList, paclet:ref/ReadList]) in the previous example, and also requires significantly more memory.

AbsoluteTiming[MemoryConstrained[Import["ExampleData/fish.data","Table"],4*1024^2]//Short]

(RefLink[ReadList, paclet:ref/ReadList]) here will use half the memory-constrained value as (RefLink[Import, paclet:ref/Import]) in the previous example:

AbsoluteTiming[MemoryConstrained[ReadList["ExampleData/fish.data",Real,RecordLists->True],2*1024^2]//Short]

When pushing the limits, it is generally a good idea to use (RefLink[ReadList, paclet:ref/ReadList]), (RefLink[BinaryReadList, paclet:ref/BinaryReadList]), or (RefLink[Get, paclet:ref/Get]) in place of (RefLink[Import, paclet:ref/Import]).

Binary Files

(RefLink[BinaryReadList, paclet:ref/BinaryReadList]) is one of the most memory- and time-efficient functions available to load data, running at what is essentially a 1-to-1 file-size-to-memory footprint.

data=RandomInteger[{0,10},{1000,100}];
Export["plaintext.dat",data];
BinaryWrite["binaryfile.dat",data];
Close["binaryfile.dat"];

ClearSystemCache[];
AbsoluteTiming[d1=ReadList["plaintext.dat",Number];]

ClearSystemCache[];
AbsoluteTiming[d2=BinaryReadList["binaryfile.dat"];]

d1==d2

DeleteFile/@{"plaintext.dat","binaryfile.dat"};

Reading ASCII Files with BinaryReadList

As the previous section illustrates, (RefLink[BinaryReadList, paclet:ref/BinaryReadList]) is generally going to run significantly faster than a comparable (RefLink[ReadList, paclet:ref/ReadList]) operation when working with data that is natively in a binary format. However, there may be some instances where your data is already in a plain text/ASCII format, in which case (RefLink[BinaryReadList, paclet:ref/BinaryReadList]) can still be used.

The first loading of the data is to read in the file as (StringTypeLink["Byte", paclet:ref/format/Byte]) (8-bit characters) and segment the data at the end of each line using (RefLink[Split, paclet:ref/Split]) (the ASCII character for end-of-line is 10).

list=Split[BinaryReadList[file,"Byte"],(#1=!=10)&];

Short[list,3]

The next step is to translate the list of bytes into ASCII characters, using (RefLink[FromCharacterCode, paclet:ref/FromCharacterCode]) (whose default format is (RefLink[$CharacterEncoding, paclet:ref/$CharacterEncoding]), in this case UTF-8) and then trim out the miscellaneous white spaces.

StringTrim[FromCharacterCode[#]]&/@list[[;;5]]

These steps can be combined into a helper function to interpret data, such as the time series data used previously.

binaryReadTimeSeries[file_]:=Module[{bin=Split[BinaryReadList[file,"Byte"],(#1=!=10)&],char},char=StringSplit[StringTrim[FromCharacterCode[#]],","]&/@bin;{DateList[First[#]],ToExpression[Last[#]]}&/@char]

binaryReadTimeSeries[file]//Short//AbsoluteTiming

Tips and Tricks

Programming Helper Functions

When creating an application or package that will load data files, performance can often be improved by creating customized helper functions to handle or assist in data loading.

These functions can be used to decrease the amount of memory and time required to load a data file; often the process is as simple as replacing (RefLink[Import, paclet:ref/Import]) with (RefLink[ReadList, paclet:ref/ReadList]) and including appropriate options.

For these examples, some example time series data is used:

file="ExampleData/financialtimeseries.csv";

During development you may use (RefLink[Import, paclet:ref/Import]) on the file to get the data in, and let the Wolfram Language handle the formatting and data interpretation.

Take[Import[file],10]

You will notice that using (RefLink[ReadList, paclet:ref/ReadList]) without any options results in a list of strings, rather than nested lists with real values and date strings.

ReadList[file,String,10]

Adding "," to (RefLink[RecordSeparators, paclet:ref/RecordSeparators]), along with the default separators, gives nested lists:

ReadList[file,{Record,Record},10,RecordSeparators->{"\r","\n","\r\n",","}]

While the (RefLink[RecordSeparators, paclet:ref/RecordSeparators]) give the proper dimensionality for the data, the items are still both being loaded as strings. The addition of (RefLink[ToExpression, paclet:ref/ToExpression]) on the second element of each list will return the desired results.

Head/@%[[1]]

ToExpression[%%[[1,2]]]

These steps can then be combined into a function to import time series data of this form.

readTimeSeries[file_]:={First[#],ToExpression[Last[#]]}&/@ReadList[file,{Record,Record},RecordSeparators->{"\n","\r\n","\r",","}]

readTimeSeries[file];//AbsoluteTiming

Import[file];//AbsoluteTiming

This function is more than five times as fast as (RefLink[Import, paclet:ref/Import]) and uses a fraction of the memory required, because it is custom-tailored to this data format, rather than a generalized function like (RefLink[Import, paclet:ref/Import]) (import of plaintext files is essentially just a very robust, generalized (RefLink[ReadList, paclet:ref/ReadList]) function that handles a wide variety of data formats, with additional post-processing).

Another benefit of creating your own data-loading function is that additional processing steps can be inserted within the loading operation, rather than having to do post-processing on the results.

Modifying readTimeSeries from the previous example:

readTimeSeries[file_]:={DateList[First[#]],ToExpression[Last[#]]}&/@ReadList[file,{Record,Record},RecordSeparators->{"\n","\r\n","\r",","}]

This improves performance versus mapping (RefLink[DateList, paclet:ref/DateList]) across the dataset.

readTimeSeries[file];//AbsoluteTiming

{DateList[First[#]],Last[#]}&/@Import[file];//AbsoluteTiming

Reading Part of a File

Large data files, especially those aggregated via scripts or logging systems, can sometimes take up so much room that they cannot be loaded into memory on the system all at once. However, this does not mean that the data is inaccessible to the Wolfram Language, and there are a number of methods available for reading out portions of the data from very large files.

(RefLink[ReadList, paclet:ref/ReadList]) can be used directly on large files to read in subsets of the data, if that data is at the top of the file.

ReadList[file,{Record,Record},5,RecordSeparators->{"\n","\r\n","\r",","}]

However, if that data is farther into the file, and it is not feasible to load in all the data before the desired information, (RefLink[OpenRead, paclet:ref/OpenRead]) and (RefLink[Find, paclet:ref/Find]) can be used to locate a particular element within the data, for example, a date.

stream=OpenRead[file]

StreamPosition[stream]

Find[stream,"Jan 12"]

StreamPosition[stream]

Note that (RefLink[Find, paclet:ref/Find]) sets the (RefLink[StreamPosition, paclet:ref/StreamPosition]) to the entry immediately following the information specified, so (RefLink[ReadList, paclet:ref/ReadList]) running on the stream will start at the next record, January 13.

ReadList[stream,{Record,Record},5,RecordSeparators->{"\n","\r\n","\r",","}]

Close[stream]

Reloading Data

Some applications may require you to reload data every time it is used, or may require accessing a specific list of data files. In these cases, data loading can be heavily optimized by using the Wolfram Language's (RefLink[DumpSave, paclet:ref/DumpSave]) function.

The Wolfram Language has two built-in methods for storing variables in data files, beyond the normal (RefLink[Write, paclet:ref/Write])/(RefLink[Export, paclet:ref/Export]) functions. (RefLink[Save, paclet:ref/Save]) and (RefLink[DumpSave, paclet:ref/DumpSave]) can be used to store variable values on a file system, allowing them to be reloaded in the future, using (RefLink[Get, paclet:ref/Get]).

There are a few important distinctions between (RefLink[Save, paclet:ref/Save]) and (RefLink[DumpSave, paclet:ref/DumpSave]). The biggest difference in terms of data storage is that (RefLink[Save, paclet:ref/Save]) uses a plaintext format to store the information, while (RefLink[DumpSave, paclet:ref/DumpSave]) uses a binary data format (which takes up less room, and can be read more quickly). (RefLink[DumpSave, paclet:ref/DumpSave]) also preserves packed arrays, which improves both speed and memory overhead.

The main limitation of (RefLink[DumpSave, paclet:ref/DumpSave]) is that its encoding system requires that the data be loaded on the same machine architecture on which it was encoded, so the files cannot be transferred between platforms.

The four panels following show the performance differences between (RefLink[Get, paclet:ref/Get]) (both on (RefLink[DumpSaved, paclet:ref/DumpSave]) and (RefLink[Saved, paclet:ref/Save]) data), (RefLink[ReadList, paclet:ref/ReadList]), and (RefLink[Import, paclet:ref/Import]).

All files were generated using a packed array of (RefLink[RandomReal, paclet:ref/RandomReal]) numbers in a 585000×8 matrix.