Parsing the Infamous Japanese Postal CSV (2020)

解析臭名昭著的日本邮政 CSV 文件 (2020)

Parsing the Infamous Japanese Postal CSV. This post is part of collections on Code and Computers, Projects, and Japanese Language Technology. Late last year I released posuto, a package presenting Japanese postal code data in an easy-to-use format. It’s based on data released by Japan Post, which is infamous for being widely used but hard to parse. 解析臭名昭著的日本邮政 CSV 文件。本文收录于“代码与计算机”、“项目”以及“日语技术”系列。去年年底，我发布了 posuto，这是一个以易用格式呈现日本邮政编码数据的软件包。它基于日本邮政发布的数据，该数据因应用广泛但难以解析而“臭名昭著”。

This adorable character by Irasutoya is cute, but the raw postal CSV data is not. I first became aware of the postal data when I entered my postal code in an online form and it auto-completed my address as “XXX-borough (except the following buildings)”. I had no idea what that parenthetical was referring to, so I looked for a common source of postal data, found the CSV, and found the issue. Irasutoya 的这个角色很可爱，但原始的邮政 CSV 数据却一点也不可爱。我第一次注意到这些邮政数据，是因为我在在线表单中输入邮政编码时，它自动补全的地址显示为“XXX区（以下建筑物除外）”。我完全不知道括号里指的是什么，于是我寻找了邮政数据的通用来源，找到了那个 CSV 文件，并发现了问题所在。

It turns out the CSV file contains parenthetical notes for anyone reading the CSV file and makes reference to the order of the rows. This causes problems. The data is mainly useful one row at a time, where the parenthetical is meaningless. Since CSV is a field-delimited format, there’s also no need for parentheticals - you could just add a note field. This is only one of many issues with ken_all.csv. 原来，CSV 文件中包含了一些供阅读者参考的括号注释，并引用了行顺序。这导致了问题。这些数据通常是逐行使用的，而括号里的内容在单行中毫无意义。由于 CSV 是一种字段分隔格式，根本不需要括号——完全可以添加一个专门的备注字段。这只是 ken_all.csv 众多问题中的一个。

You can find people complaining about it regularly on Twitter, and there was even briefly a blog just collecting posts from all over the web about it. A particularly amusing tweet describes people who expect computers to bend to the will of humans being punished in Hell by having to parse ken_all.csv forever. 你可以在 Twitter 上经常看到人们对此抱怨，甚至曾有一个博客专门收集全网关于此事的吐槽。一条特别有趣的推文写道：那些期望计算机屈从于人类意志的人，在地狱里受到的惩罚就是必须永远解析 ken_all.csv。

The README for the file explains that lines with overly long fields will be broken up into multiple lines. Specifically, if the neighborhood name is over 38 characters, or if the half-width katakana pronunciation field is over 76 characters, the line will be split into two lines. The overly-long neighborhood field will be continued and all other fields will be duplicated. 该文件的 README 说明，字段过长的行会被拆分成多行。具体来说，如果街区名称超过 38 个字符，或者半角片假名发音字段超过 76 个字符，该行就会被拆分为两行。过长的街区字段会延续到下一行，而所有其他字段则会被复制。

This is an abbreviated sample of what that looks like: 这是一个简化的示例：

12345,Tokyo,Minato,This place name is really
12345,Tokyo,Minato,very long it didn't fit in
12345,Tokyo,Minato,a single line so we had to
12345,Tokyo,Minato,split it

The motivation for this is not explained. Maybe there was a fixed-width buffer for storing a line somewhere thirty years ago. I used to process CSV and other files from hundreds of different providers at an old job and I saw many horrors, but I’ve never seen this particular formatting choice anywhere else. It should also be noted that while the length limits are as stated, the location where line breaks are inserted in long lines appears random, occurring neither at the character limit nor at normal word boundaries. 这种做法的原因并未说明。也许是三十年前某个地方存在用于存储行的固定宽度缓冲区。我以前的工作曾处理过来自数百个不同供应商的 CSV 和其他文件，见过许多“惨状”，但从未在其他地方见过这种特殊的格式选择。还需要指出的是，虽然长度限制如上所述，但长行中插入换行符的位置似乎是随机的，既不在字符限制处，也不在正常的单词边界处。

It’s worth noting not all the issues with the CSV are inherently technical; postal codes are always complicated. The postal code with the most rows in the CSV - a stunning 66 - is 〒452-0961, which refers to the Haruhi region of Kiyosu City in Aichi Prefecture. This has that many lines because every neighborhood gets a separate line. (This particular case may be related to Haruhi having been the smallest town by area in Japan from 2006 until 2009, when it was incorporated into Kiyosu City.) 值得注意的是，CSV 的所有问题并非都是技术性的；邮政编码本身就很复杂。CSV 中行数最多的邮政编码是 〒452-0961，高达 66 行，它指的是爱知县清须市的春日地区。之所以有这么多行，是因为每个街区都占了一行。（这种情况可能与春日町在 2006 年至 2009 年间曾是日本面积最小的城镇有关，后来它被并入了清须市。）

In contrast, the longest continued line, using the line break rules above, is the entry for 〒602-8368 or 〒602-8374, both with eight lines. These are both in one of a few areas in Kyoto that uses a unique, bizarre system of intersection-based addressing. 相比之下，使用上述换行规则的最长连续行是 〒602-8368 或 〒602-8374 的条目，两者都有八行。它们都位于京都少数几个使用独特且怪异的“基于交叉路口寻址”系统的区域之一。

I have used quoted fields here, but the actual CSV doesn’t quote fields and instead uses a different kind of comma. There are other issues. There are catch-all postal codes for many areas, where the neighborhood is given as “except the following”, and the only thing to do is look for that exact string and exclude it. There’s a variety of similar strings, and it’s hard to be sure I’ve caught them all. 我在这里使用了引号字段，但实际的 CSV 并不使用引号，而是使用了一种不同的逗号。还有其他问题。许多地区都有“全包式”邮政编码，其街区被标注为“以下除外”，唯一的方法就是查找该确切字符串并将其排除。类似这样的字符串有很多，很难确定我是否全部捕捉到了。

An example of another comment is 一円. Normally this would mean “one yen”, but it also means “the area surrounding”, and is a note in the CSV that should be removed from neighborhood names, except for exactly one neighborhood in Shiga where that’s actually the name (〒522-0317). 另一个注释的例子是“一円”。通常这意味着“一元”，但它也表示“周边地区”，是 CSV 中应该从街区名称中删除的注释，除非是在滋贺县的某一个街区，那里的名字确实叫这个（〒522-0317）。

There’s also a separate romaji file offered by JP Post. It’s updated less frequently than the main files, is often out of sync, and the provided romaji are extremely low quality. For the moment I’m still providing the data in posuto in the name of consistency, but honestly you should just use cutlet. 日本邮政还提供了一个单独的罗马字文件。它的更新频率低于主文件，经常不同步，而且提供的罗马字质量极低。目前为了保持一致性，我仍然在 posuto 中提供这些数据，但老实说，你应该直接使用 cutlet。

To give an example of bad romaji: 大手町 JAビル -> OTEMACHI JIEIEIBIRU. What’s happening here is that “JA” is being converted to the phonetic reading in Japanese, “ジェイエイ”. Then ジェ, which is written “large ji small e” but pronounced “je”, is being converted to “jie” by treating the small character as though it were large, and the other characters are translated as-is, turning something already in the latin alphabet into alphabet soup. For contrast, cutlet has no problem converting “JAビル” into “JA building” (case handling admittedly needs some work still). Similar issues turn “Roppongi Hills” into “Roppongihiruzu”, and “Sweden Hills” into “Suedenhiruzu”. 举一个糟糕的罗马字例子：大手町 JAビル 被转写为 OTEMACHI JIEIEIBIRU。这里发生的情况是，“JA”被转换成了日语发音“ジェイエイ”。然后，“ジェ”（写作“大 ji 小 e”但发音为“je”）被错误地处理，将小字符当作大字符处理，转换成了“jie”，其他字符则原样翻译，把原本已经是拉丁字母的东西变成了一锅字母乱炖。相比之下，cutlet 可以轻松地将“JAビル”转换为“JA building”（虽然大小写处理确实还需要改进）。类似的问题还会把“Roppongi Hills”变成“Roppongihiruzu”，把“Sweden Hills”变成“Suedenhiruzu”。

Anyway, dealing with the file was a humbling lesson in the amount of complexity it’s possible to pack into one place. I’ve glossed over many details, but you can find them covered in posuto’s README. You can use posuto as a library, or if you’re not using Python, just download the pre-processed JSON and make use of that. If you find a good use for it I’d be delighted to hear about it. Oh, and if you need a Win3.1 or DOS program to copy the data onto an IBM H floppy disk, just check the bottom of JP Post’s page - they’ve got you covered. 总之，处理这个文件是一次谦卑的教训，让我意识到在一个地方竟然可以塞进如此多的复杂性。我略过了许多细节，但你可以在 posuto 的 README 中找到它们。你可以将 posuto 作为库使用，或者如果你不使用 Python，只需下载预处理过的 JSON 文件即可。如果你发现了它的好用途，我很乐意听听。哦，如果你需要一个 Win3.1 或 DOS 程序将数据复制到 IBM H 软盘上，请查看日本邮政页面的底部——他们已经为你准备好了。