{ "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Staten
0CA1.907187e+10
1FL1.411086e+10
2NJ1.223420e+10
3CO6.011241e+09
4MN5.963134e+09
\n", "
" ], "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": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Staten
0MA132
1NJ128
2CO47
3CA47
4IL37
5PA35
6WA34
7OH26
8MI23
9FL19
10Ore15
11NC13
12CT13
13NY12
14TX11
15GA9
16AZ9
17MN7
18UT7
19WI6
20AK5
21NV4
22VA4
23ID4
24MT4
25NM3
26ME3
27OK2
28RI2
29LA2
30AR2
31MS2
32SC2
33ND1
34TN1
35NE1
36IA1
37KY1
\n", "
" ], "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 }