{
"cells": [
{
"cell_type": "code",
"execution_count": 14,
"id": "0f3a8346-0c49-4cab-ab0a-e982006db476",
"metadata": {},
"outputs": [],
"source": [
"import ibis\n",
"from ibis import _\n",
"\n",
"con = ibis.duckdb.connect()\n",
"\n",
"df = (con.\n",
" read_csv(\"landvote.csv\")\n",
" .mutate(amount = _[\"Conservation Funds Approved\"])\n",
" .mutate(conservation_funds_approved=_.amount.replace('$', '').replace(',', '').cast('float'))\n",
" .mutate(year = _.Date.year())\n",
" )\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "b36f93ae-b12b-46ed-a105-07243b86b308",
"metadata": {},
"outputs": [],
"source": [
"cols = ['State',\n",
" 'Jurisdiction Name',\n",
" 'Jurisdiction Type',\n",
" 'Date',\n",
" 'Description',\n",
" 'Finance Mechanism',\n",
" '\"Other\" Comment',\n",
" 'Purpose',\n",
" 'Conservation Funds at Stake',\n",
" 'Pass?',\n",
" 'Status',\n",
" '% Yes',\n",
" '% No',\n",
" 'Notes',\n",
" 'Voted Acq. Measure',\n",
" 'amount',\n",
" 'conservation_funds_approved',\n",
" 'year']\n",
"\n",
"df.select(cols).to_csv(\"data.csv\")"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "15dda23e-4db4-4860-9c48-20ce580d635b",
"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>State</th>\n",
" <th>n</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>CA</td>\n",
" <td>1.907187e+10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>FL</td>\n",
" <td>1.411086e+10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>NJ</td>\n",
" <td>1.223420e+10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>CO</td>\n",
" <td>6.011241e+09</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>MN</td>\n",
" <td>5.963134e+09</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" State n\n",
"0 CA 1.907187e+10\n",
"1 FL 1.411086e+10\n",
"2 NJ 1.223420e+10\n",
"3 CO 6.011241e+09\n",
"4 MN 5.963134e+09"
]
},
"execution_count": 18,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.group_by(_.State).agg(n = _.conservation_funds_approved.sum()).order_by(_.n.desc()).head().execute()"
]
},
{
"cell_type": "code",
"execution_count": 22,
"id": "1117dd18-5207-4e14-9920-4feb262d373c",
"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>State</th>\n",
" <th>n</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>MA</td>\n",
" <td>132</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>NJ</td>\n",
" <td>128</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>CO</td>\n",
" <td>47</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>CA</td>\n",
" <td>47</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>IL</td>\n",
" <td>37</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>PA</td>\n",
" <td>35</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>WA</td>\n",
" <td>34</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>OH</td>\n",
" <td>26</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>MI</td>\n",
" <td>23</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>FL</td>\n",
" <td>19</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>Ore</td>\n",
" <td>15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>NC</td>\n",
" <td>13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>CT</td>\n",
" <td>13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>NY</td>\n",
" <td>12</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>TX</td>\n",
" <td>11</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>GA</td>\n",
" <td>9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>AZ</td>\n",
" <td>9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>MN</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>UT</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>WI</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>AK</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>NV</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>VA</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>ID</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>MT</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>NM</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>ME</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>OK</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>RI</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>LA</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>30</th>\n",
" <td>AR</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>31</th>\n",
" <td>MS</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>32</th>\n",
" <td>SC</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>33</th>\n",
" <td>ND</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>34</th>\n",
" <td>TN</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>35</th>\n",
" <td>NE</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>36</th>\n",
" <td>IA</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>37</th>\n",
" <td>KY</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" State n\n",
"0 MA 132\n",
"1 NJ 128\n",
"2 CO 47\n",
"3 CA 47\n",
"4 IL 37\n",
"5 PA 35\n",
"6 WA 34\n",
"7 OH 26\n",
"8 MI 23\n",
"9 FL 19\n",
"10 Ore 15\n",
"11 NC 13\n",
"12 CT 13\n",
"13 NY 12\n",
"14 TX 11\n",
"15 GA 9\n",
"16 AZ 9\n",
"17 MN 7\n",
"18 UT 7\n",
"19 WI 6\n",
"20 AK 5\n",
"21 NV 4\n",
"22 VA 4\n",
"23 ID 4\n",
"24 MT 4\n",
"25 NM 3\n",
"26 ME 3\n",
"27 OK 2\n",
"28 RI 2\n",
"29 LA 2\n",
"30 AR 2\n",
"31 MS 2\n",
"32 SC 2\n",
"33 ND 1\n",
"34 TN 1\n",
"35 NE 1\n",
"36 IA 1\n",
"37 KY 1"
]
},
"execution_count": 22,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.filter(_.Status == \"Fail\").group_by(_.State).agg(n = _.count()).order_by(_.n.desc()).execute()"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"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.11.10"
}
},
"nbformat": 4,
"nbformat_minor": 5
}