Pandas对Excel操作数据常用方法

一、读取不同sheet中的数据

#!/usr/bin/env python #-*- coding:utf-8 -*- import pandas as pd from pprint import pprint filename = "运营平台商品清单模板_新版0712.xlsx" df = pd.read_excel(filename,encoding='utf8',sheet_name='商品类目') #指定sheet的名称，默认是第一个

二、数据处理

##查看列名和数据类型 print(df.columns) #查看列名 print(df.dtypes) #查看各列数据类型 ##查看指定行列数据 print(df.head(20)) #查看前20行数据 df1=df.loc[:,'奶粉':'喂养用品'] #选择'奶粉'到'喂养用品'列所有数据 ##删除列和行 df=df.drop(['奶粉', '喂养用品', '其他'],axis=1) #删除'奶粉', '喂养用品'和'其他'三列 df_test=df.loc[-(df['phone']=='18612345678')] #删除'phone'列等于'18612345678'的行 ##移除重复数据 df_new=df.drop_duplicates(['奶粉','玩具']) #移除'奶粉'和'玩具'列包含重复值得行，保留第一个 df_new=df.drop_duplicates(['奶粉'],take_last=True )#移除'奶粉'列包含重复值得行，保留最后一个 ##更改列名 df.rename(columns={'洗护用品':'洗漱用品'}, inplace = True) #'洗护用品'列列名改为'洗漱用品'

三、描述性统计

##计算某列变量频数 print(df['奶粉'].unique()) #输出'奶粉'列唯一值 print(df['奶粉'].value_counts()) #输出'奶粉'列各变量出现频数 ##分段统计 bins=[0,10,20,30,40,50,60,70,80,90,100] group_names=['0-10','10-20','20-30','30-40','40-50','50-60','60-70','70-80','80-90','90-100'] cats=pd.cut(df['RH'],bins,labels=group_names) pd.value_counts(cats,sort=False) ##添加一列分组列，做多维频数统计 bins=[0,10,20,30,40,50,60,70,80,90,100] group_names=['0-10','10-20','20-30','30-40','40-50','50-60','60-70','70-80','80-90','90-100'] cats=pd.cut(df['RH'],bins,labels=group_names) df_concat=pd.concat([df,cats],axis=1,ignore_index=True) df_group=df_concat[7].groupby([df_concat[0],df_concat[6],df_concat[7]]) df_fum=df_group.agg('count')

四、缺失值处理

##填充固定值 train_data.fillna(0, inplace=True) #将NaN用零填充 ##填充上下条的数据 df['DC'].fillna(method='pad', inplace=True) #没有值的就用最后一行的值填充 df['DC'].fillna(0, inplace=True) #将NaN用零填充 df['DC'].fillna(method='bfill', inplace=True) #没有值的就用NaN填充 #更多请参考：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html

五、筛选

##条件筛选loc df_sel=df.loc[(df['month']=='aug') & (df['DC']>=600)] #筛选month列等于aug且DC列大于600的所有行 ##筛选并给新列赋值 这个多用于区间匹配，例如如果A列(0,100],C列为50；A列大于100 ,C列为A列的值。 df.loc[(df['DC']>0) & (df['DC']<=100) ,'DC_na']=50 # 创建新列DC_na,DC列大于0且小于等于100，DC列为50 df.loc[df['DC']>100,'DC_na']=df['DC']# 创建新列DC_na,DC列大于100等于原值，其他为NA 这里举一个其他类似的例子: 有一组数据包含三列（列名为A,B,C），现在要新增一个D列，如果A>100且5<B<7，那么D列的值等于C列减5；如果A>100且B>=7，那么D列的值等于C列减10，其他情况D列的值等于C列的值。 df['D']=df['C'] df.loc[(df['A']>100) & (df['B']>=5) &(df['B']<=7) ,'D']=df['C']-5 df.loc[(df['A']>100) & (df['B']>=7) ,'D']=df['C']-10 ##模糊筛选/精确筛选：isin()，contains() df_sel1=df[df['day'].str.contains('fr')] #筛选day列包含fr字符的行 df_sel2=df[df['day'].isin(['fri','mon'])] #筛选day列等于fri或mon的行

六、替换

##去掉字符串两端空格 df_city['experience_new'] = df_city['experience'].map(lambda s: s.strip()) #experience列中文前后端包含空格，需对改列进行分词处理（去掉空格），赋值给新列experience_new ##替换 #将experience_new列中的应届毕业生替换为1年以下 df_city = df_city.replace({'experience_new':'应届毕业生'},'1年以下') df_city['expreienct_new']=df_city['expreienct_new'].map(lambda s:re.sub('应届毕业生','1年以下',s))

七、提取字符串

##分列 df['new']=df['day'].map(lambda s:re.compile(':').split(s)[0]) #对df['day']列按照符号':'进行分列并提取第一个值，赋值到新列df['new'] ##搜索字符串 df['xin']='U34' #增加新列，列名为xin,为新列赋值U34 df['zimu']=df['xin'].map(lambda s:re.compile("([0-9]+)").search(s).group()[0]) #搜索字母并提取第一个值 df['shuzi']=df['xin'].map(lambda s:re.compile("[a-zA-Z]+").search(s).group()[0]) #搜索字母并提取第一个值

八、关联

1、两表关联：merge(左关联，右关联) 比如有以下两个数据集 df1 客户信息表 customer_id sex city 10084 男 北京 10085 女 上海 10086 男 广州 10087 女 深圳 df2订单表 order customer_id product shouru CH001 10084 A 500 CH002 10085 B 200 CH003 10086 C 1000 CH004 10086 D 3000 ###左关联 df_merge=pd.merge(df1,df2,on='customer_id',how='left') #左关联 print(df_merge) 注意第三四列，与EXCEL匹配的逻辑稍有不同。 customer_id sex city order product shouru 10084 男 北京 CH001 A 500 10085 女 上海 CH002 B 200 10086 男 广州 CH003 C 1000 10086 男 广州 CH004 D 3000 10087 女 深圳 NA NA NA ###根据多列进行左关联 pd.merge(df1,df2,on=['key1','key2'],how='left') #多键连接 2、多表进行关联 ###轴向连接：concat() pd.concat([df1,df2],axis=1,ignore_index=True）#df1和df2横向拼接 ###多表关联：reduce() from functools import reduce df_list=[df_dau,df_gmv_zx,df_dau_zx] df_zhengti=reduce(lambda left,right:pd.merge(left,right,on=['event_date','duan'],how='left'),df_list) #按照event_date,duan 从左到右对df_list中的文件进行左关联

九、聚合和排序

聚合类似于数据透视表，类似于sumifs(),countifs(),averageifs()等函数的效果。 ###聚合groupby() df_group=df['DC'].groupby([df['month'],df['day']]) #根据month和day列对DC列进行聚合 df_fun=df_gorup.agg(['sum','mean','std']) #对df_group求和，均值和标准差 print(df_fun) ###数据透视表pd.pivot_table() 这个函数比较难记，可以参考EXCEL数据透视表去理解，index代表列，columns代表行，values代表值，aggfunc代表要对值用什么函数，fil_value代表缺失值用0填充。 df_toushi=pd.pivot_table(df,index=['month'],columns=['day'],values=['DC'],aggfunc=[np.sum,np.mean],fill_value=0) print(df_toushi) ###按照DMC列降序，DC列升序对数据集进行排序。 df_paixu=df.sort_values(by=['DMC','DC'],ascending=[0,1])

十、时间序列处理

###csv中的时间会被读取为字符串，需要批量处理为pandas可处理的时间类型 df['date']=pd.to_datetime(df['createTime']) #批量转换createTime中的时间，并赋值到date列 df[(df['date']>='20140701')&(df['date']<='20140715')]#筛选指定时间段数据 ###时间设置 from datetime import datetime, timedelta import time today = datetime.today() #今天 yesterday_ts = time.time() - 24 * 3600 #昨天 yesterday = datetime.fromtimestamp(yesterday_ts).strftime('%Y-%m-%d') #转换为年月日 fromtime = (today -timedelta(16)).strftime('%Y-%m-%d') #16天前，并转化为年月日 day_before_yesterday_ts = yesterday_ts - 24*3600 #前天 day_before_yesterday = datetime.fromtimestamp(day_before_yesterday_ts).strftime('%Y-%m-%d')

十一、将数据写入到数据库

import pandas as pd from sqlalchemy import create_engine filename = "运营平台商品清单模板_新版0712.xlsx" catagoray = {} #读取excel df = pd.read_excel(filename,encoding='utf8',sheet_name='商品类目') #指定sheet的名称，默认是第一个 for i in list(df.columns): catagoray[i] = [] for item in df[i].fillna(0): #将值为NaN用零填充 if item != 0: catagoray.get(i).append(item) #catagoray数据格式为：{'列名' : ['值1', '值2'，'值2']} engine = create_engine('mysql+pymysql://user:password@mysql_ip:3306/database', echo=False) pd.DataFrame(catagoray).to_sql('talbe_name',con=engine,if_exists='append',index=True) engine.execute("SELECT * FROM talbe_name") #查询 ##扩展，如果在数据库中插入数据为列表格式，需要在创建DataFrame对象的时候指定列名 import pandas as pd from sqlalchemy import create_engine import numpy as np filename = "运营平台商品清单模板_新版0712.xlsx" catagoray = [] #读取excel df = pd.read_excel(filename,encoding='utf8',sheet_name='商品类目') #指定sheet的名称，默认是第一个 data = np.array(df) for item in data: catagoray.append(list(item)) engine = create_engine('mysql+pymysql://user:password@mysql_ip:3306/database', echo=False) pd.DataFrame(catagoray,columns=['列1','列2','列3']).to_sql('talbe_name',con=engine,if_exists='append',index=True)

十二、不覆盖现有sheet在Excel中写入数据

import pandas as pd import numpy as np writer = pd.ExcelWriter('test_excel.xlsx') A = np.array([[1,2,3],[4,5,6]]) B = np.array([[10, 20, 30], [40, 50, 60]]) df1 = pd.DataFrame(A) df2 = pd.DataFrame(B) df1.to_excel(writer,sheet_name='AAA') df2.to_excel(writer,sheet_name='BBB') writer.close()