dashboard data pipeline

[doug-leasure]

The new data and repo reorganisation creates a few issues for the dashboard database pipeline defined in reformat_csvs_for_db.py. I added our new source data with commits 507983f12a67de3a14b8b2110f4e8958c21187ef and 62e93a9775363c721ad8ebdd49ee986fce55adcd.

I will create a checklist here, and we can follow-up with more detailed discussions of each point as needed.

• [x] Delete ./data/dashboard/nn3nn7/ and ./data/dashboard/nn3nn3/
• [x] Move source csvs from ./data/dashboard/ to a folder somewhere within ./src/dashboard/ and update the TOPICS_DIR variable accordingly
• [x] Update ENRICHED_ICS_TABLE to come from ./data/final/enhanced_ref_data.zip
• [x] Adapt code to use the revised column names in the new ./data/final/enhanced_ref_data.zip. Note that two REQUIRED columns had name changes:
  • "ics_id" > "REF impact case study identifier"
  • "countries_iso3" > "countries_extracted"
• [x] Update TOPICS_GROUPS_TABLE to use topics_groups.csv
• [x] Update TOPICS_TABLE to use topics.csv
• [x] Delete nn2_threshold0.01_reduced.xlsx. We will no longer use the model-based weights because we have manually reassigned many ICS to new topics.
• [x] Revise make_topics_and_weights() to define topic weights as 1 for the assigned topic and 0 for all other topics. The function already has an option scale_weights="binary" that can be modified to accommodate this. The topic assignments should come from ics_topics.csv.
• [x] Option A: Use topic_narrative.csv to generate html for the "narrative" column in topics.csv
• [x] Option B: Add topic_narrative.csv as a new table in the database and generate the narrative html dynamically in the front-end.

[GISRedeDev]

@doug-leasure Just to confirm that in the above nn2_threshold0.01_reduced.xlsx that you have instructed to delete, we were using nn3_threshold0.01_reduced.xlsx. Does this make any difference?

[doug-leasure]

@GISRedeDev I think these can probably all be deleted because we no longer want to use the model-based topic probablities now that we have manually reassigned a subset of the ICS into new topics.

The topic_weights table in the database can now simply use 1 or 0 so that each ICS is assigned to only one topic with a probability of 1 (i.e. see the next item in the checklist).

[GISRedeDev]

@doug-leasure I think I've fixed the script to handle these changes. Just a couple of things:

I have taken each ICS row, and made a row with that ID for each row from the topics.csv. Regardless of the BERT_prob or max_prob in the ICS row, I have given the row in the weights table a probability of 1 where ics.topic_id matches the topics.topic_id, and 0 for the rest of the rows for that ics_id. I saw that thtere were many that were NaN in the ics table – these are ignored. Also, there are ics rows in which the BERT_prob and max_prob are much lower than 1 (see image) – these have been assigned a probability of 1 in the weights table (I hope this makes sense!).

For the narrative, I decided to hardcode the html into the database. This is when I noticed there are no keywords in the topics.csv. Should there be?

I've also made a table for the UK region lookup. I've not made the database yet or added any functionality to the API yet, but will let you know once I've made some progress.

[doug-leasure]

@GISRedeDev , thanks! I think this is all good.

1. Hard coding the narrative html into the database as before is okay.
2. Let's drop the keywords from the dashboard.
3. You've done the right thing by assigning probability = 1 where ics.topic_id == topics.topic_id and probability = 0 otherwise.
4. No problem that there are some rows where BERT_prob and max_prob << 1. These should still get probability = 1 for the topic_id they are assigned to.

Good work and thanks for persevering through those changes. I think we're on track.

I will close this issue. Let's open new issues for other tasks where needed.