Create an 'available data' table

[graybeal]

Is your feedback related to a problem? If so please describe. We need a way for users to look up what data is available for what location types, and to link to it from the DCW.

Describe the idea you have or solution you'd like to see A description of what you think could happen or want to happen. Create a google sheet showing the status relationships between statistic variables and location types. As a first cut just showing whether there are any variables accessed by a given location type is sufficient.

Describe alternatives you've considered A clear and concise description of any alternative ideas or features you've considered. Expressing the relationship as a percentage—how many of the location instances have data values for a given statistic variable and location types, vs how many location instances there are—would be icing on the cake.

Additional context Add any other context or screenshots about the feedback here. Don't get distracted by the perfect or long-term solution, having the basic filled-out table is plenty good enough for now. Pass along to Marcos any lessons that could apply to the long-term in-app solution, though. (See issue #16)

[johardi]

As promised,

Statistical Variable Availability.xlsx

Let me know what I can improve before I export it to Google Sheet

[johardi]

The method to produce the Excel spreadsheet:

1. List all the DC's statistical variables as a new sheet called "All Statvars"
2. Using the DC API, list all the DC's statistical variables for the location code: zip/90249 as a new sheet called "ZIP Code"

   curl --request GET \
   --url 'https://api.datacommons.org/place/stat-vars?dcids=zip%2F90249'

3. Merge the two sheets using the Excel formulas INDEX and MATCH (see the spreadsheet to learn more about the formula) and store the output in the "All Statvars" sheet. The black circle "⬤" indicates a match was found.

Repeat Step (2) and (3) for other location types. Below are the details:

• City level, use the same curl command to query the city of Los Angeles (geoId/0644000)
• County level, to query the county of Los Angeles (geoId/06037)
• State level, to query the state of California (geoId/06)
• School district level, to query the school district of Adlai E. Stevenson High School (geoId/sch1732580) and of Eanes Independent School (geoId/sch4817760).
• Census tract, use 'geoid/06085512402' (no idea where that is)

Known limitation of the method The spreadsheet may not show the complete availability information because it is based on some arbitrary sampling of the locations.

[johardi]

The public spreadsheet is available at https://docs.google.com/spreadsheets/d/1s7jurDfn-c9iHyNQ6QnfnPGYV6j6bd75cF3fbvwf9Lc

[graybeal]

If adding other content tabs, after getting the response, edit it to:

• remove all the preliminary cruft and the places that are returned (the first few thousand lines),
• replace "," with new lines
• remove all the double quotes " You'll paste that result into your new tab for that content, following the patterns of the other tabs.

Other stuff to do to complete the spreadsheet update:

• add a column in the master tab and migrate the tables at the top rightward one column, including the chart (the row gets inserted before the column that has the Count cell around row 41)
• Add a column at the end of StatVars. Populate its cells with the formula from one of the other columns, replacing the tab name in the formula with the name of the new tab you've entered
• Copy the column that results, and paste it into the master tab, using "Paste Values" starting at the column header around row 41, in the new column

Confirm that the new calculations and charts make sense, and that you really can get data from one of those locations (or better, from all the locations in a state) for a few of those statistical variables, by querying the actual Wizard.