Context: Measuring Air Quality Levels of various air borne pollutants such as Nitrogen Monoxide (NO), Nitrogen Dioxide (NO2) and particulate matter (also called particle pollution) are all major contributors to the measure of overall air quality. For instance, NO2 is measured using micrograms in each cubic metre of air (㎍/m3). A microgram (㎍) is one millionth of a gram. A concentration of 1 ㎍/m3 means that one cubic metre of air contains one microgram of pollutant. To protect our health, the UK Government sets two air quality objectives for NO2 in their Air Quality Strategy The hourly objective, which is the concentration of NO2 in the air, averaged over a period of one hour. The annual objective, which is the concentration of NO2 in the air, averaged over a period of a year. The following table shows the colour encoding and the levels for Objective 1 above, the mean hourly ratio, adopted in the UK. Index 1 2 3 4 5 6 7 8 9 10 Band Low Low Low Moderate Moderate Moderate High High High Very High ㎍/mÂ³ 0-67 68-134 135-200 201-267 268-334 335-400 401-467 468-534 535-600 601 or more Further details of colour encodings and health warnings can be found at the DEFRA Site. The Input Data The following ZIP file provides data ranging from 2004 to 05 October 2022 taken from 19 monitoring stations in and around Bristol. Monitors come and go and may suffer down times, so the data isn't complete for all stations at all times. Download & save the data file: Shown here is the first 8 lines of the file (cropped): Note the following: There are 19 stations (monitors): 188 => 'AURN Bristol Centre', 203 => 'Brislington Depot', 206 => 'Rupert Street', 209 => 'IKEA M32', 213 => 'Old Market', 215 => 'Parson Street School', 228 => 'Temple Meads Station', 270 => 'Wells Road', 271 => 'Trailer Portway P&R', 375 => 'Newfoundland Road Police Station', 395 => "Shiner's Garage", 452 => 'AURN St Pauls', 447 => 'Bath Road', 459 => 'Cheltenham Road \ Station Road', 463 => 'Fishponds Road', 481 => 'CREATE Centre Roof', 500 => 'Temple Way', 501 => 'Colston Avenue' 672 => 'Marlborough Street' Each line represents one reading from a specific detector. Detectors take one reading every hour. If you examine the file using a programming editor, (Notepad++ can handle the job), you can see that the first row gives headers and there are another 1520995 (1.52 million+) rows (lines). There are 23 data items (columns) per line. The schema is given below: measure desc unit Date Time Date and time of measurement datetime NOx Concentration of oxides of nitrogen ㎍/m3 NO2 Concentration of nitrogen dioxide ㎍/m3 NO Concentration of nitric oxide ㎍/m3 SiteID Site ID for the station integer PM10 Concentration of particulate matter <10 micron diameter ㎍/m3 NVPM10 Concentration of non - volatile particulate matter <10 micron diameter ㎍/m3 VPM10 Concentration of volatile particulate matter <10 micron diameter ㎍/m3 NVPM2.5 Concentration of non volatile particulate matter <2.5 micron diameter ㎍/m3 PM2.5 Concentration of particulate matter <2.5 micron diameter ㎍/m3 VPM2.5 Concentration of volatile particulate matter <2.5 micron diameter ㎍/m3 CO Concentration of carbon monoxide ㎎/m3 O3 Concentration of ozone ㎍/m3 SO2 Concentration of sulphur dioxide ㎍/m3 Temperature Air temperature °C RH Relative Humidity % Air Pressure Air Pressure mbar Location Text description of location text geo_point_2d Latitude and longitude geo point DateStart The date monitoring started datetime DateEnd The date monitoring ended datetime Current Is the monitor currently operating text Instrument Type Classification of the instrument text Task 1: Crop, Cleanse and Refactor the Data Design & write appropriate PYTHON scripts to carry out the following tasks. Crop the file to delete any records before 00:00 1 Jan 2010 (1262304000).

Context: Measuring Air Quality Levels of various air borne pollutants such as Nitrogen Monoxide (NO), Nitrogen Dioxide (NO2) and particulate matter (also called particle pollution) are all major contributors to the measure of overall air quality. For instance, NO2 is measured using micrograms in each cubic metre of air (㎍/m3). A microgram (㎍) is one millionth of a gram. A concentration of 1 ㎍/m3 means that one cubic metre of air contains one microgram of pollutant. To protect our health, the UK Government sets two air quality objectives for NO2 in their Air Quality Strategy The hourly objective, which is the concentration of NO2 in the air, averaged over a period of one hour. The annual objective, which is the concentration of NO2 in the air, averaged over a period of a year. The following table shows the colour encoding and the levels for Objective 1 above, the mean hourly ratio, adopted in the UK. Index 1 2 3 4 5 6 7 8 9 10 Band Low Low Low Moderate Moderate Moderate High High High Very High ㎍/mÂ³ 0-67 68-134 135-200 201-267 268-334 335-400 401-467 468-534 535-600 601 or more Further details of colour encodings and health warnings can be found at the DEFRA Site. The Input Data The following ZIP file provides data ranging from 2004 to 05 October 2022 taken from 19 monitoring stations in and around Bristol. Monitors come and go and may suffer down times, so the data isn't complete for all stations at all times. Download & save the data file: Shown here is the first 8 lines of the file (cropped): Note the following: There are 19 stations (monitors): 188 => 'AURN Bristol Centre', 203 => 'Brislington Depot', 206 => 'Rupert Street', 209 => 'IKEA M32', 213 => 'Old Market', 215 => 'Parson Street School', 228 => 'Temple Meads Station', 270 => 'Wells Road', 271 => 'Trailer Portway P&R', 375 => 'Newfoundland Road Police Station', 395 => "Shiner's Garage", 452 => 'AURN St Pauls', 447 => 'Bath Road', 459 => 'Cheltenham Road \ Station Road', 463 => 'Fishponds Road', 481 => 'CREATE Centre Roof', 500 => 'Temple Way', 501 => 'Colston Avenue' 672 => 'Marlborough Street' Each line represents one reading from a specific detector. Detectors take one reading every hour. If you examine the file using a programming editor, (Notepad++ can handle the job), you can see that the first row gives headers and there are another 1520995 (1.52 million+) rows (lines). There are 23 data items (columns) per line. The schema is given below: measure desc unit Date Time Date and time of measurement datetime NOx Concentration of oxides of nitrogen ㎍/m3 NO2 Concentration of nitrogen dioxide ㎍/m3 NO Concentration of nitric oxide ㎍/m3 SiteID Site ID for the station integer PM10 Concentration of particulate matter <10 micron diameter ㎍/m3 NVPM10 Concentration of non - volatile particulate matter <10 micron diameter ㎍/m3 VPM10 Concentration of volatile particulate matter <10 micron diameter ㎍/m3 NVPM2.5 Concentration of non volatile particulate matter <2.5 micron diameter ㎍/m3 PM2.5 Concentration of particulate matter <2.5 micron diameter ㎍/m3 VPM2.5 Concentration of volatile particulate matter <2.5 micron diameter ㎍/m3 CO Concentration of carbon monoxide ㎎/m3 O3 Concentration of ozone ㎍/m3 SO2 Concentration of sulphur dioxide ㎍/m3 Temperature Air temperature °C RH Relative Humidity % Air Pressure Air Pressure mbar Location Text description of location text geo_point_2d Latitude and longitude geo point DateStart The date monitoring started datetime DateEnd The date monitoring ended datetime Current Is the monitor currently operating text Instrument Type Classification of the instrument text Task 1: Crop, Cleanse and Refactor the Data Design & write appropriate PYTHON scripts to carry out the following tasks. Crop the file to delete any records before 00:00 1 Jan 2010 (1262304000).

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Context: Measuring Air Quality

Levels of various air borne pollutants such as Nitrogen Monoxide (NO), Nitrogen Dioxide (NO₂) and particulate matter (also called particle pollution) are all major contributors to the measure of overall air quality.

For instance, NO₂ is measured using micrograms in each cubic metre of air (㎍/m³). A microgram (㎍) is one millionth of a gram. A concentration of 1 ㎍/m³ means that one cubic metre of air contains one microgram of pollutant.

To protect our health, the UK Government sets two air quality objectives for NO₂ in their Air Quality Strategy

The hourly objective, which is the concentration of NO₂ in the air, averaged over a period of one hour.
The annual objective, which is the concentration of NO₂ in the air, averaged over a period of a year.

The following table shows the colour encoding and the levels for Objective 1 above, the mean hourly ratio, adopted in the UK.

Index	1	2	3	4	5	6	7	8	9	10
Band	Low	Low	Low	Moderate	Moderate	Moderate	High	High	High	Very High
㎍/mÂ³	0-67	68-134	135-200	201-267	268-334	335-400	401-467	468-534	535-600	601 or more

Further details of colour encodings and health warnings can be found at the DEFRA Site.

The Input Data

The following ZIP file provides data ranging from 2004 to 05 October 2022 taken from 19 monitoring stations in and around Bristol.

Monitors come and go and may suffer down times, so the data isn't complete for all stations at all times.

Download & save the data file:

Shown here is the first 8 lines of the file (cropped):

Note the following:

There are 19 stations (monitors):
188 => 'AURN Bristol Centre',
203 => 'Brislington Depot',
206 => 'Rupert Street',
209 => 'IKEA M32',
213 => 'Old Market',
215 => 'Parson Street School',
228 => 'Temple Meads Station',
270 => 'Wells Road',
271 => 'Trailer Portway P&R',
375 => 'Newfoundland Road Police Station',
395 => "Shiner's Garage",
452 => 'AURN St Pauls',
447 => 'Bath Road',
459 => 'Cheltenham Road \ Station Road',
463 => 'Fishponds Road',
481 => 'CREATE Centre Roof',
500 => 'Temple Way',
501 => 'Colston Avenue'
672 => 'Marlborough Street'

Each line represents one reading from a specific detector. Detectors take one reading every hour. If you examine the file using a programming editor, (Notepad++ can handle the job), you can see that the first row gives headers and there are another 1520995 (1.52 million+) rows (lines). There are 23 data items (columns) per line.

The schema is given below:

measure	desc	unit
Date Time	Date and time of measurement	datetime
NOx	Concentration of oxides of nitrogen	㎍/m³
NO2	Concentration of nitrogen dioxide	㎍/m³
NO	Concentration of nitric oxide	㎍/m³
SiteID	Site ID for the station	integer
PM10	Concentration of particulate matter <10 micron diameter	㎍/m³
NVPM10	Concentration of non - volatile particulate matter <10 micron diameter	㎍/m³
VPM10	Concentration of volatile particulate matter <10 micron diameter	㎍/m³
NVPM2.5	Concentration of non volatile particulate matter <2.5 micron diameter	㎍/m³
PM2.5	Concentration of particulate matter <2.5 micron diameter	㎍/m³
VPM2.5	Concentration of volatile particulate matter <2.5 micron diameter	㎍/m³
CO	Concentration of carbon monoxide	㎎/m³
O3	Concentration of ozone	㎍/m³
SO2	Concentration of sulphur dioxide	㎍/m³
Temperature	Air temperature	°C
RH	Relative Humidity	%
Air Pressure	Air Pressure	mbar
Location	Text description of location	text
geo_point_2d	Latitude and longitude	geo point
DateStart	The date monitoring started	datetime
DateEnd	The date monitoring ended	datetime
Current	Is the monitor currently operating	text
Instrument Type	Classification of the instrument	text

Task 1: Crop, Cleanse and Refactor the Data

Design & write appropriate PYTHON scripts to carry out the following tasks.

Crop the file to delete any records before 00:00 1 Jan 2010 (1262304000).

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.