library(tidyverse)
library(fs)Note: This article is translated from my Japanese article.
In this article, I introduce Parquet, a promising alternative to CSV that is also well-suited for big data analysis.
Now, data frames are one of the most fundamental data structures in data analysis. By using packages for manipulating data frames, such as tibble/dplyr in R and pandas in Python, we can carry out data analysis that used to be done with spreadsheet software like Excel even more efficiently.
While data analysis tools have become this well-developed, CSV, which has high compatibility with software like Excel, is still widely used for storing data. However, CSV is not necessarily a file format well-suited for data analysis. So, let’s compare Parquet, which is increasingly being used as an alternative to CSV, with CSV.
Preparing sample data
To compare CSV and Parquet, let’s first prepare sample data typical of data analysis. This time, we’ll create sample data from who (World Health Organization (WHO) tuberculosis data) provided by the tidyr package.
In recent years, the concept of tidy data has become widespread in data analysis. Tidy data is data in which each variable forms a single column, and each observation (value) forms a single row.
So, can who be considered tidy data? who has many columns such as "new_sp_m014" through "newrel_f65", but each of these columns actually contains multiple variables, such as the diagnosis result (sp or sel), gender (m or f), and age group (014 or 65). Therefore, who is not tidy data. So, following this article, we’ll transform it into who_longer, which is tidy data.
In data analysis, who_longer, being tidy data, is easier to work with than who. However, we can see that who_longer (about 400,000 rows) has roughly 50 times more rows than who (about 7,000 rows). Therefore, storing data like who_longer, which is tidy data, as a text file such as CSV results in a large increase in file size.
In this way, we can see that while tidy data is well-suited for data analysis, it is not well-suited for storage as a text file like CSV. However, this data storage problem can be solved by using Parquet.
Here, let’s compare who, which is not tidy data, with who_longer, which is tidy data.
Code
levels_gender <- c("f", "m")
levels_age <- c("014", "1524", "2534", "3544", "4554", "5564", "65")
who_longer <- who |>
pivot_longer(cols = new_sp_m014:newrel_f65,
names_to = c("diagnosis", "gender", "age"),
names_pattern = "new_?(.*)_(.)(.*)",
names_transform = list(gender = ~ .x |>
readr::parse_factor(levels = levels_gender),
age = ~ .x |>
readr::parse_factor(levels = levels_age,
ordered = TRUE)),
values_to = "count")# Before tidying
print(who, n = 5)# A tibble: 7,240 × 60
country iso2 iso3 year new_sp_m014 new_sp_m1524 new_sp_m2534 new_sp_m3544
<chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Afghanis… AF AFG 1980 NA NA NA NA
2 Afghanis… AF AFG 1981 NA NA NA NA
3 Afghanis… AF AFG 1982 NA NA NA NA
4 Afghanis… AF AFG 1983 NA NA NA NA
5 Afghanis… AF AFG 1984 NA NA NA NA
# ℹ 7,235 more rows
# ℹ 52 more variables: new_sp_m4554 <dbl>, new_sp_m5564 <dbl>,
# new_sp_m65 <dbl>, new_sp_f014 <dbl>, new_sp_f1524 <dbl>,
# new_sp_f2534 <dbl>, new_sp_f3544 <dbl>, new_sp_f4554 <dbl>,
# new_sp_f5564 <dbl>, new_sp_f65 <dbl>, new_sn_m014 <dbl>,
# new_sn_m1524 <dbl>, new_sn_m2534 <dbl>, new_sn_m3544 <dbl>,
# new_sn_m4554 <dbl>, new_sn_m5564 <dbl>, new_sn_m65 <dbl>, …# After tidying
print(who_longer, n = 5)# A tibble: 405,440 × 8
country iso2 iso3 year diagnosis gender age count
<chr> <chr> <chr> <dbl> <chr> <fct> <ord> <dbl>
1 Afghanistan AF AFG 1980 sp m 014 NA
2 Afghanistan AF AFG 1980 sp m 1524 NA
3 Afghanistan AF AFG 1980 sp m 2534 NA
4 Afghanistan AF AFG 1980 sp m 3544 NA
5 Afghanistan AF AFG 1980 sp m 4554 NA
# ℹ 405,435 more rowsSaving CSV and Parquet files
In R, you can save CSV files with write_csv(). Similarly, you can save Parquet files with write_parquet() from the arrow package. Let’s try saving who_longer as both CSV and Parquet.
As you can see, both CSV and Parquet make it easy to save data.
library(arrow)
# Save as CSV
write_csv(who_longer, "who_longer.csv")
# Save as Parquet
write_parquet(who_longer, "who_longer.parquet")Advantages of Parquet compared to CSV
From here, we’ll compare the saved CSV and Parquet files of who_longer and introduce the advantages of Parquet over CSV.
Advantage 1: Smaller file size than CSV
As mentioned earlier, tidy data tends to have many rows, which makes it poorly suited for storage as CSV, and using Parquet is preferable.
In fact, the file sizes of the CSV and Parquet versions of who_longer are 14.1 MB and 154 KB, respectively, showing that Parquet takes up only about 1% of the file size of CSV.
This level of size reduction can’t be expected in every case, but since Parquet is column-oriented and compresses data, it can be said to be a data format well-suited for storing tidy data, which is commonly used in R and elsewhere.
# CSV
file_size("who_longer.csv")14.1M# Parquet
file_size("who_longer.parquet")161Kunits::set_units(file_size("who_longer.parquet") / file_size("who_longer.csv")) |>
units::set_units(`%`)1.116534 [%]Advantage 2: Easier to read than CSV
Just as data can be written with write_csv()/write_parquet(), CSV/Parquet data can be read with read_csv()/read_parquet().
Since CSV is a text format, you need to specify the type of each column with col_types when reading it (by default, the types are guessed automatically).
On the other hand, since Parquet stores the type information of each column at write time, there is no need to specify types when reading it.
# Read CSV
read_csv("who_longer.csv",
col_types = cols(.default = "c",
year = "i",
count = "i"))
# Read Parquet
read_parquet("who_longer.parquet")Advantage 3: Better suited than CSV for reading and aggregating big data
CSV is not well-suited for storing big data, and until now, it has been necessary to use something like SQL for storing big data instead.
In R, packages such as dplyr (dbplyr) and DBI make it easy to use SQL, but since SQL requires connecting to and disconnecting from a database, it works differently from CSV and may be a hurdle for beginners.
Also, since (most?) SQL databases are row-oriented, they are well-suited for adding, updating, and deleting data, but for storing and aggregating data used in data analysis, the column-oriented Parquet seems better suited.
When aggregating big data using CSV files, you need to load all the data into memory at once. This can put pressure on memory during data loading.
With Parquet, by setting as_data_frame = FALSE when reading, you can, just like with SQL, filter and aggregate data without loading it into memory.
Here, let’s calculate the number of patients in Japan by year and diagnosis. You can efficiently build the query using dplyr functions such as filter(), group_by(), and summarise(). Finally, calling collect() outputs the result as a data frame.
read_parquet("who_longer.parquet",
as_data_frame = FALSE) |>
filter(country == "Japan",
!is.na(count)) |>
group_by(country, year, diagnosis) |>
summarise(count = sum(count),
.groups = "drop") |>
collect()# A tibble: 33 × 4
country year diagnosis count
<chr> <dbl> <chr> <dbl>
1 Japan 1995 sp 14367
2 Japan 1996 sp 12867
3 Japan 1997 sp 13571
4 Japan 1998 sp 11935
5 Japan 2001 sp 11408
6 Japan 2002 sp 10807
7 Japan 2003 sp 10843
8 Japan 2004 sp 10471
9 Japan 1999 sp 12909
10 Japan 2000 sp 11853
# ℹ 23 more rowsAdvantage 4: Can handle datasets made up of multiple files
Because Parquet is column-oriented, unlike row-oriented SQL, it is not well-suited for adding, updating, or deleting data. However, Parquet makes it easy to read datasets made up of multiple files, which easily resolves this disadvantage.
Here, let’s use the "who_longer_byage" folder, which contains Parquet files with who_longer split by age group, as a sample dataset.
By using open_dataset("who_longer_byage"), we can easily perform the same data aggregation as before, even though the folder contains multiple Parquet files.
Code
dir_create("who_longer_byage")
who_longer |>
group_by(age) |>
group_walk(~ .x |>
write_parquet(str_glue("who_longer_byage/who_longer_{.y$age}.parquet")),
.keep = TRUE)open_dataset("who_longer_byage") |>
filter(country == "Japan",
!is.na(count)) |>
group_by(country, year, diagnosis) |>
summarise(count = sum(count),
.groups = "drop") |>
collect()# A tibble: 33 × 4
country year diagnosis count
<chr> <dbl> <chr> <dbl>
1 Japan 1995 sp 14367
2 Japan 1996 sp 12867
3 Japan 1997 sp 13571
4 Japan 1998 sp 11935
5 Japan 2001 sp 11408
6 Japan 2002 sp 10807
7 Japan 2003 sp 10843
8 Japan 2004 sp 10471
9 Japan 2005 sp 10931
10 Japan 2006 sp 10159
# ℹ 23 more rowsAdvantage 5: Well-suited for exchanging data between R and Python
Since Python’s pandas package supports reading and writing Parquet files, Parquet is also well-suited for exchanging data between R and Python.
Let’s try reading the 'who_longer.parquet' file created in R with pandas.
import pandas as pd
pd.read_parquet('who_longer.parquet') country iso2 iso3 year diagnosis gender age count
0 Afghanistan AF AFG 1980.0 sp m 014 NaN
1 Afghanistan AF AFG 1980.0 sp m 1524 NaN
2 Afghanistan AF AFG 1980.0 sp m 2534 NaN
3 Afghanistan AF AFG 1980.0 sp m 3544 NaN
4 Afghanistan AF AFG 1980.0 sp m 4554 NaN
... ... ... ... ... ... ... ... ...
405435 Zimbabwe ZW ZWE 2013.0 rel f 2534 4649.0
405436 Zimbabwe ZW ZWE 2013.0 rel f 3544 3526.0
405437 Zimbabwe ZW ZWE 2013.0 rel f 4554 1453.0
405438 Zimbabwe ZW ZWE 2013.0 rel f 5564 811.0
405439 Zimbabwe ZW ZWE 2013.0 rel f 65 725.0
[405440 rows x 8 columns]Summary
So far, we’ve introduced the advantages of Parquet available in both R and Python. Parquet is well-suited for storing and aggregating tidy data, which has become widespread in data analysis in recent years.
In addition, packages such as sfarrow, which can save data from the sf package (used for handling geographic data) as Parquet, have also recently appeared.
By using Parquet instead of CSV, data analysis is expected to become even easier.