大家好,又见面了,我是你们的朋友全栈君。
工作中需要用到将从数据库中下载的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)
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/131270.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...