{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Handling Missing data in Cities responses files","metadata":{}},{"cell_type":"code","source":"\nimport pandas as pd\nfrom IPython.display import display \n\nCitiesQusResponses2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv\")\nCitiesQusResponses2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv\")\nCitiesQusResponses2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv\")\n","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2021-10-08T18:33:47.27016Z","iopub.execute_input":"2021-10-08T18:33:47.270515Z","iopub.status.idle":"2021-10-08T18:34:01.224666Z","shell.execute_reply.started":"2021-10-08T18:33:47.270481Z","shell.execute_reply":"2021-10-08T18:34:01.223788Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def ShowMissingDataPercentege(df):\n    print(\"Columns with Missing Data\")\n    colMissingPerce = df.isna().sum() * 100 / df.shape[0]\n    display(colMissingPerce.sort_values(ascending = False))\n    ","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.477299Z","iopub.status.idle":"2021-10-08T18:30:48.478182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling Missing data in cities responses","metadata":{}},{"cell_type":"code","source":"        \ndef HandleAttrMissingDataByStudyRelationWithOtherAttr(df, groupByList ,handledAttribute,fillWithConst):    \n    # Study the relation between the attributes in the groupByList and the HandledAttribute\n    \n    # Try to fill missing data in every group from other non missing value in the same groupe\n    df[handledAttribute] = df.groupby(groupByList, dropna = False)[handledAttribute].fillna(method='ffill')\n    df[handledAttribute] = df.groupby(groupByList, dropna = False)[handledAttribute].fillna(method='bfill')\n    \n    # After doing the step before WE can gurantee that all groups contain both NAN and non missing values\n    # have been filled with the non missing values, \n    # all other remaining groups with missing contain only NANs so we will replace them with const \n    # or with values from other column\n\n    if fillWithConst :\n        df[handledAttribute].fillna(\"NO ANY\", inplace=True)\n    else :\n        df[handledAttribute].fillna(df[groupByList[0]], inplace=True)\n    \ndef HandleResponseAndCommnetsAttrMissingData(df,responseAnswer, comments):\n    # remove the rows where both responses and comments attributes are missing\n    # since there is no need to keep a response row while there is no response nor commnet in\n    df.dropna(axis = 0, subset=[responseAnswer,comments],inplace=True ,how='all')\n    \n    # if there is still rows where one of the Commnets or the Response Answer attributes not missing\n    # then replace the  missing with \"No Any\"\n    \n    df[comments].fillna(\"NO ANY\", inplace=True)\n    df[responseAnswer].fillna(\"NO ANY\", inplace=True)   \n\n    \ndef RemoveAttributesWithMissingDataPercent(percent,dataFrame):\n    colMissingPerce = dataFrame.isna().sum() / dataFrame.shape[0]\n\n    for i, value in colMissingPerce.items():\n        if colMissingPerce[i] > percent:\n            dataFrame.drop([i], axis = 1,inplace =True)  \n\n    ","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2021-10-08T18:30:48.582303Z","iopub.execute_input":"2021-10-08T18:30:48.58295Z","iopub.status.idle":"2021-10-08T18:30:48.59693Z","shell.execute_reply.started":"2021-10-08T18:30:48.582899Z","shell.execute_reply":"2021-10-08T18:30:48.595804Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def HandelMissingData(df):\n    print(\"Display the count of missing data in the attribute that has ones before handling missing data \")    \n    ShowMissingDataPercentege(df)\n    \n    print(\"The shape of the table before handling the missing data : \", df.shape)\n\n    HandleResponseAndCommnetsAttrMissingData(df,'Response Answer','Comments')\n    HandleAttrMissingDataByStudyRelationWithOtherAttr(df,['Section'], 'Parent Section', False)  \n    HandleAttrMissingDataByStudyRelationWithOtherAttr(df,['Section', 'Question Number', 'Row Number'],'Row Name', True)  \n    HandleAttrMissingDataByStudyRelationWithOtherAttr(df,['Section', 'Question Number', 'Column Number'],'Column Name', True)    \n    \n    # drop the attribute files name , since the files are not attched with the data and also\n    # more than 90% of them missing\n    RemoveAttributesWithMissingDataPercent(0.9, df)\n    \n    #display the final result where there is no missing data\n    print(\"Display the count of missing data in every attribute after handling missing data \")\n    ShowMissingDataPercentege(df)\n                                                    \n    print(\"The shape of the table afetr handling the missing data : \", df.shape)\n    \ndef HandleCitiesResponses():\n    print(\"Handling Cities responses 2020 file missing data\")\n    HandelMissingData(CitiesQusResponses2020)\n    print(\"#####################################################################\")\n    \n    print(\"Handling Cities responses 2019 file missing data\")\n    HandelMissingData(CitiesQusResponses2019)\n    print(\"#####################################################################\")\n    \n    print(\"Handling Cities responses 2018 file missing data\")\n    HandelMissingData(CitiesQusResponses2018)\n    print(\"#####################################################################\")","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.598582Z","iopub.execute_input":"2021-10-08T18:30:48.598978Z","iopub.status.idle":"2021-10-08T18:30:48.612921Z","shell.execute_reply.started":"2021-10-08T18:30:48.598947Z","shell.execute_reply":"2021-10-08T18:30:48.611945Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Output before and after handling every file","metadata":{}},{"cell_type":"code","source":"HandleCitiesResponses()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.614406Z","iopub.execute_input":"2021-10-08T18:30:48.614739Z","iopub.status.idle":"2021-10-08T18:30:48.650058Z","shell.execute_reply.started":"2021-10-08T18:30:48.614686Z","shell.execute_reply":"2021-10-08T18:30:48.648249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling Missing Data in climate change coperations responses","metadata":{}},{"cell_type":"code","source":"#Reading files  \n    \nClimateSolResponses2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2020_Full_Climate_Change_Dataset.csv\")\nClimateSolResponses2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2019_Full_Climate_Change_Dataset.csv\")\nClimateSolResponses2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Climate Change/2018_Full_Climate_Change_Dataset.csv\")\n","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2021-10-08T18:30:48.65134Z","iopub.status.idle":"2021-10-08T18:30:48.652081Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def HandleClimateChangeAndWaterSecResponseMissingData(df):\n    ShowMissingDataPercentege(df)\n\n    print(\"The shape of the table before handling the missing data : \", df.shape)\n    \n    HandleAttrMissingDataByStudyRelationWithOtherAttr(df,['question_unique_reference', 'question_number', \"column_name\"],\"column_number\", True)   \n    HandleAttrMissingDataByStudyRelationWithOtherAttr(df,['question_unique_reference', 'question_number', \"row_number\"],\"row_name\", True)  \n    HandleResponseAndCommnetsAttrMissingData(df,'response_value','comments')\n    \n    #drop the attribute accounting_period_to , since all its values are NAN\n    RemoveAttributesWithMissingDataPercent(0.9, df)\n    display(df.isnull().sum())\n    print(\"The shape of the table after handling the missing data : \", df.shape)\n\n","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.755002Z","iopub.execute_input":"2021-10-08T18:30:48.755351Z","iopub.status.idle":"2021-10-08T18:30:48.762625Z","shell.execute_reply.started":"2021-10-08T18:30:48.755322Z","shell.execute_reply":"2021-10-08T18:30:48.761306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndef HandleClimateChangeResponses():\n    HandleClimateChangeAndWaterSecResponseMissingData(ClimateSolResponses2020)\n    \n    print(\"********************************************************\")\n    \n    HandleAttrMissingDataByStudyRelationWithOtherAttr(ClimateSolResponses2019,['data_point_name'],'data_point_id', True)\n    HandleClimateChangeAndWaterSecResponseMissingData(ClimateSolResponses2019)\n    \n    print(\"********************************************************\")\n    \n    HandleClimateChangeAndWaterSecResponseMissingData(ClimateSolResponses2018)\n","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.764042Z","iopub.execute_input":"2021-10-08T18:30:48.764348Z","iopub.status.idle":"2021-10-08T18:30:48.777332Z","shell.execute_reply.started":"2021-10-08T18:30:48.764321Z","shell.execute_reply":"2021-10-08T18:30:48.776392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"HandleClimateChangeResponses()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.778744Z","iopub.execute_input":"2021-10-08T18:30:48.779267Z","iopub.status.idle":"2021-10-08T18:30:48.803014Z","shell.execute_reply.started":"2021-10-08T18:30:48.779223Z","shell.execute_reply":"2021-10-08T18:30:48.80169Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling Water Security Responses Missing Data\n","metadata":{}},{"cell_type":"code","source":"#Reading files  \n    \nWaterSecSolResponses2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2020_Full_Water_Security_Dataset.csv\")\nWaterSecSolResponses2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2019_Full_Water_Security_Dataset.csv\")\nWaterSecSolResponses2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Responses/Water Security/2018_Full_Water_Security_Dataset.csv\")\n","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.803983Z","iopub.status.idle":"2021-10-08T18:30:48.804598Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndef HandleWaterSecResponses():\n    HandleClimateChangeAndWaterSecResponseMissingData(WaterSecSolResponses2020)\n    \n    print(\"********************************************************\")\n    HandleClimateChangeAndWaterSecResponseMissingData(WaterSecSolResponses2019)\n    \n    print(\"********************************************************\")\n    HandleClimateChangeAndWaterSecResponseMissingData(WaterSecSolResponses2018)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.805601Z","iopub.status.idle":"2021-10-08T18:30:48.806229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"HandleWaterSecResponses()","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.807149Z","iopub.status.idle":"2021-10-08T18:30:48.807739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n# Hnadling Cities Disclosing Files\n","metadata":{}},{"cell_type":"code","source":"\n        \nCitiesDisclosing2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv\")\nCitiesDisclosing2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv\")\nCitiesDisclosing2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv\")\n\nCitiesDisclosingAllYears = pd.concat([CitiesDisclosing2020,CitiesDisclosing2019,CitiesDisclosing2018], axis=0, sort=False, ignore_index = True)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.808635Z","iopub.status.idle":"2021-10-08T18:30:48.809245Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ShowMissingDataPercentege(CitiesDisclosing2020)\nShowMissingDataPercentege(CitiesDisclosing2019)\nShowMissingDataPercentege(CitiesDisclosing2018)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.810124Z","iopub.status.idle":"2021-10-08T18:30:48.81074Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"    \ndef HandleCitiesDisclosingMissingDataA(df): \n    ShowMissingDataPercentege(df)\n    \n    print(\"The shape of the table before handling the missing data : \", df.shape)\n    \n    # we can see that year 2020 has only one missing data on population attribute\n    # replacing the missing populations attributes in other years with the mean of the three years\n    df['Population'] = df.groupby('Account Number',dropna = False).transform(lambda x: x.fillna(x.mean()))\n    \n    # check if there is a Zero Population after applying the mean \n    if((df['Population'] == 0).sum()) :\n        raise \"Error some population is zero !\"\n        \n    # since the exception is not raised , then we were lucky the only missing population value in 2020\n    # has a corresponding value in other files\n\n    #*******************************************************************************#\n    # Hnadeling City and City Location Missing data\n    # Try to fill the missing data by searching on other years \n        \n    df['City'] = df.groupby('Account Number', dropna = False)['City'].fillna(method='ffill')\n    df['City'] = df.groupby('Account Number', dropna = False)['City'].fillna(method='bfill')\n\n    \n    # some Organizations are cities , the Organization attribute that starts with one of the following is a city\n    # so fill the city attribute with the value in the Organization\n    stringsList = ['City of' , 'Municipalidad' , 'Município','Municipio de', 'Prefeitura de','Junta Municipal de' ,'Ayuntamiento de','Town of','Alcaldía Municipal de']\n    \n    for i in stringsList:\n        strLen = len(stringsList) + 1\n        df.loc[(df['Organization'].str.startswith(i) & df['City'].isnull() ),'City'] = df['Organization'].str[strLen:]\n    \n    # Trying to fill the city location missing data by searching for the same city location not missing in other observation\n    df['City Location'] = df.groupby('City', dropna = False)['City Location'].fillna(method = 'ffill') \n    df['City Location'] = df.groupby('City', dropna = False)['City Location'].fillna(method = 'bfill')\n    \n    ShowMissingDataPercentege(df)\n    # set all remaining values to 'NO ANY'\n    df['City'].fillna(\"NO ANY\", inplace=True)  \n    df['City Location'].fillna(\"NO ANY\", inplace=True)  \n      \n    display(df.isnull().sum())\n    print(\"The shape of the table after handling the missing data : \", df.shape)\n    ","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.913552Z","iopub.execute_input":"2021-10-08T18:30:48.914103Z","iopub.status.idle":"2021-10-08T18:30:48.929265Z","shell.execute_reply.started":"2021-10-08T18:30:48.914053Z","shell.execute_reply":"2021-10-08T18:30:48.92831Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"HandleCitiesDisclosingMissingDataA(CitiesDisclosingAllYears)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.930643Z","iopub.execute_input":"2021-10-08T18:30:48.931213Z","iopub.status.idle":"2021-10-08T18:30:48.954756Z","shell.execute_reply.started":"2021-10-08T18:30:48.931171Z","shell.execute_reply":"2021-10-08T18:30:48.953461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Hnadling Water Security and Climate change Missing data","metadata":{}},{"cell_type":"code","source":"\nClimateChangeDisclosing2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2020_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\nClimateChangeDisclosing2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2019_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\nClimateChangeDisclosing2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Climate Change/2018_Corporates_Disclosing_to_CDP_Climate_Change.csv\")\n\nWaterSecurityDisclosing2020 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2020_Corporates_Disclosing_to_CDP_Water_Security.csv\")\nWaterSecurityDisclosing2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2019_Corporates_Disclosing_to_CDP_Water_Security.csv\")\nWaterSecurityDisclosing2018 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Corporations/Corporations Disclosing/Water Security/2018_Corporates_Disclosing_to_CDP_Water_Security.csv\")\n\n\nClimateChangeWaterSecurityDisclosingAllYears = pd.concat([ClimateChangeDisclosing2020,ClimateChangeDisclosing2019,ClimateChangeDisclosing2018,WaterSecurityDisclosing2020,WaterSecurityDisclosing2019,WaterSecurityDisclosing2018], axis=0, sort=False, ignore_index = True)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.955757Z","iopub.status.idle":"2021-10-08T18:30:48.956359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def HandlingMissingDataDisclosingCop(df):    \n    ShowMissingDataPercentege(df)\n    \n    # the account number for every coperation is the same every year\n    # try to find the missing data from other years\n    df['region'] = df.groupby('account_number',dropna= False)['region'].fillna(method='ffill')\n    df['samples'] = df.groupby('account_number',dropna= False)['samples'].fillna(method='ffill')\n    df['primary_ticker'] = df.groupby('account_number',dropna= False)['primary_ticker'].fillna(method='ffill')\n    df['tickers'] = df.groupby('account_number',dropna= False)['tickers'].fillna(method='ffill')\n    df['minimum_tier'] = df.groupby('account_number',dropna= False)['minimum_tier'].fillna(method='ffill')\n    df['primary_questionnaire_sector'] = df.groupby('account_number',dropna= False)['primary_questionnaire_sector'].fillna(method='ffill')\n    \n    ShowMissingDataPercentege(df)\n    \n    #primary_industry , primary_sector , primary_activity have the same number of missing values \n    # and they are missing in same observations , so remove the observations\n    df.dropna(subset = [\"primary_sector\"], inplace=True)\n\n    # removing all attrinutes with missing data percent is > 80%\n    RemoveAttributesWithMissingDataPercent(0.8, df)\n    \n    #replace the remaining with constant No Any\n    # these feilds are not mandatory ,and also they are missing in all years for the same observation \n    # so if they are missing we can only replcae the value with const\n    \n    df['primary_ticker'].fillna(\"NO ANY\", inplace=True)\n    df['tickers'].fillna(\"NO ANY\", inplace=True)\n    df['minimum_tier'].fillna(\"NO ANY\", inplace=True)\n    df['primary_questionnaire_sector'].fillna(\"NO ANY\", inplace=True)\n    df['samples'].fillna(\"NO ANY\", inplace=True)\n\n    \n    ShowMissingDataPercentege(df)\n    ","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.957373Z","iopub.status.idle":"2021-10-08T18:30:48.957952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\nHandlingMissingDataDisclosingCop(ClimateChangeWaterSecurityDisclosingAllYears)","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.958844Z","iopub.status.idle":"2021-10-08T18:30:48.959416Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling Missing data in Suplementry Data files","metadata":{}},{"cell_type":"markdown","source":"# Handling Missing data in 500_Cities__Census_Tract file","metadata":{}},{"cell_type":"code","source":"\nCitiesCensusTractlevelDataGISFriendlyFormat2019 = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC 500 Cities Census Tract Data/500_Cities__Census_Tract-level_Data__GIS_Friendly_Format___2019_release.csv\")\n\nShowMissingDataPercentege(CitiesCensusTractlevelDataGISFriendlyFormat2019)\n\nprint(CitiesCensusTractlevelDataGISFriendlyFormat2019.shape)\nCitiesCensusTractlevelDataGISFriendlyFormat2019.dropna(subset=['PAPTEST_Crude95CI'], inplace =True)\nCitiesCensusTractlevelDataGISFriendlyFormat2019.dropna(subset=['COREW_CrudePrev'], inplace =True)\nCitiesCensusTractlevelDataGISFriendlyFormat2019.dropna(subset=['COREM_Crude95CI'], inplace =True)\nCitiesCensusTractlevelDataGISFriendlyFormat2019.dropna(subset=['MAMMOUSE_CrudePrev'], inplace =True)\n\nShowMissingDataPercentege(CitiesCensusTractlevelDataGISFriendlyFormat2019)\n\n","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.960255Z","iopub.status.idle":"2021-10-08T18:30:48.960808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling SVI2018_US Missing Data","metadata":{}},{"cell_type":"code","source":"\ndf = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US.csv\")\nnumberOfColWithMissing = len(df.columns[df.isna().any()].tolist())\ndisplay(df.isnull().sum().sort_values(ascending = False).head(numberOfColWithMissing))\n\n# No missing data","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.961621Z","iopub.status.idle":"2021-10-08T18:30:48.962156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling SVI2018_US_COUNTY Missing Data\n","metadata":{}},{"cell_type":"code","source":"df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/CDC Social Vulnerability Index 2018/SVI2018_US_COUNTY.csv\")\nnumberOfColWithMissing = len(df.columns[df.isna().any()].tolist())\ndisplay(df.isnull().sum().sort_values(ascending = False).head(numberOfColWithMissing))\n\n# No missing data","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:48.962968Z","iopub.status.idle":"2021-10-08T18:30:48.963496Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling NA_HQ_public_data Missing Data","metadata":{}},{"cell_type":"code","source":"df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Locations of Corporations/NA_HQ_public_data.csv\")\nnumberOfColWithMissing = len(df.columns[df.isna().any()].tolist())\ndisplay(df.isnull().sum().sort_values(ascending = False).head(numberOfColWithMissing))\n# remove the attribute with perecentege > 80 %\nRemoveAttributesWithMissingDataPercent(0.8,df)\n\n# remove the rows with address_state and address_city missing\ndf.dropna(subset=['address_state'], inplace =True)\ndf.dropna(subset=['address_city'], inplace =True)\n\nnumberOfColWithMissing = len(df.columns[df.isna().any()].tolist())\ndisplay(df.isnull().sum().sort_values(ascending = False).head(numberOfColWithMissing))","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:49.065637Z","iopub.execute_input":"2021-10-08T18:30:49.066217Z","iopub.status.idle":"2021-10-08T18:30:49.107004Z","shell.execute_reply.started":"2021-10-08T18:30:49.066184Z","shell.execute_reply":"2021-10-08T18:30:49.105245Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Handling uscities Missing Data","metadata":{}},{"cell_type":"code","source":"df = pd.read_csv(\"../input/cdp-unlocking-climate-solutions/Supplementary Data/Simple Maps US Cities Data/uscities.csv\")\nnumberOfColWithMissing = len(df.columns[df.isna().any()].tolist())\ndisplay(df.isnull().sum().sort_values(ascending = False).head(numberOfColWithMissing))\n\n#fill it with constant since it's the only missing data\ndf.loc[df['zips'].isna(), 'zips'] = \"No Any\"","metadata":{"execution":{"iopub.status.busy":"2021-10-08T18:30:49.107935Z","iopub.status.idle":"2021-10-08T18:30:49.108493Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}