mroswell / list-n Goto Github PK

The units are minutes But sometimes they show up as mmin All other times as min

remove the following from formulation_type in transform
" (Clorox® Total 360® system)"
" (use in conjunction with VHP generator)"
" CURIS®"
" HaloFogger"

/usr/local/Cellar/datasette/0.55/libexec/lib/python3.9/site-packages/datasette/templates

permissions_debug.html
messages_debug.html
index.html
patterns.html
_codemirror_foot.html
base.html
_table.html
row.html
database.html
table.html
_close_open_menus.html
_description_source_license.html
allow_debug.html
query.html
show_json.html
logout.html
error.html
_codemirror.html
_footer.html

template not functioning on vercel. for instance, should be green, not blue. Active_ingredients should be wider.

https://github.com/simonw/sqlite-transform/

active_ingredients_health_impact will be a table. Each active ingredient could have zero to many references. how to maintain it?

Similar to:
https://github.com/baltimore-sun-data/salaries-datasette ?

These are the same records where the units show up as "mmin" instead of "min." #22

I'll have three tables, one of which is routinely updated, "ListN"

Second table signals if Active Ingredients are toxic or not. This will be incorporated directly into ListN, each time EPA updates "List N" So I won't want that table to display.

Third table could have some health information related to active ingredients, a one-to-many table, since an active ingredient may show up in the scientific literature in multiple places (Possible PubMed link...?)

Related issue is here: simonw/datasette#85
But my case is more complicated because
A) the field is an array
B) I want to use the whole name of the active ingredient and not the primary key to connect.. (?)

probably doing this manually. But nice to know.

from:
https://docs.google.com/document/d/1f61st8AXtpXvjeHB3UlmUhSCG1Ddiwih9nr-nO8LTEY/edit#
datasette manatees.db -o

simonw/datasette#129

 datasette/templates/database.html 
@@ -65,13 +65,19 @@ <h3>Query parameters</h3>
{% endif %}

{% for table in tables %}
{% if show_hidden or not table.hidden %}
<div class="db-table">
    <h2><a href="/{{ database }}-{{ database_hash }}/{{ table.name|quote_plus }}">{{ table.name }}</a></h2>
    <h2><a href="/{{ database }}-{{ database_hash }}/{{ table.name|quote_plus }}">{{ table.name }}</a>{% if table.hidden %}<em> (hidden)</em>{% endif %}</h2>
    <p><em>{% for column in table.columns[:9] %}{{ column }}{% if not loop.last %}, {% endif %}{% endfor %}{% if table.columns|length > 9 %}...{% endif %}</em></p>
    <p>{{ "{:,}".format(table.table_rows) }} row{% if table.table_rows == 1 %}{% else %}s{% endif %}</p>
    <p>{{ "{:,}".format(table.count) }} row{% if table.count == 1 %}{% else %}s{% endif %}</p>
</div>

Disable Full-Text Search. Not necessary now that we've got everything on one page.

sqlite-utils disable-fts disinfectants.db listN

CREATE TABLE [listN] (
[Why on List N] TEXT,
[Registration number] TEXT,
[Company URL] TEXT,
[Formulation type] TEXT,
[Contact time] FLOAT,
[Active ingredients] TEXT,
[Use site] TEXT,
"Company" TEXT,
[Follow directions for this virus] TEXT,
[Date on List N] TEXT,
[Product name] TEXT,
[ID] INTEGER PRIMARY KEY,
[Active ingredient] TEXT,
[Surface type] TEXT
);

CREATE TABLE [listN] (
[EMER_PATH] TEXT,
[REGI_NUM] TEXT,
[INST_VIRUS] TEXT,
[COMPANY] TEXT,
[USE_SITE] TEXT,
[CONT_TIME] FLOAT,
[ACTI_ING] TEXT,
[USE_SURF] TEXT,
[COMPANY_URL] TEXT,
[DATE_ON_LIST_N] TEXT,
[FORM_TYPE] TEXT,
[ID] INTEGER PRIMARY KEY,
[PROD_NAME] TEXT
);

Created a list in Google Sheets. Need to specify Y/N details

Install new Datasette when available. Remove underscores from field names.

might be helpful: https://www.kite.com/python/answers/how-to-check-for-multiple-substrings-in-a-string-in-python

UPDATE: created csv, which I used to generate two python lists in transform.py
https://github.com/mroswell/list-N/blob/main/data/ActiveIngredientsSafeOrNot.csv

The list below came from the facets, and included counts, but I didn't include those in the csv, because those counts are always changing, and can be accessed via SQL (except for the faceting issue...)
T = Toxic. S = Safer

T | Quaternary ammonium 251
S | Hydrogen peroxide 83
T | Sodium hypochlorite 75
S | Ethanol (Ethyl alcohol) 36
Peroxyacetic acid (Peracetic acid) 30
Phenolic 29
S | Isopropanol (Isopropyl alcohol) 22
Hypochlorous acid 21
S | Citric acid 17
Sodium chlorite 11
Chlorine dioxide 9
L-Lactic acid 8
Sodium dichloroisocyanurate 7
Thymol 7
Hydrochloric acid 5
Sodium chloride 5
Glycolic acid 4
Octanoic acid 4
Potassium peroxymonosulfate 4
Ammonium bicarbonate 3
Ammonium carbonate 3
Glutaraldehyde 3
Silver 3
Sodium carbonate peroxyhydrate 3
Dodecylbenzenesulfonic Acid 2
Dodecylbenzenesulfonic acid 2
PHMB 2
Peroxyoctanoic acid 2
Silver ion 2
Tetraacetyl ethylenediamine 2

Remove ID and last 3 columns by default, with toggle to show more columns.

https://github.com/baltimore-sun-data/salaries-datasette/blob/master/templates/_rows_and_columns.html

https://github.com/baltimore-sun-data/salaries-datasette/search?q=hide-columns
https://github.com/baltimore-sun-data/salaries-datasette/pull/ 1

documentation: https://docs.datasette.io/en/stable/metadata.html. or https://docs.datasette.io/en/latest/metadata.html

Remove " 00:00:00" from times. and if EPA won't do it, fix formatting of dates.

Disable Full-Text Search. Not necessary now that we've got everything on one page.

sqlite-utils disable-fts disinfectants.db listN

two different names here: https://docs.datasette.io/en/latest/settings.html?highlight=_timelimit#sql-time-limit-ms

Leave the current GitHub Action, that shows source list-N data, as-is.
Include the transformed file, separately.

This will make it less likely that python code will introduce errors, or if it does, we'll still have all the original data

Maybe this means that the transformation should work on the saved list-N.json file...

There are 35 Active Ingredients. Would be good to include them all.

Have to figure out how to make this setting
https://docs.datasette.io/en/0.23.1/changelog.html?highlight=default%20facet%20size%2030#id12

charts just completely stopped working in this project. the chart skeleton appears, but no data.

Inspecting the console:
main.08f5d3d8.js:1 The compiled spec uses Vega v3, but current version is 4.2.0.

DevTools failed to load SourceMap: Could not load content for https://list-n-9b162wcv2-mroswell.vercel.app/-/static-plugins/datasette_vega/main.08f5d3d8.js.map: HTTP error: status code 500, net::ERR_HTTP_RESPONSE_CODE_FAILURE

Charting is still being done on semicolon version

From: https://docs.datasette.io/en/stable/getting_started.html?highlight=json1#datasette-get

$ datasette ~/Library/Application\ Support/Google/Chrome/Default/History -o
sqlite3.OperationalError: database is locked

I don't know what's causing it, but the CSV export has strange quotation marks, either doubled for all the array fields, or missing in the other fields.