<jupyter_start><jupyter_text>MSDS 620 Week 2 Lab Natalia Weakly Merging, Joining and Null Values 1/27/2019 Task 2<jupyter_code>import pandas as pd
import numpy as np
#load the data into a panda dataframe
#Air quality dataset downloaded from http://archive.ics.uci.edu/ml/machine-learning-databases/00360/
df = pd.read_csv('AirQualityUCI.csv', sep=';', decimal=",")
#look at the first few rows to make sure the data was loaded correctly
df.head()
#Look at the last few rows
df.tail()
#use info() function to find the number of missing values in each column
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 9471 entries, 0 to 9470
Data columns (total 17 columns):
Date             9357 non-null object
Time             9357 non-null object
CO(GT)           9357 non-null float64
PT08.S1(CO)      9357 non-null float64
NMHC(GT)         9357 non-null float64
C6H6(GT)         9357 non-null float64
PT08.S2(NMHC)    9357 non-null float64
NOx(GT)          9357 non-null float64
PT08.S3(NOx)     9357 non-null float64
NO2(GT)          9357 non-null float64
PT08.S4(NO2)     9357 non-null float64
PT08.S5(O3)      9357 non-null float64
T                9357 non-null float64
RH               9357 non-null float64
AH               9357 non-null float64
Unnamed: 15      0 non-null float64
Unnamed: 16      0 non-null float64
dtypes: float64(15), object(2)
memory usage: 1.2+ MB<jupyter_text>The data was loaded into a dataframe with 9471 observations of 17 variables. However, this dataset contains a large number of missing values. The last two unnamed columns do not have any non-null values. In addition, the dataframe contains several empty rows in the end. According to the metadata for this dataset, it should contain 9358 observations of 15 variables. These empty rows and columns are likely a result of a technical error introduced while merging sensor data in one data file. So, we can just delete the empty columns and rows.<jupyter_code>#Drop the last two columns
df.drop(['Unnamed: 15', 'Unnamed: 16'], axis=1, inplace=True)
#Check results
df.head()
#look for the missing values again
df.info()
#make sure that rows [9357:9471] are empty
df[9357:9471]
#drop empty rows 
df_dropna = df.dropna()
df_dropna.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 9357 entries, 0 to 9356
Data columns (total 15 columns):
Date             9357 non-null object
Time             9357 non-null object
CO(GT)           9357 non-null float64
PT08.S1(CO)      9357 non-null float64
NMHC(GT)         9357 non-null float64
C6H6(GT)         9357 non-null float64
PT08.S2(NMHC)    9357 non-null float64
NOx(GT)          9357 non-null float64
PT08.S3(NOx)     9357 non-null float64
NO2(GT)          9357 non-null float64
PT08.S4(NO2)     9357 non-null float64
PT08.S5(O3)      9357 non-null float64
T                9357 non-null float64
RH               9357 non-null float64
AH               9357 non-null float64
dtypes: float64(13), object(2)
memory usage: 1.1+ MB<jupyter_text>The df_dropna dataframe now contains 9357 non-null observations of 15 variables. However, according to the dataset description, missing values were recorded as '-200'. It is likely that malfunctioning sensors are to blame for these missing measurements.  These values, while not technically NaN, would still skew the results of the analysis. So, it is better to find and address them.<jupyter_code>#replace -200.0 with NaN
df_withnulls = df_dropna.replace(-200.0, np.NaN)
#How many nulls were introduced?
df_withnulls.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 9357 entries, 0 to 9356
Data columns (total 15 columns):
Date             9357 non-null object
Time             9357 non-null object
CO(GT)           7674 non-null float64
PT08.S1(CO)      8991 non-null float64
NMHC(GT)         914 non-null float64
C6H6(GT)         8991 non-null float64
PT08.S2(NMHC)    8991 non-null float64
NOx(GT)          7718 non-null float64
PT08.S3(NOx)     8991 non-null float64
NO2(GT)          7715 non-null float64
PT08.S4(NO2)     8991 non-null float64
PT08.S5(O3)      8991 non-null float64
T                8991 non-null float64
RH               8991 non-null float64
AH               8991 non-null float64
dtypes: float64(13), object(2)
memory usage: 1.1+ MB<jupyter_text>As the output above shows, the NMHC(GT) column contains only 914 non-null values, 8623 (or slightly over 90%) of values are missing. Imputing these values might introduce bias in the dataset. So, in this case, it might be better just to drop this column.<jupyter_code>#drop NMHC(GT) column
df_withnulls.drop('NMHC(GT)', axis=1, inplace=True)
#Check for remaining null values
df_withnulls.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 9357 entries, 0 to 9356
Data columns (total 14 columns):
Date             9357 non-null object
Time             9357 non-null object
CO(GT)           7674 non-null float64
PT08.S1(CO)      8991 non-null float64
C6H6(GT)         8991 non-null float64
PT08.S2(NMHC)    8991 non-null float64
NOx(GT)          7718 non-null float64
PT08.S3(NOx)     8991 non-null float64
NO2(GT)          7715 non-null float64
PT08.S4(NO2)     8991 non-null float64
PT08.S5(O3)      8991 non-null float64
T                8991 non-null float64
RH               8991 non-null float64
AH               8991 non-null float64
dtypes: float64(12), object(2)
memory usage: 1.1+ MB<jupyter_text>All remaining columns, except for the "Date" and "Time" have missing values. Since these measurements represent time series data from atmospheric sensors, it is likely that each data point is related to the points before and after.  So, we can use a variety of imputation methods - using a mean (median or most frequent number) for the column, as well as filling forward(backward) and interpolating. For practice purposes, I will use different methods for different columns.<jupyter_code>#Fill forward  CO(GT) column - replace values with the last known value
df_withnulls.iloc[0:9356, 2].fillna(method='ffill', inplace=True)
#Check results
df_withnulls.info()
#PT09.S1(CO) column - fill backwards - uses the newest values to replace a missing value
df_withnulls.iloc[0:9356, 3].fillna(method='bfill', inplace=True)
#Use interpolation to fill missing values - 'C6H6(GT)' column
#method "pad" - fill in NaNs using existing values
df_withnulls.iloc[:,4].interpolate(method='pad', inplace=True)
#Check results
df_withnulls.info()
#copy remaining columns 
df_toimpute=df_withnulls.iloc[:, 5:14].values
df_toimpute.shape
#Use SimpleImputer from sklearn 
from sklearn.impute import SimpleImputer
#create an imputer using a mean value for a column
imputer=SimpleImputer(missing_values=np.nan, strategy='mean')
#fit it to the data
imputer.fit(df_toimpute)
#transform the data and convert it back to a panda data frame
df_nonulls =pd.DataFrame(imputer.transform(df_toimpute))
#check results
df_nonulls.info()
#add column names to the transformed data frame
df_nonulls.columns = ['PT08.S2(NMHC)', 'NOx(GT)', 'PT08.S3(NOx)', 'NO2(GT)', 'PT08.S4(NO2)', 'PT08.S5(O3)', 'T', 'RH', 'AH' ]
#Copy initial Date, Time columns and columns to which we applied filling forward, backward and interpolation
df_final = df_withnulls[['Date', 'Time', 'CO(GT)', 'PT08.S1(CO)', 'C6H6(GT)']]
#concatenate the two data franes cleaned of null values in one dataframe
df_result=pd.concat([df_final, df_nonulls], axis=1)
#chech the first five rows
df_result.head()
#check the last five rows
df_result.tail()
#Display detailed information about the structure of the data frame
#to make sure that there are no null values
df_result.info()
#Display summary statistics for the variables (columns)
#No '-200.0' or other default values resent
df_result.describe()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08642609477334018, 'fraction_numerical': 0.080647232567099, 'mean_word_length': 3.6887417218543046, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '581108', 'license': 'MIT', 'n_tokens_mistral': 2864, 'n_tokens_neox': 2514, 'n_words': 924, 'path': '.ipynb_checkpoints/MSDS_Practicing_with_pandas_3-checkpoint.ipynb', 'repo_name': 'nweakly/MSDS_class_projects_python', 'size': '7787'}