【Python】基础文档处理

# -*- coding: utf-8 -*-
"""
Created on Thu Sep 21 11:37:48 2023

@author: UFOTO
"""

import pandas as pd
df2 = pd.read_excel(r'D:\文件\2023年第二季度\报表\变现分.xlsx')
# 重命名变量名
df2.rename(columns={'日期':'date', '应用' :'application', '国家':'country', '来源':'region', '新老用户':'users'}, inplace=True)

#删除最后一行的汇总数据
df2 = df2.drop(len(df2)-1)
#取df2表的前6列
df2_origin = df2.iloc[:, 0:6]

#处理插屏格式
df2_int = pd.DataFrame()
#提取出带int的3列
df2_int1 = df2.loc[:, ['Int ARPU', 'Int IPU', 'Int Ecpm']]
#增加一列，列名为广告类型
df2_int1['广告类型']="int"
#把df2_origin和df2_int1两个表横向合并，axis=1为横向，axis=0为默认纵向
df2_intnew = pd.concat([df2_origin,df2_int1], axis=1)
#把广告类型这一列，放到表格的最后一列
df2_del = df2_intnew.pop('广告类型')
df2_intnew.insert(loc= 6 , column= '广告类型', value= df2_del)
#重命名为ARPU、IPU、Ecpm
df2_intnew.rename(columns={'Int ARPU':'ARPU', 'Int IPU' :'IPU', 'Int Ecpm':'Ecpm'}, inplace=True)


df2_rv = pd.DataFrame()
df2_rv1 = df2.loc[:, ['RV ARPU', 'RV IPU', 'RV Ecpm']]
df2_rv1['广告类型']="rv"
df2_rvnew = pd.concat([df2_origin,df2_rv1], axis=1)
df2_del = df2_rvnew.pop('广告类型')
df2_rvnew.insert(loc= 6 , column= '广告类型', value= df2_del)
df2_rvnew.rename(columns={'RV ARPU':'ARPU', 'RV IPU' :'IPU', 'RV Ecpm':'Ecpm'}, inplace=True)

df2_splash = pd.DataFrame()
df2_splash1 = df2.loc[:, ['Splash ARPU', 'Splash IPU', 'Splash Ecpm']]
df2_splash1['广告类型']="splash"
df2_splashnew = pd.concat([df2_origin,df2_splash1], axis=1)
df2_del = df2_splashnew.pop('广告类型')
df2_splashnew.insert(loc= 6 , column= '广告类型', value= df2_del)
df2_splashnew.rename(columns={'Splash ARPU':'ARPU', 'Splash IPU' :'IPU', 'Splash Ecpm':'Ecpm'}, inplace=True)


df2_mrec = pd.DataFrame()
df2_mrec1 = df2.loc[:, ['MREC ARPU', 'MREC IPU', 'MREC Ecpm']]
df2_mrec1['广告类型']="MREC"
df2_mrecnew = pd.concat([df2_origin,df2_mrec1], axis=1)
df2_del = df2_mrecnew.pop('广告类型')
df2_mrecnew.insert(loc= 6 , column= '广告类型', value= df2_del)
df2_mrecnew.rename(columns={'MREC ARPU':'ARPU', 'MREC IPU' :'IPU', 'MREC Ecpm':'Ecpm'}, inplace=True)

df2_banner = pd.DataFrame()
df2_banner1 = df2.loc[:, ['Banner ARPU', 'Banner IPU', 'Banner Ecpm']]
df2_banner1['广告类型']="Banner"
df2_bannernew = pd.concat([df2_origin,df2_banner1], axis=1)
df2_del = df2_bannernew.pop('广告类型')
df2_bannernew.insert(loc= 6 , column= '广告类型', value= df2_del)
df2_bannernew.rename(columns={'Banner ARPU':'ARPU', 'Banner IPU' :'IPU', 'Banner Ecpm':'Ecpm'}, inplace=True)

#把上面的所有格式的表格，纵向合并
df2_new = pd.concat([df2_intnew,df2_rvnew,df2_splashnew,df2_mrecnew,df2_bannernew], axis=0)

#重命名文件
df2_new.to_excel(r'D:\文件\2023年第二季度\报表\变现分-改.xlsx', sheet_name='sheet1', index=False)