假设我们有如下行政区划数据
[
{
"code": "110000000000",
"name": "北京市",
"level": "1",
"pcode": "0"
},
{
"code": "110100000000",
"name": "市辖区",
"level": "2",
"pcode": "110000000000"
},
{
"code": "110101000000",
"name": "东城区",
"level": "3",
"pcode": "110100000000"
},
{
"code": "110101001000",
"name": "东华门街道",
"level": "4",
"pcode": "110101000000"
},
{
"code": "110101001001",
"name": "多福巷社区居委会",
"level": "5",
"pcode": "110101001000"
},
{
"code": "110101001002",
"name": "银闸社区居委会",
"level": "5",
"pcode": "110101001000"
},
{
"code": "110101001005",
"name": "东厂社区居委会",
"level": "5",
"pcode": "110101001000"
}
]
code: 12位,省2位,市2位,县2位,镇3位,村3位
level: 省1,市2,县3,镇4,村5
pcode: 直接父级别的code
我们希望将其变为:
[
{
"level": "1",
"code": "110000000000",
"name": "北京市",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "",
"2_code": "",
"3_name": "",
"3_code": "",
"4_name": "",
"4_code": "",
"5_name": "",
"5_code": ""
},
{
"level": "2",
"code": "110100000000",
"name": "市辖区",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "市辖区",
"2_code": "110100000000",
"3_name": "",
"3_code": "",
"4_name": "",
"4_code": "",
"5_name": "",
"5_code": ""
},
{
"level": "3",
"code": "110101000000",
"name": "东城区",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "市辖区",
"2_code": "110100000000",
"3_name": "东城区",
"3_code": "110101000000",
"4_name": "",
"4_code": "",
"5_name": "",
"5_code": ""
},
{
"level": "4",
"code": "110101001000",
"name": "东华门街道",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "市辖区",
"2_code": "110100000000",
"3_name": "东城区",
"3_code": "110101000000",
"4_name": "东华门街道",
"4_code": "110101001000",
"5_name": "",
"5_code": ""
},
{
"level": "5",
"code": "110101001001",
"name": "多福巷社区居委会",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "市辖区",
"2_code": "110100000000",
"3_name": "东城区",
"3_code": "110101000000",
"4_name": "东华门街道",
"4_code": "110101001000",
"5_name": "多福巷社区居委会",
"5_code": "110101001001"
},
{
"level": "5",
"code": "110101001002",
"name": "银闸社区居委会",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "市辖区",
"2_code": "110100000000",
"3_name": "东城区",
"3_code": "110101000000",
"4_name": "东华门街道",
"4_code": "110101001000",
"5_name": "银闸社区居委会",
"5_code": "110101001002"
},
{
"level": "5",
"code": "110101001005",
"name": "东厂社区居委会",
"1_name": "北京市",
"1_code": "110000000000",
"2_name": "市辖区",
"2_code": "110100000000",
"3_name": "东城区",
"3_code": "110101000000",
"4_name": "东华门街道",
"4_code": "110101001000",
"5_name": "东厂社区居委会",
"5_code": "110101001005"
}
]
由于提供有父级编码,所以可以采用自下而上的递归,子节点向上搜寻到根节点
import pandas as pd
df_origin = pd.read_csv('./area_code_2021.csv', names=['code', 'name', 'level', 'pcode'], dtype=str)
def get_df(item, df_item):
"""
递归补全数据
"""
if item.pcode != '0':
try:
item = list(df_origin.loc[df_origin['code']==item.pcode].itertuples(index=False))[0]
df_item.loc[0, f'{item.level}_code'] = item.code
df_item.loc[0, f'{item.level}_name'] = item.name
get_df(item, df_item)
except:
import pdb
pdb.set_trace()
return df_item
df_province = df_origin.loc[df_origin['level']=='1']
df = df_origin.copy()
for province in df_province.itertuples(index=False):
df_all = pd.DataFrame()
df_item = df.copy()
for item in df_item.loc[df_item['code'].apply(lambda x: x.startswith(province.code[0:2]))].itertuples():
df_item = pd.DataFrame()
# 初始化该条数据
df_item.loc[0, 'level'] = item.level
df_item.loc[0, 'code'] = item.code
df_item.loc[0, 'name'] = item.name
df_item.loc[0, f'{item.level}_name'] = item.name
df_item.loc[0, f'{item.level}_code'] = item.code
# 补全该条数据
df_item = get_df(item, df_item)
# 移除数据
df.drop(item.Index, inplace=True)
# 放入集合中
df_all = df_all.append(df_item)
df_all.to_excel(f'{province.name}.xlsx', index=False)
类似的企业微信API中的获取部门列表接口返回的数据结构与上面的类似,稍作修改就能使用