一、前言
想必工作中,会遇见数据格式不止Excel文件,比如会遇见Json格式的数据,在职场中也应该学会如何去处理,用Python将Json数据存放入Excel中,接下来完成如下步骤即可完成
二、使用步骤
代码如下:
#1.导入需要使用的包
import pandas as pd
import json
#2.读取数据
#GB2312/utf9/ISO-8859-1/GB18030
with open('data.json','r',encoding = "ISO-8859-1")as fp:
datas = json.load(fp)
print('这是文件中的json数据:',datas)
print('这是读取到文件数据的数据类型:', type(datas))
datas_string = json.dumps(datas)
datas_string.replace("'\x80", '')
print(type(datas_string))
print(datas_string)
#3.做数据变量方便后续引用
data = datas['data']
data_records_list = data['records']
#4.原数据储存到列表中,接着映射列名,再用pd.DataFrame展示出
erp_adsList = []
for y in range(len(data_records_list)):
state = data_records_list[y]['state']
groupName = data_records_list[y]['groupName']
servingStatus = data_records_list[y]['servingStatus']
tactic = str(data_records_list[y]['tactic'])
productNum = data_records_list[y]['productNum']
targetingNum = data_records_list[y]['targetingNum']
campaignName = data_records_list[y]['campaignName']
defaultBid = data_records_list[y]['defaultBid']
impressions = data_records_list[y]['impressions']
clicks = data_records_list[y]['clicks']
ctr = data_records_list[y]['ctr']
cost = data_records_list[y]['cost']
cpc = data_records_list[y]['cpc']
adsOrders = data_records_list[y]['adsOrders']
cvr = data_records_list[y]['cvr']
cpa = data_records_list[y]['cpa']
adsSales = data_records_list[y]['adsSales']
acos = data_records_list[y]['acos']
roas = data_records_list[y]['roas']
lists = {'有效': state, '广告组': groupName, '状态': servingStatus,
'投放类型': tactic, '商品':productNum,
'关键词投放': targetingNum, '所属广告组': campaignName,
'每日预算€$': defaultBid, '曝光量': impressions,
'点击量': clicks, 'CTR%': ctr, '花费€$': cost,
'CPC€$': cpc, '广告总订单量': adsOrders,
'CVR%': cvr, 'CPA€$': cpa,
'广告总销售额€$': adsSales,'ACOS%': acos, 'ROAS%': roas}
erp_adsList.append(lists)
erpTotalData = pd.DataFrame(erp_adsList)
erpTotalData
# 5.将数据里的值映射成更明确的含义
state_name = {0:'启用',
1:'暂停'}
servingStatus_name = {'CAMPAIGN_OUT_OF_BUDGET': '广告活动超出预算',
'AD_GROUP_STATUS_ENABLED': '正在投放',
'CAMPAIGN_PAUSED': '广告活动已暂停'}
tactic_name = {'T00030': '-',
'T00020': '商品投放'}
erpTotalData['有效'] = erpTotalData['有效'].map(state_name)
erpTotalData['状态'] = erpTotalData['状态'].map(servingStatus_name)
erpTotalData['投放类型'] = erpTotalData['投放类型'].map(tactic_name)
erpTotalData
# 6.保存到Excel
erpTotalData.to_excel('data.xlsx',sheet_name='数据',index=False)
用Python读处理前后效果如图:
(数据为网上下载的json虚拟数据集,仅供学习数据上的参考使用)
前:
后: