Table Join only returns first record from CSV table in QGIS 3.0.3

Question

I have a .csv file that contains instances of hospitalization with diagnosis codes and zip codes and I want to join it to the corresponding polygons in the TIGER 2017 zcta510 shapefile. Every time I attempt to do this by selecting the Joins button in Layer Properties...then +...Join layer is my .csv import...join field is Zip...Target field is GEOID10.

Anyway, it seems to be only importing the initial instance when each zip code should have multiple instances. I am trying to use the symbology tools to create a shaded view of where patients are coming from.

I have a feeling that I'm missing something simple...but I've over-thought it now.

Answer 1

Based on @csk suggestion and what you mentioned in the post it seems like you are doing a one-to-many join (one zip code-to-many records of hospitalizations). Since vector files are organized in such a manner that one record/row represents one feature (zip code boundary in your instance), when you are doing a join you will only get a record of the first instance that is in this zip code. You have two options to join the datasets:

1. Do one-to-many table relationship if you need to preserve info about each hospitalization - but then you wouldn't be able to do a choropleth (Joining multiple records to single feature using QGIS?)
2. Summarize the attributes when joining the tables. Easiest way would probably be to do a pivot table in excel and get the table formatted so that each row is a zip code and columns are summary of instances of hospitalization (i.e. zip code 11000 had 210 hospitalizations in month X). If the hospitalizations have coordinate info beyond the zip code (i.e. GPS coordinates), you can map out the points of hospitalizations and then do a spatial join, with option of summarizing attributes/counts of input points.