<jupyter_start><jupyter_text>PRELIMINARIES<jupyter_code># import the pandas module to read the data
import numpy 
import pandas as pd
# read the data.
# the values are seperated by a semicolon (;) hence the need for the parameter sep=";"
df = pd.read_csv("bristol-air-quality-data.csv", sep=";")
# display the dataset
df
# The dataset has 1,408,384 rows and 23 columns<jupyter_output><empty_output><jupyter_text>DATA CLEANING<jupyter_code># check the data types of the various columns
df.dtypes<jupyter_output><empty_output><jupyter_text>Date Time, DateStart, and DateEnd columns are datetime columns, but they are recognized as objects by pandasSiteID is an integer column, but it is recognized as a float by pandas.Our data types need to be in the right format if we are to carry out any meaningful work on our data.<jupyter_code># convert Date Time, DateStart, and DateEnd columns from object to datetime
df["Date Time"]= pd.to_datetime(df["Date Time"], utc=True)
df["DateStart"]= pd.to_datetime(df["DateStart"], utc=True)
df["DateEnd"]= pd.to_datetime(df["DateEnd"], utc=True)
# notice the change
df.dtypes
# let's do the same for SiteID
df = df.astype({"SiteID":'Int64'})
# notice the change
df.dtypes
# PLEASE NOTE: The changes made in the dataframe do not affect the original dataset. It only makes pandas understand the datat types in the columns 
df<jupyter_output><empty_output><jupyter_text>TASK 1  Question 1Crop the file to delete any records before 00:00 1 Jan 2010<jupyter_code># The first step is to sort the dataframe by Date Time
df = df.sort_values(by='Date Time', ignore_index=True)
# Now the data is sorted in ascending order
df.head(10)
# Next we delete any records before 00:00 1 Jan 2010
df.loc[663630:663639, :]
# Notice the row 663634 contains the first record of 00:00 1 Jan 2010
# Next, we delete all records before that row
df_trunc = df.truncate(before = 663634)
df_trunc<jupyter_output><empty_output><jupyter_text>Question 2Filter for and remove any dud records where there is no value for SiteID or there is a mismatch between SiteID and Location.(This script should print the lines number and mismatch field values for each dud record.)<jupyter_code># print dud records for entire dataset where there is no value for SiteID
dud_records = df_trunc.columns[df_trunc.isnull().any()]
df_trunc[dud_records].isnull().sum()
# print dud records where there is no value for SiteID
print(df_trunc[df_trunc["SiteID"].isnull()][dud_records])<jupyter_output>Date Time     NOx   NO2     NO  SiteID  PM10  NVPM10  \
981146 2013-08-25 16:00:00+00:00  139.75  46.5  60.75    <NA>   NaN     NaN   
986360 2013-09-25 16:00:00+00:00  139.75  46.5  60.75    <NA>   NaN     NaN   

        VPM10  NVPM2.5  PM2.5  ...  SO2  Temperature  RH  Air Pressure  \
981146    NaN      NaN    NaN  ...  NaN          NaN NaN           NaN   
986360    NaN      NaN    NaN  ...  NaN          NaN NaN           NaN   

             Location                  geo_point_2d                 DateStart  \
981146  Rupert Street  51.4554331987,-2.59626237324 2003-01-01 00:00:00+00:00   
986360  Rupert Street  51.4554331987,-2.59626237324 2003-01-01 00:00:00+00:00   

                         DateEnd Current         Instrument Type  
981146 2015-12-31 00:00:00+00:00   False  Continuous (Reference)  
986360 2015-12-31 00:00:00+00:00   False  Continuous (Reference)  

[2 rows x 23 columns]<jupyter_text>PLEASE NOTE: SiteID is a numerical variable while Location is a categorical variable, so checking for a mismatch would onerous  Question 3Process the file and replace any empty fields with the value: NaN<jupyter_code>All values have been automatically replaced by NaN, NaT and <NA> values respectively using pandas<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08019246190858059, 'fraction_numerical': 0.08340016038492382, 'mean_word_length': 3.8408796895213455, 'pattern_counts': {'":': 1, '<': 27, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '421073', 'license': 'MIT', 'n_tokens_mistral': 1434, 'n_tokens_neox': 1191, 'n_words': 440, 'path': 'Task 1.ipynb', 'repo_name': 'Jhoie/Bristol-Air-Quality-', 'size': '3741'}