French DVF Real-Estate → Analytics Schema

View on GitHub

What

Reshape France's official "Demandes de valeurs foncières" (DVF) raw real-estate transaction export into a clean analytics CSV with ISO dates, proper euro amounts, and a repaired postal code.

Why interesting

DVF is the canonical open dataset for French property prices (every registered sale, published by the tax authority DGFiP), and the raw file is a textbook example of a European-government CSV that quietly breaks US-centric tooling: (1) it is pipe-delimited (|), not comma-delimited, so a default read_csv sees one giant column; (2) monetary values use a comma decimal — 346,50 means 346.50 €, but a parser that assumes , is a thousands separator reads it as 34650 (a 100× overvaluation) or splits the field; (3) dates are DD/MM/YYYY, so 02/01/2024 is 2 January, not 1 February; (4) the Code postal column was stored as a number by the producer, so Ain's 01230 already arrives as 1230 — its leading zero is gone before you even open the file.

Edge cases sourced from.

• DGFiP — "Notice descriptive du fichier DVF" — the official field/format description (pipe delimiter, comma decimals, column meanings)
• French postal codes are five digits with a leading zero for départements 01–09; storing them numerically (a recurring open-data defect) drops it

Data source. DGFiP — Demandes de valeurs foncières (data.gouv.fr) (this slice: 9 real transactions from the 2024 file, département 01 / Ain, hand-picked to show a comma-decimal price, each property type, and several postal codes that lost their leading zero). Licence Ouverte / Etalab (CC-BY-compatible).

At full scale

The committed sample.csv is a 9-row teaching slice; the real 2024 file is every property transaction registered in France that year. Pull it and run the same template against the whole thing:

bash fetch-full.sh          # downloads + extracts ./full/ValeursFoncieres-2024.txt (~446 MB)
bxp-cli --config full.json  # processes all ~3.5M transactions

Measured on the reference machine (ReleaseFast, 8 cores):

metric	value
input	3,499,931 rows / 446 MB (pipe-delimited)
output	3,499,931 rows / 213 MB (1:1)
wall time	~4.9 s
peak RSS	~22 MB (flat — does not grow with the file)

The tricks

(see inline comments in sample.json):

1. Pipe delimiter — csv_delimiter_in: "|".
2. Comma decimal — csv_decimal_separator_in: "," so 346,50 arrives as 346.5 instead of 34650 or a split field.
3. DD/MM/YYYY → ISO — DATE_CONVERT([Date mutation], 'DD/MM/YYYY', 'YYYY-MM-DD').
4. Leading-zero département preserved — [Code departement] is stored as text (01) and passes through verbatim.
5. Damaged postal code repaired — [Code postal] already lost its zero in the source (1230); LPAD([Code postal], 5, '0') re-pads it back to 01230. (This only emits the correct value since the bxp leading-zero fix — previously the padded result was re-canonicalised straight back to 1230.)

Final result

A pipe-delimited row with a comma decimal and a broken postal code becomes a clean analytics row:

raw   02/01/2024 | 346,50   | CHALEY | 01 | 1230
clean 2024-01-02 , 346.5    , CHALEY , 01 , 1230 (raw) , 01230 (fixed)

• 346,50 → 346.5. A naive importer reads that comma as a thousands separator and logs the sale at €34,650 — a 100× error that silently corrupts every price aggregate downstream.
• 1230 → 01230. Join the raw value against a postal-code reference table and every département 01–09 row misses. The repaired column joins.

Trace it in the GUI

Click the price_eur cell: the trace pane shows [Valeur fonciere] resolving the comma-decimal field to 346.5. Click postal_fixed to watch LPAD rebuild the zero the source threw away.

Sample data

Run it with bxp-cli --config ./sample.json --template dvf_realestate_to_analytics:

sample.json (config)sample.csvsample.csvx (result)

{
  // French DGFiP "Demandes de valeurs foncières" (DVF) — the raw open-data
  // real-estate transaction export. A textbook European-government CSV:
  // pipe-delimited, comma-decimal money, DD/MM/YYYY dates, and a postal-code
  // column the producer already stored as a number (leading zero lost).
  conversion_templates: {
    dvf_realestate_to_analytics: {
      data_dir:                  ".",
      file_pattern_in:           ".csv",
      file_pattern_out:          ".csvx",
      // TRICK 0 — the file is pipe-delimited, not comma-delimited.
      csv_delimiter_in:          "|",
      // TRICK 1 — monetary values use a comma decimal: "346,50" = 346.50 €.
      // A US-centric parser reads "346,50" as 34650 (comma = thousands) — a
      // 100x error — or splits the field. Declaring the decimal separator
      // makes [Valeur fonciere] arrive as a clean 346.5.
      csv_decimal_separator_in:  ",",
      csv_delimiter_out:         ",",
      csv_text_quote_out:        "double",

      input_schema: {
        // TRICK 2 — French dates are DD/MM/YYYY; convert to ISO-8601.
        $date:          "DATE_CONVERT([Date mutation], 'DD/MM/YYYY', 'YYYY-MM-DD')",
        $sale_type:     "[Nature mutation]",
        $price_eur:     "[Valeur fonciere]",
        $commune:       "[Commune]",

        // TRICK 3 — `Code departement` is stored as text and keeps its
        // leading zero ("01" = Ain). bxp passes it through verbatim.
        $dept:          "[Code departement]",

        // TRICK 4 — `Code postal`, by contrast, was stored as a NUMBER by the
        // producer, so its leading zero is already gone ("01230" → "1230").
        // Re-pad it back to five digits with LPAD, which says the intent
        // outright — the older concat-and-slice spelling RIGHT('00000' & x, 5)
        // makes the reader simulate it. (Either way the value only survives as
        // "01230" since the bxp leading-zero fix — before it, the padded value
        // was re-canonicalised back to "1230".)
        $postal_raw:    "[Code postal]",
        $postal_fixed:  "LPAD([Code postal], 5, '0')",

        $property_type: "[Type local]",
        $area_m2:       "[Surface reelle bati]",
        $rooms:         "[Nombre pieces principales]"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        date:          "$date",
        sale_type:     "$sale_type",
        price_eur:     "$price_eur",
        commune:       "$commune",
        dept:          "$dept",
        postal_raw:    "$postal_raw",
        postal_fixed:  "$postal_fixed",
        property_type: "$property_type",
        area_m2:       "$area_m2",
        rooms:         "$rooms"
      }
    }
  }
}

Identifiant de document|Reference document|1 Articles CGI|2 Articles CGI|3 Articles CGI|4 Articles CGI|5 Articles CGI|No disposition|Date mutation|Nature mutation|Valeur fonciere|No voie|B/T/Q|Type de voie|Code voie|Voie|Code postal|Commune|Code departement|Code commune|Prefixe de section|Section|No plan|No Volume|1er lot|Surface Carrez du 1er lot|2eme lot|Surface Carrez du 2eme lot|3eme lot|Surface Carrez du 3eme lot|4eme lot|Surface Carrez du 4eme lot|5eme lot|Surface Carrez du 5eme lot|Nombre de lots|Code type local|Type local|Identifiant local|Surface reelle bati|Nombre pieces principales|Nature culture|Nature culture speciale|Surface terrain
|||||||000001|02/01/2024|Vente|346,50||||B020|LE DELIVRE|1230|CHALEY|01|76||B|514||||||||||||0||||||P||99
|||||||000001|03/01/2024|Vente|329500,00|29||PL|0500|DU JURA|1170|GEX|01|173||AI|551||175||29||||||||2|3|Dépendance||0|0|||
|||||||000001|03/01/2024|Vente|329500,00|9001||PL|0500|DU JURA|1170|GEX|01|173||AI|551||50||||||||||1|3|Dépendance||0|0|||
|||||||000001|03/01/2024|Vente|329500,00|29||PL|0500|DU JURA|1170|GEX|01|173||AI|551||175||29||||||||2|2|Appartement||89|4|||
|||||||000001|03/01/2024|Vente|220000,00|5043|||B152|AU MOLLARD|1640|BOYEUX-SAINT-JEROME|01|56||C|2523||||||||||||0|1|Maison||40|1|S||488
|||||||000001|05/01/2024|Vente|330000,00|6189|||B037|LA POLETTE|1800|VILLIEU-LOYES-MOLLON|01|450||B|2056||||||||||||0|1|Maison||88|4|S||594
|||||||000001|04/01/2024|Vente|5000,00||||B001|LES ABERROUX|1100|APREMONT|01|11||A|598||||||||||||0||||||T||1027
|||||||000001|02/01/2024|Vente|130000,00|177||PL|0300|DE LA CROIX BLANCHE|1390|SAINT-ANDRE-DE-CORCY|01|333||AN|270||2|61,64|||||||||1|4|Local industriel. commercial ou assimilé||61|0|||
|||||||000001|09/01/2024|Vente en l'état futur d'achèvement|615000,00||||B025|GRAND PRE|1630|ST-GENIS-POUILLY|01|354||AB|587||||||||||||0||||||AB||395

date,sale_type,price_eur,commune,dept,postal_raw,postal_fixed,property_type,area_m2,rooms
2024-01-02,Vente,346.5,CHALEY,01,1230,01230,,,
2024-01-03,Vente,329500,GEX,01,1170,01170,Dépendance,0,0
2024-01-03,Vente,329500,GEX,01,1170,01170,Dépendance,0,0
2024-01-03,Vente,329500,GEX,01,1170,01170,Appartement,89,4
2024-01-03,Vente,220000,BOYEUX-SAINT-JEROME,01,1640,01640,Maison,40,1
2024-01-05,Vente,330000,VILLIEU-LOYES-MOLLON,01,1800,01800,Maison,88,4
2024-01-04,Vente,5000,APREMONT,01,1100,01100,,,
2024-01-02,Vente,130000,SAINT-ANDRE-DE-CORCY,01,1390,01390,Local industriel. commercial ou assimilé,61,0
2024-01-09,Vente en l'état futur d'achèvement,615000,ST-GENIS-POUILLY,01,1630,01630,,,

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.