Skip to content

Instantly share code, notes, and snippets.

@ScienceTony

ScienceTony/Investigation of 70s and 80s and biggest nukes.ipynb

Created July 27, 2019 00:49
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ScienceTony/ca3758a49307fc2bcfa2151ae673aa2f to your computer and use it in GitHub Desktop.
Save ScienceTony/ca3758a49307fc2bcfa2151ae673aa2f to your computer and use it in GitHub Desktop.
Download ZIP
Creating dataframe for all occurrences between 1970 - 1980. Look at the complete range and find the biggest magnitude and its location
Display the source blob
Display the rendered blob
Raw
Investigation of 70s and 80s and biggest nukes.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Where occurrences took place in 70s and 80s\n",
"[Tony McDonald](https://twitter.com/ScienceTony)<br>"
]
},
{
"cell_type": "code",
"execution_count": 104,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'27-07-2019'"
]
},
"execution_count": 104,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from datetime import datetime\n",
"datetime.today().strftime('%d-%m-%Y')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# License \n",
"\n",
"MIT License with Copyright (c) 2019 Socratica https://github.com/socratica/data/blob/master/LICENSE\n",
"<br>\n",
"This license allows me to freely download, use, manipulate and distribute. \n",
"<br>\n",
"This is taken from the [Socratica](https://www.youtube.com/Socratica) SQL YouTube video for [SELECT](https://www.youtube.com/watch?v=VkabxQgtGsA&list=PLi01XoE8jYojRqM4qGBF1U90Ee1Ecb5tt&index=7). \n",
"<br>\n",
"### This notebook covers\n",
"<ul>\n",
" <li>Creating dataframe for all occurrences between 1970 - 1980 </li>\n",
" <li>Look at the complete range and find the biggest magnitude and its location</li>\n",
" </ul> "
]
},
{
"cell_type": "code",
"execution_count": 105,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import matplotlib.pyplot as plt\n",
"import numpy as np\n",
"import seaborn as sns"
]
},
{
"cell_type": "code",
"execution_count": 106,
"metadata": {},
"outputs": [],
"source": [
"from pandas import DataFrame\n",
"from pandasql import sqldf\n",
"\n",
"# Then create a simple wrapper function to allow us to supply the query 'q' without \n",
"# the surrounding syntax.\n",
"pysqldf = lambda q: sqldf(q, globals())"
]
},
{
"cell_type": "code",
"execution_count": 107,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"data-master from YT/songs.csv\r\n",
"data-master from YT/earthquake.csv\r\n"
]
}
],
"source": [
"!find 'data-master from YT' -name '*csv'"
]
},
{
"cell_type": "code",
"execution_count": 108,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<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>earthquake_id</th>\n",
" <th>occurred_on</th>\n",
" <th>latitude</th>\n",
" <th>longitude</th>\n",
" <th>depth</th>\n",
" <th>magnitude</th>\n",
" <th>calculation_method</th>\n",
" <th>network_id</th>\n",
" <th>place</th>\n",
" <th>cause</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1</td>\n",
" <td>1969-01-01 9:07:06</td>\n",
" <td>51.096</td>\n",
" <td>-179.392</td>\n",
" <td>45.0</td>\n",
" <td>5.6</td>\n",
" <td>mw</td>\n",
" <td>iscgem812771</td>\n",
" <td>Andreanof Islands, Aleutian Islands, Alaska</td>\n",
" <td>earthquake</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2</td>\n",
" <td>1969-01-02 17:50:48</td>\n",
" <td>-56.096</td>\n",
" <td>-27.842</td>\n",
" <td>80.1</td>\n",
" <td>6.0</td>\n",
" <td>mw</td>\n",
" <td>iscgemsup812819</td>\n",
" <td>South Sandwich Islands region</td>\n",
" <td>earthquake</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>3</td>\n",
" <td>1969-01-03 3:16:40</td>\n",
" <td>37.140</td>\n",
" <td>57.899</td>\n",
" <td>10.0</td>\n",
" <td>5.5</td>\n",
" <td>mw</td>\n",
" <td>iscgem812826</td>\n",
" <td>Turkmenistan-Iran border region</td>\n",
" <td>earthquake</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>4</td>\n",
" <td>1969-01-03 13:28:12</td>\n",
" <td>51.132</td>\n",
" <td>-179.306</td>\n",
" <td>15.0</td>\n",
" <td>5.9</td>\n",
" <td>mw</td>\n",
" <td>iscgem812841</td>\n",
" <td>Andreanof Islands, Aleutian Islands, Alaska</td>\n",
" <td>earthquake</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>5</td>\n",
" <td>1969-01-04 22:36:48</td>\n",
" <td>-6.850</td>\n",
" <td>129.821</td>\n",
" <td>105.0</td>\n",
" <td>5.8</td>\n",
" <td>mw</td>\n",
" <td>iscgem812879</td>\n",
" <td>Banda Sea</td>\n",
" <td>earthquake</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" earthquake_id occurred_on latitude longitude depth magnitude \\\n",
"0 1 1969-01-01 9:07:06 51.096 -179.392 45.0 5.6 \n",
"1 2 1969-01-02 17:50:48 -56.096 -27.842 80.1 6.0 \n",
"2 3 1969-01-03 3:16:40 37.140 57.899 10.0 5.5 \n",
"3 4 1969-01-03 13:28:12 51.132 -179.306 15.0 5.9 \n",
"4 5 1969-01-04 22:36:48 -6.850 129.821 105.0 5.8 \n",
"\n",
" calculation_method network_id \\\n",
"0 mw iscgem812771 \n",
"1 mw iscgemsup812819 \n",
"2 mw iscgem812826 \n",
"3 mw iscgem812841 \n",
"4 mw iscgem812879 \n",
"\n",
" place cause \n",
"0 Andreanof Islands, Aleutian Islands, Alaska earthquake \n",
"1 South Sandwich Islands region earthquake \n",
"2 Turkmenistan-Iran border region earthquake \n",
"3 Andreanof Islands, Aleutian Islands, Alaska earthquake \n",
"4 Banda Sea earthquake "
]
},
"execution_count": 108,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"earthquake_data = pd.read_csv('data-master from YT/earthquake.csv')\n",
"earthquake_data.head(5)"
]
},
{
"cell_type": "code",
"execution_count": 109,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"waiting for server to start....2019-07-27 01:39:17.399 BST [6959] LOG: listening on IPv6 address \"::1\", port 5432\n",
"2019-07-27 01:39:17.399 BST [6959] LOG: listening on IPv4 address \"127.0.0.1\", port 5432\n",
"2019-07-27 01:39:17.400 BST [6959] LOG: listening on Unix socket \"/tmp/.s.PGSQL.5432\"\n",
"2019-07-27 01:39:17.418 BST [6960] LOG: database system was shut down at 2019-07-27 01:38:32 BST\n",
"2019-07-27 01:39:17.424 BST [6959] LOG: database system is ready to accept connections\n",
" done\n",
"server started\n"
]
}
],
"source": [
"# Start the postgres server. \n",
"!./serverStart.command"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Stop the postgres server. \n",
"!./serverStop.command"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Load in the sql extensions:\n",
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 111,
"metadata": {},
"outputs": [],
"source": [
"DB_ENGINE='postgresql' # tells ipython-sql that we will use postgreSQL as our database engine\n",
"DB_USER='postgres' # id of the user who is logging in\n",
"DB_PWD='postgres' # the user's password\n",
"DB_ADDR='localhost:5432' # the host and port on which the database engine is listening\n",
"DB_NAME='earthquake' # the name of the database"
]
},
{
"cell_type": "code",
"execution_count": 112,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'postgresql://postgres:postgres@localhost:5432/earthquake'"
]
},
"execution_count": 112,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#Construct the connection string\n",
"DB_CONNECTION = '{engine}://{user}:{pwd}@{addr}/{name}'.format(engine=DB_ENGINE,\n",
" user=DB_USER,\n",
" pwd=DB_PWD,\n",
" addr=DB_ADDR,\n",
" name=DB_NAME)\n",
"\n",
"#Preview the connection string\n",
"DB_CONNECTION"
]
},
{
"cell_type": "code",
"execution_count": 113,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: postgres@earthquake'"
]
},
"execution_count": 113,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql $DB_CONNECTION"
]
},
{
"cell_type": "code",
"execution_count": 114,
"metadata": {},
"outputs": [],
"source": [
"date_range_query = \"\"\"\n",
" SELECT occurred_on, magnitude, place, cause\n",
" FROM earthquake_data\n",
" WHERE (cause = 'nuclear explosion' OR 'explosion') AND (occurred_on >= '1970-1-1' AND occurred_on <= '1989-12-31')\n",
" ;\n",
" \n",
"\"\"\""
]
},
{
"cell_type": "code",
"execution_count": 115,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<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>occurred_on</th>\n",
" <th>magnitude</th>\n",
" <th>place</th>\n",
" <th>cause</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>1973-10-27 6:59:57</td>\n",
" <td>6.9</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>1973-09-12 6:59:54</td>\n",
" <td>6.8</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>1975-10-18 8:59:56</td>\n",
" <td>6.7</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>1974-11-02 4:59:57</td>\n",
" <td>6.7</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>1975-10-21 11:59:57</td>\n",
" <td>6.5</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>1974-08-29 9:59:56</td>\n",
" <td>6.4</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>55</th>\n",
" <td>1979-06-23 2:56:58</td>\n",
" <td>6.3</td>\n",
" <td>eastern Kazakhstan</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1973-07-23 1:22:58</td>\n",
" <td>6.3</td>\n",
" <td>eastern Kazakhstan</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>74</th>\n",
" <td>1980-09-14 2:42:39</td>\n",
" <td>6.2</td>\n",
" <td>eastern Kazakhstan</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>87</th>\n",
" <td>1981-12-27 3:43:14</td>\n",
" <td>6.2</td>\n",
" <td>eastern Kazakhstan</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" occurred_on magnitude place cause\n",
"4 1973-10-27 6:59:57 6.9 Novaya Zemlya, Russia nuclear explosion\n",
"2 1973-09-12 6:59:54 6.8 Novaya Zemlya, Russia nuclear explosion\n",
"16 1975-10-18 8:59:56 6.7 Novaya Zemlya, Russia nuclear explosion\n",
"10 1974-11-02 4:59:57 6.7 Novaya Zemlya, Russia nuclear explosion\n",
"17 1975-10-21 11:59:57 6.5 Novaya Zemlya, Russia nuclear explosion\n",
"8 1974-08-29 9:59:56 6.4 Novaya Zemlya, Russia nuclear explosion\n",
"55 1979-06-23 2:56:58 6.3 eastern Kazakhstan nuclear explosion\n",
"1 1973-07-23 1:22:58 6.3 eastern Kazakhstan nuclear explosion\n",
"74 1980-09-14 2:42:39 6.2 eastern Kazakhstan nuclear explosion\n",
"87 1981-12-27 3:43:14 6.2 eastern Kazakhstan nuclear explosion"
]
},
"execution_count": 115,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pysqldf(date_range_query).sort_values(by='magnitude', ascending=False)[:10]"
]
},
{
"cell_type": "code",
"execution_count": 116,
"metadata": {},
"outputs": [],
"source": [
"# data range\n",
"big_bang_query = \"\"\"\n",
" SELECT occurred_on, magnitude, place, cause\n",
" FROM earthquake_data\n",
" WHERE cause = 'nuclear explosion' OR 'explosion' \n",
" ;\n",
" \n",
"\"\"\""
]
},
{
"cell_type": "code",
"execution_count": 117,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<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>magnitude</th>\n",
" <th>place</th>\n",
" <th>cause</th>\n",
" </tr>\n",
" <tr>\n",
" <th>occurred_on</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1973-10-27 6:59:57</th>\n",
" <td>6.9</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1973-09-12 6:59:54</th>\n",
" <td>6.8</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1974-11-02 4:59:57</th>\n",
" <td>6.7</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1975-10-18 8:59:56</th>\n",
" <td>6.7</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1975-10-21 11:59:57</th>\n",
" <td>6.5</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1992-05-21 4:59:58</th>\n",
" <td>6.5</td>\n",
" <td>southern Xinjiang, China</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1974-08-29 9:59:56</th>\n",
" <td>6.4</td>\n",
" <td>Novaya Zemlya, Russia</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1979-06-23 2:56:58</th>\n",
" <td>6.3</td>\n",
" <td>eastern Kazakhstan</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2017-09-03 3:30:02</th>\n",
" <td>6.3</td>\n",
" <td>21km ENE of Sungjibaegam, North Korea</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1973-07-23 1:22:58</th>\n",
" <td>6.3</td>\n",
" <td>eastern Kazakhstan</td>\n",
" <td>nuclear explosion</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" magnitude place \\\n",
"occurred_on \n",
"1973-10-27 6:59:57 6.9 Novaya Zemlya, Russia \n",
"1973-09-12 6:59:54 6.8 Novaya Zemlya, Russia \n",
"1974-11-02 4:59:57 6.7 Novaya Zemlya, Russia \n",
"1975-10-18 8:59:56 6.7 Novaya Zemlya, Russia \n",
"1975-10-21 11:59:57 6.5 Novaya Zemlya, Russia \n",
"1992-05-21 4:59:58 6.5 southern Xinjiang, China \n",
"1974-08-29 9:59:56 6.4 Novaya Zemlya, Russia \n",
"1979-06-23 2:56:58 6.3 eastern Kazakhstan \n",
"2017-09-03 3:30:02 6.3 21km ENE of Sungjibaegam, North Korea \n",
"1973-07-23 1:22:58 6.3 eastern Kazakhstan \n",
"\n",
" cause \n",
"occurred_on \n",
"1973-10-27 6:59:57 nuclear explosion \n",
"1973-09-12 6:59:54 nuclear explosion \n",
"1974-11-02 4:59:57 nuclear explosion \n",
"1975-10-18 8:59:56 nuclear explosion \n",
"1975-10-21 11:59:57 nuclear explosion \n",
"1992-05-21 4:59:58 nuclear explosion \n",
"1974-08-29 9:59:56 nuclear explosion \n",
"1979-06-23 2:56:58 nuclear explosion \n",
"2017-09-03 3:30:02 nuclear explosion \n",
"1973-07-23 1:22:58 nuclear explosion "
]
},
"execution_count": 117,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pysqldf(big_bang_query).sort_values(by='magnitude', ascending=False).set_index('occurred_on')[:10]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Novaya Zemlya, Russia had the highest magnitudes with the 70s being a big time for big nukes"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[View Full Report](https://www.sciencetony.com/2019/07/nuclear-test-map-for-1969-to-2017.html)<br>\n",
"[Sciencetony.com](https://www.sciencetony.com/)<br>\n",
"[View My LinkedIn CV](https://www.linkedin.com/in/tony-mcdonald-sciencetony/)<br>\n",
"[My Twitter](https://twitter.com/ScienceTony)<br>\n",
"[My YouTube](https://www.youtube.com/Dreamazium)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.2"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment