onsdigital / sdg_11.2.1 Goto Github PK

• Import OA boundary data
• join on OA11CD code

Now the data is found, a la #33 , now needs to be imported.

link to data : https://github.com/james-westwood/SDG_11.2.1/raw/master/data/nomis_QS303.csv
https://github.com/james-westwood/SDG_11.2.1/blob/master/data/nomis_QS303.csv

• create (4 way table) df showing number of people with access to high frequency stops vs no access
• split ubran/rural access/no access

• create df showing number of people with access to high frequency stops vs no access
• split disability yes/no and access/no access

• get data from the Naptan zip
• can this be opened from the remote source (because it's pretty big)
• consider processing with Dask if it's really large (find out size first)

data for urban/rural: https://geoportal.statistics.gov.uk/search?collection=Dataset&sort=name&tags=all(PRD_RUC_OA)

pick 2011 data

@MusaChirikeni is there any Urban/rural classification data available for Scotland?

It might be that the time tables for trains are not available any other way. This could affect many "Stops" in our calculation. http://data.atoc.org/sites/all/themes/atoc/files/RSPS5046.pdf

• QMI should be created
  Example QMI https://datasciencecampus.ons.gov.uk/projects/quality-and-methodology-extent-and-change-of-surface-water-statistics/

Possibly NOMIS

• LC3207
• LC32005

• hardly any of the data in the data folder is being used. Delete the unneeded stuff.

• create df showing number of people with access to high frequency stops vs no access
• limit whole table to urban population only
• split male/female with access/no access

• Birmingham_stops = gpd.sjoin(just_birmingham_geom, stops_geo_df, how = 'left', op = 'intersects') where Birmingham_LSOAs is the shapefile of just birmingham.
• check that they are on the same projection 'just_birmingham_geom.crs == stops_geo_df.crs' if True, then it's good. Both should be EPSG:27700

• Connecting to SQL Server (probably MySQL eventually BigQuery)
• Creating a New Database
• Connecting to the Database
• Creating Tables
• Populating the Tables
• Reading Data formatting Output into a pandas DataFrame

Requires a points-in-polygons enquiry. Sum populations of those pop-weighted-centroids

Downloading a CSV is a manual and slow process. Consider making the data acquisition part of an automated data pipeline, which updates when run and doesn't require raw data storage.

Check if functions exist to bucket-ize values
If no function exists, write a function to bucketize the age values.
Looking at other SDG indicators, buckets should be:

From Indicator 3.6.1

• 4 and under
• 5 to 7
• 8 to 11
• 12 to 15
• 16 to 19
• 20 to 29
• 30 to 39
• 40 to 49
• 50 to 59
• 60 to 69
• 70 to 79
• 80 and over

And from Indicator 3.4.2

• 10 to 14
• 15 to 19
• 20 to 24
• 25 to 29
• 30 to 34
• 35 to 39
• 40 to 44
• 45 to 49
• 50 to 54
• 55 to 59
• 60 to 64
• 65 to 69
• 70 to 74
• 75 to 79
• 80 to 84
• 85 to 89
• 90 and over

These do not agree with each other. Need to ask.

@jwestw to change crs of projections to 27700

• Need help from Geospatial on this.
• Do functions for this already exist?
• What is supplied, what is returned?

@jwestw to research the methodology used by other govt. statistical departments for this indicator.

Hi Data team. @TomosJH @EmmaWoodONS @denzil86

I need your help. In order to get 11.2.1 to a stage where it has working output for the platform, I need to understand the data team's requirements for this stage of the project in terms of:

• data format
• layout including exact column titles
• number of decimal places, where applicable
• any other requirements

Whatever your requirements are, please can I get a mock-up of the data so I have a visual reference for what I am aiming for?

I stress "this stage" of the project because I am working towards version 1.0 of the output, which is working code that correctly calculates the numbers for the whole country. See the projects page for other stages.

• Take the population estimates for the west mids and create new csv sheet of pure data
• import all of WMs data into pandas

Create a function that can be re-used for the whole population.

• create df showing number of people with access to high frequency stops vs no access

This may involve finding differently geocoded data for the population.
Work out how to join to NUTS3 data.

• import shapefiles from Output_Areas__December_2011__Population_Weighted_Centroids-shp.zip into a dataframe
• left join on OA code into the filtered population data
• if it's too complicated to filter/join then use whole country

Due to the problem with Conda environments not activating properly, there's tonnes of superfluous requirements listed.

17th December is the deadline

The number of those serviced and not serviced at every age bracket needs to be calculated.

Running the script is getting slower which is a pain for debugging. So I will use feathers and geofeathers to store dataframes for quick retrieval

Before we start this, we should estimate how long and how much effort it would take to complete. An alternative is just to go through a manual process using FileZilla and a zip utility. The manual process with some instructions in the user docs is MVP.

The problem: We need to access the TNDS data at ftp.tnds.basemap.co.uk . I don't know how often the data is updated but I suspect it will need a regular re-downloading, hence, an auto-download feature would be a nice to have.

We have an FTP module already so can this be adapted to fetch data from the Travel Line (TNDS) FTP server without too much extra work?

• I have the username and password already.
• the files are all zipped (so if we were to implement and auto-download feature, then we would need to adapt the _import_extract_delete_zip function.
• File format is in xml

Because CRS is EPSG:27700 and Easting and Northing, a value 500 for the buffer should correspond to 500 metres.

It seems the naptan data has some duplicates in it, e.g in the naptan code (which I thought was unique). I don't want to dedupe without knowing what to keep so creating this issue/task.

@MusaChirikeni I have a few questions about this ticket:

• What is the most universal geo file formats to output to? (e.g. shp, kml or geojson?)
• Are those formats different in size? Which one is most efficient ?
• Would it be helpful to output example maps as jpeg?
• Should I output a file for the whole country (which would be a lot of data) or a smaller regional one for example?

• it can read the geospatial files and give visual reference
• overlay layers to visualise

Refer to the comments in #7

• Find out where are the areas with the outliers in them are?

This ticket is unrelated to the project really, but should be investigated.