利用pandas进行数据EXCEL比对(合集、交、补)

Python publisher01 53℃

一、需求:运营工作需要将两个系统A和系统B中两份数据进行比对,A系统为外部系统无API接口只能导出EXCEL,B系统为自有系统可以进行数据库连接

主要思路:对导出数据进行清洗,生成df1,利用数据连接生成df2,对两者进行比对

#将符合条件的某列变为相反数,利用loc函数

df.loc[df[‘交易类型’]== ‘单位汇总(出)’,’金额(元)’]= -df[‘金额(元)’]

#对数据里进行筛选,显示某三列,并限定显示某列特定的数据

df= df[[‘卡号’,’交易类型’,’金额(元)’,’交易时间’]][df[‘交易类型’].isin([‘单位汇总(出)’,’单位分配(进)’])]

#对时间列进行筛选,需要先转换成日期类型,然后设定index索引,然后来筛选

df[‘交易时间’]= pd.to_datetime(df[‘交易时间’])#将数据类型转换为日期类型

# yesterday = (date.today() + timedelta(days=-1)).strftime(“%Y-%m-%d”)

# nowTime = yesterday

nowTime= datetime.now().strftime(‘%Y-%m-%d’)#获取当前时间

df= df.set_index (‘交易时间’)#将列设置为index

# df = df.truncate(after =nowTime)

df= df[nowTime]

#对卡号进行分组并对金额进行求和,这种分组求和需要注意学习

df_ex= df.groupby([‘卡号’],as_index=False).sum()

连接数据库

db= pymysql.connect(host=host,user= username,password=db_pwd,db = db_name)

cur= db.cursor()

#对于python中sql语句还有各种引号可以在sql赋值时使用”””,并使用传入参数,而不是硬传入

sql= “””sql %s””” % (s)

cur.execute(sql)

data= cur.fetchall()

#数据库获取的数据为list需要转换成dataframe结构

df_db= pd.DataFrame(list(data))

#转换列名

df_db.rename(columns={0:’卡号’,1:’金额(元)’},inplace=True)

#转换列的数据格式

df_db[‘金额(元)’]= df_db[‘金额(元)’].astype(‘float64’)

比对思路 利用append()函数:df1 df2

df1并df2  去重 为空集 则说明 df1 == df2

如果不为空 说明两则不一样

df1并df2再并df2 去重,得到的集合为 df1包含且不再df2中的集合 如图片中A

df1= df1.append(df2)

df1= df1.append(df2)

df_d= df1.drop_duplicates(subset=[‘卡号’,’金额(元)’],keep=False)#keep 参数表示是否表示保留重复数据,F是一个不保留,T或者不写是保留第一行数据

同理,df2并两次df1,去重,得到的集合为df2包含且不再df1中的集合 图中B

取交集:print(pd.merge(df1,df2,on=[‘name’, ‘age’, ‘sex’]))  途中C

取并集:print(pd.merge(df1,df2,on=[‘name’, ‘age’, ‘sex’], how=’outer’)) 途中 A+B+C

转载请注明:Python量化投资 » 利用pandas进行数据EXCEL比对(合集、交、补)

喜欢 (0)or分享 (0)