Code
library(polars)3 Import/Export
3.1 Import data
3.1.1 Read a csv file or URL
The read_csv() method can be used to import a csv file from a file or an URL. read_csv() returns a DataFrame.
Its main arguments are:
path: path to a file or URL.
sep: single character to use as delimiter in the csv file.
ignore_errors: boolean. Indicate if the first row of dataset is a header or not. If set toFALSE, column names will be autogenerated in the following format: column_x, with x being an enumeration over every column in the dataset starting at 1.
skip_rows: integer. Start reading after skip_rows lines. The header will be parsed at this offset.
n_rows: integer. Stop reading after reading n_rows.
cache: boolean. Cache the result after reading.
overwrite_dtype: named list of dtypes where name points to a column. Can overwrite dtypes during inference.
low_memory: boolean. Reduce memory usage in expense of performance.
comment_char: single byte character used for csv quoting, default = “. Set to NA to turn off special handling and escaping of quotes.
null_values: values to interpret as null values.
infer_schem_length: maximum number of rows to read to infer the column types. If set to 0, all columns will be read as UTF-8. If NULL, a full table scan will be done (slow).
skip_rows_after_header: boolean. Skip this number of rows when the header is parsed.
encoding: either “utf8” or “utf8-lossy”. Lossy means that invalid utf8 values are replaced with “?” characters.
row_count_name: string. Name of a added row count column.
row_count_offset: integer. Offset to start the row_count column (only used if the name is set).
parse_dates: boolean. Try to automatically parse dates. If this does not succeed, the column remains of data typeUtf8.
reuse_downloaded: boolean. If TRUE(default) and a URL was provided, cache the downloaded files in session for an easy reuse.
By default, polars takes the first row of the csv file as the header to set column names. If the first row is not a header, you can set the argument has_header = FALSE and the column names will be column_1, column_2…
3.1.1.1 From a file
pl$read_csv("examples/iris.csv")shape: (150, 5)
┌──────────────┬─────────────┬──────────────┬─────────────┬───────────┐
│ Sepal.Length ┆ Sepal.Width ┆ Petal.Length ┆ Petal.Width ┆ Species │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 ┆ f64 ┆ str │
╞══════════════╪═════════════╪══════════════╪═════════════╪═══════════╡
│ 5.1 ┆ 3.5 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.9 ┆ 3.0 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.7 ┆ 3.2 ┆ 1.3 ┆ 0.2 ┆ setosa │
│ 4.6 ┆ 3.1 ┆ 1.5 ┆ 0.2 ┆ setosa │
│ 5.0 ┆ 3.6 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ … ┆ … ┆ … ┆ … ┆ … │
│ 6.7 ┆ 3.0 ┆ 5.2 ┆ 2.3 ┆ virginica │
│ 6.3 ┆ 2.5 ┆ 5.0 ┆ 1.9 ┆ virginica │
│ 6.5 ┆ 3.0 ┆ 5.2 ┆ 2.0 ┆ virginica │
│ 6.2 ┆ 3.4 ┆ 5.4 ┆ 2.3 ┆ virginica │
│ 5.9 ┆ 3.0 ┆ 5.1 ┆ 1.8 ┆ virginica │
└──────────────┴─────────────┴──────────────┴─────────────┴───────────┘read.csv("examples/iris.csv") Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1 5.1 3.5 1.4 0.2 setosa
2 4.9 3.0 1.4 0.2 setosa
3 4.7 3.2 1.3 0.2 setosa
4 4.6 3.1 1.5 0.2 setosa
5 5.0 3.6 1.4 0.2 setosa
6 5.4 3.9 1.7 0.4 setosa
7 4.6 3.4 1.4 0.3 setosa
8 5.0 3.4 1.5 0.2 setosa
9 4.4 2.9 1.4 0.2 setosa
10 4.9 3.1 1.5 0.1 setosa
11 5.4 3.7 1.5 0.2 setosa
12 4.8 3.4 1.6 0.2 setosa
13 4.8 3.0 1.4 0.1 setosa
14 4.3 3.0 1.1 0.1 setosa
15 5.8 4.0 1.2 0.2 setosa
16 5.7 4.4 1.5 0.4 setosa
17 5.4 3.9 1.3 0.4 setosa
18 5.1 3.5 1.4 0.3 setosa
19 5.7 3.8 1.7 0.3 setosa
20 5.1 3.8 1.5 0.3 setosa
21 5.4 3.4 1.7 0.2 setosa
22 5.1 3.7 1.5 0.4 setosa
23 4.6 3.6 1.0 0.2 setosa
24 5.1 3.3 1.7 0.5 setosa
25 4.8 3.4 1.9 0.2 setosa
26 5.0 3.0 1.6 0.2 setosa
27 5.0 3.4 1.6 0.4 setosa
28 5.2 3.5 1.5 0.2 setosa
29 5.2 3.4 1.4 0.2 setosa
30 4.7 3.2 1.6 0.2 setosa
31 4.8 3.1 1.6 0.2 setosa
32 5.4 3.4 1.5 0.4 setosa
33 5.2 4.1 1.5 0.1 setosa
34 5.5 4.2 1.4 0.2 setosa
35 4.9 3.1 1.5 0.2 setosa
36 5.0 3.2 1.2 0.2 setosa
37 5.5 3.5 1.3 0.2 setosa
38 4.9 3.6 1.4 0.1 setosa
39 4.4 3.0 1.3 0.2 setosa
40 5.1 3.4 1.5 0.2 setosa
41 5.0 3.5 1.3 0.3 setosa
42 4.5 2.3 1.3 0.3 setosa
43 4.4 3.2 1.3 0.2 setosa
44 5.0 3.5 1.6 0.6 setosa
45 5.1 3.8 1.9 0.4 setosa
46 4.8 3.0 1.4 0.3 setosa
47 5.1 3.8 1.6 0.2 setosa
48 4.6 3.2 1.4 0.2 setosa
49 5.3 3.7 1.5 0.2 setosa
50 5.0 3.3 1.4 0.2 setosa
51 7.0 3.2 4.7 1.4 versicolor
52 6.4 3.2 4.5 1.5 versicolor
53 6.9 3.1 4.9 1.5 versicolor
54 5.5 2.3 4.0 1.3 versicolor
55 6.5 2.8 4.6 1.5 versicolor
56 5.7 2.8 4.5 1.3 versicolor
57 6.3 3.3 4.7 1.6 versicolor
58 4.9 2.4 3.3 1.0 versicolor
59 6.6 2.9 4.6 1.3 versicolor
60 5.2 2.7 3.9 1.4 versicolor
61 5.0 2.0 3.5 1.0 versicolor
62 5.9 3.0 4.2 1.5 versicolor
63 6.0 2.2 4.0 1.0 versicolor
64 6.1 2.9 4.7 1.4 versicolor
65 5.6 2.9 3.6 1.3 versicolor
66 6.7 3.1 4.4 1.4 versicolor
67 5.6 3.0 4.5 1.5 versicolor
68 5.8 2.7 4.1 1.0 versicolor
69 6.2 2.2 4.5 1.5 versicolor
70 5.6 2.5 3.9 1.1 versicolor
71 5.9 3.2 4.8 1.8 versicolor
72 6.1 2.8 4.0 1.3 versicolor
73 6.3 2.5 4.9 1.5 versicolor
74 6.1 2.8 4.7 1.2 versicolor
75 6.4 2.9 4.3 1.3 versicolor
76 6.6 3.0 4.4 1.4 versicolor
77 6.8 2.8 4.8 1.4 versicolor
78 6.7 3.0 5.0 1.7 versicolor
79 6.0 2.9 4.5 1.5 versicolor
80 5.7 2.6 3.5 1.0 versicolor
81 5.5 2.4 3.8 1.1 versicolor
82 5.5 2.4 3.7 1.0 versicolor
83 5.8 2.7 3.9 1.2 versicolor
84 6.0 2.7 5.1 1.6 versicolor
85 5.4 3.0 4.5 1.5 versicolor
86 6.0 3.4 4.5 1.6 versicolor
87 6.7 3.1 4.7 1.5 versicolor
88 6.3 2.3 4.4 1.3 versicolor
89 5.6 3.0 4.1 1.3 versicolor
90 5.5 2.5 4.0 1.3 versicolor
91 5.5 2.6 4.4 1.2 versicolor
92 6.1 3.0 4.6 1.4 versicolor
93 5.8 2.6 4.0 1.2 versicolor
94 5.0 2.3 3.3 1.0 versicolor
95 5.6 2.7 4.2 1.3 versicolor
96 5.7 3.0 4.2 1.2 versicolor
97 5.7 2.9 4.2 1.3 versicolor
98 6.2 2.9 4.3 1.3 versicolor
99 5.1 2.5 3.0 1.1 versicolor
100 5.7 2.8 4.1 1.3 versicolor
101 6.3 3.3 6.0 2.5 virginica
102 5.8 2.7 5.1 1.9 virginica
103 7.1 3.0 5.9 2.1 virginica
104 6.3 2.9 5.6 1.8 virginica
105 6.5 3.0 5.8 2.2 virginica
106 7.6 3.0 6.6 2.1 virginica
107 4.9 2.5 4.5 1.7 virginica
108 7.3 2.9 6.3 1.8 virginica
109 6.7 2.5 5.8 1.8 virginica
110 7.2 3.6 6.1 2.5 virginica
111 6.5 3.2 5.1 2.0 virginica
112 6.4 2.7 5.3 1.9 virginica
113 6.8 3.0 5.5 2.1 virginica
114 5.7 2.5 5.0 2.0 virginica
115 5.8 2.8 5.1 2.4 virginica
116 6.4 3.2 5.3 2.3 virginica
117 6.5 3.0 5.5 1.8 virginica
118 7.7 3.8 6.7 2.2 virginica
119 7.7 2.6 6.9 2.3 virginica
120 6.0 2.2 5.0 1.5 virginica
121 6.9 3.2 5.7 2.3 virginica
122 5.6 2.8 4.9 2.0 virginica
123 7.7 2.8 6.7 2.0 virginica
124 6.3 2.7 4.9 1.8 virginica
125 6.7 3.3 5.7 2.1 virginica
126 7.2 3.2 6.0 1.8 virginica
127 6.2 2.8 4.8 1.8 virginica
128 6.1 3.0 4.9 1.8 virginica
129 6.4 2.8 5.6 2.1 virginica
130 7.2 3.0 5.8 1.6 virginica
131 7.4 2.8 6.1 1.9 virginica
132 7.9 3.8 6.4 2.0 virginica
133 6.4 2.8 5.6 2.2 virginica
134 6.3 2.8 5.1 1.5 virginica
135 6.1 2.6 5.6 1.4 virginica
136 7.7 3.0 6.1 2.3 virginica
137 6.3 3.4 5.6 2.4 virginica
138 6.4 3.1 5.5 1.8 virginica
139 6.0 3.0 4.8 1.8 virginica
140 6.9 3.1 5.4 2.1 virginica
141 6.7 3.1 5.6 2.4 virginica
142 6.9 3.1 5.1 2.3 virginica
143 5.8 2.7 5.1 1.9 virginica
144 6.8 3.2 5.9 2.3 virginica
145 6.7 3.3 5.7 2.5 virginica
146 6.7 3.0 5.2 2.3 virginica
147 6.3 2.5 5.0 1.9 virginica
148 6.5 3.0 5.2 2.0 virginica
149 6.2 3.4 5.4 2.3 virginica
150 5.9 3.0 5.1 1.8 virginica3.1.1.2 From multiple files
First, let’s create a dozen csv files
dir.create("Datasets")
mydf <- data.frame(
col1 = 1:3,
col2 = c("a", "b", "c")
)
for (i in 1:10) {
write.csv(mydf, file = paste0("Datasets/example_data_",i,".csv"))
}
Important
June 2023: Reading those multiple files into a single DataFrame is not yet implemented in R. See here for an example in Python.
3.1.1.3 From an URL
The read_csv() method also works with an URL:
pl$read_csv("https://j.mp/iriscsv")tmp file placed in
/tmp/RtmpdH6GrA/file210a2cefd383shape: (150, 5)
┌──────────────┬─────────────┬──────────────┬─────────────┬───────────┐
│ sepal_length ┆ sepal_width ┆ petal_length ┆ petal_width ┆ species │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 ┆ f64 ┆ str │
╞══════════════╪═════════════╪══════════════╪═════════════╪═══════════╡
│ 5.1 ┆ 3.5 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.9 ┆ 3.0 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.7 ┆ 3.2 ┆ 1.3 ┆ 0.2 ┆ setosa │
│ 4.6 ┆ 3.1 ┆ 1.5 ┆ 0.2 ┆ setosa │
│ 5.0 ┆ 3.6 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ … ┆ … ┆ … ┆ … ┆ … │
│ 6.7 ┆ 3.0 ┆ 5.2 ┆ 2.3 ┆ virginica │
│ 6.3 ┆ 2.5 ┆ 5.0 ┆ 1.9 ┆ virginica │
│ 6.5 ┆ 3.0 ┆ 5.2 ┆ 2.0 ┆ virginica │
│ 6.2 ┆ 3.4 ┆ 5.4 ┆ 2.3 ┆ virginica │
│ 5.9 ┆ 3.0 ┆ 5.1 ┆ 1.8 ┆ virginica │
└──────────────┴─────────────┴──────────────┴─────────────┴───────────┘👉 For a complete list of arguments to use with the read_csv() method, see this page.
3.1.2 Scan a csv file
The scan_csv() method can be used to lazily read a csv file from a file.
pl$scan_csv() returns a LazyFrame.
It’s argument are the same as read_csv() method (see section above).
This allows the query optimizer to push down predicates and projections to the scan level, thereby potentially reducing memory overhead.
pl$scan_csv(
"examples/iris.csv")$select( # lazy, don't do a thing
pl$col(c("Petal.Length","Petal.Width")) # select only 2 columns
)$
filter(
pl$col("Petal.Length") > 4 # the filter is pushed down the scan, so less data is read into memory
)$collect() # <- don't forget collect() here!shape: (84, 2)
┌──────────────┬─────────────┐
│ Petal.Length ┆ Petal.Width │
│ --- ┆ --- │
│ f64 ┆ f64 │
╞══════════════╪═════════════╡
│ 4.7 ┆ 1.4 │
│ 4.5 ┆ 1.5 │
│ 4.9 ┆ 1.5 │
│ 4.6 ┆ 1.5 │
│ 4.5 ┆ 1.3 │
│ … ┆ … │
│ 5.2 ┆ 2.3 │
│ 5.0 ┆ 1.9 │
│ 5.2 ┆ 2.0 │
│ 5.4 ┆ 2.3 │
│ 5.1 ┆ 1.8 │
└──────────────┴─────────────┘👉 For a complete list of arguments to use with the lazy_csv_reader() method, see this page.
Important
June 2023: arguments available in Python eol_char and with_column_names not yet supporting in R
3.1.3 Scan a parquet file
3.1.3.1 From a single file
The scan_parquet() method can be used to lazily read a parquet file from a file.
Scanning delays the actual parsing of the file and pl$scan_parquet() returns a LazyFrame.
Its main arguments are:
path: path to file.
n_rows: integer. Limit rows to scan.
cache: boolean. Cache the result.
parallel: string. Either “Auto”, “None”, “Columns” or “RowGroups”. The way to parallelized the scan.
rechunk: boolean.rechunkreorganize memory layout, potentially make future operations faster , however perform reallocation now.
row_count_name: string. Name of a added row count column.
row_count_offset: integer. Offset to start the row_count column (only used if the name is set).
low_memory: boolean. Reduce memory usage in expense of performance.
pl$scan_parquet("examples/iris.parquet")polars LazyFrame
$describe_optimized_plan() : Show the optimized query plan.
Naive plan:
Parquet SCAN examples/iris.parquet
PROJECT */5 COLUMNS👉 For a complete list of arguments to use with the scan_parquet() method, see this page.
At the end of the query, don’t forget to use the collect() method to inform Polars that you want to execute it.
pl$scan_parquet("examples/iris.parquet")$
collect()shape: (150, 5)
┌──────────────┬─────────────┬──────────────┬─────────────┬───────────┐
│ Sepal.Length ┆ Sepal.Width ┆ Petal.Length ┆ Petal.Width ┆ Species │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 ┆ f64 ┆ cat │
╞══════════════╪═════════════╪══════════════╪═════════════╪═══════════╡
│ 5.1 ┆ 3.5 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.9 ┆ 3.0 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.7 ┆ 3.2 ┆ 1.3 ┆ 0.2 ┆ setosa │
│ 4.6 ┆ 3.1 ┆ 1.5 ┆ 0.2 ┆ setosa │
│ 5.0 ┆ 3.6 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ … ┆ … ┆ … ┆ … ┆ … │
│ 6.7 ┆ 3.0 ┆ 5.2 ┆ 2.3 ┆ virginica │
│ 6.3 ┆ 2.5 ┆ 5.0 ┆ 1.9 ┆ virginica │
│ 6.5 ┆ 3.0 ┆ 5.2 ┆ 2.0 ┆ virginica │
│ 6.2 ┆ 3.4 ┆ 5.4 ┆ 2.3 ┆ virginica │
│ 5.9 ┆ 3.0 ┆ 5.1 ┆ 1.8 ┆ virginica │
└──────────────┴─────────────┴──────────────┴─────────────┴───────────┘
Caution
August 2023 : Export methods have not yet been implemented in R. This methods start with write_ (write_parquet(), write_parquet(), write_json(), write_ndjson()…)
3.1.3.2 From multiple files
The scan_parquet() method can also be used to lazily read multiple parquet files in the same folder.
This is particularly useful for partitioned files! For example:
# Write multiple parquet files in examples folder
arrow::write_dataset(dataset = iris,
path = "examples",
partitioning = "Species")
# Reading all parquet files in the example folder and its subfolders
pl$scan_parquet("examples/*/*.parquet")$
collect()shape: (150, 5)
┌──────────────┬─────────────┬──────────────┬─────────────┬───────────┐
│ Sepal.Length ┆ Sepal.Width ┆ Petal.Length ┆ Petal.Width ┆ Species │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ f64 ┆ f64 ┆ f64 ┆ f64 ┆ str │
╞══════════════╪═════════════╪══════════════╪═════════════╪═══════════╡
│ 5.1 ┆ 3.5 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.9 ┆ 3.0 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ 4.7 ┆ 3.2 ┆ 1.3 ┆ 0.2 ┆ setosa │
│ 4.6 ┆ 3.1 ┆ 1.5 ┆ 0.2 ┆ setosa │
│ 5.0 ┆ 3.6 ┆ 1.4 ┆ 0.2 ┆ setosa │
│ … ┆ … ┆ … ┆ … ┆ … │
│ 6.7 ┆ 3.0 ┆ 5.2 ┆ 2.3 ┆ virginica │
│ 6.3 ┆ 2.5 ┆ 5.0 ┆ 1.9 ┆ virginica │
│ 6.5 ┆ 3.0 ┆ 5.2 ┆ 2.0 ┆ virginica │
│ 6.2 ┆ 3.4 ┆ 5.4 ┆ 2.3 ┆ virginica │
│ 5.9 ┆ 3.0 ┆ 5.1 ┆ 1.8 ┆ virginica │
└──────────────┴─────────────┴──────────────┴─────────────┴───────────┘In the code above:
/*refers to all subfolders in theexamplefolder
/*.parquetrefers to all files with a.parquetextension
Important
In this case, note that the Species column which been used for partitioning is missing
Of course, the advantage of using pl$scan_parquet() is that you can query several partitioned files and retrieve the result of the query in R. See an example here.
3.2 Export data to Excel
As with Python, there are no native method in Rust for exporting to Excel format.
The best current solution is to use the data.frame conversion method and then use the {openxlsx} package or one of its {tablexlsx} wrapper to export these DataFrames in xlsx format. The more recent{openxlxs2} package is also a great tool for exporting to xlsx files.
Let’s look at the syntax of a simple export with theses 3 packages:
# install.packages("openxlsx")
library(openxlsx)
openxlsx::write.xlsx(
iris,
file = tempdir()
)# install.packages("tablexlsx")
library(tablexlsx)
iris |> toxlsx(path = tempdir())# install.packages("openxlsx2")
library(openxlsx2)
openxlsx2::write_xlsx(
iris,
file = tempdir()
)