00_GBQ_setup.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"provenance":[],"mount_file_id":"1jm_6P25Hs3h22Mh0scEpKPjczquuSMgu","authorship_tag":"ABX9TyN6hN+djz3J54PrkDC5BrtG"},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"markdown","source":["## 1. Set up project in GCP\n","Using console.cloud.google.com, I set up a project called **grav-waves**. This project was automatically assigned a suffix (358320) by Google (most probably because this project exists somewhere else already on GCP).\n","\n","## 2. Generate owner JSON key\n","Next, I created a service account and generate a JSON key with owner access to that service account (see [these instructions](https://cloud.google.com/docs/authentication/getting-started#auth-cloud-implicit-python)).\n","\n","## 3. Upload JSON key to Colab\n","I uploaded data and the JSON key from step 2 to the `grav-waves` folder containing this and the other scripts.\n","\n","Next, I ran the code below to stand-up the database."],"metadata":{"id":"2cz0fzNAj18s"}},{"cell_type":"code","execution_count":null,"metadata":{"id":"TomB59ufjxf8"},"outputs":[],"source":["from google.cloud import bigquery\n","from google.oauth2 import service_account\n","import pandas as pd\n","import numpy as np\n","import datetime\n","\n","!pip install gwosc\n","!pip install gwpy\n","from gwosc import datasets\n","from gwpy.timeseries import TimeSeries\n","from gwpy.time import tconvert\n","import h5py\n","\n","# !pip install rpy2==3.5.1 # problems with newer versions, still not fixed: https://stackoverflow.com/questions/74283327/conversion-py2rpy-not-defined-for-objects-of-type-class-str\n","# %load_ext rpy2.ipython"]},{"cell_type":"code","source":["# Mount drive - needed for GBQ creds\n","from google.colab import drive\n","drive.mount('/content/drive')"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"uJ5YCW-4PgQV","executionInfo":{"status":"ok","timestamp":1682791530021,"user_tz":300,"elapsed":738,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"9a3c095c-196b-4a7a-9116-c6041c40ead2"},"execution_count":3,"outputs":[{"output_type":"stream","name":"stdout","text":["Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"]}]},{"cell_type":"code","source":["root_path = '/content/drive/MyDrive/GitHub'"],"metadata":{"id":"4xdrxxDkj6S3","executionInfo":{"status":"ok","timestamp":1682791533066,"user_tz":300,"elapsed":103,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":4,"outputs":[]},{"cell_type":"code","source":["credentials = service_account.Credentials.from_service_account_file(\n","    f'{root_path}/grav-waves/grav-waves-358320-34ebfeae2689.json', scopes=[\"https://www.googleapis.com/auth/cloud-platform\"],\n",")\n","\n","client = bigquery.Client(credentials=credentials, project=credentials.project_id,)"],"metadata":{"id":"4c7yvb3Lj6xo","executionInfo":{"status":"ok","timestamp":1682791534778,"user_tz":300,"elapsed":108,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":5,"outputs":[]},{"cell_type":"markdown","source":["## 4. Import GWOSC data & clean\n","Load in data and create a timestamp variable so events can be accessed based on human-readable dates."],"metadata":{"id":"ZjLwpPDBj_P4"}},{"cell_type":"markdown","source":["#### A. Feature data\n","\n","First, I import the feature data. This is almost like \"metadata\" for the event that's derived from the timeseries data. It includes the masses of the merging objects, the final mass of the merged object, date-time of the merging, GPS time of the merging (special unit of time used mainly in the gravitational wave sphere) and more."],"metadata":{"id":"h9XxY1llK3yv"}},{"cell_type":"code","source":["gwosc_events = pd.read_csv('https://www.gw-openscience.org/eventapi/csv/allevents/')"],"metadata":{"id":"CHPIeQi5j8Zo"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Split out date and time from ID column, convert date to date\n","gwosc_events[['merger_date', 'merger_time']] = gwosc_events.commonName.str.split('_', expand = True)\n","gwosc_events['merger_date'] = gwosc_events['merger_date'].str.replace('GW|GRB', '', regex = True)\n","\n","gwosc_events['merger_date'] = gwosc_events['merger_date'].str.replace('blind', '991231')\n","gwosc_events['merger_date'] = gwosc_events['merger_date'].str.replace('151012.2', '151012')\n","gwosc_events['merger_date'] = pd.to_datetime(gwosc_events['merger_date'], format='%y%m%d')\n"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"uFcLKspjkXO2","executionInfo":{"status":"ok","timestamp":1681944470818,"user_tz":300,"elapsed":145,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"e0c6670b-abe5-418b-ac73-e3c946c3d62e"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stderr","text":["<ipython-input-6-db8c01b4ace8>:6: FutureWarning: The default value of regex will change from True to False in a future version.\n","  gwosc_events['merger_date'] = gwosc_events['merger_date'].str.replace('151012.2', '151012')\n"]}]},{"cell_type":"code","source":["gwosc_events"],"metadata":{"id":"-a04Ze48muwq","colab":{"base_uri":"https://localhost:8080/","height":644},"executionInfo":{"status":"ok","timestamp":1681944473392,"user_tz":300,"elapsed":131,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"1a5b145e-e5c8-4f09-a483-8fbabebf89d7"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["                     id       commonName  version   catalog.shortName  \\\n","0             151008-v1           151008        1     GWTC-1-marginal   \n","1           151012.2-v1         151012.2        1     GWTC-1-marginal   \n","2             151116-v1           151116        1     GWTC-1-marginal   \n","3             161202-v1           161202        1     GWTC-1-marginal   \n","4             161217-v1           161217        1     GWTC-1-marginal   \n","..                  ...              ...      ...                 ...   \n","176  GW200308_173609-v1  GW200308_173609        1    GWTC-3-confident   \n","177  GW200311_115853-v1  GW200311_115853        1    GWTC-3-confident   \n","178  GW200316_215756-v1  GW200316_215756        1    GWTC-3-confident   \n","179  GW200322_091133-v1  GW200322_091133        1    GWTC-3-confident   \n","180  blind_injection-v1  blind_injection        1  Initial_LIGO_Virgo   \n","\n","              GPS                                          reference  \\\n","0    1.128349e+09                  https://doi.org/10.7935/82H3-HH23   \n","1    1.128667e+09                  https://doi.org/10.7935/82H3-HH23   \n","2    1.131749e+09                  https://doi.org/10.7935/82H3-HH23   \n","3    1.164686e+09                  https://doi.org/10.7935/82H3-HH23   \n","4    1.165994e+09                  https://doi.org/10.7935/82H3-HH23   \n","..            ...                                                ...   \n","176  1.267724e+09                                           /GWTC-3/   \n","177  1.267963e+09                                           /GWTC-3/   \n","178  1.268431e+09                                           /GWTC-3/   \n","179  1.268904e+09                                           /GWTC-3/   \n","180  9.686546e+08  https://www.ligo.org/scientists/GW100916/index...   \n","\n","                                               jsonurl  mass_1_source  \\\n","0    https://gwosc.org/eventapi/json/GWTC-1-margina...            NaN   \n","1    https://gwosc.org/eventapi/json/GWTC-1-margina...            NaN   \n","2    https://gwosc.org/eventapi/json/GWTC-1-margina...            NaN   \n","3    https://gwosc.org/eventapi/json/GWTC-1-margina...            NaN   \n","4    https://gwosc.org/eventapi/json/GWTC-1-margina...            NaN   \n","..                                                 ...            ...   \n","176  https://gwosc.org/eventapi/json/GWTC-3-confide...           36.4   \n","177  https://gwosc.org/eventapi/json/GWTC-3-confide...           34.2   \n","178  https://gwosc.org/eventapi/json/GWTC-3-confide...           13.1   \n","179  https://gwosc.org/eventapi/json/GWTC-3-confide...           34.0   \n","180  https://gwosc.org/eventapi/json/Initial_LIGO_V...            NaN   \n","\n","     mass_1_source_lower  mass_1_source_upper  ...  far_lower  far_upper  \\\n","0                    NaN                  NaN  ...        NaN        NaN   \n","1                    NaN                  NaN  ...        NaN        NaN   \n","2                    NaN                  NaN  ...        NaN        NaN   \n","3                    NaN                  NaN  ...        NaN        NaN   \n","4                    NaN                  NaN  ...        NaN        NaN   \n","..                   ...                  ...  ...        ...        ...   \n","176                 -9.6                 11.2  ...        NaN        NaN   \n","177                 -3.8                  6.4  ...        NaN        NaN   \n","178                 -2.9                 10.2  ...        NaN        NaN   \n","179                -18.0                 48.0  ...        NaN        NaN   \n","180                  NaN                  NaN  ...        NaN        NaN   \n","\n","     p_astro  p_astro_lower  p_astro_upper  final_mass_source  \\\n","0        NaN            NaN            NaN                NaN   \n","1        NaN            NaN            NaN                NaN   \n","2        NaN            NaN            NaN                NaN   \n","3        NaN            NaN            NaN                NaN   \n","4        NaN            NaN            NaN                NaN   \n","..       ...            ...            ...                ...   \n","176  0.85660            NaN            NaN               47.4   \n","177  0.99000            NaN            NaN               59.0   \n","178  0.99000            NaN            NaN               20.2   \n","179  0.61501            NaN            NaN               53.0   \n","180      NaN            NaN            NaN                NaN   \n","\n","     final_mass_source_lower  final_mass_source_upper  merger_date  \\\n","0                        NaN                      NaN   2015-10-08   \n","1                        NaN                      NaN   2015-10-12   \n","2                        NaN                      NaN   2015-11-16   \n","3                        NaN                      NaN   2016-12-02   \n","4                        NaN                      NaN   2016-12-17   \n","..                       ...                      ...          ...   \n","176                     -7.7                     11.1   2020-03-08   \n","177                     -3.9                      4.8   2020-03-11   \n","178                     -1.9                      7.4   2020-03-16   \n","179                    -26.0                     38.0   2020-03-22   \n","180                      NaN                      NaN   1999-12-31   \n","\n","     merger_time  \n","0           None  \n","1           None  \n","2           None  \n","3           None  \n","4           None  \n","..           ...  \n","176       173609  \n","177       115853  \n","178       215756  \n","179       091133  \n","180    injection  \n","\n","[181 rows x 45 columns]"],"text/html":["\n","  <div id=\"df-b17849f5-d6de-4a89-9740-2b7c77d0505e\">\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>id</th>\n","      <th>commonName</th>\n","      <th>version</th>\n","      <th>catalog.shortName</th>\n","      <th>GPS</th>\n","      <th>reference</th>\n","      <th>jsonurl</th>\n","      <th>mass_1_source</th>\n","      <th>mass_1_source_lower</th>\n","      <th>mass_1_source_upper</th>\n","      <th>...</th>\n","      <th>far_lower</th>\n","      <th>far_upper</th>\n","      <th>p_astro</th>\n","      <th>p_astro_lower</th>\n","      <th>p_astro_upper</th>\n","      <th>final_mass_source</th>\n","      <th>final_mass_source_lower</th>\n","      <th>final_mass_source_upper</th>\n","      <th>merger_date</th>\n","      <th>merger_time</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>151008-v1</td>\n","      <td>151008</td>\n","      <td>1</td>\n","      <td>GWTC-1-marginal</td>\n","      <td>1.128349e+09</td>\n","      <td>https://doi.org/10.7935/82H3-HH23</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-1-margina...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>2015-10-08</td>\n","      <td>None</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>151012.2-v1</td>\n","      <td>151012.2</td>\n","      <td>1</td>\n","      <td>GWTC-1-marginal</td>\n","      <td>1.128667e+09</td>\n","      <td>https://doi.org/10.7935/82H3-HH23</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-1-margina...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>2015-10-12</td>\n","      <td>None</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>151116-v1</td>\n","      <td>151116</td>\n","      <td>1</td>\n","      <td>GWTC-1-marginal</td>\n","      <td>1.131749e+09</td>\n","      <td>https://doi.org/10.7935/82H3-HH23</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-1-margina...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>2015-11-16</td>\n","      <td>None</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>161202-v1</td>\n","      <td>161202</td>\n","      <td>1</td>\n","      <td>GWTC-1-marginal</td>\n","      <td>1.164686e+09</td>\n","      <td>https://doi.org/10.7935/82H3-HH23</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-1-margina...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>2016-12-02</td>\n","      <td>None</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>161217-v1</td>\n","      <td>161217</td>\n","      <td>1</td>\n","      <td>GWTC-1-marginal</td>\n","      <td>1.165994e+09</td>\n","      <td>https://doi.org/10.7935/82H3-HH23</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-1-margina...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>2016-12-17</td>\n","      <td>None</td>\n","    </tr>\n","    <tr>\n","      <th>...</th>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","      <td>...</td>\n","    </tr>\n","    <tr>\n","      <th>176</th>\n","      <td>GW200308_173609-v1</td>\n","      <td>GW200308_173609</td>\n","      <td>1</td>\n","      <td>GWTC-3-confident</td>\n","      <td>1.267724e+09</td>\n","      <td>/GWTC-3/</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-3-confide...</td>\n","      <td>36.4</td>\n","      <td>-9.6</td>\n","      <td>11.2</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>0.85660</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>47.4</td>\n","      <td>-7.7</td>\n","      <td>11.1</td>\n","      <td>2020-03-08</td>\n","      <td>173609</td>\n","    </tr>\n","    <tr>\n","      <th>177</th>\n","      <td>GW200311_115853-v1</td>\n","      <td>GW200311_115853</td>\n","      <td>1</td>\n","      <td>GWTC-3-confident</td>\n","      <td>1.267963e+09</td>\n","      <td>/GWTC-3/</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-3-confide...</td>\n","      <td>34.2</td>\n","      <td>-3.8</td>\n","      <td>6.4</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>0.99000</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>59.0</td>\n","      <td>-3.9</td>\n","      <td>4.8</td>\n","      <td>2020-03-11</td>\n","      <td>115853</td>\n","    </tr>\n","    <tr>\n","      <th>178</th>\n","      <td>GW200316_215756-v1</td>\n","      <td>GW200316_215756</td>\n","      <td>1</td>\n","      <td>GWTC-3-confident</td>\n","      <td>1.268431e+09</td>\n","      <td>/GWTC-3/</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-3-confide...</td>\n","      <td>13.1</td>\n","      <td>-2.9</td>\n","      <td>10.2</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>0.99000</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>20.2</td>\n","      <td>-1.9</td>\n","      <td>7.4</td>\n","      <td>2020-03-16</td>\n","      <td>215756</td>\n","    </tr>\n","    <tr>\n","      <th>179</th>\n","      <td>GW200322_091133-v1</td>\n","      <td>GW200322_091133</td>\n","      <td>1</td>\n","      <td>GWTC-3-confident</td>\n","      <td>1.268904e+09</td>\n","      <td>/GWTC-3/</td>\n","      <td>https://gwosc.org/eventapi/json/GWTC-3-confide...</td>\n","      <td>34.0</td>\n","      <td>-18.0</td>\n","      <td>48.0</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>0.61501</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>53.0</td>\n","      <td>-26.0</td>\n","      <td>38.0</td>\n","      <td>2020-03-22</td>\n","      <td>091133</td>\n","    </tr>\n","    <tr>\n","      <th>180</th>\n","      <td>blind_injection-v1</td>\n","      <td>blind_injection</td>\n","      <td>1</td>\n","      <td>Initial_LIGO_Virgo</td>\n","      <td>9.686546e+08</td>\n","      <td>https://www.ligo.org/scientists/GW100916/index...</td>\n","      <td>https://gwosc.org/eventapi/json/Initial_LIGO_V...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>...</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>1999-12-31</td>\n","      <td>injection</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>181 rows × 45 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b17849f5-d6de-4a89-9740-2b7c77d0505e')\"\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-b17849f5-d6de-4a89-9740-2b7c77d0505e 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-b17849f5-d6de-4a89-9740-2b7c77d0505e');\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":7}]},{"cell_type":"code","source":["# Overwrite some random values in time column\n","gwosc_events['merger_time'] = gwosc_events['merger_time'].str.replace('injection', '000000')\n","gwosc_events['merger_time'] = gwosc_events['merger_time'].fillna('000000') # Fill in NAs with 0s\n","\n","merger_tm = gwosc_events['merger_time'].str\n","\n","# Create list of time objects\n","gw_time = list(map(datetime.time, merger_tm[:2].astype(int), merger_tm[2:4].astype(int), merger_tm[4:].astype(int)))\n","\n","# Combine date and time to create timestamp\n","gwosc_events['merger_datetime'] = list(map(datetime.datetime.combine, gwosc_events['merger_date'], gw_time))"],"metadata":{"id":"0YIa4QsAmc42"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Keep only date-time column\n","gwosc_events = gwosc_events.drop(['merger_date', 'merger_time'], axis = 1)\n","\n","# Rename column with invalid syntax for GCP\n","gwosc_events = gwosc_events.rename(columns={'catalog.shortName': 'catalog_shortName'})"],"metadata":{"id":"oyxA8xaMkzpx"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["gwosc_events.dtypes"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"rgLo2qx7nwtu","executionInfo":{"status":"ok","timestamp":1659646373372,"user_tz":300,"elapsed":354,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"a4ba4d57-0c25-42c2-804b-a5aaeaf092b3"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["id                                          object\n","commonName                                  object\n","version                                      int64\n","catalog_shortName                           object\n","GPS                                        float64\n","reference                                   object\n","jsonurl                                     object\n","mass_1_source                              float64\n","mass_1_source_lower                        float64\n","mass_1_source_upper                        float64\n","mass_2_source                              float64\n","mass_2_source_lower                        float64\n","mass_2_source_upper                        float64\n","network_matched_filter_snr                 float64\n","network_matched_filter_snr_lower           float64\n","network_matched_filter_snr_upper           float64\n","luminosity_distance                        float64\n","luminosity_distance_lower                  float64\n","luminosity_distance_upper                  float64\n","chi_eff                                    float64\n","chi_eff_lower                              float64\n","chi_eff_upper                              float64\n","total_mass_source                          float64\n","total_mass_source_lower                    float64\n","total_mass_source_upper                    float64\n","chirp_mass_source                          float64\n","chirp_mass_source_lower                    float64\n","chirp_mass_source_upper                    float64\n","chirp_mass                                 float64\n","chirp_mass_lower                           float64\n","chirp_mass_upper                           float64\n","redshift                                   float64\n","redshift_lower                             float64\n","redshift_upper                             float64\n","far                                        float64\n","far_lower                                  float64\n","far_upper                                  float64\n","p_astro                                    float64\n","p_astro_lower                              float64\n","p_astro_upper                              float64\n","final_mass_source                          float64\n","final_mass_source_lower                    float64\n","final_mass_source_upper                    float64\n","merger_datetime                     datetime64[ns]\n","dtype: object"]},"metadata":{},"execution_count":39}]},{"cell_type":"markdown","source":["#### B. Timeseries data\n","\n","**Bi. Raw timeseries data**\n","\n","Next, I import the timeseries data using the `gwosc` package. Note that, before running this chunk, I had already run the first code chunk in section 5 to create the `mergers` dataset in which to store this data (in the `mergers.timeseries` table)."],"metadata":{"id":"9DVhhTn4LODN"}},{"cell_type":"code","source":["def create_event_dict(event_nm, obs):\n","\n","  try:\n","\n","    # GPS of event\n","    event_gps = datasets.event_gps(event_nm)\n","\n","    # Fetch 30s of data for event from given observatory\n","    gw = np.array(TimeSeries.fetch_open_data(obs, event_gps - 15, event_gps + 15))\n","\n","    # Convert GPS to human-readable dates\n","    event_dts = list(map(tconvert, [event_gps - 15, event_gps + 15]))\n","\n","    # Create range of date-times between start and end\n","    capture_dttms = pd.date_range(event_dts[0], event_dts[1], periods = len(gw))\n","\n","    event_dict = {'id': [event_nm]*len(gw),\n","                  'observatory': [obs]*len(gw),\n","                  'size': gw,\n","                  'capture_dttm': capture_dttms.round(freq = 'us')} # round to microsec for GCP compatibility\n","\n","    return pd.DataFrame(event_dict)\n","\n","  # Some events are blanks, so skip these\n","  except ValueError:\n","    pass"],"metadata":{"id":"aA2QZ9cZLr93"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Get a list of all the GW events\n","gw_events = datasets.find_datasets()\n","invalid_events = gw_events[180:] + ['BKGW170608_16KHZ_R1'] # These aren't real events\n","gw_events_valid = [i for i in gw_events if i not in invalid_events]"],"metadata":{"id":"txIheRQoHqp2"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Loop through events in batches of 10 and create event dictionaries; load into GCP\n","# Don't use too big of a batch or you'll get a \"read-timeout\" error\n","step_sz = 10\n","\n","for i in np.arange(len(gw_events_valid), step = step_sz):\n","\n","  events_batch = gw_events_valid[i:(i+step_sz)]\n","  obs_list = ['L1']*len(events_batch) + ['H1']*len(events_batch)\n","\n","  events_df = list(map(create_event_dict, events_batch*2, obs_list))\n","  events_full = pd.concat(events_df)\n","\n","  # BigQuery appends rows to table by default if it exists, so don't need to set write disposition\n","  # https://cloud.google.com/bigquery/docs/samples/bigquery-load-table-dataframe\n","  job_config = bigquery.LoadJobConfig(schema = [bigquery.SchemaField('id', 'STRING'),\n","                                                bigquery.SchemaField('observatory', 'STRING')],\n","                                    autodetect = False,\n","                                    source_format = bigquery.SourceFormat.CSV)\n","  \n","  job = client.load_table_from_dataframe(events_full, 'grav-waves-358320.mergers.timeseries', job_config = job_config)"],"metadata":{"id":"MsTPkpCsK8wR"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["**Bii. Cleaned timeseries data**\n","\n","The data below come from the GWOSC website, from the \"Strain data after noise subtraction\" section of the [170817 page](https://gwosc.org/events/GW170817/)."],"metadata":{"id":"03auvaE_NlHr"}},{"cell_type":"code","source":["# From \"after noise subtraction\" section: \n","hf = h5py.File('/content/drive/MyDrive/GitHub/grav-waves/data/H-H1_LOSC_CLN_4_V1-1187007040-2048.hdf5', 'r')\n","# list(hf.keys())\n","strain_170817 = np.array(hf['strain']['Strain'])\n","strain_170817_df = pd.DataFrame({'strain': strain_170817})\n","strain_170817_df.to_csv('/content/drive/MyDrive/GitHub/grav-waves/data/170817_H14096Hz.csv')"],"metadata":{"id":"0umC3XCDHhEN"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["**Biii. Generated timeseries data**\n","\n","The below data was provided from Rafia and contains 4,000 timeseries of BNS data. This first load creates a relational format for the data. However, after some experimentation, I've found it will be much easier to store the data in its original wide format (the exact format we'll use for training the autoencoder)."],"metadata":{"id":"dMI9UsDRj2lJ"}},{"cell_type":"code","source":["# The shape of the file is 4000 x 32769. The first 32768 numbers are time series data, and the last one is the label.\n","bns = np.load('/content/drive/MyDrive/GitHub/grav-waves/data/strain.npy')"],"metadata":{"id":"_cc5Yll3j1zP","executionInfo":{"status":"ok","timestamp":1682789117367,"user_tz":300,"elapsed":8670,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":5,"outputs":[]},{"cell_type":"code","source":["bns_ls = []\n","\n","for i in range(4000):\n","\n","  strain_dict = {}\n","  row_arr = bns[i][0]\n","\n","  strain_dict['event_id'] = [i]*32768\n","  strain_dict['strain'] = row_arr[:32768]\n","  strain_dict['time_seq'] = list(np.arange(1, 32769))\n","  strain_dict['label'] = [row_arr[32768]]*32768\n","\n","  bns_ls.append(strain_dict)"],"metadata":{"id":"hEnyNAmJkVmc"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["for i in range(4000):\n","\n","  bns_single_df = pd.DataFrame(bns_ls[i])\n","\n","  # BigQuery appends rows to table by default if it exists, so don't need to set write disposition\n","  # https://cloud.google.com/bigquery/docs/samples/bigquery-load-table-dataframe\n","  job_config = bigquery.LoadJobConfig(autodetect = True, source_format = bigquery.SourceFormat.CSV)\n","  \n","  job = client.load_table_from_dataframe(bns_single_df, 'grav-waves-358320.mergers.bns_generated', job_config = job_config)"],"metadata":{"id":"Cq-fcr40kWXw"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["In the code below, I pull in both the generated BNS strain data as well as the noise and store it in its original wide format in GBQ, since the wide format is what will be used to train the autoencoder."],"metadata":{"id":"PEGZ8eBmDC1d"}},{"cell_type":"code","source":["bns = np.load('/content/drive/MyDrive/GitHub/grav-waves/data/strain.npy')"],"metadata":{"id":"koR2S4L9DKCF"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Remove the outer nesting on each row, as well as the label stored at the end (1.0 for every observation)\n","bns_unnested = np.array([bns[x][0][:32768] for x in range(4000)])\n","\n","# Clean up for GBQ\n","bns_df = pd.DataFrame(bns_unnested)\n","bns_df = bns_df.add_prefix('x_')\n","\n","bns_df.reset_index(inplace = True)\n","bns_df = bns_df.rename(columns = {'index':'event_id'})\n","\n","# Push wide table to GBQ - results in a 400 error; limit of wide tables are 10K cols\n","job_config = bigquery.LoadJobConfig(autodetect = True, source_format = bigquery.SourceFormat.CSV)\n","\n","job = client.load_table_from_dataframe(bns_df, 'grav-waves-358320.mergers.bns_generated_wide', job_config = job_config)"],"metadata":{"id":"fuxW2WFbDOGW","executionInfo":{"status":"ok","timestamp":1682789362979,"user_tz":300,"elapsed":359,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":18,"outputs":[]},{"cell_type":"code","source":["background = np.load('/content/drive/MyDrive/GitHub/grav-waves/data/background.npy')"],"metadata":{"id":"J5om3cvGE8Hr","executionInfo":{"status":"ok","timestamp":1682791551087,"user_tz":300,"elapsed":6404,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":6,"outputs":[]},{"cell_type":"code","source":["# Session crashes when I try to use list comprehension :(\n","# background_ls = [{'event_id': [x]*32768, 'strain': background[x][0][:32768], 'time_seq': list(np.arange(1, 32769)), 'label': [0]*32768} for x in range(4000)]"],"metadata":{"id":"gLEv8gtILCej"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["bgd_ls = []\n","\n","for i in range(4000):\n","\n","  strain_dict = {}\n","  row_arr = background[i][0]\n","\n","  strain_dict['event_id'] = [i]*32768\n","  strain_dict['strain'] = row_arr[:32768]\n","  strain_dict['time_seq'] = list(np.arange(1, 32769))\n","  strain_dict['label'] = [row_arr[32768]]*32768\n","\n","  bgd_ls.append(strain_dict)"],"metadata":{"id":"Y0NAZyJBMEGY","executionInfo":{"status":"ok","timestamp":1682791577472,"user_tz":300,"elapsed":12294,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":7,"outputs":[]},{"cell_type":"code","source":["for i in range(4000):\n","\n","  bgd_single_df = pd.DataFrame(bgd_ls[i])\n","\n","  # BigQuery appends rows to table by default if it exists, so don't need to set write disposition\n","  # https://cloud.google.com/bigquery/docs/samples/bigquery-load-table-dataframe\n","  job_config = bigquery.LoadJobConfig(autodetect = True, source_format = bigquery.SourceFormat.CSV)\n","  \n","  job = client.load_table_from_dataframe(bgd_single_df, 'grav-waves-358320.mergers.background', job_config = job_config)"],"metadata":{"id":"dg-4XBD-MZoZ","executionInfo":{"status":"ok","timestamp":1682796472139,"user_tz":300,"elapsed":4807089,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}}},"execution_count":10,"outputs":[]},{"cell_type":"markdown","source":["**Biv. BBH waveforms extracted using template-free method in Akhshi et al. 2021**\n","\n","The below data was obtained from Dr. Akshi, who's paper detailing the method for obtaining this data is [here](https://www.nature.com/articles/s41598-021-98821-z)."],"metadata":{"id":"3T8seFt8R_GH"}},{"cell_type":"code","source":["bbh_waves = pd.read_csv('/content/drive/MyDrive/GitHub/grav-waves/data/bbh_waveforms_tf.csv')"],"metadata":{"id":"KxJMUiZER-WR"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["bbh_waves.head()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"id":"IzaJ_WXhStGU","executionInfo":{"status":"ok","timestamp":1682474660095,"user_tz":300,"elapsed":167,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"baa2ac03-c730-4c1c-bfc4-2726047dcc33"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["      time     event interferometer  raw_value\n","0 -0.15942  GW150914             L1   0.024682\n","1 -0.15918  GW150914             L1   2.285600\n","2 -0.15894  GW150914             L1  -0.110150\n","3 -0.15869  GW150914             L1  -2.042000\n","4 -0.15845  GW150914             L1  -1.070900"],"text/html":["\n","  <div id=\"df-e90e4779-9913-4d4b-95e6-55e9b2d56928\">\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>time</th>\n","      <th>event</th>\n","      <th>interferometer</th>\n","      <th>raw_value</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>-0.15942</td>\n","      <td>GW150914</td>\n","      <td>L1</td>\n","      <td>0.024682</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>-0.15918</td>\n","      <td>GW150914</td>\n","      <td>L1</td>\n","      <td>2.285600</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>-0.15894</td>\n","      <td>GW150914</td>\n","      <td>L1</td>\n","      <td>-0.110150</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>-0.15869</td>\n","      <td>GW150914</td>\n","      <td>L1</td>\n","      <td>-2.042000</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>-0.15845</td>\n","      <td>GW150914</td>\n","      <td>L1</td>\n","      <td>-1.070900</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e90e4779-9913-4d4b-95e6-55e9b2d56928')\"\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-e90e4779-9913-4d4b-95e6-55e9b2d56928 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-e90e4779-9913-4d4b-95e6-55e9b2d56928');\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":12}]},{"cell_type":"code","source":["# BBH extracted waveforms table\n","job_config = bigquery.LoadJobConfig(\n","    autodetect = True,\n","    source_format = bigquery.SourceFormat.CSV)\n","\n","job = client.load_table_from_dataframe(bbh_waves, 'grav-waves-358320.mergers.bbh_waveforms', job_config = job_config)"],"metadata":{"id":"UyxGmgpxTf5_"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["#### C. Event metadata\n","\n","Load in metadata on the type of objects that merged (as papers/other analyses have derived). Note once again that, before running this chunk, I had already run the first code chunk in section 5 to create the `mergers` dataset in which to store this data (in the `mergers.objects` table)."],"metadata":{"id":"oxboF9PtCeJH"}},{"cell_type":"code","source":["merger_metadata = pd.read_csv('/content/drive/MyDrive/GitHub/grav-waves/data/mergers_mass.csv')"],"metadata":{"id":"lNH6OnViC52j"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["merger_metadata.head()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":206},"id":"5ro493DJDLTO","executionInfo":{"status":"ok","timestamp":1666917910599,"user_tz":300,"elapsed":157,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"9edd73c5-4ebc-4023-af5f-7c14f4df3faf"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["         id observing_run  primary_mass  secondary_mass  final_mass  \\\n","0  GW150914            01          36.0            31.0        63.0   \n","1  GW151012            01          23.0            14.0        36.0   \n","2  GW151226            01          14.0             7.7        21.0   \n","3  GW170104            02          31.0            20.0        49.0   \n","4  GW170608            02          11.0             7.6        18.0   \n","\n","  primary_object secondary_object final_object      merger_datetime  \n","0             BH               BH           BH  2015-09-14 00:00:00  \n","1             BH               BH           BH  2015-10-12 00:00:00  \n","2             BH               BH           BH  2015-12-26 00:00:00  \n","3             BH               BH           BH  2017-01-04 00:00:00  \n","4             BH               BH           BH  2017-06-08 00:00:00  "],"text/html":["\n","  <div id=\"df-c58f173d-300a-40e7-88f2-39700a329b16\">\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>id</th>\n","      <th>observing_run</th>\n","      <th>primary_mass</th>\n","      <th>secondary_mass</th>\n","      <th>final_mass</th>\n","      <th>primary_object</th>\n","      <th>secondary_object</th>\n","      <th>final_object</th>\n","      <th>merger_datetime</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>GW150914</td>\n","      <td>01</td>\n","      <td>36.0</td>\n","      <td>31.0</td>\n","      <td>63.0</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>2015-09-14 00:00:00</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>GW151012</td>\n","      <td>01</td>\n","      <td>23.0</td>\n","      <td>14.0</td>\n","      <td>36.0</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>2015-10-12 00:00:00</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>GW151226</td>\n","      <td>01</td>\n","      <td>14.0</td>\n","      <td>7.7</td>\n","      <td>21.0</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>2015-12-26 00:00:00</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>GW170104</td>\n","      <td>02</td>\n","      <td>31.0</td>\n","      <td>20.0</td>\n","      <td>49.0</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>2017-01-04 00:00:00</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>GW170608</td>\n","      <td>02</td>\n","      <td>11.0</td>\n","      <td>7.6</td>\n","      <td>18.0</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>BH</td>\n","      <td>2017-06-08 00:00:00</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c58f173d-300a-40e7-88f2-39700a329b16')\"\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-c58f173d-300a-40e7-88f2-39700a329b16 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-c58f173d-300a-40e7-88f2-39700a329b16');\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":6}]},{"cell_type":"code","source":["merger_metadata.dtypes"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"UHg3lRciFmSB","executionInfo":{"status":"ok","timestamp":1666918571393,"user_tz":300,"elapsed":5,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"2933aa1f-b2cd-4106-fa80-842c9ebae206"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["id                   object\n","observing_run        object\n","primary_mass        float64\n","secondary_mass      float64\n","final_mass          float64\n","primary_object       object\n","secondary_object     object\n","final_object         object\n","merger_datetime      object\n","dtype: object"]},"metadata":{},"execution_count":21}]},{"cell_type":"code","source":["len(merger_metadata)"],"metadata":{"id":"lqM8Wi5yG6Hv","executionInfo":{"status":"ok","timestamp":1666918889001,"user_tz":300,"elapsed":124,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"53634e1b-30a6-4d31-d5a8-4dbe183e39f1","colab":{"base_uri":"https://localhost:8080/"}},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["91"]},"metadata":{},"execution_count":24}]},{"cell_type":"markdown","source":["## 5. Push dataframes to GCP\n","Create a dataset called **mergers** within the project (`grav-waves-358320`). Upload the newly cleaned dataframe to the `grav-waves-358320.mergers.events` table. Both the dataset and table are persistent, so this code only needs to be run once. Note that the timeseries data is already loaded using the above code.\n","\n","For help specifying the schema, see [these instructions](https://cloud.google.com/bigquery/docs/schemas)."],"metadata":{"id":"LTSjqpSYnQXA"}},{"cell_type":"code","source":["# Create merger dataset\n","mergers = bigquery.Dataset('grav-waves-358320.mergers')\n","mergers.location = 'US'\n","mergers = client.create_dataset(mergers)"],"metadata":{"id":"nIoi-pg6nEDq"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Features table\n","job_config = bigquery.LoadJobConfig(\n","    schema = [bigquery.SchemaField('id', 'STRING'),\n","              bigquery.SchemaField('commonName', 'STRING'),\n","              bigquery.SchemaField('catalog_shortName', 'STRING'),\n","              bigquery.SchemaField('reference', 'STRING'),\n","              bigquery.SchemaField('jsonurl', 'STRING')],\n","    autodetect = False,\n","    source_format = bigquery.SourceFormat.CSV)\n","\n","job = client.load_table_from_dataframe(gwosc_events, 'grav-waves-358320.mergers.features', job_config = job_config)"],"metadata":{"id":"s4HbV7JHnVm6"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Objects table\n","job_config = bigquery.LoadJobConfig(\n","    \n","    #\n","    # schema = [bigquery.SchemaField('id', 'STRING'),\n","    #           bigquery.SchemaField('observing_run', 'STRING'),\n","    #           bigquery.SchemaField('primary_mass', 'FLOAT64'),\n","    #           bigquery.SchemaField('secondary_mass', 'FLOAT64'),\n","    #           bigquery.SchemaField('final_mass', 'FLOAT64'),\n","    #           bigquery.SchemaField('primary_object', 'STRING'),\n","    #           bigquery.SchemaField('secondary_object', 'STRING'),\n","    #           bigquery.SchemaField('final_object', 'STRING'),\n","    #           bigquery.SchemaField('merger_datetime', 'TIMESTAMP')],\n","    autodetect = True,\n","    source_format = bigquery.SourceFormat.CSV)\n","\n","job = client.load_table_from_dataframe(merger_metadata, 'grav-waves-358320.mergers.objects', job_config = job_config)"],"metadata":{"id":"KGFtF-Q-EAtk"},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":["## 6. Test load\n","Check that the data loaded successfully. Save to a df called `gw_2015`."],"metadata":{"id":"Kbf_XjNupkHx"}},{"cell_type":"code","source":["%%bigquery --project grav-waves-358320 gw_2015\n","SELECT *\n","FROM `mergers.features`\n","WHERE EXTRACT(YEAR FROM merger_datetime) = 2015;"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"GBinbaB0pidH","executionInfo":{"status":"ok","timestamp":1659646826600,"user_tz":300,"elapsed":6,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"eecd5a6a-83e9-4f80-fd01-41e96c474d1f"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stderr","text":["Could not save output to variable 'gw_2015'.\n","\n","ERROR:\n"," (\"Failed to retrieve http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/default/?recursive=true from the Google Compute Enginemetadata service. Status: 404 Response:\\nb''\", <google.auth.transport.requests._Response object at 0x7f9199fed150>)\n"]}]},{"cell_type":"code","source":["features_q = 'SELECT * FROM `grav-waves-358320.mergers.features` LIMIT 10'\n","gw = client.query(features_q).to_dataframe()"],"metadata":{"id":"4bVcHyu4qob5"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["gw.head()"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":386},"id":"0AL4XLjDqrUV","executionInfo":{"status":"ok","timestamp":1659647005602,"user_tz":300,"elapsed":6,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"efac936c-ef47-49c0-b992-397639d0af08"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["                   id       commonName  version catalog_shortName  \\\n","0  GW190408_181802-v1  GW190408_181802        1            GWTC-2   \n","1         GW190412-v3         GW190412        3            GWTC-2   \n","2  GW190413_052954-v1  GW190413_052954        1            GWTC-2   \n","3  GW190413_134308-v1  GW190413_134308        1            GWTC-2   \n","4  GW190421_213856-v1  GW190421_213856        1            GWTC-2   \n","\n","            GPS reference                                            jsonurl  \\\n","0  1.238783e+09  /GWTC-2/  https://www.gw-openscience.org/eventapi/json/G...   \n","1  1.239082e+09  /GWTC-2/  https://www.gw-openscience.org/eventapi/json/G...   \n","2  1.239169e+09  /GWTC-2/  https://www.gw-openscience.org/eventapi/json/G...   \n","3  1.239198e+09  /GWTC-2/  https://www.gw-openscience.org/eventapi/json/G...   \n","4  1.239918e+09  /GWTC-2/  https://www.gw-openscience.org/eventapi/json/G...   \n","\n","   mass_1_source  mass_1_source_lower  mass_1_source_upper  ...       far  \\\n","0           24.6                 -3.4                  5.1  ...  0.000010   \n","1           30.1                 -5.1                  4.7  ...  0.000010   \n","2           34.7                 -8.1                 12.6  ...  0.071683   \n","3           47.5                -10.7                 13.5  ...  0.043657   \n","4           41.3                 -6.9                 10.4  ...  0.000774   \n","\n","   far_lower  far_upper   p_astro p_astro_lower p_astro_upper  \\\n","0       None       None  1.000000          None          None   \n","1       None       None  1.000000          None          None   \n","2       None       None  0.977790          None          None   \n","3       None       None  0.984900          None          None   \n","4       None       None  0.999768          None          None   \n","\n","   final_mass_source  final_mass_source_lower  final_mass_source_upper  \\\n","0               41.1                     -2.8                      3.9   \n","1               37.3                     -3.8                      3.9   \n","2               56.0                     -9.2                     12.5   \n","3               75.5                    -11.4                     16.4   \n","4               69.7                     -8.7                     12.5   \n","\n","      merger_datetime  \n","0 2019-04-08 18:18:02  \n","1 2019-04-12 00:00:00  \n","2 2019-04-13 05:29:54  \n","3 2019-04-13 13:43:08  \n","4 2019-04-21 21:38:56  \n","\n","[5 rows x 44 columns]"],"text/html":["\n","  <div id=\"df-e0e166ee-631b-4ef3-bc35-28177647b120\">\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>id</th>\n","      <th>commonName</th>\n","      <th>version</th>\n","      <th>catalog_shortName</th>\n","      <th>GPS</th>\n","      <th>reference</th>\n","      <th>jsonurl</th>\n","      <th>mass_1_source</th>\n","      <th>mass_1_source_lower</th>\n","      <th>mass_1_source_upper</th>\n","      <th>...</th>\n","      <th>far</th>\n","      <th>far_lower</th>\n","      <th>far_upper</th>\n","      <th>p_astro</th>\n","      <th>p_astro_lower</th>\n","      <th>p_astro_upper</th>\n","      <th>final_mass_source</th>\n","      <th>final_mass_source_lower</th>\n","      <th>final_mass_source_upper</th>\n","      <th>merger_datetime</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>GW190408_181802-v1</td>\n","      <td>GW190408_181802</td>\n","      <td>1</td>\n","      <td>GWTC-2</td>\n","      <td>1.238783e+09</td>\n","      <td>/GWTC-2/</td>\n","      <td>https://www.gw-openscience.org/eventapi/json/G...</td>\n","      <td>24.6</td>\n","      <td>-3.4</td>\n","      <td>5.1</td>\n","      <td>...</td>\n","      <td>0.000010</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>1.000000</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>41.1</td>\n","      <td>-2.8</td>\n","      <td>3.9</td>\n","      <td>2019-04-08 18:18:02</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>GW190412-v3</td>\n","      <td>GW190412</td>\n","      <td>3</td>\n","      <td>GWTC-2</td>\n","      <td>1.239082e+09</td>\n","      <td>/GWTC-2/</td>\n","      <td>https://www.gw-openscience.org/eventapi/json/G...</td>\n","      <td>30.1</td>\n","      <td>-5.1</td>\n","      <td>4.7</td>\n","      <td>...</td>\n","      <td>0.000010</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>1.000000</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>37.3</td>\n","      <td>-3.8</td>\n","      <td>3.9</td>\n","      <td>2019-04-12 00:00:00</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>GW190413_052954-v1</td>\n","      <td>GW190413_052954</td>\n","      <td>1</td>\n","      <td>GWTC-2</td>\n","      <td>1.239169e+09</td>\n","      <td>/GWTC-2/</td>\n","      <td>https://www.gw-openscience.org/eventapi/json/G...</td>\n","      <td>34.7</td>\n","      <td>-8.1</td>\n","      <td>12.6</td>\n","      <td>...</td>\n","      <td>0.071683</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>0.977790</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>56.0</td>\n","      <td>-9.2</td>\n","      <td>12.5</td>\n","      <td>2019-04-13 05:29:54</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>GW190413_134308-v1</td>\n","      <td>GW190413_134308</td>\n","      <td>1</td>\n","      <td>GWTC-2</td>\n","      <td>1.239198e+09</td>\n","      <td>/GWTC-2/</td>\n","      <td>https://www.gw-openscience.org/eventapi/json/G...</td>\n","      <td>47.5</td>\n","      <td>-10.7</td>\n","      <td>13.5</td>\n","      <td>...</td>\n","      <td>0.043657</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>0.984900</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>75.5</td>\n","      <td>-11.4</td>\n","      <td>16.4</td>\n","      <td>2019-04-13 13:43:08</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>GW190421_213856-v1</td>\n","      <td>GW190421_213856</td>\n","      <td>1</td>\n","      <td>GWTC-2</td>\n","      <td>1.239918e+09</td>\n","      <td>/GWTC-2/</td>\n","      <td>https://www.gw-openscience.org/eventapi/json/G...</td>\n","      <td>41.3</td>\n","      <td>-6.9</td>\n","      <td>10.4</td>\n","      <td>...</td>\n","      <td>0.000774</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>0.999768</td>\n","      <td>None</td>\n","      <td>None</td>\n","      <td>69.7</td>\n","      <td>-8.7</td>\n","      <td>12.5</td>\n","      <td>2019-04-21 21:38:56</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>5 rows × 44 columns</p>\n","</div>\n","      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e0e166ee-631b-4ef3-bc35-28177647b120')\"\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-e0e166ee-631b-4ef3-bc35-28177647b120 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-e0e166ee-631b-4ef3-bc35-28177647b120');\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":54}]},{"cell_type":"markdown","source":["## 7. Add metadata"],"metadata":{"id":"ybP5KJvbrAbt"}},{"cell_type":"code","source":["dataset = client.get_dataset('mergers')  # Make an API request.\n","dataset.description = 'This dataset contains data on gravitational wave merger detections made since 2015.'\n","dataset = client.update_dataset(dataset, [\"description\"])  # Make an API request."],"metadata":{"id":"iBBaDgMFq_zz"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Dataset description\n","dataset = client.get_dataset('mergers')\n","dataset.description"],"metadata":{"colab":{"base_uri":"https://localhost:8080/","height":35},"id":"4T5hzeBOrEQT","executionInfo":{"status":"ok","timestamp":1659647060023,"user_tz":300,"elapsed":601,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"6c50ac6e-741e-42e2-d6b0-1ba4962eed25"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["'This dataset contains data on gravitational wave merger detections made since 2015.'"],"application/vnd.google.colaboratory.intrinsic+json":{"type":"string"}},"metadata":{},"execution_count":55}]},{"cell_type":"code","source":["# Tables in mergers dataset\n","merger_tables = list(client.list_tables('mergers'))\n","merger_tbl = [i.table_id for i in merger_tables]\n","merger_tbl"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"MxAsMAWlrHb-","executionInfo":{"status":"ok","timestamp":1660864623269,"user_tz":300,"elapsed":476,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"5716dbec-34dd-45df-e252-4c86d413318d"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["['features', 'timeseries']"]},"metadata":{},"execution_count":10}]},{"cell_type":"code","source":["# Descriptions for tables\n","table = client.get_table('grav-waves-358320.mergers.features')"],"metadata":{"id":"XG1-nHTBrJ2o"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["table.schema"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"yT1YSAICrOtY","executionInfo":{"status":"ok","timestamp":1659647095687,"user_tz":300,"elapsed":286,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"557554f2-3b3c-4009-bc8a-24b4d7548535"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["[SchemaField('id', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('commonName', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('version', 'INTEGER', 'NULLABLE', None, ()),\n"," SchemaField('catalog_shortName', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('GPS', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('reference', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('jsonurl', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('mass_1_source', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('mass_1_source_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('mass_1_source_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('mass_2_source', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('mass_2_source_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('mass_2_source_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('network_matched_filter_snr', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('network_matched_filter_snr_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('network_matched_filter_snr_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('luminosity_distance', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('luminosity_distance_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('luminosity_distance_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chi_eff', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chi_eff_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chi_eff_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('total_mass_source', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('total_mass_source_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('total_mass_source_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chirp_mass_source', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chirp_mass_source_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chirp_mass_source_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chirp_mass', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chirp_mass_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('chirp_mass_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('redshift', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('redshift_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('redshift_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('far', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('far_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('far_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('p_astro', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('p_astro_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('p_astro_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('final_mass_source', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('final_mass_source_lower', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('final_mass_source_upper', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('merger_datetime', 'DATETIME', 'NULLABLE', None, ())]"]},"metadata":{},"execution_count":58}]},{"cell_type":"code","source":["# Update the features table description\n","table.description = '''This table contains mass data for gravitational wave merger detections made since 2015.\n","Of note:\n","\n","\"(i) GW191219_163120 and GW200115_042309: two mergers between possible neutron star-black hole pairs; the neutron star in GW191219_163120 is one of the least massive ever observed;\n","(ii) GW200210_092254: a merger between a black hole and an object which could either be a light black hole or a heavy neutron star;\n","(iii) GW200220_061928: a massive pair of black holes orbiting each other, with a combined mass 145 times heavier than the Sun;\n","(iv) GW191204_171526: a pair of black holes orbiting each other, in which at least one of the pair is spinning upright;\n","(v) GW191109_010717: a pair of black holes orbiting each other which have a combined mass 112 times heavier than the Sun, which seems to be spinning upside-down;\n","(vi) GW191129_134029: a ‘light’ pair of black holes that together weigh only 18 times the mass of the Sun.\"\n","\n","Source: ARC Centre of Excellence for Gravitational Wave Discovery, LIGO/VIRGO/KAGRA\n","Datasource: https://www.gw-openscience.org/eventapi/html/allevents/\n","Information on fields in dataset: https://www.gw-openscience.org/eventportaldocs/'''\n","\n","table = client.update_table(table, [\"description\"])"],"metadata":{"id":"o7WUucXyrQCS"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["# Check that the description was updated\n","table = client.get_table('grav-waves-358320.mergers.features')\n","print(table.description)"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"WfYD9yInrpX5","executionInfo":{"status":"ok","timestamp":1659647219551,"user_tz":300,"elapsed":620,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"f6306782-cbd9-4eff-c156-56b33403baaf"},"execution_count":null,"outputs":[{"output_type":"stream","name":"stdout","text":["This table contains mass data for gravitational wave merger detections made since 2015.\n","Of note:\n","\n","\"(i) GW191219_163120 and GW200115_042309: two mergers between possible neutron star-black hole pairs; the neutron star in GW191219_163120 is one of the least massive ever observed;\n","(ii) GW200210_092254: a merger between a black hole and an object which could either be a light black hole or a heavy neutron star;\n","(iii) GW200220_061928: a massive pair of black holes orbiting each other, with a combined mass 145 times heavier than the Sun;\n","(iv) GW191204_171526: a pair of black holes orbiting each other, in which at least one of the pair is spinning upright;\n","(v) GW191109_010717: a pair of black holes orbiting each other which have a combined mass 112 times heavier than the Sun, which seems to be spinning upside-down;\n","(vi) GW191129_134029: a ‘light’ pair of black holes that together weigh only 18 times the mass of the Sun.\"\n","\n","Source: ARC Centre of Excellence for Gravitational Wave Discovery, LIGO/VIRGO/KAGRA\n","Datasource: https://www.gw-openscience.org/eventapi/html/allevents/\n","Information on fields in dataset: https://www.gw-openscience.org/eventportaldocs/\n"]}]},{"cell_type":"code","source":["# Descriptions for timeseries table\n","ts_tbl = client.get_table('grav-waves-358320.mergers.timeseries')"],"metadata":{"id":"SDlSe94xPzu8"},"execution_count":null,"outputs":[]},{"cell_type":"code","source":["ts_tbl.schema"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"FCzV9IjkP8Y9","executionInfo":{"status":"ok","timestamp":1660864687149,"user_tz":300,"elapsed":153,"user":{"displayName":"Nicole Sullivan","userId":"18031182080523530747"}},"outputId":"9111c765-b4cb-4ca9-9e7c-7c3698e2401d"},"execution_count":null,"outputs":[{"output_type":"execute_result","data":{"text/plain":["[SchemaField('id', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('observatory', 'STRING', 'NULLABLE', None, ()),\n"," SchemaField('size', 'FLOAT', 'NULLABLE', None, ()),\n"," SchemaField('capture_dttm', 'DATETIME', 'NULLABLE', None, ())]"]},"metadata":{},"execution_count":12}]},{"cell_type":"code","source":["# Update the features table description\n","ts_tbl.description = '''This table contains the calibrated strain data for gravitational wave merger detections made since 2015.\n","\n","Per the gwpy docs:\n","\"Gravitational-wave detectors are time-domain instruments, recording gravitational-wave amplitude as a differential change in the lengths of each of the interferometer arms. The primary output of these detectors is a single time-stream of gravitational-wave strain (also referred to as h(t)).\n","\n","Alongside the strain data, thousands of instrumental control and error signals and environmental monitors are recorded in real-time and archived for off-line study. These data are colloquially called the auxiliary channels.\"\n","\n","Source: https://gwpy.github.io/docs/latest/timeseries/\n","Datasource: https://www.gw-openscience.org/eventapi/html/allevents/'''\n","\n","ts_tbl = client.update_table(ts_tbl, [\"description\"])"],"metadata":{"id":"CguJa7vxP-p8"},"execution_count":null,"outputs":[]}]}