{"id":1851,"date":"2025-03-22T11:28:09","date_gmt":"2025-03-22T10:28:09","guid":{"rendered":"https:\/\/lorentzen.ch\/?p=1851"},"modified":"2025-03-30T12:14:15","modified_gmt":"2025-03-30T10:14:15","slug":"converting-csv-to-parquet","status":"publish","type":"post","link":"https:\/\/lorentzen.ch\/index.php\/2025\/03\/22\/converting-csv-to-parquet\/","title":{"rendered":"Converting arbitrarily large CSVs to Parquet with Python"},"content":{"rendered":"\n

Conversion from CSV to Parquet in streaming mode? No problem for the two power houses Polars<\/a><\/strong> and DuckDB<\/a>. We can even throw in some data preprocessing steps in-between, like column selection, data filters, or sorts.<\/p>\n\n\n\n

Edit: Streaming writing (or “lazy sinking”) of data with Polars was introduced with release 1.25.2 in March 2025, thanks Christian for pointing this out.<\/p>\n\n\n\n

pip install polars<\/code><\/p>\n\n\n\n

\"\"<\/p>\n\n\n\n

pip install duckdb<\/code><\/code><\/p>\n\n\n\n


\"\"<\/p>\n\n\n\n

Run times are on a normal laptop, dedicating 8 threads to the crunching.<\/p>\n\n\n\n

Let’s generate a 2 GB csv file first<\/h2>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
Python<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
import duckdb  # 1.2.1\nimport numpy as np  # 1.26.4\nimport polars as pl  # 1.25.2\n\nn = 100_000_000\n\nrng = np.random.default_rng(42)\n\ndf = pl.DataFrame(\n    {\n        \"X\": rng.choice([\"a\", \"b\", \"c\"], n),\n        \"Y\": rng.uniform(0, 1, n),\n        \"Z\": rng.choice([1, 2, 3, 4, 5], n),\n    }\n)\n\ndf.write_csv(\"data.csv\")<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
\"\"<\/figure>\n\n\n\n
Polars<\/h2>\n\n\n\n
Let’s use Polars in Lazy mode to connect to the CSV, apply some data operations, and stream the result into a Parquet file. <\/p>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
Python<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
# Native API with POLARS_MAX_THREADS = 8\n(\n    pl.scan_csv(\"data.csv\")\n    .filter(pl.col(\"X\") == \"a\")\n    .drop(\"X\")\n    .sort([\"Y\", \"Z\"])\n    .sink_parquet(\"data.parquet\", row_group_size=100_000)  # \"zstd\" compression\n)\n# 3.5 s<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
In case you prefer to write SQL code, you can alternatively use the SQL API of Polars. Curiously, run time is substantially longer: <\/p>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
Python<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
# Via SQL API (slower!?)\n(\n    pl.scan_csv(\"data.csv\")\n    .sql(\"SELECT Y, Z FROM self WHERE X == 'a' ORDER BY Y, Z\")\n    .sink_parquet(\"data.parquet\", row_group_size=100_000)\n)\n\n# 6.8 s<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
In both cases, the result looks as expected, and the resulting Parquet file is about 170 MB large.<\/p>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
Python<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
pl.scan_parquet(\"data.parquet\").head(5).collect()\n\n# Output\n        Y   Z\n      f64 i64\n3.7796e-8\t4\n5.0273e-8\t5\n5.7652e-8\t4\n8.0578e-8\t3\n8.1598e-8\t4<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
DuckDB<\/h2>\n\n\n\n
As an alternative, we use DuckDB<\/a>. Thread pool size and RAM limit can be set on the fly. Setting a low memory limit (e.g., 500 MB) will lead to longer run times, but it works.<\/p>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
Python<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
con = duckdb.connect(config={\"threads\": 8, \"memory_limit\": \"4GB\"})\n\ncon.sql(\n    \"\"\"\n    COPY (\n        SELECT Y, Z\n        FROM 'data.csv'\n        WHERE X == 'a'\n        ORDER BY Y, Z\n    ) TO 'data.parquet' (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100_000)\n    \"\"\"\n)\n\n# 3.9 s<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
Again, the output looks as expected. The Parquet file is again 170 MB large, thanks to using the same compression (“zstd”) as with Polars..<\/p>\n\n\n
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t
Python<\/div>\n\t\t\t<\/div><\/div>\n\t\t\t<\/div>\n\t\t\t
\n\n
con.sql(\"SELECT * FROM 'data.parquet' LIMIT 5\")\n\n# Output\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502           Y            \u2502   Z   \u2502\n\u2502         double         \u2502 int64 \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502  3.779571322581887e-08 \u2502     4 \u2502\n\u2502 5.0273087692787044e-08 \u2502     5 \u2502\n\u2502   5.76523543349694e-08 \u2502     4 \u2502\n\u2502  8.057776434977626e-08 \u2502     3 \u2502\n\u2502  8.159834352650108e-08 \u2502     4 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/pre><\/div>\n\n<\/div><\/div>\n\t\t<\/div>\n\n\n
Final words<\/h2>\n\n\n\n