berlin / haushaltsdaten Goto Github PK

View Code? Open in Web Editor NEW

8.0 6.0 3.0 9.75 MB

Data visualization of Berlin's public expenditures

Home Page: https://haushaltsdaten.odis-berlin.de

License: MIT License

Shell 0.18% JavaScript 2.87% TypeScript 96.07% CSS 0.89%

berlin berlinopendata city-government data-visualization expenditures open-data taxes

haushaltsdaten's Introduction

Berliner Haushaltsdaten - Dataviz

This data visualization communicates Berlin's public expenditures. It describes in which fields data is spent (or earned) and which district or area is responsible for the expenditure.

The means of visualization is a so-called treemap which allows comparing different expense areas and zooming into the hierarchies of expenses.

The treemap visualization is embedabble by using the Einbetten option.

In addition, a list below the visualization renders the items which belong to the currently selected expense field (ordered by amount in €).

A homepage and FAQ page provide contextual information.

Attention: This is a prototype, not a finished software.

The data

The data used is available as Open Data in Berlin's official Open Data portal. It is usually published bi-annually and includes the data for the next 2 years.

The data comes as Excel files (.xlsx), which is converted to CSV and then loaded into a PostgreSQL database.

We currently use a PostgreSQL database provided by Supabase. Find information for this setup in the haushaltsdaten-supabase repo.

A note on our usage of Supabase: We are using Supabase because it provides a convenient data access layer. There are certainly alternatives for accessing the data which could be explored in the future.

Tech stack

This website is a NextJS app configured with:

Typescript
Linting with ESLint
Formatting with Prettier
Linting, typechecking and formatting on by default using husky for commit hooks
Testing with Jest and react-testing-library

Project structure

Basic Next.js app

Components

The most important component is probably the treemap component which can be found in src/components/TreeMap/index.tsx. The component expects hierarchical data (hierarchy prop) to be passed to it (as specified in its type declaration). It then utilizes D3 to render a treemap SVG which can be zoomed into and out.

The treemap can be controlled with the filters defined in src/components/TreeMapControls/index.tsx.

Texts

The texts are currently hard-coded into the pages/components. In the future a more beginner-friendly structure could be applied (such as extracting all text content into a single content provider file).

Getting started

Requirements

Node.js

This project is a Next.js app which requires you to have Node.js installed.

Supabase

As explained in the data section earlier, a Supabase instance is used for storing the data.

If you simply want to continue working on this repository, you can use the already existing Supabase instance.

If you are planning to re-deploy this project, you will need to obtain a Supabase instance yourself, either by using their cloud offering or by self-hosting Supabase.

Installation

Clone the repository to your local machine:

git clone [email protected]:berlin/haushaltsdaten.git

Move into the repository folder:

cd haushaltsdaten

Make sure you use the Node.js version specified in .nvmrc. Find out which Node version you're currently on with:

node --version

If this version differs from the one specified in .nvmrc, please install the required version, either manually, or using a tool such as nvm, which allows switching to the correct version via:

nvm use

With the correct Node version, install the dependencies:

npm install

Because the data is stored in a Supabase database, you will need to provide connection details in your environment. In this repository you can find a file .env.example. Duplicate this file and name it .env.

In .env you must enter the connection details to the Supabase instance as suggested in .env.example. If you do not know how to obtain the necessary details, please ask a repository maintainer for access.

You are now ready to start a local development server on http://localhost:3000 via:

npm run dev

Workflow

New features, fixes, etc. should always be developed on a separate branch:

In your local repository, checkout the main branch.
Run git checkout -b <name-of-your-branch> to create a new branch (ideally following Conventional Commits guidelines).
Make your changes
Push your changes to the remote: git push -U origin HEAD
Open a pull request.

You can commit using the npm run cm command to ensure your commits follow our conventions.

Deployment

Berliner Haushaltsdaten - Dataviz is deployed to the cloud with Vercel (Documentation).

Page analytics

We use Matomo for website analytics. Matomo is respectful of the users' privacy, the page visits are tracked anonymously.

In the production environment, a NEXT_PUBLIC_MATOMO_URL and NEXT_PUBLIC_MATOMO_SITE_ID is configured for this purpose.

Contributors

Thanks goes to these wonderful people (emoji key):

_{Lisa Stubert}
📆 🤔 🧑‍🏫 📋 🔣

_{Max B. Eckert}
🤔 📋

_{Dennis Ostendorf}
🎨 💻 📖

_{Lucas Vogel}
🎨 💻 📖

_{Fabian Morón Zirfas}
💻 📖

_{Simon L.}
🤔 🎨 💻

_{Florian Stanek}
🤔 🎨 💻

_{Julia Zet}
🤔 💻 🎨

This project follows the all-contributors specification. Contributions of any kind welcome!

Content Licencing

Texts and content available as CC BY.

Credits

Together with:

A project by:

Supported by:

Special thanks

This project is inspired by the website offenerhaushalt.de of the Open Knowledge Foundation. This was a website that made budget data for cities and municipalities for Germany viewable in a centralized and standardized way. In recent years, the state of Berlin has used Offener Haushalt to visualize its budget data and also present it via embedding on its own Berlin.de website. Since 2021, however, Offener Haushalt can no longer be actively maintained. The reason for this is that in the current funding situation, it is difficult to operate platforms that are oriented towards the common good on a permanent basis, and strategies for adoption on the part of the administration are unfortunately lacking.

haushaltsdaten's People

Contributors

Stargazers

Watchers

Forkers

toffeltonius nort4n redeem-grimm-satoshi

haushaltsdaten's Issues

Make it more responsive

Show list contents based on current treemap level

We want all Einzeltitel belonging to a certain group (Hauptfunktion, Oberfunktion, Funktion) to be displayed in the list. For the MVP this can limited to the first 100 items.

Ideally, the table also holds information about the different group levels.

Sehr kleine TreeChart Bereiche werden nicht angezeigt

In Chrome auf Android on small devices

Small tool: Tax calculator

Inspired by the Moneten Kieker: https://www.schleswig-holstein.de/DE/fachinhalte/S/steuern/monetenkieker.html

Make testing more robust

We've got a couple of tests already and we might not need that many since it's a rather simple data visualization. But we might wanna add more tests, especially for gaining confidence when making changes or just updating dependencies.

Key features that we might wanna test:

the treemap controls: does changing the filters dispatch the correct events at all times?
the list view: is it always in sync with the current treemap level?

Perhaps we should consider integration testing over unit testing for these things?

I wonder if we should test anything treemap-itself-related. Or can we rely on D3 testing all our used functionalities?i

Show all results in list view via virtualised view

This ticket is for extending the list view so that it can display more than the initial 100 items.

Fix React key issue for Rücklagen expense

Tree map feature: Compare SOLL and IST-values

Make year dynamic

Currently we only display the data for 2022 (and excluding any IST data as opposed to the SOLL data).

Ideally we include 2023 as well as previous years, so that each year can be viewed. This may require us to think about the way we store the data. Currently we use a PostgreSQL view haushaltsdaten_2022. Can we just create new views for each year or is there a better way? What is the most friction-less way? What can be automated?

Make columns in list view clickable

When a category is clicked on a list element, the relevant filter should be activated.

Treemap Feature: Show Budget calculated per head

The treemap could be extended by a function, so that one can select whether the monetary values are to be indicated as total values or as calculated per capita. In the district display, the values would then be calculated on the district population.

Add fuzzy matching full text search

If there are no results we could run a fuzzy search with the same search term and show these results

Some questions about quality of input data

We (some people from Code for Germany) had a discussion about your project and wonder if you could lobby for better input data. Berlin seems to still provide that data as a XLSX file without documentation. So no advancement in direction to more structured, semantic (even linked) OpenData at the moment.

Have you also consider to use a different workflow in the UI? At the moment it's same workflow designed in OffenerHaushalt at 2012?

Had you had a look at things like https://specs.frictionlessdata.io/fiscal-data-package/#introduction and consider this as an interopable format for the data (e.g. as reexport from the app)?

Finish social image

Add context infos to each level/selection

Some levels are not self-explanatory. We've got some textual information for these that could be displayed next to the treemap.

Example: Maybe the group Allgemeine Dienste is not self-explanatory. In this case we could show 2 - 3 sentences next to the treemap or below it to explain and contextualize the current view.

Money "contextualizer"

A small bar (or something similar) should display the currently selected view and its amount of € and contextualize it with the highest level value of €.

Treemap lib suchen

Make each treemap depth sharable

What do we want?

We want a stable link for each filter/treemap combination, so that it can be shared with others. At the moment, we can already share the expense type and district combination. But we also want to be able to share a view that is already zoomed into the treemap. We already have URL params for this (mainTopic, midTopic, deepTopic), but these need to be put to use.

Idea for how this could work:

If a prop such as path (e.g. ['allgemeine_dienste', 'rechtsschutz']) is provided, we jump to the appropriate depth again by simulating clicks like we did initially.

However, this only ever happens on the first render of the treemap.

We avoid running server-side code again by passing shallow: true when we update the URL params for the path (on click within the Treemap component's D3 code).

But when we share the current link, there is a path present in the URL params, so that the treemap jumps to the correct depth on first render.

When changing district or showExpenses, we remove the path-related URL params, so that we always start in the highest depth for a new filter.

So much for the theory, hope this works out.

Next JS infrastructure

Duplicate our the Qtrees NextJS setup and prepare it for the project

Improve spinner on full text search

The placement of the spinner on the full text search could get some love

Graph auf Infoseite bei 2018 abgeschnitten und Mouseover Text für 2018 auch abgeschnitten. Kompletter Chart aber sichtbar, wenn man das Display dreht.

In Chrome auf Android small devices

Add treemap to Next project

Highlight search term in search results

Make sure the searched term is easily visually identifiable.

Make treemap react to filters

Add colours to treemap

Based on... group?

Add repo link to website

It's open source, let's show it!

Dependency Dashboard

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Rate-Limited

These updates are currently rate-limited. Click on a checkbox below to force their creation now.

fix(deps): update dependency @headlessui/react to v2
fix(deps): update dependency @mdx-js/loader to v3
fix(deps): update dependency maplibre-gl to v4
fix(deps): update dependency react-type-animation to v3
fix(deps): update dependency swr to v2
fix(deps): update dependency use-debounce to v10
🔐 Create all rate-limited PRs at once 🔐

Open

These updates have all been created already. Click a checkbox below to force a retry/rebase of any.

Detected dependencies

github-actions

.github/workflows/build.yml

actions/checkout v3

actions/setup-node v3

npm

package.json

@headlessui/react 1.6.6

@mdx-js/loader 2.1.2

@next/mdx 12.2.0

@supabase/supabase-js 1.35.4

classnames 2.3.1

d3 7.6.1

just-snake-case 3.0.1

maplibre-gl 2.1.9

react 17.0.2

react-cool-dimensions 2.0.7

react-dom 17.0.2

react-type-animation ^1.1.3

swr 1.3.0

use-debounce 8.0.1

@storybook/addon-actions 6.5.13

@storybook/addon-essentials 6.5.13

@storybook/addon-links 6.5.13

@storybook/addon-postcss 2.0.0

@storybook/react 6.5.13

@tailwindcss/typography 0.5.8

@testing-library/jest-dom 5.16.5

@testing-library/react 12.1.5

@testing-library/user-event 14.4.3

@types/d3 7.4.0

@types/jest 27.5.2

@types/node 20.10.6

@types/react 17.0.52

@types/react-dom 17.0.18

@types/react-type-animation 1.1.1

@typescript-eslint/eslint-plugin 4.33.0

@typescript-eslint/parser 4.33.0

autoprefixer 10.4.13

babel-jest 27.5.1

babel-loader 8.3.0

commitizen 4.2.5

eslint 7.32.0

eslint-config-next 12.3.3

eslint-config-prettier 8.5.0

eslint-import-resolver-typescript 2.7.1

eslint-plugin-import 2.26.0

eslint-plugin-jsx-a11y 6.6.1

eslint-plugin-prettier 4.2.1

eslint-plugin-react 7.31.11

eslint-plugin-react-hooks 4.6.0

husky 7.0.4

identity-obj-proxy 3.0.0

jest 27.5.1

jest-fetch-mock 3.0.3

jest-watch-typeahead 1.1.0

lint-staged 12.5.0

next 12.3.3

postcss 8.4.19

prettier 2.7.1

storybook-addon-next-router 3.1.1

storybook-css-modules-preset 1.1.1

tailwindcss 3.2.4

tsconfig-paths-webpack-plugin 3.5.2

typescript 4.9.3

nvm

.nvmrc

node v20.10.0

Check this box to trigger a request for Renovate to run again on this repository

Use filter icon on mobile screens

On mobile the filters are taking a considerable portion of the screen, thus leaving less space for the visualisation itself. We might want to find another solution here, such as having a button with a filter icon that, when clicked, opens a menu with selects and switches.

Improve mobile breadcrumbs path

On mobile, the breadcrumbs path is often cropped so that we can not see the last level we're in and usually only the first depth (see screenshot).

It would be ideal if we could only show the last level in this breadcrumb. Maybe the display for small and large screens could even be different.

Search feature: Export search result as CSV or JSON

JSON is easy
CSV is relatively easy
At first limited to the max rows visible on the page

List view not always in sync with treemap depth

This only happens navigating up from depth 1 to depth 0. This is probably because the mapTopicDepthToColumn util doesn't handle a depth of 0.

API feature

Should we make our "cleaned" data available as an API endpoint? Is there a value to this?

Add more columns to list view

At the moment we have title, main group, district, and amount. This leads to (seemingly) duplicates sometimes:

If we would add the columns oberfunktions_bezeichnung and funktions_bezeichnung the differences would become clear. However then we have to deal with space issues because we tend to have long values in these columns.

Show total result size instead of 500+ in full text search page

Show loading feedback when treemap is loading

At the moment we have quite long loading times. As a first step we could add a loading bar like in Stadtpuls.

But ideally we should improve the loading times.

Improve Info-Page and FAQ

More content should be added to the Info Page. The FAQ page contains only a few entries so far. Here a brainstorming and an elaboration of further questions and texts should be done.

Header und Footer ohne Seitenabstände, Logos im Footer ein bisschen eng

In Chrome auf Android in sehr kleine Devices.

Add information about full text search to README

At least some basic info which API we are using, sorting/relevance of results, etc.

Create structure for home and FAQ

Add routes and menu. Also merge Lisa's PR with example content.

Add more infos to README and issues (viz repo)

For README:

add where the data comes from (Supabase and possible alternatives)
rework the "Getting started" guide
basic information where to find the important components
which pages exist
(how to change texts)

Also check if the code needs refactoring to make it a bit clearer for future work.

For issues:

add more context to currently existing issues
add issues for future feature ideas
more testing as an issue?

Check and change logos in Footer

Refactor some of the code

Some parts were done really quick and dirty. Let's try refactoring these a bit so that the code remains understandable in the future and for others.

Treemap feature: "Einzeplan"/"Gruppen"/"Funktion" Selector

A feature could be added to the treemap to allow a dropdown to select whether the levels shown should be based on the function, on the groups, or on the individual plans.

Tree map feature: Add historic data for years before 2022

Make a table per year
Load data into supabase
Add filter in UI
Load the relevant data
In search, add a Year UI filter

berlin / haushaltsdaten Goto Github PK

haushaltsdaten's Introduction

Berliner Haushaltsdaten - Dataviz

The data

Tech stack

Project structure

Pages

Components

Texts

Getting started

Requirements

Node.js

Supabase

Installation

Workflow

Deployment

Page analytics

Contributors

Content Licencing

Credits

Special thanks

haushaltsdaten's People

Contributors

Stargazers

Watchers

Forkers

haushaltsdaten's Issues

What do we want?

Idea for how this could work:

Rate-Limited

Open

Detected dependencies

For README:

For issues:

Recommend Projects

Recommend Topics

Recommend Org