excel转json操作

工作中需要用到将从数据库中下载的excel每行数据转成json文件，用于规则回溯,参考网上资料，通过以下代码可实现mark记录一下。

核心思想：将每条数据写成字典dict形式，再利用json.dumps转成json

核心代码：

import json
# 设定转出的json数据类型,可根据需要调整
class NpEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, np.integer):
            return str(obj)
        elif isinstance(obj, np.floating):
            return str(obj)
        elif isinstance(obj, np.ndarray):
            return obj.tolist()
        else:
            return super(NpEncoder, self).default(obj)
# 将dict转json            
test_dict = {'id':'1234','car':['A','B'],'city':'beijing'}
b = json.dumps(test_dict,ensure_ascii=False,cls=NpEncoder)
# 写出文件 要有encoding='utf-8',要不容易报错
with open(r'C:\Users\Administrator\Desktop\test.json','w',encoding='utf-8') as f_w:
    f_w.write(b)

工作用代码：

数据列名：

代码：

import pandas as pd
import numpy as np
import json
import datetime

# 导入数据
# 由于phone2有缺失值，如果不加converters ={'phone2':str}，导致读入会变成float形式，导致有值的手机号码后会加点0，如13812341234.0
data= pd.read_excel(r'C:\Users\Administrator\Desktop\20201229142002.xlsx',converters ={'phone2':str})

# 数据处理
#时间转成datetime 后再转成str，这样导出json后时间格式到时分秒，后面不会加.0
data.apply_submit_time = pd.to_datetime(data.apply_submit_time)
data.apply_submit_time = data.apply_submit_time.astype('str')
#将缺失值填充""空字符，即使nan转json程序不会报错，但是把转好的json放在json格式校正中，会提示错误，所以都填充空字符串。
data.fillna(value="",inplace=True)

# 拆分数据
# 由于导出的数据带有连续人信息，每个联系人一行，如果提供多个连续人，会导致同一进件多条记录，需要将数据做区分
# 将数据分成两部分 联系人人及非联系人
# 第一步：非联系人部分去重，写唯一值
# 第二步：联系人部分，循环写入列表
# 用apply_id 或 transport_id 关联

data_a = data.iloc[:,:52]
data_a.drop_duplicates(inplace=True)
data_contact = data[['old_transport_id','apply_id','contact_name','relation_me','relation_me_desc','telephone','mortgagor_contact_type','mortgagor_contact_type_desc']]

# 转json用到的格式，可根据需求更改，这里都转成str形式
class NpEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, np.integer):
            return str(obj)
        elif isinstance(obj, np.floating):
            return str(obj)
        elif isinstance(obj, np.ndarray):
            return obj.tolist()
        else:
            return super(NpEncoder, self).default(obj)


# 具体转换
starttime = datetime.datetime.now()

for i in data.old_transport_id.unique():
    l = data_a[data_a.old_transport_id == i].reset_index()
    transprot_id = l.old_transport_id[0]    # 当文件名
    test_dict = {}
# 层级一级，没有嵌套
    test_dict['name']=l.cust_name[0]
    test_dict['idNumber']=l.certificate_num[0]
    test_dict['mobile']=l.phone1[0]
    test_dict['mobile2']=l.phone2[0]
    test_dict['intoTime']=l.apply_submit_time[0]
    test_dict['submitDeptName']=l.submit_dept_name[0]
    test_dict['submitDeptNo']=l.submit_dept_no[0]
    test_dict['submitDeptDescInfo']=l.submit_dept_desc_info[0]
    test_dict['transport_id']=l.old_transport_id[0]
    test_dict['productType']=l.apply_product[0]
    test_dict['applyId']=l.apply_id[0]
    test_dict['occupationalCode'] = l.POSITION_CODE[0]
 # 公司      
    company_dict = {}

    company_dict['name'] =l.org_name[0]
    company_dict['recruitmentDate'] =l.entry_date[0]
    # 地址
    company_adress_dict = {}
    company_adress_dict['province'] =l.org_province_desc[0]
    company_adress_dict['city'] =l.org_city_desc[0]
    company_adress_dict['district'] =l.org_county_desc[0]
    company_adress_dict['detail'] =l.org_addr[0]

    company_dict['address'] =company_adress_dict
        
    # 电话
    company_phone_dict = {}
    company_phone_dict['phoneNumber'] = l.org_phone[0]
    company_phone_dict['number'] = l.dept_phone[0]
    company_phone_dict['areaCode'] = ""

    company_dict['phoneNumber'] =company_phone_dict
    # 合并到大字典中
    test_dict['company'] = company_dict
# 居住地
    adress_dict={}
    adress_dict['province'] = l.house_province_desc[0]
    adress_dict['city'] = l.house_city_desc[0]
    adress_dict['district'] = l.house_county_desc[0]
    adress_dict['detail'] = l.house_addr[0]
    # 合并到大字典中
    test_dict['address'] = adress_dict
# 户籍地
    domicile_dict={}
    domicile_dict['province'] =l.census_province_desc[0]
    domicile_dict['city'] =l.census_city_desc[0]
    domicile_dict['district'] =l.census_county_desc[0]
    domicile_dict['detail'] =l.census_addr[0]
    # 合并到大字典汇总
    test_dict['domicile'] = domicile_dict
#车辆信息
    car_dict={}
    car_dict['vehicleChassisNumber'] = l.car_no[0]
    car_dict['carFactoryDate'] = l.car_factory_date[0]
    car_dict['carRegister'] = l.car_register[0]
    car_dict['recentlyReplacementDate'] = l.recently_replacement_date[0]
    car_dict['carBrandRemark'] = l.car_brand_remark[0]
    car_dict['carBrand'] = l.car_brand_desc[0]
    car_dict['carSeriesRemark'] = l.car_series_remark[0]
    car_dict['carSeries'] = l.car_series_desc[0]

    # 合并到大字典中
    test_dict['car'] = car_dict
# 联系人信息
    df = data_contact[data_contact.old_transport_id == i]
    contact_list=[]
    for j in range(df.shape[0]): 
            s = df.iloc[j,:]
            contact_dict = {}
            contact_dict['borrowerRelation'] = s.relation_me
            contact_dict['name'] = s.contact_name
            contact_dict['mobile'] = s.telephone
            contact_dict['relationship'] = s.mortgagor_contact_type_desc
            contact_dict['borrowerRelationDesc'] = s.relation_me_desc
            contact_list.append(contact_dict)
    # 合并到大字典中
    test_dict['contacts'] = contact_list
    # 写出  -----------重要---------------------------------
    b = json.dumps(test_dict,ensure_ascii=False,cls=NpEncoder)
    with open(r'C:\Users\Administrator\Desktop\json\{}.json'.format(transprot_id),'w',encoding='utf-8') as f_w:
        f_w.write(b)
        
endtime = datetime.datetime.now()  
print (endtime - starttime)