G-sn-data-geo-analysis.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"provenance":[{"file_id":"1BHkP5HWN9wwJmr_vQNqzRxHmVJn6ufql","timestamp":1678704445542}],"authorship_tag":"ABX9TyOGiJHeQH/YHawMKnLucD/D"},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"code","execution_count":null,"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"WB9ZFSI6VFfe","executionInfo":{"status":"ok","timestamp":1680581441018,"user_tz":-330,"elapsed":31249,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"b174cb53-d2fb-48bf-d701-25c6c499899c"},"outputs":[{"output_type":"stream","name":"stdout","text":["Mounted at /content/drive/\n"]}],"source":["# mount drive to access data\n","from google.colab import drive\n","drive.mount('/content/drive/')"]},{"cell_type":"code","source":["# change path HERE to reflect your folder structure\n","my_path = '/content/drive/MyDrive/suicide-project/data/'"],"metadata":{"id":"ZmcUvyv8oY0I"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["!head -1 /content/drive/MyDrive/suicide-project/data/KEYWORDS_FILTERED_PROCESSED_TEXT_CORPUS_v2.csv"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"nSk9ECzEbo0J","executionInfo":{"status":"ok","timestamp":1680581441840,"user_tz":-330,"elapsed":838,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"bd19cffd-62fb-46d5-ed50-ea55624cbce9"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["post_id,post_text,date,lemmatized_body,keywords_matched,group_name,year,month\n"]}]},{"cell_type":"code","source":["# create dataframe from csv data file\n","\n","import pandas as pd\n","from ast import literal_eval\n","df = pd.read_csv(my_path + 'KEYWORDS_FILTERED_PROCESSED_TEXT_CORPUS_v2.csv',usecols = ['post_id','year','month','group_name',\n","                                                                                       'keywords_matched','lemmatized_body'])\n","df.info()"],"metadata":{"id":"AWerUJrKWLde","executionInfo":{"status":"ok","timestamp":1680581445227,"user_tz":-330,"elapsed":3400,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"colab":{"base_uri":"https://localhost:8080/"},"outputId":"2d268cf8-0bac-4387-b298-8f6e1feebd15"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 128759 entries, 0 to 128758\n","Data columns (total 6 columns):\n"," #   Column            Non-Null Count   Dtype \n","---  ------            --------------   ----- \n"," 0   post_id           128759 non-null  int64 \n"," 1   lemmatized_body   128759 non-null  object\n"," 2   keywords_matched  128759 non-null  object\n"," 3   group_name        128759 non-null  object\n"," 4   year              128759 non-null  int64 \n"," 5   month             128759 non-null  int64 \n","dtypes: int64(3), object(3)\n","memory usage: 5.9+ MB\n"]}]},{"cell_type":"code","source":["df.head(5)"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"id":"cFXRcC5VYkwe","executionInfo":{"status":"ok","timestamp":1680581445230,"user_tz":-330,"elapsed":87,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"1da4cb25-f09a-4daa-8bd3-5c2c43551ca1"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   post_id                                    lemmatized_body  \\\n","0  1641475  one relate symmetry huge hang one taking pictu...   \n","1  1709493  pm bit detailed question hamvil said get back ...   \n","2   233329                              feel like ” hanging ”   \n","3   241976  nervous planning reading lot sn dmso get wedne...   \n","4   409855  sleep increase carbmazapine given diazepam fee...   \n","\n","  keywords_matched           group_name  year  month  \n","0         ['hang']          ['hanging']  2022      6  \n","1         ['hang']          ['hanging']  2022      9  \n","2      ['hanging']          ['hanging']  2019      2  \n","3           ['sn']               ['sn']  2019      2  \n","4     ['diazepam']  ['benzodiazepines']  2019      9  "],"text/html":["\n","  <div id=\"df-5878290b-4a62-45a4-aaea-a190cd82d742\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>post_id</th>\n","      <th>lemmatized_body</th>\n","      <th>keywords_matched</th>\n","      <th>group_name</th>\n","      <th>year</th>\n","      <th>month</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1641475</td>\n","      <td>one relate symmetry huge hang one taking pictu...</td>\n","      <td>['hang']</td>\n","      <td>['hanging']</td>\n","      <td>2022</td>\n","      <td>6</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>1709493</td>\n","      <td>pm bit detailed question hamvil said get back ...</td>\n","      <td>['hang']</td>\n","      <td>['hanging']</td>\n","      <td>2022</td>\n","      <td>9</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>233329</td>\n","      <td>feel like ” hanging ”</td>\n","      <td>['hanging']</td>\n","      <td>['hanging']</td>\n","      <td>2019</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>241976</td>\n","      <td>nervous planning reading lot sn dmso get wedne...</td>\n","      <td>['sn']</td>\n","      <td>['sn']</td>\n","      <td>2019</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>409855</td>\n","      <td>sleep increase carbmazapine given diazepam fee...</td>\n","      <td>['diazepam']</td>\n","      <td>['benzodiazepines']</td>\n","      <td>2019</td>\n","      <td>9</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5878290b-4a62-45a4-aaea-a190cd82d742')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-5878290b-4a62-45a4-aaea-a190cd82d742 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-5878290b-4a62-45a4-aaea-a190cd82d742');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "]},"metadata":{},"execution_count":5}]},{"cell_type":"code","source":["df = df.explode('group_name')\n","df.reset_index(drop = True,inplace = True)\n","len(df)"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"DXQwpHwitu24","executionInfo":{"status":"ok","timestamp":1680581445233,"user_tz":-330,"elapsed":79,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"39886067-4c8f-47d2-ca35-17c365f2a62d"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["128759"]},"metadata":{},"execution_count":6}]},{"cell_type":"code","source":["df.info()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"Y4dXUVmpc2zk","executionInfo":{"status":"ok","timestamp":1680581445237,"user_tz":-330,"elapsed":68,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"df3d4fc8-d1e3-427c-d482-078e184c1557"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 128759 entries, 0 to 128758\n","Data columns (total 6 columns):\n"," #   Column            Non-Null Count   Dtype \n","---  ------            --------------   ----- \n"," 0   post_id           128759 non-null  int64 \n"," 1   lemmatized_body   128759 non-null  object\n"," 2   keywords_matched  128759 non-null  object\n"," 3   group_name        128759 non-null  object\n"," 4   year              128759 non-null  int64 \n"," 5   month             128759 non-null  int64 \n","dtypes: int64(3), object(3)\n","memory usage: 5.9+ MB\n"]}]},{"cell_type":"code","source":["df.head(3)"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":143},"id":"F36i5ZSGgT1z","executionInfo":{"status":"ok","timestamp":1680581445239,"user_tz":-330,"elapsed":56,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"9ad22bc3-9367-401a-8518-4a32809f7a70"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   post_id                                    lemmatized_body  \\\n","0  1641475  one relate symmetry huge hang one taking pictu...   \n","1  1709493  pm bit detailed question hamvil said get back ...   \n","2   233329                              feel like ” hanging ”   \n","\n","  keywords_matched   group_name  year  month  \n","0         ['hang']  ['hanging']  2022      6  \n","1         ['hang']  ['hanging']  2022      9  \n","2      ['hanging']  ['hanging']  2019      2  "],"text/html":["\n","  <div id=\"df-8181d706-853e-41f1-9d10-43ca8136c100\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>post_id</th>\n","      <th>lemmatized_body</th>\n","      <th>keywords_matched</th>\n","      <th>group_name</th>\n","      <th>year</th>\n","      <th>month</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1641475</td>\n","      <td>one relate symmetry huge hang one taking pictu...</td>\n","      <td>['hang']</td>\n","      <td>['hanging']</td>\n","      <td>2022</td>\n","      <td>6</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>1709493</td>\n","      <td>pm bit detailed question hamvil said get back ...</td>\n","      <td>['hang']</td>\n","      <td>['hanging']</td>\n","      <td>2022</td>\n","      <td>9</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>233329</td>\n","      <td>feel like ” hanging ”</td>\n","      <td>['hanging']</td>\n","      <td>['hanging']</td>\n","      <td>2019</td>\n","      <td>2</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8181d706-853e-41f1-9d10-43ca8136c100')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-8181d706-853e-41f1-9d10-43ca8136c100 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-8181d706-853e-41f1-9d10-43ca8136c100');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "]},"metadata":{},"execution_count":9}]},{"cell_type":"code","source":["groups = ['sn','hanging','firearm','benzodiazepines','opioids',\n","          'antiemetic','barbiturates','acid regulator','cyanides','other preservatives',\n","          'plant-based poisons','household chemicals','ricin','nitric oxide']\n","\n","for i in groups:\n","  df[i] = 0 # Create one column for each group\n","\n","df.group_name = df.group_name.apply(literal_eval)\n","\n","for i in range(len(df)):\n","  curr_lis = df.loc[i,'group_name']\n","  for name in curr_lis:\n","    df.loc[i,name] = df.loc[i,name] + 1"],"metadata":{"id":"sMcTCLM3nglV"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["df.info()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"T6fWg-5-oUFZ","executionInfo":{"status":"ok","timestamp":1680581461766,"user_tz":-330,"elapsed":46,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"84c17430-8c89-4694-f1cb-c724582e5c5f"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 128759 entries, 0 to 128758\n","Data columns (total 20 columns):\n"," #   Column               Non-Null Count   Dtype \n","---  ------               --------------   ----- \n"," 0   post_id              128759 non-null  int64 \n"," 1   lemmatized_body      128759 non-null  object\n"," 2   keywords_matched     128759 non-null  object\n"," 3   group_name           128759 non-null  object\n"," 4   year                 128759 non-null  int64 \n"," 5   month                128759 non-null  int64 \n"," 6   sn                   128759 non-null  int64 \n"," 7   hanging              128759 non-null  int64 \n"," 8   firearm              128759 non-null  int64 \n"," 9   benzodiazepines      128759 non-null  int64 \n"," 10  opioids              128759 non-null  int64 \n"," 11  antiemetic           128759 non-null  int64 \n"," 12  barbiturates         128759 non-null  int64 \n"," 13  acid regulator       128759 non-null  int64 \n"," 14  cyanides             128759 non-null  int64 \n"," 15  other preservatives  128759 non-null  int64 \n"," 16  plant-based poisons  128759 non-null  int64 \n"," 17  household chemicals  128759 non-null  int64 \n"," 18  ricin                128759 non-null  int64 \n"," 19  nitric oxide         128759 non-null  int64 \n","dtypes: int64(17), object(3)\n","memory usage: 19.6+ MB\n"]}]},{"cell_type":"code","source":["!python -m spacy download en_core_web_lg"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"mW_phjAPtVKq","executionInfo":{"status":"ok","timestamp":1680581495516,"user_tz":-330,"elapsed":33786,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"6bfa5372-5eef-4e44-fedf-e2b21d720e03"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["/usr/local/lib/python3.9/dist-packages/torch/cuda/__init__.py:546: UserWarning: Can't initialize NVML\n","  warnings.warn(\"Can't initialize NVML\")\n","2023-04-04 04:11:08.035152: I tensorflow/core/platform/cpu_feature_guard.cc:182] This TensorFlow binary is optimized to use available CPU instructions in performance-critical operations.\n","To enable the following instructions: AVX2 FMA, in other operations, rebuild TensorFlow with the appropriate compiler flags.\n","2023-04-04 04:11:09.176754: W tensorflow/compiler/tf2tensorrt/utils/py_utils.cc:38] TF-TRT Warning: Could not find TensorRT\n","2023-04-04 04:11:11.105761: E tensorflow/compiler/xla/stream_executor/cuda/cuda_driver.cc:266] failed call to cuInit: CUDA_ERROR_NO_DEVICE: no CUDA-capable device is detected\n","Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n","Collecting en-core-web-lg==3.5.0\n","  Downloading https://github.com/explosion/spacy-models/releases/download/en_core_web_lg-3.5.0/en_core_web_lg-3.5.0-py3-none-any.whl (587.7 MB)\n","\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m587.7/587.7 MB\u001b[0m \u001b[31m2.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n","\u001b[?25hRequirement already satisfied: spacy<3.6.0,>=3.5.0 in /usr/local/lib/python3.9/dist-packages (from en-core-web-lg==3.5.0) (3.5.1)\n","Requirement already satisfied: spacy-legacy<3.1.0,>=3.0.11 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (3.0.12)\n","Requirement already satisfied: murmurhash<1.1.0,>=0.28.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (1.0.9)\n","Requirement already satisfied: wasabi<1.2.0,>=0.9.1 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (1.1.1)\n","Requirement already satisfied: pydantic!=1.8,!=1.8.1,<1.11.0,>=1.7.4 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (1.10.7)\n","Requirement already satisfied: catalogue<2.1.0,>=2.0.6 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2.0.8)\n","Requirement already satisfied: pathy>=0.10.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (0.10.1)\n","Requirement already satisfied: setuptools in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (67.6.1)\n","Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (23.0)\n","Requirement already satisfied: preshed<3.1.0,>=3.0.2 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (3.0.8)\n","Requirement already satisfied: smart-open<7.0.0,>=5.2.1 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (6.3.0)\n","Requirement already satisfied: jinja2 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (3.1.2)\n","Requirement already satisfied: langcodes<4.0.0,>=3.2.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (3.3.0)\n","Requirement already satisfied: numpy>=1.15.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (1.22.4)\n","Requirement already satisfied: spacy-loggers<2.0.0,>=1.0.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (1.0.4)\n","Requirement already satisfied: thinc<8.2.0,>=8.1.8 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (8.1.9)\n","Requirement already satisfied: tqdm<5.0.0,>=4.38.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (4.65.0)\n","Requirement already satisfied: requests<3.0.0,>=2.13.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2.27.1)\n","Requirement already satisfied: srsly<3.0.0,>=2.4.3 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2.4.6)\n","Requirement already satisfied: cymem<2.1.0,>=2.0.2 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2.0.7)\n","Requirement already satisfied: typer<0.8.0,>=0.3.0 in /usr/local/lib/python3.9/dist-packages (from spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (0.7.0)\n","Requirement already satisfied: typing-extensions>=4.2.0 in /usr/local/lib/python3.9/dist-packages (from pydantic!=1.8,!=1.8.1,<1.11.0,>=1.7.4->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (4.5.0)\n","Requirement already satisfied: urllib3<1.27,>=1.21.1 in /usr/local/lib/python3.9/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (1.26.15)\n","Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.9/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (3.4)\n","Requirement already satisfied: charset-normalizer~=2.0.0 in /usr/local/lib/python3.9/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2.0.12)\n","Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.9/dist-packages (from requests<3.0.0,>=2.13.0->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2022.12.7)\n","Requirement already satisfied: confection<1.0.0,>=0.0.1 in /usr/local/lib/python3.9/dist-packages (from thinc<8.2.0,>=8.1.8->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (0.0.4)\n","Requirement already satisfied: blis<0.8.0,>=0.7.8 in /usr/local/lib/python3.9/dist-packages (from thinc<8.2.0,>=8.1.8->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (0.7.9)\n","Requirement already satisfied: click<9.0.0,>=7.1.1 in /usr/local/lib/python3.9/dist-packages (from typer<0.8.0,>=0.3.0->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (8.1.3)\n","Requirement already satisfied: MarkupSafe>=2.0 in /usr/local/lib/python3.9/dist-packages (from jinja2->spacy<3.6.0,>=3.5.0->en-core-web-lg==3.5.0) (2.1.2)\n","Installing collected packages: en-core-web-lg\n","Successfully installed en-core-web-lg-3.5.0\n","\u001b[38;5;2m✔ Download and installation successful\u001b[0m\n","You can now load the package via spacy.load('en_core_web_lg')\n"]}]},{"cell_type":"code","source":["%%time\n","import spacy\n","from spacy import displacy\n","nlp = spacy.load('en_core_web_lg')"],"metadata":{"id":"gdx5QpxTRxuJ","executionInfo":{"status":"ok","timestamp":1680581504564,"user_tz":-330,"elapsed":9055,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"colab":{"base_uri":"https://localhost:8080/"},"outputId":"d0267ba2-06c6-4588-e266-bf4554858652"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stderr","text":["/usr/local/lib/python3.9/dist-packages/torch/cuda/__init__.py:546: UserWarning: Can't initialize NVML\n","  warnings.warn(\"Can't initialize NVML\")\n"]},{"output_type":"stream","name":"stdout","text":["CPU times: user 7.48 s, sys: 1.09 s, total: 8.57 s\n","Wall time: 9.08 s\n"]}]},{"cell_type":"code","source":["%%time\n","df['location_detected'] = ''\n","df['org_detected'] = ''\n","for i in range(len(df)):\n","  doc = nlp(df['lemmatized_body'][i])\n","  locations = []\n","  orgs = []\n","  if(len(doc.ents)>0):\n","    for ent in doc.ents:\n","      if ent.label_ in ['GPE']:\n","        locations.append(str(ent))\n","      if ent.label_ in ['ORG']:\n","        orgs.append(str(ent))\n","    if(len(locations)>0):\n","      df['location_detected'][i] = locations\n","    if(len(orgs)>0):\n","      df['org_detected'][i] = orgs"],"metadata":{"id":"VluOB-RGC4gR","executionInfo":{"status":"ok","timestamp":1680583827761,"user_tz":-330,"elapsed":2323216,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"colab":{"base_uri":"https://localhost:8080/"},"outputId":"ebff8e88-7802-4559-bf9e-34eee19be607"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stderr","text":["<timed exec>:16: SettingWithCopyWarning: \n","A value is trying to be set on a copy of a slice from a DataFrame\n","\n","See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n","<timed exec>:14: SettingWithCopyWarning: \n","A value is trying to be set on a copy of a slice from a DataFrame\n","\n","See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"]},{"output_type":"stream","name":"stdout","text":["CPU times: user 38min 10s, sys: 6.72 s, total: 38min 17s\n","Wall time: 38min 42s\n"]}]},{"cell_type":"code","source":["df.info()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"dN2lruDWeZdG","executionInfo":{"status":"ok","timestamp":1680583827762,"user_tz":-330,"elapsed":21,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"ee28be89-8114-44f5-d4fa-403d649fea6e"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 128759 entries, 0 to 128758\n","Data columns (total 22 columns):\n"," #   Column               Non-Null Count   Dtype \n","---  ------               --------------   ----- \n"," 0   post_id              128759 non-null  int64 \n"," 1   lemmatized_body      128759 non-null  object\n"," 2   keywords_matched     128759 non-null  object\n"," 3   group_name           128759 non-null  object\n"," 4   year                 128759 non-null  int64 \n"," 5   month                128759 non-null  int64 \n"," 6   sn                   128759 non-null  int64 \n"," 7   hanging              128759 non-null  int64 \n"," 8   firearm              128759 non-null  int64 \n"," 9   benzodiazepines      128759 non-null  int64 \n"," 10  opioids              128759 non-null  int64 \n"," 11  antiemetic           128759 non-null  int64 \n"," 12  barbiturates         128759 non-null  int64 \n"," 13  acid regulator       128759 non-null  int64 \n"," 14  cyanides             128759 non-null  int64 \n"," 15  other preservatives  128759 non-null  int64 \n"," 16  plant-based poisons  128759 non-null  int64 \n"," 17  household chemicals  128759 non-null  int64 \n"," 18  ricin                128759 non-null  int64 \n"," 19  nitric oxide         128759 non-null  int64 \n"," 20  location_detected    128759 non-null  object\n"," 21  org_detected         128759 non-null  object\n","dtypes: int64(17), object(5)\n","memory usage: 21.6+ MB\n"]}]},{"cell_type":"code","source":["import numpy as np\n","df['location_detected'].replace('', np.nan, inplace=True)\n","df['org_detected'].replace('', np.nan, inplace=True)\n","df.dropna(subset=['location_detected'], inplace=True)\n","df.dropna(subset=['org_detected'], inplace=True)\n","df.info()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"IxnJ8wodmq3V","executionInfo":{"status":"ok","timestamp":1680583879550,"user_tz":-330,"elapsed":11,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"05d5a692-beab-45e5-f7d0-7972d6e75a0c"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["<class 'pandas.core.frame.DataFrame'>\n","Int64Index: 7053 entries, 16 to 128752\n","Data columns (total 22 columns):\n"," #   Column               Non-Null Count  Dtype \n","---  ------               --------------  ----- \n"," 0   post_id              7053 non-null   int64 \n"," 1   lemmatized_body      7053 non-null   object\n"," 2   keywords_matched     7053 non-null   object\n"," 3   group_name           7053 non-null   object\n"," 4   year                 7053 non-null   int64 \n"," 5   month                7053 non-null   int64 \n"," 6   sn                   7053 non-null   int64 \n"," 7   hanging              7053 non-null   int64 \n"," 8   firearm              7053 non-null   int64 \n"," 9   benzodiazepines      7053 non-null   int64 \n"," 10  opioids              7053 non-null   int64 \n"," 11  antiemetic           7053 non-null   int64 \n"," 12  barbiturates         7053 non-null   int64 \n"," 13  acid regulator       7053 non-null   int64 \n"," 14  cyanides             7053 non-null   int64 \n"," 15  other preservatives  7053 non-null   int64 \n"," 16  plant-based poisons  7053 non-null   int64 \n"," 17  household chemicals  7053 non-null   int64 \n"," 18  ricin                7053 non-null   int64 \n"," 19  nitric oxide         7053 non-null   int64 \n"," 20  location_detected    7053 non-null   object\n"," 21  org_detected         7053 non-null   object\n","dtypes: int64(17), object(5)\n","memory usage: 1.2+ MB\n"]}]},{"cell_type":"code","source":["df.head(3)"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":428},"id":"ehRtTYcjmwTn","executionInfo":{"status":"ok","timestamp":1680583891476,"user_tz":-330,"elapsed":13,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"929eb8a4-3a25-4e36-ecdd-13a07988d000"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["     post_id                                    lemmatized_body  \\\n","16   1402143  dissapointed relapsed jiminyjeesus withdrawal ...   \n","80   1364467  sadness woke minute ago0730 want sleep lil bit...   \n","106  1724621  im falling im tired ive long seem stick head h...   \n","\n","    keywords_matched     group_name  year  month  sn  hanging  firearm  \\\n","16    ['sn', 'hang']  [hanging, sn]  2021     11   1        1        0   \n","80            ['sn']           [sn]  2021     10   1        0        0   \n","106         ['fent']      [opioids]  2022      9   0        0        0   \n","\n","     benzodiazepines  ...  barbiturates  acid regulator  cyanides  \\\n","16                 0  ...             0               0         0   \n","80                 0  ...             0               0         0   \n","106                0  ...             0               0         0   \n","\n","     other preservatives  plant-based poisons  household chemicals  ricin  \\\n","16                     0                    0                    0      0   \n","80                     0                    0                    0      0   \n","106                    0                    0                    0      0   \n","\n","     nitric oxide                                  location_detected  \\\n","16              0                                            [noone]   \n","80              0                           [trinken, müssn wichsen]   \n","106             0  [obrir, torno, però, però, los pasos yo, encue...   \n","\n","                                          org_detected  \n","16                                   [fuckfuckfuck im]  \n","80   [dignitas, gelitten dieser einbahnstraße ein r...  \n","106  [ctb, mestic tornant ensorrar, altre cop estic...  \n","\n","[3 rows x 22 columns]"],"text/html":["\n","  <div id=\"df-97c881ce-1892-42a1-854c-22d9c525a749\">\n","    <div class=\"colab-df-container\">\n","      <div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>post_id</th>\n","      <th>lemmatized_body</th>\n","      <th>keywords_matched</th>\n","      <th>group_name</th>\n","      <th>year</th>\n","      <th>month</th>\n","      <th>sn</th>\n","      <th>hanging</th>\n","      <th>firearm</th>\n","      <th>benzodiazepines</th>\n","      <th>...</th>\n","      <th>barbiturates</th>\n","      <th>acid regulator</th>\n","      <th>cyanides</th>\n","      <th>other preservatives</th>\n","      <th>plant-based poisons</th>\n","      <th>household chemicals</th>\n","      <th>ricin</th>\n","      <th>nitric oxide</th>\n","      <th>location_detected</th>\n","      <th>org_detected</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>16</th>\n","      <td>1402143</td>\n","      <td>dissapointed relapsed jiminyjeesus withdrawal ...</td>\n","      <td>['sn', 'hang']</td>\n","      <td>[hanging, sn]</td>\n","      <td>2021</td>\n","      <td>11</td>\n","      <td>1</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>...</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>[noone]</td>\n","      <td>[fuckfuckfuck im]</td>\n","    </tr>\n","    <tr>\n","      <th>80</th>\n","      <td>1364467</td>\n","      <td>sadness woke minute ago0730 want sleep lil bit...</td>\n","      <td>['sn']</td>\n","      <td>[sn]</td>\n","      <td>2021</td>\n","      <td>10</td>\n","      <td>1</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>...</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>[trinken, müssn wichsen]</td>\n","      <td>[dignitas, gelitten dieser einbahnstraße ein r...</td>\n","    </tr>\n","    <tr>\n","      <th>106</th>\n","      <td>1724621</td>\n","      <td>im falling im tired ive long seem stick head h...</td>\n","      <td>['fent']</td>\n","      <td>[opioids]</td>\n","      <td>2022</td>\n","      <td>9</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>...</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>0</td>\n","      <td>[obrir, torno, però, però, los pasos yo, encue...</td>\n","      <td>[ctb, mestic tornant ensorrar, altre cop estic...</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>3 rows × 22 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-97c881ce-1892-42a1-854c-22d9c525a749')\"\n","              title=\"Convert this dataframe to an interactive table.\"\n","              style=\"display:none;\">\n","        \n","  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n","       width=\"24px\">\n","    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n","    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n","  </svg>\n","      </button>\n","      \n","  <style>\n","    .colab-df-container {\n","      display:flex;\n","      flex-wrap:wrap;\n","      gap: 12px;\n","    }\n","\n","    .colab-df-convert {\n","      background-color: #E8F0FE;\n","      border: none;\n","      border-radius: 50%;\n","      cursor: pointer;\n","      display: none;\n","      fill: #1967D2;\n","      height: 32px;\n","      padding: 0 0 0 0;\n","      width: 32px;\n","    }\n","\n","    .colab-df-convert:hover {\n","      background-color: #E2EBFA;\n","      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n","      fill: #174EA6;\n","    }\n","\n","    [theme=dark] .colab-df-convert {\n","      background-color: #3B4455;\n","      fill: #D2E3FC;\n","    }\n","\n","    [theme=dark] .colab-df-convert:hover {\n","      background-color: #434B5C;\n","      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n","      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n","      fill: #FFFFFF;\n","    }\n","  </style>\n","\n","      <script>\n","        const buttonEl =\n","          document.querySelector('#df-97c881ce-1892-42a1-854c-22d9c525a749 button.colab-df-convert');\n","        buttonEl.style.display =\n","          google.colab.kernel.accessAllowed ? 'block' : 'none';\n","\n","        async function convertToInteractive(key) {\n","          const element = document.querySelector('#df-97c881ce-1892-42a1-854c-22d9c525a749');\n","          const dataTable =\n","            await google.colab.kernel.invokeFunction('convertToInteractive',\n","                                                     [key], {});\n","          if (!dataTable) return;\n","\n","          const docLinkHtml = 'Like what you see? Visit the ' +\n","            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n","            + ' to learn more about interactive tables.';\n","          element.innerHTML = '';\n","          dataTable['output_type'] = 'display_data';\n","          await google.colab.output.renderOutput(dataTable, element);\n","          const docLink = document.createElement('div');\n","          docLink.innerHTML = docLinkHtml;\n","          element.appendChild(docLink);\n","        }\n","      </script>\n","    </div>\n","  </div>\n","  "]},"metadata":{},"execution_count":20}]},{"cell_type":"code","source":["df.rename(columns={ df.columns[13]: 'acid_regulator'}, inplace=True)\n","df.rename(columns={ df.columns[15]: 'other_preservatives'}, inplace=True)\n","df.rename(columns={ df.columns[16]: 'plantbased_poisons'}, inplace=True)\n","df.rename(columns={ df.columns[17]: 'household_chemicals'}, inplace=True)\n","df.rename(columns={ df.columns[19]: 'nitric_oxide'}, inplace=True)"],"metadata":{"id":"EkzeTuwv3YRK"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["df.to_csv(my_path+'NER.csv', sep=',', header=True, index=False)"],"metadata":{"id":"mOiNi-amX-M4"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# save post ids for each group for notebook B2-sentiment-analysis.ipynb\n","sn_posts = df.query(\"sn==1\")['post_id'].tolist()\n","hang_posts = df.query(\"hanging==1\")['post_id'].tolist()\n","firearm_posts = df.query(\"firearm==1\")['post_id'].tolist()\n","benzo_posts = df.query(\"benzodiazepines==1\")['post_id'].tolist()\n","opioid_posts = df.query(\"opioids==1\")['post_id'].tolist()\n","antiem_posts = df.query(\"antiemetic==1\")['post_id'].tolist()\n","barb_posts = df.query(\"barbiturates==1\")['post_id'].tolist()\n","acidreg_posts = df.query(\"acid_regulator==1\")['post_id'].tolist()\n","cyan_posts = df.query(\"cyanides==1\")['post_id'].tolist()\n","othpres_posts = df.query(\"other_preservatives==1\")['post_id'].tolist()\n","plantbp_posts = df.query(\"plantbased_poisons==1\")['post_id'].tolist()\n","housechem_posts = df.query(\"household_chemicals==1\")['post_id'].tolist()\n","ricin_posts = df.query(\"ricin==1\")['post_id'].tolist()\n","no_posts = df.query(\"nitric_oxide==1\")['post_id'].tolist()\n","\n","with open(my_path+'groupwise_postids.csv', 'w') as f:\n","  f.write('sn,')\n","  f.write(','.join(str(i) for i in sn_posts))\n","  f.write('\\n')\n","  f.write('hanging,')\n","  f.write(','.join(str(i) for i in hang_posts))\n","  f.write('\\n')\n","  f.write('firearm,')\n","  f.write(','.join(str(i) for i in firearm_posts))\n","  f.write('\\n')\n","  f.write('benzodiazepines,')\n","  f.write(','.join(str(i) for i in benzo_posts))\n","  f.write('\\n')\n","  f.write('opioids,')\n","  f.write(','.join(str(i) for i in opioid_posts))\n","  f.write('\\n')\n","  f.write('antiemetic,')\n","  f.write(','.join(str(i) for i in antiem_posts))\n","  f.write('\\n')\n","  f.write('barbiturates,')\n","  f.write(','.join(str(i) for i in barb_posts))\n","  f.write('\\n')\n","  f.write('acid_regulator,')\n","  f.write(','.join(str(i) for i in acidreg_posts))\n","  f.write('\\n')\n","  f.write('cyanides,')\n","  f.write(','.join(str(i) for i in cyan_posts))\n","  f.write('\\n')\n","  f.write('other_preservatives,')\n","  f.write(','.join(str(i) for i in othpres_posts))\n","  f.write('\\n')\n","  f.write('plantbased_poisons,')\n","  f.write(','.join(str(i) for i in plantbp_posts))\n","  f.write('\\n')\n","  f.write('household_chemicals,')\n","  f.write(','.join(str(i) for i in housechem_posts))\n","  f.write('\\n')\n","  f.write('ricin,')\n","  f.write(','.join(str(i) for i in ricin_posts))\n","  f.write('\\n')\n","  f.write('nitric_oxide,')\n","  f.write(','.join(str(i) for i in no_posts))\n","  f.write('\\n')"],"metadata":{"id":"miZ72LZotZU_"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["!wc -l /content/drive/MyDrive/suicide-project/data/groupwise_postids.csv\n","!tail -2 /content/drive/MyDrive/suicide-project/data/groupwise_postids.csv"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"gYUxnbuQ7sdz","executionInfo":{"status":"ok","timestamp":1680589449183,"user_tz":-330,"elapsed":20,"user":{"displayName":"Sudeshna Das","userId":"08332010396883382094"}},"outputId":"11df9451-5157-4d1b-b2d0-f3983ee6c3b8"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["14 /content/drive/MyDrive/suicide-project/data/groupwise_postids.csv\n","ricin,589340,589352,565079,720261,1359067,1032527,104388,632889,555576,1370750,174551,390232,390781\n","nitric_oxide,947020,879541,952942,1236297,1398216,1021192,567210,142239,469532,690285,1276664,1723661,1716035,457902,482634,567203,1610259,567213,856188,647477,1570632,1687249\n"]}]},{"cell_type":"code","source":["# save locations and organizations for manual verification\n","\n","locations = df['location_detected'].tolist()\n","orgs = df['org_detected'].tolist()\n","\n","import itertools\n","locations_list = list(itertools.chain(*locations))\n","orgs_list = list(itertools.chain(*orgs))\n","\n","print(len(locations_list))\n","print(len(orgs_list))\n","\n","from collections import Counter\n","locations_dict = Counter(locations_list)\n","orgs_dict = Counter(orgs_list)\n","\n","with open(my_path+'organizations-lg.csv', 'w') as f:\n","  for org in orgs_dict:\n","    f.write(org)\n","    f.write(',')\n","    f.write(str(orgs_dict.get(org)))\n","    f.write('\\n')\n","\n","with open(my_path+'locations-lg.csv', 'w') as f:\n","  for loc in locations_dict:\n","    f.write(loc)\n","    f.write(',')\n","    f.write(str(locations_dict.get(loc)))\n","    f.write('\\n')"],"metadata":{"id":"z8Y73iIJsh5r"},"execution_count":null,"outputs":[]}]}