Loading and Saving Data with DataFrames.jl

The process of any machine learning project begins with data, and efficiently getting that data into your Julia environment is the first practical step. DataFrames.jl is Julia's foundation package for handling tabular data, akin to Pandas in Python or data frames in R. Common tasks involve loading data from various file formats into Julia DataFrames and saving processed DataFrames back to disk, which are essential skills for any data preparation workflow.

Reading Data into DataFrames

Most datasets you'll encounter will be stored in flat files, with Comma-Separated Values (CSV) files being common. Julia, with the help of the CSV.jl package, provides efficient tools for reading these files directly into a DataFrame.

Working with CSV Files

To get started, you'll first need to have the CSV and DataFrames packages installed and available in your environment. If you followed the setup in Chapter 1, you likely already have them. If not, you can add them using Julia's package manager:

using Pkg
Pkg.add(["CSV", "DataFrames"])

Once installed, you can bring them into your current Julia session:

using CSV
using DataFrames

The primary function for reading CSV files is CSV.read. Its most basic usage requires the path to the file and specifying that you want the output as a DataFrame:

# Assume you have a file named 'iris.csv' in your working directory
# Contents of iris.csv:
# sepal_length,sepal_width,petal_length,petal_width,species
# 5.1,3.5,1.4,0.2,setosa
# 4.9,3.0,1.4,0.2,setosa
# ... (many more rows)

df_iris = CSV.read("iris.csv", DataFrame)

This simple command reads the iris.csv file and loads its content into a DataFrame object named df_iris. The CSV.read function is quite smart and will often infer data types, delimiters, and whether a header row exists. However, data is rarely so straightforward. You'll frequently need to provide more specific instructions.

Let's consider a slightly more complex scenario. Imagine a file student_grades.csv with the following content:

student_id;name;subject;grade_percent;attendance_rate
S001;Alice;Math;85;0.95
S002;Bob;Science;N/A;0.88
S003;Charlie;Math;72;0.90

Notice the semicolon delimiter and the "N/A" string representing a missing grade. Here's how you might read this:

# Contents of student_grades.csv (as above)
file_path = "student_grades.csv"

# Create the dummy file for the example to work
open(file_path, "w") do f
    write(f, "student_id;name;subject;grade_percent;attendance_rate\n")
    write(f, "S001;Alice;Math;85;0.95\n")
    write(f, "S002;Bob;Science;N/A;0.88\n")
    write(f, "S003;Charlie;Math;72;0.90\n")
end

df_students = CSV.read(
    file_path,
    DataFrame;
    delim=';',
    missingstrings=["N/A", ""], # Treat "N/A" and empty strings as missing
    types=Dict(:grade_percent => Union{Missing, Float64}, :attendance_rate => Float64),
    header=true # Explicitly state there's a header row
)

# Display the first few rows to verify
println(first(df_students, 5))

This will output:

3×5 DataFrame
 Row │ student_id  name     subject  grade_percent  attendance_rate
     │ String      String   String   Float64?       Float64
─────┼───────────────────────────────────────────────────────────────
   1 │ S001        Alice    Math             85.0             0.95
   2 │ S002        Bob      Science         missing           0.88
   3 │ S003        Charlie  Math             72.0             0.9

Let's break down some of the common arguments for CSV.read:

delim: Specifies the character used to separate values. Common alternatives to commas include semicolons (;), tabs (\t), or spaces.
header: Can be an integer indicating the row number of the header, a Range for multi-line headers, or a boolean (true if the first row is a header, false otherwise). You can also pass an array of strings to provide custom column names.
missingstrings: A vector of strings that should be interpreted as missing values. CSV.jl will convert these into Julia's missing value. By default, it often handles empty fields as missing.
types: Allows you to specify the data type for each column or for specific columns by name or index. This is useful for ensuring data is read in the correct format, especially for columns that might be ambiguously interpreted (e.g., numbers that should be strings, or ensuring a column can hold missing values with Union{Missing, YourType}).
select: A vector of column names (as Symbol or String) or indices to read. Useful for loading only a subset of columns.
drop: Similar to select, but specifies columns to exclude.
datarow: An integer specifying the first row of actual data, useful if there are introductory lines before the header or data.
normalizenames: If true (the default), column names are sanitized to be valid Julia identifiers (e.g., "Column Name" becomes Column_Name).

After loading your data, it's good practice to inspect it immediately:

# Assuming df_students is loaded as above

# Display the first 5 rows
println("First 5 rows:")
println(first(df_students, 5))

# Get dimensions (rows, columns)
println("\nDimensions:", size(df_students))

# Get column names
println("\nColumn names:", names(df_students))

# Get data types of columns
println("\nColumn types:")
println(eltype.(eachcol(df_students)))

# Get a statistical summary (count, mean, min, max, missing count, etc.)
println("\nSummary statistics:")
println(describe(df_students))

This initial inspection helps you verify that the data was loaded as expected and gives you a first look at its structure and content before proceeding to cleaning and transformation.

Reading Other File Formats

While CSV is prevalent, you might encounter other formats.

Excel Files (.xlsx, .xls): The XLSX.jl package is commonly used. It allows you to read data from specific sheets within an Excel workbook.

using XLSX
# Read the first sheet from an Excel file
# xf = XLSX.readxlsx("my_data.xlsx")
# sheet = xf[XLSX.sheetnames(xf)[1]]
# df_excel = DataFrame(XLSX.gettable(sheet)...)

# Or directly read a sheet into a DataFrame
# df_excel_direct = DataFrame(XLSX.readtable("my_data.xlsx", "Sheet1")...)

Note: The XLSX.readtable function returns a tuple of data columns and header names, which can be splatted (...) into the DataFrame constructor.

JSON and Other Formats: For JSON, JSON3.jl or JSON.jl are popular choices. You'd typically parse the JSON structure and then convert the relevant parts (often an array of objects) into a DataFrame. Other specialized packages exist for formats like Parquet (Parquet.jl), Arrow (Arrow.jl), and database connections (JDBC.jl, ODBC.jl, LibPQ.jl for PostgreSQL, etc.). The general pattern involves using a specific package to read the data into a Julia structure and then, if necessary, converting it to a DataFrame.

Saving DataFrames to Disk

After manipulating and preparing your data, you'll often need to save the resulting DataFrame back to a file. Again, CSV.jl is your primary tool for this when dealing with CSV files.

The function CSV.write is used for this purpose:

# Assume df_processed is a DataFrame you've worked on
# For example, let's create a sample one:
df_processed = DataFrame(
    ID = [1, 2, 3],
    Name = ["Processed_Alice", "Processed_Bob", "Processed_Charlie"],
    Score = [95.0, 88.5, 70.0]
)

CSV.write("processed_data.csv", df_processed)

This will save df_processed to a file named processed_data.csv in your current working directory. CSV.write also accepts several optional arguments:

delim: Specify the delimiter to use (default is ',').
header: A boolean indicating whether to write the column names as the first line (default is true).
append: If true, appends the DataFrame to an existing file. If false (default), it overwrites the file.
bom: (Byte Order Mark) If true, writes a BOM character, which can sometimes help with compatibility in software like Excel for UTF-8 encoded files.
missingstring: The string to use for representing missing values in the output file (default is an empty string).

For example, to save with a tab delimiter and represent missing values as "MISSING":

# Let's add a missing value to df_processed for demonstration
df_processed_with_missing = copy(df_processed)
df_processed_with_missing.Score[2] = missing

CSV.write(
    "processed_data_tab.tsv",
    df_processed_with_missing;
    delim='\t',
    missingstring="MISSING"
)

Similar to reading, if you need to save to other formats like Excel, you would use the relevant package (e.g., XLSX.jl provides XLSX.writetable and other functions to construct and save Excel files).

# Example for saving to Excel (requires XLSX.jl)
# using XLSX
# data_to_save = [
#     "ID" => df_processed.ID,
#     "Name" => df_processed.Name,
#     "Score" => df_processed.Score
# ]
# XLSX.writetable("processed_output.xlsx", data_to_save, sheetname="results", anchor_cell="A1")

Mastering the loading and saving of data is a fundamental skill. With DataFrames.jl and complementary packages like CSV.jl, Julia provides a powerful and flexible environment for these initial I/O operations, setting the stage for the subsequent steps of data cleaning, transformation, and feature engineering that you'll explore next. Always remember to inspect your data after loading to ensure it matches your expectations before getting into more complex manipulations.

Was this section helpful?

References

DataFrames.jl Documentation, The DataFrames.jl Contributors, 2024 - A guide for using DataFrames.jl, including its design principles, functions for data handling, and integration with other Julia packages.
CSV.jl Documentation, The JuliaData Contributors, 2024 - An official resource for efficient reading and writing of CSV files in Julia, detailing various parsing and writing options.
XLSX.jl Documentation, The JuliaData Contributors, 2024 - A resource for working with Excel files, covering how to read data from spreadsheets and save DataFrames to Excel workbooks.