Recipe to transform latest RNA data to parquet or download latest data

README.md

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)