6 ms·

Someone should throw the sqlite database up with Datasette, probably some very interesting faceted search that could be had here

simonw 6y ago
If you want to try it out on a Mac laptop you can do the following: brew install datasette # if you don't have it already wget https://gitlab.com/shanghai-ccp-member-db/shanghai-ccp-member-db/-/raw/master/shanghai-ccp-member.db datasette shanghai-ccp-member.db --setting sql_time_limit_ms 10000 --setting facet_time_limit_ms 10000 I recommend bumping up those default time limits because the database has almost 2,000,000 rows in and faceting will time out if you don't increase them. You can easily facet by sex, ethnicity, hometown and organization using the cog icon on each column. The data is all in Chinese so you'll need to run it through a translator to see what it means.
- simonw 6y ago
  Also interesting: the latest version of my sqlite-utils CLI tool added a new "sqlite-utils analyze-tables shanghai-ccp-member.db" command which outputs interesting statistics about table columns - documentation here: https://sqlite-utils.readthedocs.io/en/stable/cli.html#analyzing-tables https://sqlite-utils.readthedocs.io/en/stable/cli.html#analy... Here's a partial output from running it against that database file: member.name: (2/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 1072319 Most common: 650: 张伟 621: 张敏 620: 王伟 579: 张磊 507: 王磊 488: 陈洁 479: 张杰 448: 王勇 442: 李伟 434: 张静 member.sex: (3/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2 Most common: 1229406: 男 727325: 女 member.ethnicity: (4/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 51 Most common: 1935764: 汉族 7636: 回族 4588: 满族 2282: 蒙古族 1468: 土家族 954: 壮族 741: 朝鲜族 638: 苗族 351: 白族 319: 侗族 member.hometown: (5/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2875 Most common: 846850: 上海 350361: null 139903: 江苏 80958: 浙江 29935: 上海宝山 29705: 山东 26366: 上海崇明 26120: 安徽 17697: 上海嘉定 14745: 上海南汇 member.organization: (6/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 77453 Most common: 3533: 退休支部 1925: 第一党支部 1796: 机关党支部 1759: 校逾期未归 1552: 退休一支部 1528: 第二党支部 1508: 退休二支部 1443: 退休党支部 1243: 第三党支部 955: 离退休党支部 member.education: (11/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 22 Most common: 589531: 大学 360788: 大专 300370: 初中 230117: 普通高中 161803: 硕士研究生 158041: 中等专科 93859: 小学 25460: 博士研究生 14729: 技工学校 8894: 其他
  - dgrin91 6y ago
    All of that looks like it can be very interesting, unfortunately I can't read it.
    - simonw 6y ago
      I got pretty convincing looking results by pasting individual lines into Google Translate.
  - izgzhen 6y ago
    Men/Women ratio is about 2/1; About 99% are Han people (which is not surprising considering it is Shanghai); Most common org: retirement branch. Actually, so many of them has "退休" in it which means "retirement". Can it tell us something about the "age" distribution as well? Education: most common - Bachelor's degree. Secondly common is a professional university degree, which is considered a second-class compared to the Bachelor's degree. "93859" of them only graduates from primary school, which should overlap highly with older people.
    - yorwba 6y ago
      > Actually, so many of them has "退休" in it which means "retirement". Actually, there are 77453 distinct values for the "organization" field, so retired people are only the biggest group because employed people are divided according to where they are employed. As for the age distribution, the OP on LIHKG posted it in a reply: https://lih.kg/qwNiyuX https://lih.kg/qwNiyuX
  - Zarel 6y ago
    Translation: member.name: (2/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 1072319 Most common: 650: 张伟 - Zhang Wei (Grand) 621: 张敏 - Zhang Ming (Agile) 620: 王伟 - Wang Wei (Grand) 579: 张磊 - Zhang Lei (Open) 507: 王磊 - Wang Lei (Open) 488: 陈洁 - Chen Jie (Clean) 479: 张杰 - Zhang Jie (Outstanding) 448: 王勇 - Wang Yong (Brave) 442: 李伟 - Li Wei (Grand) 434: 张静 - Zhang Jing (Calm) I've annotated the names with their meanings because I find them pretty interesting. In Chinese, names should not overlap (unlike most of the rest of the world, where names are chosen by picking from a list of common names, in China, names are chosen by picking a unique sequence of characters not used by anyone else you're aware of). These names are all one-character long, rather than the standard two characters, and the surnames are some of the most common in China, so they end up having nonzero overlap. member.sex: (3/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2 Most common: 1229406: 男 - male 727325: 女 - female member.ethnicity: (4/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 51 Most common: 1935764: 汉族 - Han 7636: 回族 - Hui 4588: 满族 - Manchu 2282: 蒙古族 - Mongol 1468: 土家族 - Tujia 954: 壮族 - Zhuang 741: 朝鲜族 - Korean 638: 苗族 - Miao 351: 白族 - Bai 319: 侗族 - Dong member.hometown: (5/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2875 Most common: 846850: 上海 - Shanghai 350361: null 139903: 江苏 - Jiangsu 80958: 浙江 - Zhejiang 29935: 上海宝山 - Shanghai Baoshan district 29705: 山东 - Shandong 26366: 上海崇明 - Shanghai Chongming county 26120: 安徽 - Anhui 17697: 上海嘉定 - Shanghai Jiading district 14745: 上海南汇 - Shanghai Nanhui district member.organization: (6/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 77453 Most common: 3533: 退休支部 - retirement branch 1925: 第一党支部 - first party branch 1796: 机关党支部 - party branch 1759: 校逾期未归 - [not completed] 1552: 退休一支部 - retirement 1 branch 1528: 第二党支部 - second party branch 1508: 退休二支部 - retirement 2 branch 1443: 退休党支部 - retirement party branch 1243: 第三党支部 - third party branch 955: 离退休党支部 - ending retirement party branch member.education: (11/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 22 Most common: 589531: 大学 - university 360788: 大专 - junior college 300370: 初中 - in progress 230117: 普通高中 - regular high school 161803: 硕士研究生 - master's student 158041: 中等专科 - specialized secondary school 93859: 小学 - elementary 25460: 博士研究生 - doctoral student 14729: 技工学校 - vocational high school 8894: 其他 - other
hertzrat 6y ago
Doing that might put you at risk of retaliation by China... crazy to have to worry about that

simonw 6y ago

If you want to try it out on a Mac laptop you can do the following: brew install datasette # if you don't have it already wget https://gitlab.com/shanghai-ccp-member-db/shanghai-ccp-member-db/-/raw/master/shanghai-ccp-member.db datasette shanghai-ccp-member.db --setting sql_time_limit_ms 10000 --setting facet_time_limit_ms 10000 I recommend bumping up those default time limits because the database has almost 2,000,000 rows in and faceting will time out if you don't increase them. You can easily facet by sex, ethnicity, hometown and organization using the cog icon on each column. The data is all in Chinese so you'll need to run it through a translator to see what it means.

simonw 6y ago
Also interesting: the latest version of my sqlite-utils CLI tool added a new "sqlite-utils analyze-tables shanghai-ccp-member.db" command which outputs interesting statistics about table columns - documentation here: https://sqlite-utils.readthedocs.io/en/stable/cli.html#analyzing-tables https://sqlite-utils.readthedocs.io/en/stable/cli.html#analy... Here's a partial output from running it against that database file: member.name: (2/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 1072319 Most common: 650: 张伟 621: 张敏 620: 王伟 579: 张磊 507: 王磊 488: 陈洁 479: 张杰 448: 王勇 442: 李伟 434: 张静 member.sex: (3/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2 Most common: 1229406: 男 727325: 女 member.ethnicity: (4/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 51 Most common: 1935764: 汉族 7636: 回族 4588: 满族 2282: 蒙古族 1468: 土家族 954: 壮族 741: 朝鲜族 638: 苗族 351: 白族 319: 侗族 member.hometown: (5/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2875 Most common: 846850: 上海 350361: null 139903: 江苏 80958: 浙江 29935: 上海宝山 29705: 山东 26366: 上海崇明 26120: 安徽 17697: 上海嘉定 14745: 上海南汇 member.organization: (6/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 77453 Most common: 3533: 退休支部 1925: 第一党支部 1796: 机关党支部 1759: 校逾期未归 1552: 退休一支部 1528: 第二党支部 1508: 退休二支部 1443: 退休党支部 1243: 第三党支部 955: 离退休党支部 member.education: (11/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 22 Most common: 589531: 大学 360788: 大专 300370: 初中 230117: 普通高中 161803: 硕士研究生 158041: 中等专科 93859: 小学 25460: 博士研究生 14729: 技工学校 8894: 其他
- dgrin91 6y ago
  All of that looks like it can be very interesting, unfortunately I can't read it.
  - simonw 6y ago
    I got pretty convincing looking results by pasting individual lines into Google Translate.
- izgzhen 6y ago
  Men/Women ratio is about 2/1; About 99% are Han people (which is not surprising considering it is Shanghai); Most common org: retirement branch. Actually, so many of them has "退休" in it which means "retirement". Can it tell us something about the "age" distribution as well? Education: most common - Bachelor's degree. Secondly common is a professional university degree, which is considered a second-class compared to the Bachelor's degree. "93859" of them only graduates from primary school, which should overlap highly with older people.
  - yorwba 6y ago
    > Actually, so many of them has "退休" in it which means "retirement". Actually, there are 77453 distinct values for the "organization" field, so retired people are only the biggest group because employed people are divided according to where they are employed. As for the age distribution, the OP on LIHKG posted it in a reply: https://lih.kg/qwNiyuX https://lih.kg/qwNiyuX
- Zarel 6y ago
  Translation: member.name: (2/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 1072319 Most common: 650: 张伟 - Zhang Wei (Grand) 621: 张敏 - Zhang Ming (Agile) 620: 王伟 - Wang Wei (Grand) 579: 张磊 - Zhang Lei (Open) 507: 王磊 - Wang Lei (Open) 488: 陈洁 - Chen Jie (Clean) 479: 张杰 - Zhang Jie (Outstanding) 448: 王勇 - Wang Yong (Brave) 442: 李伟 - Li Wei (Grand) 434: 张静 - Zhang Jing (Calm) I've annotated the names with their meanings because I find them pretty interesting. In Chinese, names should not overlap (unlike most of the rest of the world, where names are chosen by picking from a list of common names, in China, names are chosen by picking a unique sequence of characters not used by anyone else you're aware of). These names are all one-character long, rather than the standard two characters, and the surnames are some of the most common in China, so they end up having nonzero overlap. member.sex: (3/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2 Most common: 1229406: 男 - male 727325: 女 - female member.ethnicity: (4/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 51 Most common: 1935764: 汉族 - Han 7636: 回族 - Hui 4588: 满族 - Manchu 2282: 蒙古族 - Mongol 1468: 土家族 - Tujia 954: 壮族 - Zhuang 741: 朝鲜族 - Korean 638: 苗族 - Miao 351: 白族 - Bai 319: 侗族 - Dong member.hometown: (5/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 2875 Most common: 846850: 上海 - Shanghai 350361: null 139903: 江苏 - Jiangsu 80958: 浙江 - Zhejiang 29935: 上海宝山 - Shanghai Baoshan district 29705: 山东 - Shandong 26366: 上海崇明 - Shanghai Chongming county 26120: 安徽 - Anhui 17697: 上海嘉定 - Shanghai Jiading district 14745: 上海南汇 - Shanghai Nanhui district member.organization: (6/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 77453 Most common: 3533: 退休支部 - retirement branch 1925: 第一党支部 - first party branch 1796: 机关党支部 - party branch 1759: 校逾期未归 - [not completed] 1552: 退休一支部 - retirement 1 branch 1528: 第二党支部 - second party branch 1508: 退休二支部 - retirement 2 branch 1443: 退休党支部 - retirement party branch 1243: 第三党支部 - third party branch 955: 离退休党支部 - ending retirement party branch member.education: (11/11) Total rows: 1956731 Null rows: 0 Blank rows: 0 Distinct values: 22 Most common: 589531: 大学 - university 360788: 大专 - junior college 300370: 初中 - in progress 230117: 普通高中 - regular high school 161803: 硕士研究生 - master's student 158041: 中等专科 - specialized secondary school 93859: 小学 - elementary 25460: 博士研究生 - doctoral student 14729: 技工学校 - vocational high school 8894: 其他 - other

hertzrat 6y ago

Doing that might put you at risk of retaliation by China... crazy to have to worry about that