<jupyter_start><jupyter_text>___ SAWS Data Summary<jupyter_code>saws.info()
saws.describe()
saws.isnull().sum()<jupyter_output><empty_output><jupyter_text>Takeaways:- How should we handle these nulls?<jupyter_code>plt.figure(figsize=(13,22))
sns.histplot(y=saws['Service Location'], hue=saws['Service Location'])
plt.legend([],[], frameon=False)
plt.show()
saws['ZIP Code'].value_counts()<jupyter_output><empty_output><jupyter_text>___  SAWS Cleaning- Should do somehting about the values that are just an asterisk    - All of the date com        - Replace with 0            - Reason because * stands for water is not being provided to that resident during that time.- Change columns of dates into rows of dates    - Flip it        - ![Screen Shot 2021-07-06 at 2.09.37 PM.png](attachment:517a0327-984f-4c30-91a7-08ac983c92c3.png)- Drop    - Unnamed: 0    - ZIP Code- Handle Nulls    - Replace with 0        - Reason for this is because we are assuming that the house            - wasnt built yet,             - not rented out yet            - water shut off            - ect.- Concat Prefix and Suffix onto Service Location    - Then drop Preffix, Suffix, and Service Location<jupyter_code>saws.head(10)<jupyter_output><empty_output><jupyter_text>Cosa Cleaning- Drop    - LAT    - LONG    - Zone    - Sensor ID    - Sensor Model    - Sensor Status- Make new features    - Daily Total    - Mothly Total    - Daily Average    - Monthly Average- Specific to data sets    - Air quality        - is there a way for us to make a feature for how good or bad the air is?        - Handle nulls            - AlertTriggered                - Replace with None                    - Because majority of these values are NONE                        - So it is safe to assume that they would also be None    - Flood        - Drop             - Alert Triggered                - Because they are all the same    - Sound        - Drop            - AlertTriggered                - They are all none    - Weather        - Drop            - AlertTriggered                - They are all Null values<jupyter_code>air.head(1)
air.isnull().sum()
air.AlertTriggered.value_counts()
flood.head(1)
flood.AlertTriggered.value_counts()
weather.head(1)
weather.isnull().sum()
weather.shape
weather.AlertTriggered.value_counts()
sound.head(1)
sound.isnull().sum()
sound.AlertTriggered.value_counts()<jupyter_output><empty_output><jupyter_text>___  Prepare Air quality- Drop    - LAT    - LONG    - Zone    - Sensor ID    - Sensor Model    - Sensor Status- Make new features    - Daily Total    - Mothly Total    - Daily Average    - Monthly Average        - **Waiting for now**            - Reason for this is because we would be adding in 28 new features.                - We plan to see if it would be helpful to do it during exploration- is there a way for us to make a feature for how good or bad the air is?- Handle nulls    - AlertTriggered        - Replace with None            - Because majority of these values are NONE                - So it is safe to assume that they would also be None<jupyter_code>air = pd.read_csv('med_center_air.csv')
air.head(1)
def clean_air():
    '''Drops unneeded columns from the air quality df
    then handles the nulls in alert triggered column
    set to date time format'''
    # read the csv
    air = pd.read_csv('med_center_air.csv')
    # drop the colums
    air = air.drop(columns=['LAT', 'LONG', 'Zone', 
                            'Sensor_id', 'SensorModel', 
                            'SensorStatus', 'Vendor'])
    # replace nulls in ALertTriggered to None
    air.fillna("None", inplace = True)
    # set to date time format
    air.DateTime = pd.to_datetime(air.DateTime)
    # rename features
    air = air.rename(columns={"DateTime": "datetime",
                              "AlertTriggered":"alert_triggered"})
    # return new df
    return air
air = clean_air()
air.head(1)
air.isnull().sum()<jupyter_output><empty_output><jupyter_text>___ Clean Flood  - Drop    - LAT    - LONG    - Zone    - Sensor ID    - Sensor Model    - Sensor Status- Make new features    - Daily Total    - Mothly Total    - Daily Average    - Monthly Average- Flood    - Drop         - Alert Triggered            - Because they are all the same<jupyter_code>flood.head(1)
def clean_flood():
    '''Drops unneeded columns from the med center flooding df
    Makes sure DateTime is in DateTime format'''
    # read csv
    flood = pd.read_csv('med_center_flood.csv')
    # drop the colums
    flood = flood.drop(columns=['LAT', 'LONG', 'Zone', 
                          'Sensor_id', 'SensorModel', 
                          'SensorStatus', 'AlertTriggered', 
                          'Temp_C', 'Temp_F', 'Vendor'])
    # Set to date time format
    flood.DateTime = pd.to_datetime(flood.DateTime)
    flood = flood.rename(columns={"DateTime": "datetime", 
                        "DistToWL_ft": "water_level_feet", 
                        "DistToWL_m": "water_level_meters", 
                        "DistToDF_ft": "flood_width_feet",
                        "DistToDF_m": "flood_width_meters"})
    # return new df
    return flood
flood = clean_flood()
flood.head()
air = air.replace(to_replace=-999, value=0)
air['dates'] = pd.to_datetime(air['datetime']).dt.date
air['time'] = pd.to_datetime(air['datetime']).dt.time
air['hour'] = pd.to_datetime(air['datetime']).dt.hour
air['weekday'] = pd.to_datetime(air['datetime']).dt.weekday
air['AQI_CO'] = pd.cut(air.CO, 
                            bins = [-1,4.5,9.5,12.5,15.5,30.5,4000],
                            labels = ['Good', 'Moderate', 
                                      'Unhealthy for Sensitive Groups', "Unhealthy", 
                                      "Very Unhealthy", 'Hazardous'])
CO_24hr = air.groupby('dates', as_index=False)['CO'].mean()
CO_24hr = CO_24hr.rename(columns={'CO':'CO_24hr'})
air = air.merge(CO_24hr, on = 'dates', how ='left')
air['AQI_CO_24hr'] = pd.cut(air.CO_24hr, 
                            bins = [-1,4.5,9.5,12.5,15.5,30.5,4000],
                            labels = ['Good', 'Moderate', 
                                      'Unhealthy for Sensitive Groups', "Unhealthy", 
                                      "Very Unhealthy", 'Hazardous'])
pm_25_24hr = air.groupby('dates', as_index=False)['Pm2_5'].mean()
pm_25_24hr = pm_25_24hr.rename(columns={'Pm2_5':'Pm_25_24hr'})
air = air.merge(pm_25_24hr, on = 'dates', how ='left')
air['AQI_pm_25_24hr'] = pd.cut(air.Pm_25_24hr, 
                            bins = [-1,12.1,35.5,55.5,150.5,250.5,4000],
                            labels = ['Good', 'Moderate', 
                                      'Unhealthy for Sensitive Groups', "Unhealthy", 
                                      "Very Unhealthy", 'Hazardous'])
air.sample()
pm_10_24hr = air.groupby('dates', as_index=False)['Pm10'].mean()
pm_10_24hr = pm_10_24hr.rename(columns={'Pm10':'Pm_10_24hr'})
air = air.merge(pm_10_24hr, on = 'dates', how ='left')
air['AQI_pm10_24hr'] = pd.cut(air.Pm_10_24hr, 
                            bins = [-1,55,154,255,355,425,4000],
                            labels = ['Good', 'Moderate', 
                                      'Unhealthy for Sensitive Groups', "Unhealthy", 
                                      "Very Unhealthy", 'Hazardous'])
air.sample()
air
air.AQI_pm10_24hr.value_counts()
air.AQI_pm_25_24hr.value_counts()
air.AQI_CO_24hr.value_counts()
def clean_air():
    '''Drops unneeded columns from the air quality df
    then handles the nulls in alert triggered column
    set to date time format'''
    # read the csv
    air = pd.read_csv('med_center_air.csv')
    # drop the colums
    air = air.drop(columns=['LAT', 'LONG', 'Zone', 
                            'Sensor_id', 'SensorModel', 
                            'SensorStatus', 'Vendor'])
    # replace nulls in ALertTriggered to None
    air.fillna("None", inplace = True)
    # set to date time format
    air.DateTime = pd.to_datetime(air.DateTime)
    # rename features
    air = air.rename(columns={"DateTime": "datetime",
                              "AlertTriggered":"alert_triggered"})
    air = air.replace(to_replace=-999, value=0)
    # create time series features
    air['dates'] = pd.to_datetime(air['datetime']).dt.date
    air['time'] = pd.to_datetime(air['datetime']).dt.time
    air['hour'] = pd.to_datetime(air['datetime']).dt.hour
    air['weekday'] = pd.to_datetime(air['datetime']).dt.weekday
    # make all CO bins
    air['AQI_CO'] = pd.cut(air.CO, 
                            bins = [-1,4.5,9.5,12.5,15.5,30.5,4000],
                            labels = ['Good', 'Moderate', 
                                      'Unhealthy for Sensitive Groups', "Unhealthy", 
                                      "Very Unhealthy", 'Hazardous'])
    
    CO_24hr = air.groupby('dates', as_index=False)['CO'].mean()
    CO_24hr = CO_24hr.rename(columns={'CO':'CO_24hr'})
    air = air.merge(CO_24hr, on = 'dates', how ='left')
    air['AQI_CO_24hr'] = pd.cut(air.CO_24hr, 
                                bins = [-1,4.5,9.5,12.5,15.5,30.5,4000],
                                labels = ['Good', 'Moderate', 
                                          'Unhealthy for Sensitive Groups', "Unhealthy", 
                                          "Very Unhealthy", 'Hazardous'])
    
    pm_25_24hr = air.groupby('dates', as_index=False)['Pm2_5'].mean()
    pm_25_24hr = pm_25_24hr.rename(columns={'Pm2_5':'Pm_25_24hr'})
    air = air.merge(pm_25_24hr, on = 'dates', how ='left')
    air['AQI_pm_25_24hr'] = pd.cut(air.Pm_25_24hr, 
                                bins = [-1,12.1,35.5,55.5,150.5,250.5,4000],
                                labels = ['Good', 'Moderate', 
                                          'Unhealthy for Sensitive Groups', "Unhealthy", 
                                          "Very Unhealthy", 'Hazardous'])
    
    pm_10_24hr = air.groupby('dates', as_index=False)['Pm10'].mean()
    pm_10_24hr = pm_10_24hr.rename(columns={'Pm10':'Pm_10_24hr'})
    air = air.merge(pm_10_24hr, on = 'dates', how ='left')
    air['AQI_pm10_24hr'] = pd.cut(air.Pm_10_24hr, 
                                bins = [-1,55,154,255,355,425,4000],
                                labels = ['Good', 'Moderate', 
                                          'Unhealthy for Sensitive Groups', "Unhealthy", 
                                          "Very Unhealthy", 'Hazardous'])
    # return new df
    return air
air = clean_air()
air
sa_weather = pd.read_csv('SA_weather.csv')
sa_weather
sa_weather['Date_Time'] = sa_weather['Date'] + ' ' + sa_weather['Time']
sa_weather.Date_Time = pd.to_datetime(sa_weather.Date_Time)
sa_weather
sa_weather['DateTime'] = sa_weather['Date_Time'].dt.round('60min')
sa_weather = sa_weather.set_index('DateTime')
sa_weather = sa_weather.drop(columns=['Date_Time'])
sa_weather.head()
import wrangle
weather = wrangle.wrangle_weather()
weather.head(5)
weather['DateTime'] = weather['datetime'].dt.round('60min')
weather.head()
weather = weather.set_index('DateTime')
weather
weather = weather.join(sa_weather, how='right')
weather = weather.replace(to_replace=-999, value=0)
weather
weather.dropna(inplace = True)
weather
def wrangle_weather():
    '''
    This function will drop unneccessary columns, 
    change datetime to a pandas datetime datatype,
    and rename columns to be more readable to return
    a clean dataframe.  
    '''
    #read csv and turn into pandas dataframe
    weather = pd.read_csv('med_center_weather.csv')
    sa_weather = pd.read_csv('SA_weather.csv')
    # concat sa date and time
    sa_weather['Date_Time'] = sa_weather['Date'] + ' ' + sa_weather['Time']
    # put into date time format
    sa_weather.Date_Time = pd.to_datetime(sa_weather.Date_Time)
    # round to nearest hour
    sa_weather['DateTime'] = sa_weather['Date_Time'].dt.round('60min')
    # set sa weather index
    sa_weather = sa_weather.set_index('DateTime')
    # drop old datetime
    sa_weather = sa_weather.drop(columns=['Date_Time'])
    #drop columns we will not be using
    weather.drop(columns=[
    'Sensor_id', 
    'Vendor', 
    'SensorModel', 
    'LAT', 
    'LONG', 
    'Zone', 
    'AlertTriggered', 
    'SensorStatus'], inplace=True)
    #rename columns to be more readable
    weather = weather.rename(columns={"DateTime": "datetime", 
                            "Temp_C": "celsius", 
                            "Temp_F": "farenheit", 
                            "Humidity": "humidity",
                            "DewPoint_C": "dewpoint_celsius",
                            "DewPoint_F": "dewpoint_farenheit",
                            "Pressure_Pa": "pressure"})
    #change datetime to pandas datetime object
    weather.datetime = pd.to_datetime(weather.datetime)
    # round to hour
    weather['DateTime'] = weather['datetime'].dt.round('60min')
    # set index
    weather = weather.set_index('DateTime')
    # join the 2 df's
    weather = weather.join(sa_weather, how='right')
    # repalce -999
    weather = weather.replace(to_replace=-999, value=0)
    # drop nulls
    weather.dropna(inplace = True)
    #return clean weather df
    return weather
weather = wrangle_weather()
weather<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12443267776096822, 'fraction_numerical': 0.030559757942511347, 'mean_word_length': 2.238853503184713, 'pattern_counts': {'":': 16, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 22, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '203009', 'license': 'MIT', 'n_tokens_mistral': 4503, 'n_tokens_neox': 4079, 'n_words': 968, 'path': 'workbooks/caitlyn/acquire&prepare.ipynb', 'repo_name': 'slc-data/2021-datathon', 'size': '13220'}