%%time
import pandas as pd
import numpy as np
df = pd.read_excel("N186数据源.xlsx")
df['身份证']=df['身份证'].apply(lambda x:"|"+str(x))
df.head(2)
CPU times: total: 46.9 ms
Wall time: 41.9 ms
| 期间 | 科室编号 | 科室名称 | 员工编号 | 姓名 | 身份证 | 医保 | 养保编外 | 养保编内 | 公积金 | 失保 | 职业年金 | 医疗互助 | 代缴公积金 | 补缴公积金 | 代缴医保 | 代缴大病互助 |
---|
0 | 201601 | 601 | 办公室 | 101 | 小白01 | |432801199903094020 | 68.75 | NaN | 103.12 | 412 | 34.37 | NaN | 50 | 412 | NaN | NaN | NaN |
---|
1 | 201601 | 602 | 办公室 | 102 | 小白02 | |432801199903094021 | 69.75 | NaN | 104.12 | 413 | 35.37 | NaN | 51 | 413 | NaN | NaN | NaN |
---|
df_zong=pd.pivot_table(df, index=["科室编号","科室名称","员工编号","姓名","身份证"], values=["医保","养保编外","养保编内","公积金","失保","职业年金","医疗互助","代缴公积金","补缴公积金","代缴医保","代缴大病互助"],
margins=False, aggfunc=[np.sum])
df_zong.columns = [('_'.join(col)).replace("sum_","") for col in df_zong.columns.values]
df_zong = df_zong.reset_index()
df_zong.to_excel("N186_五险一金按名字和身份证汇聚(单表).xlsx",index=0)
df_zong.head(2)
| 科室编号 | 科室名称 | 员工编号 | 姓名 | 身份证 | 代缴公积金 | 代缴医保 | 代缴大病互助 | 公积金 | 养保编内 | 养保编外 | 医保 | 医疗互助 | 失保 | 职业年金 | 补缴公积金 |
---|
0 | 601 | 办公室 | 101 | 小白01 | |432801199903094020 | 824 | 0.0 | 0.0 | 824 | 206.24 | 0.0 | 137.5 | 100 | 68.74 | 0.0 | 0.0 |
---|
1 | 602 | 办公室 | 102 | 小白02 | |432801199903094021 | 826 | 0.0 | 0.0 | 826 | 208.24 | 0.0 | 139.5 | 102 | 70.74 | 0.0 | 0.0 |
---|