0

I have a table (CSV) with statistical data for different neighbourhoods in a city (population according to different age categories).

My problem is that the neighbourhood doesn't have one field per category, but instead they have one row/feature per category in the table (i.e any given neighbourhood has 7 features/rows in the table).

I want to merge these features so each neighbourhood only has one row, to calculate child density. I can't figure out to do this when it's only a table, as the 'merge selected features' is disabled.

I have tried joining the table to a vector layer of the neighbourhoods but a lot of data is then lost. How can I do this?

*Edit: Example of what it looks like (not real data):

id Year Neighbourhood_name Category Gender Amount
1 2019 Emilstorp 0-5 years Male 15
2 2019 Emilstorp 6-15 years Female 25
3 2021 Emilstorp 0-5 years Male 20

And so it goes on. Each neighbourhood has at least seven rows/features, and sometimes from several years. I want all age categories to be in the same row for the year that I'm planning on using.

*Edit:

Attribute table

Error message DB Manager

14
  • Which software are you working with?
    – Erik
    Feb 12 at 13:31
  • Sorry, QGIS (version 3.28.14-Firenze)
    – Klara H
    Feb 12 at 13:37
  • 1
    Hi Klara H! Welcome to GIS StackExchange. Can you share example data? Just edit your post and paste raw CSV text data. It can also be dummy data that illustrates the issue. This would be very helpful in order to give an accurate answer.
    – winnewoerp
    Feb 12 at 14:16
  • 1
    Yes, @aborruso, I want each row to have one field per age category with the neighborhood's respective values for the categories, instead of them being separate rows as they are now. The reason is that I want to add a field for child density in each neighbourhood
    – Klara H
    Feb 13 at 9:19
  • 1
    To be able to aggregate data using multiple attributes you can use the Aggragate processing algorithm. Not having an example dataset available, I cannot go ahead with an example. docs.qgis.org/3.34/en/docs/user_manual/processing_algs/qgis/…
    – pigreco
    Feb 13 at 13:24

4 Answers 4

3

Solution using the Group Stats Plugin

using the data in the table attached to the question as input data.

enter image description here

2

If the number of Categories are known and not too many (to type in manually in the query), you can create a query layer / virtual layer.

Open the DB Manager and execute the query in the Virtual Layers section.

My csv table is named population, with fields named Year, Neighborhood_name, Category, Amount. Add more case when rows for all categories:

SELECT row_number() over() as id,
    "Year", "Neighborhood_name", 
    CASE WHEN "Category" = '0-5 years' THEN sum("Amount") ELSE 0 END ages_0_to_5,
    CASE WHEN "Category" = '6-15 years' THEN sum("Amount") ELSE 0 END ages_6_to_15,
    CASE WHEN "Category" = '16-20 years' THEN sum("Amount") ELSE 0 END ages_16_to_20
    --Add more rows for each Category
FROM population
GROUP BY "Year", "Neighborhood_name"
ORDER BY "Neighborhood_name", "Year"

After you've loaded the table you can right-click and export it to file.

enter image description here

4
  • Thanks. I'm trying to use the DB manager but I get an error message.. I don't think I completely understand how I should tailor your example to my data. I tried pasting a picture here but couldn't. What does "row_number() over () as id" mean? Do I need to add something there? @BERA
    – Klara H
    Feb 14 at 13:36
  • yes, I did change the names to match mine, which is why it's so weird... The error message is "Query execution error on CREATE TEMP VIEW _tview as SELECT row_number() over as id, [..and then a copy of the whole query..] [...] GROUP BY "område": -1 near "CASE": syntax error"
    – Klara H
    Feb 15 at 7:49
  • I have added two screenshots of the query and error message + the table. I have now extracted the data from only 2022 so I don't have the year issue anymore
    – Klara H
    Feb 15 at 12:17
  • thanks! I didn't get the syntax error now. However, now the new table has a lot of missing values and where there are values, they're incorrect.. I don't know whats wrong now :/
    – Klara H
    Feb 15 at 13:48
2

I add also a simple SQL way, using the great duckdb cli.

Running

duckdb --csv -c "PIVOT (
  SELECT
    * EXCLUDE (id, Gender)
  FROM
    read_csv_auto('input.csv')
) ON Category USING SUM(Amount)" >output.csv

you get

Year,Neighbourhood_name,"0-5 years","6-15 years"
2019,Emilstorp,15,25
2021,Emilstorp,40,

I have used this input file:

id,Year,Neighbourhood_name,Category,Gender,Amount
1,2019,Emilstorp,0-5 years,Male,15
2,2019,Emilstorp,6-15 years,Female,25
3,2021,Emilstorp,0-5 years,Male,20
4,2021,Emilstorp,0-5 years,Female,20
1

You could start from reshape the CSV.

If the input is in example this

id,Year,Neighbourhood_name,Category,Gender,Amount
1,2019,Emilstorp,0-5 years,Male,15
2,2019,Emilstorp,6-15 years,Female,25
3,2021,Emilstorp,0-5 years,Male,20
4,2021,Emilstorp,0-5 years,Female,20

Using the great Miller, you could run

mlrgo --csv cut -x -f id then \
stats1 -a sum -f Amount -g Year,"Neighbourhood_name",Category then \
reshape -s Category,Amount_sum then \
unsparsify input.csv >output.csv

to get

Year Neighbourhood_name 0-5 years 6-15 years
2019 Emilstorp 15 25
2021 Emilstorp 40

Some notes:

  • cut -x -f id to remove the "id" field (manual)
  • stats1 -a sum -f Amount -g Year,"Neighbourhood_name",Category to sum the Amount grouping by Year, Neighbourhood_name and Category
  • reshape -s Category,Amount, to reshape the table from long to wide (manual)
  • unsparsify to have field names absent in a given record but present in others, fills in a value (manual)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.