← Back to Data Exploration with rakoon-ds
Practical Work 4

Enrich the fires with weather and holidays, and measure what it bought you

Multi-key joins, deduplication before joining, a holiday flag in one click, and the honest question: did the enrichment actually help?

Duration 75 min
Level Intermediate
Session Session 4
Mission dep-4

Objectives

By the end of this session you will be able to:

  • Join two datasets on a composite key built from a date
  • Deduplicate a right-hand table before joining it, and know why
  • Add a public holiday flag without leaving the studio, and compare it to a join
  • Compute a geographic distance with the Haversine formula in one click
  • Measure the gain of each enrichment, and accept when it is zero

Prerequisites

  • A rakoon-ds account on https://rakoon-ds.apps.way-up.io (free, browser only, nothing to install), and the group join code your instructor gives you
  • A recent Chrome, Edge or Firefox. Computation runs either in your own tab (browser engine) or on the server; the header pill tells you which
  • The mission for this session, assigned to your group. Open a project, then the Mission button in the workshop header: the panel opens next to Report. Click Check after each step
  • Practical work 2 finished: you reuse its cleaned dataset
  • Transformation, chart and algorithm names come from the rakoon-ds registry and are served in French even when the interface is in English. Every step below gives you the French label you will click and, in code font, the registry key the mission checks against.

Data

Two files, both already hosted on this site. This practical work is a lighter version of the EPITA exam subject car crashes, San Francisco; if your instructor gives you the exam pack (car-crashes.csv, weather-sfcsv.csv, holidays.xml), run the same steps on it, the shape of the work is identical.

FileLinkSizeRole
California_Fire_Incidents.csvdownload1 636 x 40the events
weather-sf.csvdownload8 205 x 11daily weather, one row per day

The weather file measures one station in San Francisco, while the fires happen all over California. Joining them is technically correct and scientifically weak. Do it anyway, then measure the result: this session is about learning to tell the two apart.

Timing

The steps below add up to the announced duration. If you fall behind, Step 1 to Step 3 are the ones that must be finished.

#What you doTime
Step 1Load both, and look at the keys12 min
Step 2Make the weather joinable10 min
Step 3The three-key join13 min
Step 4Holidays: one click against one join15 min
Step 5Two enrichments of your own15 min
Step 6Retrain, and be honest about the gain10 min
Total75 min

Instructions

Step 1: Load both, and look at the keys12 min

  1. New project PW4 Enrichment, or continue in your PW2 project.
  2. Import both CSV files. Weather must read 8 205 x 11.
  3. Open Explore → Overview on the weather file.

Two things to notice immediately:

  • Date is written 19980301: an eight-digit integer, not a date.
  • Three columns are called min. A CSV can do that; a join cannot. After a join you will see min, min.1, min.2.

Rebuild the cleaned fire dataset of Practical work 2 if you are in a new project: drop_columns on the constant and identifier columns, then to_date and date_parts on Started with year, month, weekday, is_weekend, dayofyear and day.

Step 2: Make the weather joinable10 min

  1. On weather-sf: family Dates, card Convertir en date (to_date), column Date, format %Y%m%d.
  2. Read the preview note: it tells you how many values it did not understand. It should be zero.
  3. Then Décomposer une date/heure (date_parts) on Date with year, month, day. Name it weather-ymd: 8 205 x 14.

Why not join on the date itself? Because the fire timestamp carries hours and minutes and the weather row does not: 2013-08-17 15:25:00 will never equal 2013-08-17 00:00:00. Joining on the triplet (year, month, day) is the click-only way to join "same day". It is the same trick that replaced a missing date-truncation function on the SNCF exam.

Step 3: The three-key join13 min

  1. Family Combiner, card Fusion (join) (join).
  2. Left: your fire dataset with date parts. Right: weather-ymd.
  3. Join type left.
  4. Left keys: Started_year, Started_month, Started_day. Click Add a key twice to get three rows.
  5. Right keys: Date_year, Date_month, Date_day.
  6. Preview, read the note, then apply. Name it calfire-meteo.

Expected: rows_before: 1 636, rows_after: 1 636, and 48 columns. The row count is the only proof that the join did what you meant. Check it every single time.

Try it wrong once, on purpose: join on Started_month alone. You will see the multiplication warning in red before you apply anything. Then put the three keys back.

If the right-hand table had several rows per day (an hourly weather file, for instance), a left join on the day would multiply your fires by 24. The fix is Supprimer les doublons (drop_duplicates) on the join keys before joining. On the exam version of this subject the hourly file goes from 6 901 to 5 972 rows that way.

Step 4: Holidays: one click against one join15 min

rakoon-ds ships a holiday calendar. Use it, then judge it.

  1. On calfire-meteo: family Dates, card Jour férié (FR / US) (holiday_flag), column Started, country US, Ajouter le nom du jour férié = oui.
  2. Two columns appear: Started_is_holiday and Started_holiday_name. Name the result calfire-feries.
  3. Count them: family Agréger, card Agréger (group by) (aggregate), group by Started_is_holiday, aggregate AcresBurned with count.

Reference run: 54 fires started on a US public holiday, 1 579 did not (3 rows have no usable date).

Now the pedagogical catch, and it matters for your exams. A holiday file supplied with a subject is often better than the built-in calendar: on the air passengers exam, the built-in flag marked 454 flights while the supplied holidays.xml marked 666, because the file also contains the days before and after a holiday, which is what actually moves traffic.

So: when a subject gives you a holiday file, do the join. What is being assessed is the join, not the flag. When nobody gives you a file, the one-click flag is a perfectly good answer, and you say so in your report.

Step 5: Two enrichments of your own15 min

The mission checks the first and the third, so that everybody in the room has the same two. The second is a variant to try if you have time.

  1. Distance to a reference point. The Distance géographique (lat/lon) (distance) card asks for four columns, so build the fixed point first: family Colonnes, card Colonne par formule (expression), new column ref_lat, formula 38.5816; then again for ref_lon = -121.4944 (Sacramento). A bare number is a valid formula, it fills the whole column. Then run distance with point 1 = Latitude / Longitude, point 2 = ref_lat / ref_lon, unit km.
  2. Fire size bands. Card Discrétiser (bins) (bin_numeric) on AcresBurned, method quantile, 4 bins. Or Catégoriser (seuils) (categorize) with thresholds you choose and names you write.
  3. Season. Card Catégoriser (seuils) (categorize) on Started_month, thresholds 2, 5, 8, 11, labels winter, spring, summer, autumn, winter2.

categorize on a column that has holes used to break training with a message nobody could read. It is fixed: missing values stay missing and the imputation step handles them. If you meet an old instance, that is what the error means.

Step 6: Retrain, and be honest about the gain10 min

Train the same random forest as in Practical work 2, on the enriched dataset, with the honest columns plus your new ones. Then compare in the Comparison panel.

Reference runs, all with a 75 / 25 random split:

DatasetAlgorithmAccuracyF1AUC
date parts onlyBaseline0.76530.66350.500
date parts onlyRandom forest0.77750.70240.8185
+ weather + holidaysRandom forest0.77510.69740.8209
+ weather + holidaysGradient boosting0.82400.80960.8376

Read the third line. Twenty extra columns, two joins, one holiday flag, and the accuracy went down by 0.24 points. That is the correct result and it is the point of the session: the weather of one San Francisco station says nothing about a fire in Shasta County. Changing the algorithm bought five points; enriching bought nothing.

Write the Préparation section of your report: each enrichment, one line, with the score before and the score after. An enrichment that costs a join and gains nothing is a result, and you write it down.

What you should have

  • A three-key join that keeps 1 636 rows, and one deliberate row explosion seen and undone
  • A holiday flag with its count, and one sentence on flag against join
  • Two enrichments of your own choosing
  • A comparison table of at least four models
  • The dep-4 mission at 5 / 5

Deliverables

  • Mission: dep-4 validated
  • Report: Préparation section with the enrichment table (enrichment, cost in clicks, gain in score)
  • The join log: for each join, the keys, the type, rows before, rows after

Bonus

  • Rerun the whole chain on the exam pack (car-crashes.csv, weather-sfcsv.csv, holidays.xml) if your instructor gives it to you. Reference numbers from the exam trial: 8 008 crashes, weather deduplicated on four keys from 6 901 to 5 972 rows, 7 961 crashes matched, baseline forest at 0.829 accuracy.
  • Import the holidays as XML instead of using the flag, and compare the two counts on the same year.
  • Pin a Densité 2D (heatmap) of Longitude against Latitude and look at where the fires actually are, relative to your weather station.

Resources

  • Session 4 slides (the lecture this practical work follows)
  • Course page: both programmes, all fifteen sessions
  • rakoon-ds studio
  • missions/dep-4.json
  • The exam trial this session is built from: Examens EPITA car crashes (San Francisco) dans rakoon-ds, nine tasks out of ten done with clicks only