Recipe to transform latest RNA data to parquet or download data (for 2nd case, uncomment line starting with #)

Using Bash

You need to install curl, jq and duckdb

curl https://www.data.gouv.fr/api/1/datasets/58e53811c751df03df38f42d/| jq -r -c '[.resources[]|select(."type" == "main")]|sort_by(.created_at)|.[].url' | tail -n2 >| latest_urls.txt
# If you want to download, uncomment but here we take remote sources using Duckdb zipfs
# wget -i latest_urls.txt

for url in $(cat latest_urls.txt);
  do filename=$(basename $url);
     filename_no_extension="${filename%%.*}";
  echo $url;
  echo $filename;
  echo $filename_no_extension;
  duckdb -c "INSTALL zipfs FROM community;LOAD zipfs;COPY (SELECT * FROM read_csv('zip://"$url"/*.csv', union_by_name=true, filename = true)) TO '"$filename_no_extension".parquet' (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100_000);"
done;

Using Python

You need to install duckdb library

import json
from urllib.request import urlopen, urlretrieve
import duckdb

url = 'https://www.data.gouv.fr/api/1/datasets/58e53811c751df03df38f42d/'
with urlopen(url) as response:
    json_data = json.loads(response.read().decode('utf-8'))

urls_to_download = [resource['url'] for resource in sorted([resource for resource in json_data['resources'] if resource["type"] == "main"], key=lambda x: x['created_at'])[-2:]]

# If you want to download, uncomment but here we take remote sources using Duckdb zipfs
# for url_to_download in urls_to_download:
#     filename = url_to_download.split('/')[-1]
#     print(filename, url_to_download)
#     urlretrieve(url_to_download, filename)

with duckdb.connect(":memory:") as con:
    con.sql("INSTALL zipfs FROM community")
    con.sql("LOAD zipfs")
    for url_to_download in urls_to_download:
        filename = url_to_download.split('/')[-1].replace('.txt','')
        query = f"""COPY
        (SELECT * FROM read_csv('zip://{url_to_download}/*.csv', union_by_name=true, filename = true))
        TO '{filename}.parquet'
        (FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100_000);"""
        con.sql(query)