Working with Data in Julia#

Introduction#

Working with data is one of the most common tasks in any programming language, including Julia. For example, loading data into a program, doing some calculations using that data, and then saving the results to a file (either locally or in the cloud) describes the majority of programs you will write in this course.

There are various tools and approaches to reading and writing data files in Julia. This chapter will consider a few common approaches and introduce some patterns for working with data files. In particular, we will:

Introduce reading and writing Comma seperated value (CSV) files, a common data format for numerical data files
Introduce more expressive tools such as DataFrames, which are in-memory tabular data structures
Introduce reading and writing data in Binary file formats, a common approach for large data files and more complex data structures

Comma seperated value (CSV) files#

Arguably, the most widely used data file format for the universe of possible applications is the comma separated value (CSV) format. A CVS file is a delimited text file that uses a comma to separate data values. Each line of a CSV file is called a record, where each record consists of one or more fields (data values), separated by commas; hence the name comma separated value file.

A CSV file is typically used to store tabular data (numbers and text) in plain text, where each line has the same number of fields. However, the CSV file format is not standardized. For example, column headers may or may not be present. Further, while separating fields in records with commas is standard, various exceptions and exceptional cases sometimes make CSV files problematic, e.g., fields with commas in the data or records with a variable number of fields can make working CSV files challenging.

Fortunately, the applications we are interested in typically involve numerical CSV files that are easily handled using the CSV.jl package. The CSV.jl package can be downloaded and installed using the Julia package manager. To load the CSV package into your program, or the REPL, issue the using or import commands. Once loaded, the CSV package publishes several commands to read and write CSV files.

The main methods exported by the CSV.jl package are the CVS.read and CVS.write methods.

CVS.read allows the user to load a data file into one of several data structures
CVS.write allows the user to write a tabular data structure to a CSV file.

For most of the applications, we will be doing, the source and sink data structure will be DataFrames, a handy tabular data structure. Thus, we do not read (or write) a DataFrame directly; instead, we store it as (or read from) a CSV file.

DataFrames#

DataFrame.jl is a Julia package that provides tools for working with tabular data in Julia, e.g., numerical data tables. The DataFrame.jl package can be downloaded and installed using the Julia package manager. To load the DataFrames package into your program, or the REPL, issue the using or import commands. Once the DataFrames package is loaded, it publishes several commands to work with tabular data.

Objects of type DataFrame represent data tables where each column corresponds vector of values. The simplest way to construct a DataFrame is to pass vectors holding the values for each column using keyword arguments or pairs:

# This example was reproduced from the DataFrames.jl package documentation
# https://dataframes.juliadata.org/stable/man/basics/#First-Steps-with-DataFrames.jl

# Load external packages
using DataFrames

# build a sample DataFrame -> store in variable df
df = DataFrame(A=1:4, B=["M", "F", "F", "M"])

4×2 DataFrame

Row	A	B
	Int64	String
1	1	M
2	2	F
3	3	F
4	4	M

A DataFrame can also be created from a Dictionary, where the column header names are constructed from the keys of the dictionary, and the column values are the data associated with each key:

# This example was reproduced from the DataFrames.jl package documentation
# https://dataframes.juliadata.org/stable/man/basics/#First-Steps-with-DataFrames.jl

# Load external packages
using DataFrames

# create a dictionary w/data -> variable dict
dict = Dict(
  :customer_age => [22, 20, 25], 
  :first_name => ["Rohit", "Rahul", "Akshat"],
  :state => ["IN","CA","OR"]
);

# convert the dictionary to a DataFrame -> variable df
df = DataFrame(dict)

3×3 DataFrame

Row	customer_age	first_name	state
	Int64	String	String
1	22	Rohit	IN
2	20	Rahul	CA
3	25	Akshat	OR

Notice the Dictionary keys are of type Symbol and not Strings; a Symbol represents a special type of identifier in Julia. Symbols are often used as names or labels to identify an entity, e.g., as a dictionary key as shown above. Symbols can be entered using the : quote operator or constructed from Strings using the Symbol(x...) constructor method.

Why are DataFrames interesting?#

DataFrames are interesting because, in many ways, they act like Arrays, e.g., they can be indexed to access values, and they are iterable, i.e., they can be used in loops. Further, entire columns can be accessed by the column names.

For example, suppose we have a DataFrame stored in a variable df that holds demographic data about clients, e.g., names, ages, etc. We can access entire columns (or the data from a collection of columns) by the column name using the syntax df[! colname] or with dot notation df.colname for a single column:

# modified from the DataFrames.jl package documentation
# https://dataframes.juliadata.org/stable/man/basics/#First-Steps-with-DataFrames.jl

# Load external packages
using DataFrames

# create a dictionary w/data -> variable dict
dict = Dict(
  :customer_age => [22, 20, 25], 
  :first_name => ["Rohit", "Rahul", "Akshat"],
  :state => ["IN","CA","OR"]
);

# convert the dictionary to a DataFrame -> variable df
df = DataFrame(dict);

# get all the values in the first name col -
df[!,[:first_name, :state]]

3×2 DataFrame

Row	first_name	state
	String	String
1	Rohit	IN
2	Rahul	CA
3	Akshat	OR

Finally, DataFrames can also be used in complex data manipulation operations, e.g., sorting, filtering or Join operations.

For example, suppose in our customer dataset, we wanted all customers from Oregon; we can do this with the filter function:

# modified from the DataFrames.jl package documentation
# https://dataframes.juliadata.org/stable/man/basics/#First-Steps-with-DataFrames.jl

# Load external packages
using DataFrames

# create a dictionary w/data -> variable dict
dict = Dict(
  :customer_age => [22, 20, 25], 
  :first_name => ["Rohit", "Rahul", "Akshat"],
  :state => ["IN","CA","OR"]
);

# convert the dictionary to a DataFrame -> variable df
df = DataFrame(dict);

# get all customers from OR
OR_customers = filter(:state=>x->x=="OR", df)

1×3 DataFrame

Row	customer_age	first_name	state
	Int64	String	String
1	25	Akshat	OR

There is a large variety of data wrangling functions published by the DataFrame.jl package, please review the Getting started section of the DataFrame.jl documentation for more information.

Reading and writing DataFrames#

While it’s easy to create a DataFrame, e.g., from a Dictionary, we are also often interested in applications that require reading and writing of data files from disk.

Converting data files e.g., a table of numerical values, into a DataFrame object, (or exporting a DataFrame object to a file) can be done with the CSV.jl package described above.

Loading a CSV file into a DataFrame#

Suppose we have a CSV file which holds numerical data, e.g., stock price values for the firm with ticker symbol XYZ. We can load this file from disk, and automatically convert the data to a DataFrame using the read method in the CSV.jl package:

# Load external packages
using CSV
using DataFrames

# setup the path to the data file -
path_to_datafile = joinpath(pwd(),"data", "AMD-OCHL-from-05-07-22-to-2022-08-05.csv")

# load the CSV file into a DataFrame -> df
df = CSV.read(path_to_datafile, DataFrame)

# show the first few rows of data 
df[1:4,:] # Indexing allows DataFrames to behave like arrays 

4×8 DataFrame

Row	volume	vwap	open	close	high	low	timestamp	n
	Float64	Float64	Float64	Float64	Float64	Float64	DateTime	Int64
1	1.44524e8	88.7007	92.865	86.36	93.74	85.38	2022-05-09T04:00:00	901147
2	1.37235e8	88.0634	89.18	88.73	90.7721	85.41	2022-05-10T04:00:00	735895
3	1.36138e8	90.3059	87.84	87.92	93.46	86.53	2022-05-11T04:00:00	859974
4	1.29214e8	85.6857	86.6	87.06	88.55	83.27	2022-05-12T04:00:00	882934

Saving a DataFrame to a CSV file#

Conversely, suppose we have a DataFrame holding numerical data want to save. In this case, we can take advantage of the save methods published by the CSV.jl package; save takes two arguments, the first is the path to the file we want to save, and the second is the DataFrame instance that we want to save.

For example, assume we have a variable of type DataFrame named df, then the following code snippet shows how we could save df to a CSV file:

# Load external packages 
using CSV
using DataFrames

# some mythical process generates the DataFrame df
df = ...

# Dump df to disk as a CSV file 
path_to_csv_file = ...

# write out the file 
CSV.write(path_to_csv_file, df)

Binary file formats#

Binary file formats have been developed to store and organize large amounts of complex structured data. The basis of many binary formats is the Hierarchical Data Format (HDF), a set of file formats initially developed by the U.S. National Center for Supercomputing Applications (NCSA).

The JLD2.jl package saves and loads Julia data structures in a format that is a subset of HDF5, the latest iteration of the Hierarchical Data Format (HDF).

The JLD2.jl package can be downloaded and installed using the Julia package manager. To load the JLD2 package into your program, or the REPL, issue the using or import commands. Once the JLD2 package is loaded, it publishes several commands to load and save data in the HDF5 format. Let’s look at the save and load functions.

save#

Consider an example of saving a JLD2 file; in this example, we downloaded stock price data for several firms from an external application programming interface (API) and saved this dataset as a Dictionary. The price history for each firm is stored in a DataFrame, while the overall dataset for N = 25 firms is stored as a Dictionary where the ticker symbol for firm XYZ (of type String) is used as the Dictionary key.

The save function accepts a path and an AbstractDict holding the key/value pairs that we wish to save, where the key is a string representing the name of the dataset and the value is its contents:

# load external packages
using DataFrames
using JLD2
using FileIO

# external process to download data (not shown)
dd = api({arguments}); # dd -> Dict{String,DataFrame}

# path to data file 
filename = "Portfolio-Data-06-20-22.jld2"

# save
save("Portfolio-Data-06-20-22.jld2", Dict("dd" => dd)); # saved dd in Dict with key dd

load#

Consider an example of loading a JLD2 file; in this example, a Dictionary holding stock market data is loaded from disk. In particular, the price data for each of N = 25 firms is stored in a DataFrame, where the ticker symbol for each firm XYZ is used as the Dictionary key; the ticker symbol XYZ is of type String. Thus, the loaded Dictionary is of type Dict{String,DataFrame}.

The load function reads a *.jld2 file from the disk. When called with a path argument only, the load function loads the dataset from the given file into a Dictionary:

# load external packages
using DataFrames
using JLD2
using FileIO

# set path to the data file
# joinpath is cross platform: handles / vs \
path_to_data_file = joinpath(".", "data", "Portfolio-Data-06-20-22.jld2")

# load the dataset: the original data is stored in a Dict with key `dd`
dd = load(path_to_data_file)["dd"] # dd -> Dict{String,DataFrame}

Taken together, the JLD2.jl package provides tools to directly persist (and load) complex hierarchical data structures, which go well beyond just numerical data. For more information, please see the JLD2.jl package documentation.

Summary#

In this chpater we:

Introduced reading and writing Comma seperated value (CSV) files, a standard data format for working with numerical data files
Introduced more expressive tools such as DataFrames, which are in-memory tabular data structures. DataFrames are highly flexible data structures offering many advanced storage and query capabilities.
Introduced reading and writing data in Binary file formats, a common approach for large data files and more complex data structures

ENGRI 1120

Working with Data in Julia

Contents