0

I have a CSV with census population data and a shapefile with census tracts, a classic example where you would use a table join. For four of the files that i joined, there has been no problem besides poor data quality from the Japanese government (sigh). However for one of my files, the join is not completed, and only the data from one province is properly joined, with the tracts in other provinces not being assigned any new attributes

The problem is not mismatched key-values, as I can manually find the data when searching individual key codes, nor is it a datatype error, as both fields on the CSV and SHP are strings. see photos for more context

Blue indicates population data was properly joined, purple means it was not

Here is a view of the attribute table of the shapefile (2005 Kanto Census tracts) and the CSV (2005 full) As you can see, key codes match up about 1-1 between the two files (with a few exceptions because the Japanese government sucks at making and distributing census data)

On the right is what the features should look like, but instead most of them look like the feature on the left, without any of the population attributes on the bottom

Here is what the log looks like when I run the join. Most of the features are unable to be joined.

[error log4

Running MacOS Catalina 10.15.7 (19H2026)

Does anyone have any ideas on how to more easily access demographic data for Japan? The datasets given by the government are some of the worst I have ever worked with, but I can't find any other place with age data at the same resolution...

I have been stuck on this for so long.

Improve this question
9
  • Are the two key fields comprising of Japanese characters ? Or both have numbers? Also, do you have cases where the key values exist in the 2 files but no match is done?
    – GforGIS
    Commented Aug 24, 2023 at 0:58
  • @GforGIS no, they are both numbers stored as text/strings. Look at images 2 and 3 and you can see an example of them in KEY_CODE. I have gone through the key codes in excel and know for certain that they are exclusively numbers
    – Tronald
    Commented Aug 24, 2023 at 1:42
  • Have you tried isolating an unjoined feature and copy/pasting the join key ID from one layer to the other? Sometimes that helps identify odd characters (it helps me with the Hawai'i datasets, for example).
    – snow-raven
    Commented Aug 24, 2023 at 1:48
  • Second troubleshooting idea: have you tried breaking the spatial file into pieces? Since the unjoined features are largely regionally constrained, I wonder if it could be running into some sort of shortterm memory cap during the processing?
    – snow-raven
    Commented Aug 24, 2023 at 1:49
  • 1
    I think i got it to work, I re-exported the file from excel and somehow it fixed everything. I know it wasn't a problem of encoding because all of my other files @GforGIS didn't have this problem, despite the shapefiles all using Shift_JIS and the CSV files using UTF-8 I'm really annoyed because like this has wasted hours of my time and I still don't even know what caused my problem
    – Tronald
    Commented Aug 24, 2023 at 2:15

1 Answer 1

Reset to default
1

Re-exporting the CSV file from excel somehow fixed everything. I am still unsure what is the cause of this problem, however I think that something went wrong with the CSV and rebuilding it fixed the issue.

This does not exactly resolve this post but does provide a solution at least :|

Improve this answer

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.