# Scratchboard - Radabstellplätze Wien ```bash id=c2adfdfe-42e1-4095-bb8b-a64c9092fe27 pip3 install pandas numpy ``` ## Aktuelle Daten von data.gv.at laden ```clojure id=d749281d-0b2f-4769-b47b-473fb69bb91d (require '[clojure.data.json :as json] '[clojure.java.io :as io] '[clojure.string :as str]) (def json-url "https://data.wien.gv.at/daten/geo?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:FAHRRADABSTELLANLAGEOGD&srsName=EPSG:4326&outputFormat=json") (def date (.format (java.text.SimpleDateFormat. "yyyy-MM-dd") (java.util.Date.))) (def file-name (str "/results/input-" date ".json")) ;; store input data for reference in results (io/copy (io/reader json-url) (io/file file-name)) (def current-data (-> file-name io/reader json/read)) ``` [input-2021-06-03.json][nextjournal#output#d749281d-0b2f-4769-b47b-473fb69bb91d#input-2021-06-03.json] ```clojure id=90a7cf96-bd80-49df-ba32-2b9374cda86c (require '[panthera.panthera :as pt] '[libpython-clj.python :as py :refer [py* py** call-attr]] '[panthera.pandas.utils :as u]) ``` ```clojure id=9a62f494-74e1-4c43-bdbc-12bd90163ca0 (defn show ([obj] (show obj {})) ([obj opts] (if (pt/series? obj) {:nextjournal/viewer :html :nextjournal.viewer/value (py* (call-attr obj "to_frame") to_html [] opts)} {:nextjournal/viewer :html :nextjournal.viewer/value (py* obj to_html [] opts)}))) ``` ```clojure id=eef08ab2-581a-48aa-b439-3da63fdc5a4f (def features (pt/data-frame (mapv (fn [f] (get f "properties")) (get current-data "features")))) (-> features (pt/subset-cols "BEZIRK" "ANZAHL") (pt/groupby ["BEZIRK"]) pt/sum show) ``` ```clojure id=ac50c65b-1628-4b3c-ab4a-4b5d393199bf (show (pt/head features) {:index false}) ``` [result][nextjournal#output#ac50c65b-1628-4b3c-ab4a-4b5d393199bf#result] ## Historische Daten [FAHRRADABSTELLANLAGEOGD-2014-08-28.json][nextjournal#file#73672648-4892-40e7-b028-68fe73a4169c] [FAHRRADABSTELLANLAGEN-2015-04.csv][nextjournal#file#d65f1657-bd1b-4357-8e3a-8a18e647fb2a] [input-2018-12-29.json][nextjournal#file#563a90e7-f3a2-4de5-8731-bad475c28a5b] [input-2020-04-28.json][nextjournal#file#b2ef3ab2-f4e3-4653-a8b8-a08cef2599e6] [FAHRRADABSTELLANLAGEOGD-2020-11-19.json][nextjournal#file#c7b5e301-975b-48b2-8e49-3814245967c2] ```clojure id=1bd616d2-b6fe-4ce9-be21-cc739518ca64 (def data {"28.08.2014" [reference][nextjournal#reference#40552077-0fc0-456f-9f9d-04653e487daa] "30.04.2015" [reference][nextjournal#reference#0cfae066-b80b-4efc-9bfb-144ab21398d8] "29.12.2018" [reference][nextjournal#reference#2d132e37-f2e8-47b1-9a7f-0432d71dd68a] "19.11.2020" [reference][nextjournal#reference#47d5fcd2-305f-4f4a-86ee-3ef43a0d200f]}) ``` ```clojure id=2f8df34b-95c9-403f-9fbe-6b811e33ef42 (defn features-df [uri] (cond (str/ends-with? uri ".json") (pt/data-frame (mapv (fn [f] (get f "properties")) (-> uri io/reader json/read (get "features")))) (str/ends-with? uri ".csv") (-> (pt/read-csv uri) (pt/rename {:columns {"bezirk" "BEZIRK" "anzahl" "ANZAHL"}})))) (defn features-by-district-df [uri sum-k] (-> uri features-df (pt/subset-cols "BEZIRK" "ANZAHL") (pt/groupby ["BEZIRK"] {:as-index false}) pt/sum (pt/rename {:columns {"BEZIRK" "Bezirk" "ANZAHL" sum-k}}) (pt/astype {"Bezirk" "int32" sum-k "int32"}))) ``` ```clojure id=8d006292-385b-4d8c-9d58-e19e72ca6deb (def history (reduce pt/merge-ordered (mapv (fn [[k v]] (features-by-district-df v k)) data))) ``` ```clojure id=a0ecea17-45a1-4172-b517-9b6a76a32e9e (def diff (pt/ops (pt/subset-cols history "19.11.2020") (pt/subset-cols history "29.12.2018") :-)) (vec diff) ``` ## Anzahl Radabstellplätze je Bezirk im Jahresvergleich ```clojure id=8067612a-bcc2-45b3-a42c-093fbfb6f49b (def history-with-diff (pt/assign history {"Differenz Dez 2018 - aktuell" diff})) (pt/to-csv history-with-diff "/results/radabbstellplaetze-2014-2020.csv") (show history-with-diff {:index false}) ``` [result][nextjournal#output#8067612a-bcc2-45b3-a42c-093fbfb6f49b#result] [radabbstellplaetze-2014-2020.csv][nextjournal#output#8067612a-bcc2-45b3-a42c-093fbfb6f49b#radabbstellplaetze-2014-2020.csv] ## Veränderung Radabstellplätze am Neubau ```clojure id=b7a5dbed-d4bb-405a-b2d9-9b6064f4e04a (def features-19 (features-df (get data "29.12.2018"))) (def features-20 (features-df (get data "19.11.2020"))) ``` ```clojure id=c554ded2-d72d-4fb3-8d28-87f4eeb6ca2a (defn pandas-merge [left right & [attrs]] (py/call-attr-kw u/pd "merge" [left right] (u/keys->pyargs attrs))) ``` ```clojure id=34d58d47-cd0c-4c2f-aadd-425fcbcf76fc (defn address-sum [df] (-> df (pt/groupby ["ADRESSE"] {:as-index false}) (py/get-attr "ANZAHL") pt/sum)) ``` ```clojure id=286f5acd-f445-4777-a904-67a661a5ef83 (def features-neubau-19 (-> features-19 (pt/filter-rows #(-> % (pt/subset-cols "BEZIRK") (pt/eq 7) pt/values)) address-sum)) (def features-neubau-20 (-> features-20 (pt/filter-rows #(-> % (pt/subset-cols "BEZIRK") (pt/eq 7) pt/values)) address-sum)) ``` ```clojure id=c92532b8-a143-4fdd-8e07-5dd5ff8b8263 (require '[panthera.pandas.generics :as generics :refer [sort-values]]) (def combined (pandas-merge features-neubau-19 features-neubau-20 {:on ["ADRESSE" "ANZAHL"] :indicator true :suffixes ["-19" "-20"] :how "outer"})) (def added (-> combined (pt/filter-rows #(-> % (pt/subset-cols "_merge") (pt/eq "right_only") pt/values)) (sort-values {:by "ADRESSE"}))) (show added) ``` [result][nextjournal#output#c92532b8-a143-4fdd-8e07-5dd5ff8b8263#result] ```clojure id=01202d9d-b6d9-4999-92c8-0ed904d701b4 (-> combined pt/tail show) ``` ```clojure id=be7db119-4b12-442c-9271-f8621a11a6c3 (def changed (-> combined (pt/filter-rows #(-> % (pt/subset-cols "_merge") (pt/ne "both") pt/values)) pt/->clj)) (def changed-agg (->> changed (reduce (fn [m f] (update m (:ADRESSE f) (fn [a] (if (= (:-merge f) "right_only") (+ (or a 0) (:ANZAHL f)) (- (or a 0) (:ANZAHL f)))))) {}) (into (sorted-map-by compare)))) (show (-> (pt/data-frame {:ADRESSE (keys changed-agg) :ANZAHL (vals changed-agg)}))) ``` [result][nextjournal#output#be7db119-4b12-442c-9271-f8621a11a6c3#result] ```clojure id=c8bd2aa2-a16b-4413-ba98-cdba6af4a63e (def removed (-> combined (pt/filter-rows #(-> % (pt/subset-cols "_merge") (pt/eq "left_only") pt/values)) (sort-values {:by "ADRESSE"}))) (show removed) ``` [result][nextjournal#output#c8bd2aa2-a16b-4413-ba98-cdba6af4a63e#result] # Appendix ```edn no-exec id=ffcf0396-b3f9-40e6-a0c2-654401879781 {:deps {org.clojure/clojure {:mvn/version "1.10.1"} org.clojure/data.json {:mvn/version "0.2.6"} clj-python/libpython-clj {:mvn/version "1.41"} panthera {:mvn/version "0.1-alpha.19"}}} ``` [nextjournal#output#d749281d-0b2f-4769-b47b-473fb69bb91d#input-2021-06-03.json]: [nextjournal#output#ac50c65b-1628-4b3c-ab4a-4b5d393199bf#result]: [nextjournal#file#73672648-4892-40e7-b028-68fe73a4169c]: [nextjournal#file#d65f1657-bd1b-4357-8e3a-8a18e647fb2a]: [nextjournal#file#563a90e7-f3a2-4de5-8731-bad475c28a5b]: [nextjournal#file#b2ef3ab2-f4e3-4653-a8b8-a08cef2599e6]: [nextjournal#file#c7b5e301-975b-48b2-8e49-3814245967c2]: [nextjournal#reference#40552077-0fc0-456f-9f9d-04653e487daa]: <#nextjournal#reference#40552077-0fc0-456f-9f9d-04653e487daa> [nextjournal#reference#0cfae066-b80b-4efc-9bfb-144ab21398d8]: <#nextjournal#reference#0cfae066-b80b-4efc-9bfb-144ab21398d8> [nextjournal#reference#2d132e37-f2e8-47b1-9a7f-0432d71dd68a]: <#nextjournal#reference#2d132e37-f2e8-47b1-9a7f-0432d71dd68a> [nextjournal#reference#47d5fcd2-305f-4f4a-86ee-3ef43a0d200f]: <#nextjournal#reference#47d5fcd2-305f-4f4a-86ee-3ef43a0d200f> [nextjournal#output#8067612a-bcc2-45b3-a42c-093fbfb6f49b#result]: [nextjournal#output#8067612a-bcc2-45b3-a42c-093fbfb6f49b#radabbstellplaetze-2014-2020.csv]: [nextjournal#output#c92532b8-a143-4fdd-8e07-5dd5ff8b8263#result]: [nextjournal#output#be7db119-4b12-442c-9271-f8621a11a6c3#result]: [nextjournal#output#c8bd2aa2-a16b-4413-ba98-cdba6af4a63e#result]:
This notebook was exported from https://nextjournal.com/a/Kgcwta6oqK67gbwLjjYpo?change-id=CwjMPLy2Hj44uEu4eGTcVk ```edn nextjournal-metadata {:article {:settings nil, :nodes {"01202d9d-b6d9-4999-92c8-0ed904d701b4" {:compute-ref #uuid "97a4c40f-950f-4464-b6f5-fd995101b5c6", :exec-duration 67, :id "01202d9d-b6d9-4999-92c8-0ed904d701b4", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "0cfae066-b80b-4efc-9bfb-144ab21398d8" {:id "0cfae066-b80b-4efc-9bfb-144ab21398d8", :kind "reference", :link [:output "d65f1657-bd1b-4357-8e3a-8a18e647fb2a" nil]}, "1bd616d2-b6fe-4ce9-be21-cc739518ca64" {:compute-ref #uuid "addfb947-0d2a-4e2a-9fec-0f975e883c2a", :exec-duration 61, :id "1bd616d2-b6fe-4ce9-be21-cc739518ca64", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "286f5acd-f445-4777-a904-67a661a5ef83" {:compute-ref #uuid "2907aded-f25b-4f56-b8c8-0c0c46ad70b5", :exec-duration 125, :id "286f5acd-f445-4777-a904-67a661a5ef83", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "2d132e37-f2e8-47b1-9a7f-0432d71dd68a" {:id "2d132e37-f2e8-47b1-9a7f-0432d71dd68a", :kind "reference", :link [:output "563a90e7-f3a2-4de5-8731-bad475c28a5b" nil]}, "2f8df34b-95c9-403f-9fbe-6b811e33ef42" {:compute-ref #uuid "189124d8-427c-4e35-b7fb-1b60bb8912c7", :exec-duration 109, :id "2f8df34b-95c9-403f-9fbe-6b811e33ef42", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "34d58d47-cd0c-4c2f-aadd-425fcbcf76fc" {:compute-ref #uuid "09e3ce67-1afe-4fc4-a381-5c77782c7f88", :exec-duration 62, :id "34d58d47-cd0c-4c2f-aadd-425fcbcf76fc", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "40552077-0fc0-456f-9f9d-04653e487daa" {:id "40552077-0fc0-456f-9f9d-04653e487daa", :kind "reference", :link [:output "73672648-4892-40e7-b028-68fe73a4169c" nil]}, "47d5fcd2-305f-4f4a-86ee-3ef43a0d200f" {:id "47d5fcd2-305f-4f4a-86ee-3ef43a0d200f", :kind "reference", :link [:output "c7b5e301-975b-48b2-8e49-3814245967c2" nil]}, "563a90e7-f3a2-4de5-8731-bad475c28a5b" {:id "563a90e7-f3a2-4de5-8731-bad475c28a5b", :kind "file"}, "73672648-4892-40e7-b028-68fe73a4169c" {:id "73672648-4892-40e7-b028-68fe73a4169c", :kind "file"}, "80403b0a-1226-48ff-9bcc-624ed02e3635" {:environment [:environment {:article/nextjournal.id #uuid "5b45eb52-bad4-413d-9d7f-b2b573a25322", :change/nextjournal.id #uuid "5df69cc3-ac89-473f-b791-4ea85a37786c", :node/id "0ae15688-6f6a-40e2-a4fa-52d81371f733"}], :environment? true, :id "80403b0a-1226-48ff-9bcc-624ed02e3635", :kind "runtime", :language "clojure", :name "clj-pandas", :type :nextjournal, :docker/environment-image "docker.nextjournal.com/environment@sha256:aee68cf483c1fc64f11f794d211d3152ff8697ed4505055d7c3ea89a3d6bc6fa", :runtime/mounts [{:src [:node "ffcf0396-b3f9-40e6-a0c2-654401879781"], :dest "/deps.edn"}]}, "8067612a-bcc2-45b3-a42c-093fbfb6f49b" {:compute-ref #uuid "a2483f57-edde-4a60-9747-92cad47e637d", :exec-duration 164, :id "8067612a-bcc2-45b3-a42c-093fbfb6f49b", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "8d006292-385b-4d8c-9d58-e19e72ca6deb" {:compute-ref #uuid "59612b95-cd9e-4624-b005-3a66e581e17e", :exec-duration 3812, :id "8d006292-385b-4d8c-9d58-e19e72ca6deb", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "90a7cf96-bd80-49df-ba32-2b9374cda86c" {:compute-ref #uuid "1982a0b8-e1c3-4914-ac7f-fbe3f7944b57", :exec-duration 6805, :id "90a7cf96-bd80-49df-ba32-2b9374cda86c", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "9a62f494-74e1-4c43-bdbc-12bd90163ca0" {:compute-ref #uuid "45bf7e88-38c0-4c20-bfbd-63778510bf0e", :exec-duration 7617, :id "9a62f494-74e1-4c43-bdbc-12bd90163ca0", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "a0ecea17-45a1-4172-b517-9b6a76a32e9e" {:compute-ref #uuid "45d5f4a8-809f-449b-99a2-3dc5b153e150", :exec-duration 216, :id "a0ecea17-45a1-4172-b517-9b6a76a32e9e", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "a5332636-a848-4ba1-8351-aff5c58bd48c" {:environment [:environment "80403b0a-1226-48ff-9bcc-624ed02e3635"], :id "a5332636-a848-4ba1-8351-aff5c58bd48c", :kind "runtime", :language "clojure", :type :nextjournal}, "ac50c65b-1628-4b3c-ab4a-4b5d393199bf" {:compute-ref #uuid "51768c1e-a531-4038-96ff-e31436fcc462", :exec-duration 321, :id "ac50c65b-1628-4b3c-ab4a-4b5d393199bf", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "b2ef3ab2-f4e3-4653-a8b8-a08cef2599e6" {:id "b2ef3ab2-f4e3-4653-a8b8-a08cef2599e6", :kind "file"}, "b7a5dbed-d4bb-405a-b2d9-9b6064f4e04a" {:compute-ref #uuid "9e027d74-f216-4a0f-877d-0f5ec5183499", :exec-duration 1826, :id "b7a5dbed-d4bb-405a-b2d9-9b6064f4e04a", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "be7db119-4b12-442c-9271-f8621a11a6c3" {:compute-ref #uuid "e0f9a14e-61c4-4338-bd0f-6448017cdd32", :exec-duration 631, :id "be7db119-4b12-442c-9271-f8621a11a6c3", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "c2adfdfe-42e1-4095-bb8b-a64c9092fe27" {:compute-ref #uuid "5808e3f6-5c41-4374-bbb1-7228ed6462bc", :exec-duration 12515, :id "c2adfdfe-42e1-4095-bb8b-a64c9092fe27", :kind "code", :locked? true, :output-log-lines {:stdout 12}, :runtime [:runtime "80403b0a-1226-48ff-9bcc-624ed02e3635"]}, "c554ded2-d72d-4fb3-8d28-87f4eeb6ca2a" {:compute-ref #uuid "1a8b9eac-2792-4ed7-9982-e60dae230e70", :exec-duration 71, :id "c554ded2-d72d-4fb3-8d28-87f4eeb6ca2a", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "c7b5e301-975b-48b2-8e49-3814245967c2" {:id "c7b5e301-975b-48b2-8e49-3814245967c2", :kind "file"}, "c8bd2aa2-a16b-4413-ba98-cdba6af4a63e" {:compute-ref #uuid "f82be163-9821-47a7-99c3-5c47ad848b55", :exec-duration 118, :id "c8bd2aa2-a16b-4413-ba98-cdba6af4a63e", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "c92532b8-a143-4fdd-8e07-5dd5ff8b8263" {:compute-ref #uuid "c25a5431-835b-4ae5-8122-9dd18906b732", :exec-duration 390, :id "c92532b8-a143-4fdd-8e07-5dd5ff8b8263", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"], :stdout-collapsed? false}, "d65f1657-bd1b-4357-8e3a-8a18e647fb2a" {:id "d65f1657-bd1b-4357-8e3a-8a18e647fb2a", :kind "file"}, "d749281d-0b2f-4769-b47b-473fb69bb91d" {:compute-ref #uuid "9e04c341-84c4-4ba9-aceb-8ec72299df67", :exec-duration 38331, :id "d749281d-0b2f-4769-b47b-473fb69bb91d", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "eef08ab2-581a-48aa-b439-3da63fdc5a4f" {:compute-ref #uuid "f286cb62-49d1-4dee-94b6-08b914a169da", :exec-duration 117838, :id "eef08ab2-581a-48aa-b439-3da63fdc5a4f", :kind "code", :output-log-lines {}, :runtime [:runtime "a5332636-a848-4ba1-8351-aff5c58bd48c"]}, "ffcf0396-b3f9-40e6-a0c2-654401879781" {:id "ffcf0396-b3f9-40e6-a0c2-654401879781", :kind "code-listing", :name "deps.edn"}}, :nextjournal/id #uuid "029bcded-a904-4c61-864c-7f30b429a9c0", :article/change {:nextjournal/id #uuid "60b8c9f2-c243-416d-a20a-dd3a9ca86c4f"}}} ```