How to add nested budgetdata into openspendending next?


#1

Hello everyone I am responsible for finances with my local students parliament(german homepage) and want to make fiscal data more transparent.
I already played around with openspendingjs and made this visualisation of our budget

openspending next looks really good so far but I don’t understand how to tell open spending that there are accounts that have sub accounts.I am especially interested in having a treemap

for example there is “goods and services” which is split up into several accounts , but there are also other accounts that don’t have subaccounts

my csv table used to look like this
table1
|account id |partent account | Account name | Amount | year |
|--------------|--------- ---------- | ------------------ |------------|------- |
|4.1| goods and services | phone and internet | 5000|2016
|4.1.1| phone and internet | phone | 2000|2016
|4.1.2| phone and internet | internet | 3000|2016
|4.2|goods and services | food | 1200|2016

then I did something like this
table2
|account id |top level | level 2 |level 3| Amount | year |
|--------------|--------- ---------- | ------------------ |------------|------- |
|4.1.1| goods and services| phone and internet | phone | 2000|2016
|4.1.2| goods and services| phone and internet | internet | 2000|2016
|4.2|goods and services | ---------empty-----------| food|1200|2016
|5|elections | ---------empty-----------| —empty-----|8000|2016

table2 somewhat works with openspendingjs but I don’t know how to talk to penspendingnext
in order to understand the file.

also I’d love to add more information as account description and the actual amount spent next to the planned account in the budget.

thank you for your time


#2

Hi @theNivatius,

Full instructions with visual support are available here (remove the spaces :slight_smile: )
https://github.com/VictoriaVlad/docs/blob/master/docs/contributors/ prepare.md
https://github.com/VictoriaVlad/docs/blob/master/docs/contributors/ package.md
https://github.com/VictoriaVlad/docs/blob/master/docs/contributors/ load.md
https://github.com/VictoriaVlad/docs/blob/master/docs/users/ interact.md
https://github.com/VictoriaVlad/docs/blob/master/docs/users/ export.md
https://github.com/VictoriaVlad/docs/blob/master/docs/users/ explore.md

Please note that these are drafts for docs.openspending.org and will be added to the website itself soon.

A quick summary:
Preparing data
Data for upload should meet a minimum set of quality requirements:
The file must have headers on the first row.
There must not be any blank rows.
There must not be any mismatch between the length of a row, and the length of the headers.
Each column must have a consistent “data type” (date columns should contain dates, amount columns should contain numbers without currency signs or names).

Packaging data
After your data source is validated, you can continue to step 2, “Describe your data.”
Assign a DataType to each column.
Click in the box under “DataType” to see the options.
Assign at least a date and a value to your data to continue.
Once you have successfully assigned at least a “Date” and an “Amount” and any other DataType, you can click “Continue” to go to Step 3.

Loading data
The Packager provides a simple user interface to load data into OpenSpending. Follow these steps to load data to OpenSpending:
At step 3 of the OS Packager upload process, “Provide metadata,” include at least a Data Package name and a unique identifier.
As soon as you provide the details about your data, you will get a message saying “Congratulations! Now you can verify your Data Package and download it”:
Click “Continue” to proceed to Step 4 to either “Download” the JSON file or “Publish this Data Package.”
Once your file was published, you can continue to exploring and visualizing your data in OS Viewer, by clicking “Explore and visualize your data now!”
If you want your data to be available to the larger audience, publish it from OS Admin.

Interacting with data
Now that you have opened your data package in OS Viewer, you can interact with the tool to a. explore your data; b. apply filters; c. create a desired visualization; d. download the data package or View Raw Source Data; or e. Analyze the data set in the Data Mine:
At first, you see the visualizations menu and a table preview of the data.
Click on the “Create visualisation and select a view of your choice” to select from five visualization types (Tree Map, Pie Chart, Bubble Tree, Bar Chart or Line Chart), a table or a pivot table.
On the left side of the screen, you will see a menu with “Measures,” “Hierarchies,” and “Filters.” Apply these dimensions to select data for your visualization.
Use the Share visualization button at the upper-right corner to get the visualization link and embed code.

Please let us know of you have any questions, or any of the steps are not clear.


#3

Thanks for your reply @VictoriaVlad1
unfortunatelly you don’t seem to relate to my question too much.

I hoped for the package.md to explain how to do the hierarchy of accounts. unfortunatelly the doc just explains as much as the visual tool does (which is good but not enough for me)

I am pretty sure my accounts are either Economic classification or Functional classification, not sure what the difference is yet. since my account names relate to what use the money should serve I’m leaning to functional.

so I take my csv file and try to map unterkonto (sub-account) to
Display Name (functional-classification ❯ generic ❯ level2 ❯ label)
but I get this error:
Couldn't find a column mapped to the matching 'code' (functional-classification:generic:level2:code)

I’m pretty confused as on how to correctly mark the hierarchical colums.

If I understand the fiscal dataprotocol corretly table 1 should somehow work as well


#4

Hi,

As discussed on the call today, (1) prepare your csv file: make sure you have a code and a display name for every column. (2) at step 2 of the Packager, start with “top level” for the first level of the hierarchy. Map the respective column’s code and display name as “top level”. Continue with level 2, 3, etc.

Also, make sure you map all levels under the same classification, and that id’s for different categories are being able to be grouped, ex. Similar IDs will be summed together.
(1) personel
(1) personel
(2) goods and materials
(2) goods and materials
(2) goods and materials

Please share with us the link to the visualization when you finalize the exercise. Thank you!


#5

Hi Victoria, thank you for taking the time to walk me through everything. :blush:

I’m going ahead trying to explain how to do it for everyone to understand.

account with subaccounts in openspending next

say you want to plan your spending on advertising in each city.

country > state > city
level1 > level2 > level3
###Data
your data should look something like this:

year || level1 id || level1 label|| level2 id || level2 label|| level3 id || level3 label||budget ||
_----------------------------------------------------------------------------------------------------------------
2016|| 1|| USA || 1|| NewYork||1 || New York || 4030
2016|| 1|| USA || 1|| NewYork||2 || Jamestown || 200
2016|| 1|| USA || 2|| Texas ||3 || Dallas || 628
2016|| 2|| Germany || 3|| Hessen||4 || Frankfurt || 628

As you can see each id is only unique inside of a level.

Transcribing your data

when you successfully uploaded your data to next.openspending.org

you will get to the point to transcribe your data
http://next.openspending.org/packager/describe-data

starting with the top level, you go ahead and first select the “level 1 id” column and select:

functional-classification ❯ Non-standard Functional Classification ❯ top level ❯ Unique Identifier ❯ Complete Identifier

then go to your " level1 label" column and you choose:

functional-classification ❯ Non-standard Functional Classification ❯ top level ❯Display Name

the same with level 2

functional-classification ❯ Non-standard Functional Classification ❯2nd Classification Level❯ Unique Identifier ❯ Complete Identifier

and you repeat this for all your levels with id and label.

dont forget to map date and amount as well.

Date

Date (date) ❯ Fiscal year

Amount

❯Amount (value)
choose a currency and make sure your decimal seperator is correct

here is the result
not perefect , but a good start