{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\n#Importing Text Analysis Libraries:\nfrom textblob import TextBlob\nfrom nltk.tokenize import sent_tokenize, word_tokenize\nfrom nltk.stem import PorterStemmer\n!pip install textblob \nfrom nltk.corpus import stopwords\nimport nltk\nnltk.download()\nfrom nltk.sentiment.vader import SentimentIntensityAnalyzer as vader\nprint('Libraries Imported')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.set_style('darkgrid')\nmatplotlib.rcParams['font.size'] = 10\nmatplotlib.rcParams['figure.facecolor'] = '#00000000'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c1=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2018_Cities_Disclosing_to_CDP.csv')\nc2=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv')\nc3=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2020_Cities_Disclosing_to_CDP.csv')\nc21=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2018_Full_Cities_Dataset.csv')\nc22=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv')\nc23=pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2020_Full_Cities_Dataset.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c2","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c3","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c21.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c22.head()\nc23.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c1['Country'] = c1['Country'].replace(['United States of America'],'USA')\nc2['Country'] = c2['Country'].replace(['United States of America'],'USA')\nc3['Country'] = c3['Country'].replace(['United States of America'],'USA')\nc1['Country'] = c1['Country'].replace(['Republic of Korea'],'S.Korea')\nc2['Country'] = c2['Country'].replace(['Republic of Korea'],'S.Korea')\nc3['Country'] = c3['Country'].replace(['Republic of Korea'],'S.Korea')\nc1['Country'] = c1['Country'].replace(['United Kingdom of Great Britain and Northern Ireland'],'UK')\nc2['Country'] = c2['Country'].replace(['United Kingdom of Great Britain and Northern Ireland'],'UK')\nc3['Country'] = c3['Country'].replace(['United Kingdom of Great Britain and Northern Ireland'],'UK')\nc1['Country'] = c1['Country'].replace(['Bolivia (Plurinational State of)'],'Bolivia')\nc2['Country'] = c2['Country'].replace(['Bolivia (Plurinational State of)'],'Bolivia')\nc3['Country'] = c3['Country'].replace(['Bolivia (Plurinational State of)'],'Bolivia')\nc1['Country'] = c1['Country'].replace(['Taiwan, Greater China'],'Taiwan')\nc2['Country'] = c2['Country'].replace(['Taiwan, Greater China'],'Taiwan')\nc3['Country'] = c3['Country'].replace(['Taiwan, Greater China'],'Taiwan')\nc1['Country'] = c1['Country'].replace(['Democratic Republic of the Congo'],'DR Congo')\nc2['Country'] = c2['Country'].replace(['Democratic Republic of the Congo'],'DR Congo')\nc3['Country'] = c3['Country'].replace(['Democratic Republic of the Congo'],'DR Congo')\nc1['Country'] = c1['Country'].replace(['China, Hong Kong Special Administrative Region'],'Hong Kong')\nc2['Country'] = c2['Country'].replace(['China, Hong Kong Special Administrative Region'],'Hong Kong')\nc3['Country'] = c3['Country'].replace(['China, Hong Kong Special Administrative Region'],'Hong Kong')\nc1['Country'] = c1['Country'].replace(['United Republic of Tanzania'],'Tanzania')\nc2['Country'] = c2['Country'].replace(['United Republic of Tanzania'],'Tanzania')\nc3['Country'] = c3['Country'].replace(['United Republic of Tanzania'],'Tanzania')\nc1['Country'] = c1['Country'].replace(['Russian Federation'],'Russia')\nc2['Country'] = c2['Country'].replace(['Russian Federation'],'Russia')\nc3['Country'] = c3['Country'].replace(['Russian Federation'],'Russia')\nc1['Country'] = c1['Country'].replace(['United Arab Emirates'],'UAE')\nc2['Country'] = c2['Country'].replace(['United Arab Emirates'],'UAE')\nc3['Country'] = c3['Country'].replace(['United Arab Emirates'],'UAE')\nc1['Country'] = c1['Country'].replace(['Venezuela (Bolivarian Republic of)'],'UAE')\nc2['Country'] = c2['Country'].replace(['Venezuela (Bolivarian Republic of)'],'UAE')\nc3['Country'] = c3['Country'].replace(['Venezuela (Bolivarian Republic of)'],'UAE')\nc1['Country'] = c1['Country'].replace(['State of Palestine'],'Palestine')\nc2['Country'] = c2['Country'].replace(['State of Palestine'],'Palestine')\nc3['Country'] = c3['Country'].replace(['State of Palestine'],'Palestine')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(20,25))\nplt.subplot(1,2,1)\nsns.countplot(y=c1['Country'],order = c1['Country'].value_counts().index,palette='rainbow')\nplt.ylabel('COUNTRY',fontsize=25)\nplt.xlabel('COUNT',fontsize=30)\nplt.title('2018', fontsize= 20);\n\nplt.subplot(1,2,2)\nsns.countplot(y=c2['Country'],order = c2['Country'].value_counts().index,palette='rainbow')\nplt.ylabel('',fontsize=10)\nplt.xlabel('COUNT',fontsize=30)\nplt.title('2019',fontsize= 20);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,25))\nsns.countplot(y=c3['Country'],order = c3['Country'].value_counts().index,palette='rainbow')\nplt.ylabel('COUNTRY - 2020',fontsize=40)\nplt.xlabel('COUNT',fontsize=30)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(30,30))\nplt.subplot(3,1,1)\nsns.countplot(y=c1['CDP Region'],order = c1['CDP Region'].value_counts().index,palette='rocket')\nplt.ylabel('REGION - 2018',fontsize=40)\nplt.xlabel('COUNT',fontsize=25)\nplt.yticks(fontsize=15);\n\nplt.subplot(3,1,2)\nsns.countplot(y=c2['CDP Region'],order = c2['CDP Region'].value_counts().index,palette='rocket')\nplt.ylabel('REGION - 2019',fontsize=40)\nplt.xlabel('COUNT',fontsize=25)\nplt.yticks(fontsize=15);\n\n\nplt.subplot(3,1,3)\nsns.countplot(y=c3['CDP Region'],order = c3['CDP Region'].value_counts().index,palette='rocket')\nplt.ylabel('REGION - 2020',fontsize=40)\nplt.xlabel('COUNT',fontsize=25)\nplt.yticks(fontsize=15);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(20,5))\nplt.subplot(1,3,1)\nsns.countplot(c1['Access'],palette='summer')\nplt.ylabel('COUNT',fontsize=20)\nplt.title('2018', fontsize= 20);\n\nplt.subplot(1,3,2)\nsns.countplot(c2['Access'],palette='summer')\nplt.ylabel('')\nplt.title('2019', fontsize= 20);\n\nplt.subplot(1,3,3)\nsns.countplot(c3['Access'],palette='summer')\nplt.ylabel('')\nplt.title('2020', fontsize= 20);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c21.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c21.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c21.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,7))\nsns.countplot(y=c21['Parent Section'],order = c21['Parent Section'].value_counts().index,alpha=0.7,palette='spring')\nplt.yticks(fontsize=10)\nplt.ylabel('PARENT_SECTION - 2018', fontsize='22')\nplt.xlabel('COUNT', fontsize='15')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,10))\nplt.subplot(2,1,1)\nsns.countplot(y=c22['Parent Section'],order = c22['Parent Section'].value_counts().index,alpha=0.7,palette='spring')\nplt.yticks(fontsize=10);\nplt.ylabel('PARENT_SECTION - 2019', fontsize='20')\nplt.xlabel('COUNT', fontsize='15')\n\nplt.subplot(2,1,2)\nsns.countplot(y=c23['Parent Section'],order = c23['Parent Section'].value_counts().index,alpha=0.7,palette='spring')\nplt.yticks(fontsize=10);\nplt.ylabel('PARENT_SECTION - 2020', fontsize='20')\nplt.xlabel('COUNT', fontsize='15')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,15))\nsns.countplot(y=c21['Section'],order = c21['Section'].value_counts().index,alpha=0.7,palette='magma')\nplt.yticks(fontsize=10);\nplt.ylabel('SECTION - 2018', fontsize='50')\nplt.xlabel('COUNT', fontsize='25')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,15))\nsns.countplot(y=c22['Section'],order = c22['Section'].value_counts().index,alpha=0.7,palette='magma')\nplt.yticks(fontsize=10);\nplt.ylabel('SECTION - 2019', fontsize='50')\nplt.xlabel('COUNT', fontsize='25')\nText(0.5, 0, 'COUNT')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,15))\nsns.countplot(y=c23['Section'],order = c23['Section'].value_counts().index,alpha=0.7,palette='magma')\nplt.yticks(fontsize=10);\nplt.ylabel('SECTION - 2020', fontsize='50')\nplt.xlabel('COUNT', fontsize='25')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(c21['Question Name'].duplicated().sum())\nprint(c22['Question Name'].duplicated().sum())\nprint(c23['Question Name'].duplicated().sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c211 = c21['Question Name']\nc211 = c211.drop_duplicates().reset_index()\nc211 = c211.drop('index',axis=1)\nc222 = c22['Question Name']\nc222 = c222.drop_duplicates().reset_index()\nc222 = c222.drop('index',axis=1)\nc233 = c23['Question Name']\nc233 = c233.drop_duplicates().reset_index()\nc233 = c233.drop('index',axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c31 = c211.merge(c222, on='Question Name', how='right')\nc32 = c31.merge(c233,on='Question Name', how='right')\nc32","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c32['Question Name'] = c32['Question Name'].apply(lambda x: \" \".join(x.lower() for x in x.split()))\nc32['Question Name'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c32['Question Name'] = c32['Question Name'].str.replace('[^\\w\\s]','')\nc32['Question Name'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"stop = stopwords.words('english')\n\nc32['Question Name'] = c32['Question Name'].apply(lambda x: \" \".join(x for x in x.split() if x not in stop));\nc32['Question Name'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"freq = pd.Series(' '.join(c32['Question Name']).split()).value_counts()[:10]\nfreq","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"freq = list(freq.index)\nc32['Question Name'] = c32['Question Name'].apply(\n    lambda x: \" \".join(x for x in x.split() if x not in freq))\nc32['Question Name'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"st = PorterStemmer()\nc32['Question Name'].apply(lambda x: \" \".join([st.stem(word) for word in x.split()]))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c32['sentiment'] = c32['Question Name'].apply(lambda x: TextBlob(x).sentiment[0] )\nc32[['Question Name','sentiment']].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"vader=vader()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\nc32['scores'] = c32['Question Name'].apply(lambda x: vader.polarity_scores(x))\nc32['compound']=c32['scores'].apply(lambda score_dict: score_dict['compound']) \nc32['pos']=c32['scores'].apply(lambda score_dict: score_dict['pos'])\nc32['neg']=c32['scores'].apply(lambda score_dict: score_dict['neg']) \nc32['neu']=c32['scores'].apply(lambda score_dict: score_dict['neu'])\nc32=c32.drop('scores',axis=1)\nc32","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c32['sentiment']=c32['sentiment'].astype(float)\nc32.sentiment[c32.sentiment>0]=1\nc32.sentiment[c32.sentiment<0]=-1;","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c32","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(len(c21['Response Answer'].unique()))\nprint(len(c22['Response Answer'].unique()))\nprint(len(c23['Response Answer'].unique()))\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c41=c21['Response Answer']\nc42=c22['Response Answer']\nc43=c23['Response Answer']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(c41.duplicated().sum())\nprint(c42.duplicated().sum())\nprint(c43.duplicated().sum())\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c41=c41.drop_duplicates().reset_index()\nc42=c42.drop_duplicates().reset_index()\nc43=c43.drop_duplicates().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c41=c41.drop('index', axis=1)\nc42=c42.drop('index', axis=1)\nc43=c43.drop('index', axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c51 = c41.merge(c42, how='right',on='Response Answer')\nc52 = c51.merge(c43, how='right',on='Response Answer')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c53 = c52.dropna()\nc53","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c53['Response Answer'] = c53['Response Answer'].apply(lambda x: \" \".join(x.lower() for x in x.split()))\nc53['Response Answer'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c53['Response Answer'] = c53['Response Answer'].str.replace('[^\\w\\s]','')\nc53['Response Answer'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"stop = stopwords.words('english')\n\nc53['Response Answer'] = c53['Response Answer'].apply(lambda x: \" \".join(x for x in x.split() if x not in stop));\nc53['Response Answer'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"freq = pd.Series(' '.join(c53['Response Answer']).split()).value_counts()[:10]\nfreq","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"freq = list(freq.index)\nc53['Response Answer'] = c53['Response Answer'].apply(\n    lambda x: \" \".join(x for x in x.split() if x not in freq))\nc53['Response Answer'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"freq = pd.Series(' '.join(c53['Response Answer']).split()).value_counts()[-10:]\nfreq","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"freq = list(freq.index)\nc53['Response Answer'] = c53['Response Answer'].apply(\n    lambda x: \" \".join(x for x in x.split() if x not in freq))\nc53['Response Answer'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"st = PorterStemmer()\nc53['Response Answer'].apply(lambda x: \" \".join([st.stem(word) for word in x.split()]))\nc53['Response Answer'].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c53['sentiment'] = c53['Response Answer'].apply(lambda x: TextBlob(x).sentiment[0] )\nc53[['Response Answer','sentiment']].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c53['scores'] = c53['Response Answer'].apply(lambda x: vader.polarity_scores(x))\nc53['compound']=c53['scores'].apply(lambda score_dict: score_dict['compound']) \nc53['pos']=c53['scores'].apply(lambda score_dict: score_dict['pos'])\nc53['neg']=c53['scores'].apply(lambda score_dict: score_dict['neg']) \nc53['neu']=c53['scores'].apply(lambda score_dict: score_dict['neu'])\nc53=c53.drop('scores',axis=1)\nc53","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"c53['sentiment']=c53['sentiment'].astype(float)\nc53.sentiment[c53.sentiment>0]=1\nc53.sentiment[c53.sentiment<0]=-1;","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(20,8))\nplt.subplot(1,2,1)\nax=sns.countplot(c32['sentiment'],palette='winter')\nax.set_xticklabels(['Negative','Neutral','Positive']);\nplt.ylabel('SENTIMENTAL DISTRIBUTION', fontsize=25)\nplt.title('QUESTION NAME', fontsize=15)\n\nplt.subplot(1,2,2)\nax=sns.countplot(c53['sentiment'],palette='winter')\nax.set_xticklabels(['Negative','Neutral','Positive'])\nplt.title('RESPONSE ANSWER', fontsize=15)\nplt.ylabel('')","execution_count":null,"outputs":[]}],"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":4,"nbformat_minor":4}