pandas常用操作-进阶

1. read_excel参数详解
2. ExcelWriter
3. merge
4. query
5. filter
6. pandas语句与sql语句对比

import pandas as pd

file_path = r'.\工作统计表202209.xls'
file_path2 = r'.\合并.xlsx'

# read_excel参数详解

"""
pandas其他参数：
    io, # 文件路径
    sheet_name=0,# 读取的sheet名
    skiprows=None, # 跳过指定的行,skiprows=1 跳过第1行,skiprows=3 跳过前3行,skiprows=[1,3,5] 跳过第1,3,5行,skiprows=lambda x: x % 2 == 0 跳过偶数行
    header=0,# 指定第几行作为表头，header以上的行将会被忽略
    names=None, # 指定表头名称，需要传递一个列表并且长度与DataFrame列数一致
    index_col=None, # 指定列为索引列，None：从0开始自动生成。整数：指定第几列为索引，从0开始
    usecols=None, # 指定解析的列数,默认为None，解析所有列。如果为str,则表示Excel列字母和列范围的逗号分隔列表（例如“ A：E”或“ A，C，E：F”）
                  # 如果为int列表，则表示解析那几列
    squeeze=False, # 默认为False。如果设置squeeze=True则表示如果解析的数据只包含一列，则返回一个Series.此参数后面版本将作废
    dtype=None, # 指定数据类型，默认为None，不改变数据类型。
    engine=None, # 可以接受的参数有'xlrd'，'openpyxl'或'odf'
    converters=None, # 对指定列的数据进行指定函数的处理，传入参数为列名与函数组成的字典。key 可以是列名或者列的序号，values是函数，可以def函数或者直接lambda都行
    true_values=None,# 将指定的文本转换为True，默认为None
    false_values=None,# 将指定的文本转换为False，默认为None
    nrows=None, # 指定需要读取前多少行，通常用于较大的数据文件中
    na_values=None, # 指定某些列的某些值为NaN
    keep_default_na=True, # 表示导入数据时是否导入空值。默认为True，即自动识别空值并导入
    na_filter=True, # 当数据为空时是否设置为NaN
    verbose=False, # 是否显示程序处理过程中的一些额外信息,类似于输出log
    parse_dates=False, # parse_dates将Date列设置为时间类型
    date_parser=None,# 利用lambda函数，将某个字符串列，解析为日期格式；一般是配合parse_dates参数，一起使用
    thousands=None,# 指定千分位分隔符
    comment=None,
    skipfooter=0, # skipfooter = 0不忽略，skipfooter = 1 忽略最后一行，skipfooter = 2 忽略最后两行
    convert_float=True,
    mangle_dupe_cols=True,
    storage_options: StorageOptions = None,
"""

# sheet_name # 指定读取的sheet名
df = pd.read_excel(file_path,sheet_name='9月')
df.head(2)

# skiprows # 跳过指定的行,skiprows=1 跳过第1行,skiprows=3 跳过前3行,skiprows=[1,3,5] 跳过第1,3,5行,skiprows=lambda x: x % 2 == 0 跳过偶数行
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3) # 忽略前3行，第四行作为表头
df.head(2) 

# names
names = ['序号', '分中心', '姓名', '主调次数', '陪调次数', '上会数量']
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,names=names) # 重新为表头命名
df.head(2) 

# header # 指定第几行作为表头，header以上的行将会被忽略
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,header=6) # header指定第几行作为表头，会忽略表头以上的数据行
df.head(2) 

# index_col # 指定列为索引列，None：从0开始自动生成。整数：指定第几列为索引，从0开始
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,index_col=2) # index_col指定第几列作为索引列
df.head(2) 

# usecols # 指定解析的列数,默认为None，解析所有列。如果为str,则表示Excel列字母和列范围的逗号分隔列表（例如“ A：E”或“ A，C，E：F”）
          # 如果为int列表，则表示解析那几列
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2]) # 整数列表形式
# df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols='B:C') # 字符串形式，'B:E','A,C,D'
df.head(2) 

# squeeze # 默认为False。如果设置squeeze=True则表示如果解析的数据只包含一列，则返回一个Series.此参数后面版本将作废
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[2],squeeze=True) # index_col指定第几列作为索引列
df.head(2) 

C:\Users\AppData\Local\Temp\ipykernel_4420\1678458970.py:2: FutureWarning: The squeeze argument has been deprecated and will be removed in a future version. Append .squeeze("columns") to the call to squeeze.

​

df = pd.read_excel(file_path,sheet_name=‘9月’,skiprows=3,usecols=[2],squeeze=True) # index_col指定第几列作为索引列

# dtype # 指定数据类型，默认为None，不改变数据类型。
df1 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[0,2]) # 
print('df1-->',df1.info())
df2 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[0,2],dtype=str) # 全部列转成str
# df2 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[0,2],names=['a','b'],dtype={'a':'float16'}) # a列转为float16，需要命名列名
print('df2-->',df2.info())

RangeIndex: 9 entries, 0 to 8
Data columns (total 2 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   Unnamed: 0  7 non-null      float64
 1   Unnamed: 2  7 non-null      object 
dtypes: float64(1), object(1)
memory usage: 272.0+ bytes
df1--> None

RangeIndex: 9 entries, 0 to 8
Data columns (total 2 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   Unnamed: 0  7 non-null      object
 1   Unnamed: 2  7 non-null      object
dtypes: object(2)
memory usage: 272.0+ bytes
df2--> None

# converters：对指定列的数据进行指定函数的处理，传入参数为列名与函数组成的字典。
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[2,3]) # 
df.head(2) 

# key 可以是列名或者列的序号，values是函数，可以def函数或者直接lambda都行
df = pd.read_excel(file_path2,sheet_name='Sheet2',usecols='E,P',converters={'授信金额':lambda x:x/10000}) # {1:lambda x:x/10000}
df.head() 

# true_values,false_values
# 典型应用，性别
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2]) # 
print(df)
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2],true_values=['城阳'],false_values=['平均','合计']) # 
df

# nrows # 指定需要读取前多少行
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2],nrows=3) # 指定读取的行数
df

# na_values，（scalar, str, list-like, or dict, default None）# 指定某些列的某些值为NaN
df = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2],na_values='王腾') # 指定某些列的某些值为NaN
df

# na_filter （bool,default True）# 当数据为空时是否设置为NaN
df1 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2],na_filter=True) # True时为NaN
df2 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2],na_filter=False) # False时为空字符串
print(df1)
print(df2)

print(df1.iloc[8,1],type(df1.iloc[8,1]))
print(df2.iloc[8,1],type(df2.iloc[8,1]))

nan 
 

# verbose # 是否显示程序处理过程中的一些额外信息,类似于输出log
df1 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2],verbose=True) 
df1

# parse_dates,date_parser,利用lambda函数，将某个字符串列，解析为日期格式；一般是配合parse_dates参数，一起使用
df1 = pd.read_excel(file_path2,sheet_name='Sheet4',usecols='E,S',parse_dates=[1],
                    date_parser=lambda x: pd.to_datetime(x,format="%Y-%m-%d")) # 合同首签日列转成datetime类型
df1.head()

df1.dtypes

客户号              object
合同首签日    datetime64[ns]
dtype: object

ExcelWriter

多个df写入同一个文件

df1 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[1,2]) # 指定某些列的某些值为NaN
df1.to_excel(r'C:\Users\Desktop\测试\out.xlsx',index=False,sheet_name='out1')

df2 = pd.read_excel(file_path,sheet_name='9月',skiprows=3,usecols=[2,3]) # 指定某些列的某些值为NaN
df2.to_excel(r'C:\Users\Desktop\测试\out.xlsx',index=False,sheet_name='out2')

# to_excel到同一个文件，默认会覆盖前面写入的数据

# 通过ExcelWriter将多个df写入同一个文件
writer = pd.ExcelWriter(r'C:\Users\Desktop\测试\out.xlsx')
df1.to_excel(writer,sheet_name='out1')
df2.to_excel(writer,sheet_name='out2')
writer.save()

# df输出到已存在的文件中
# a模式必须文件已存在
writer = pd.ExcelWriter(r'C:\Users\Desktop\测试\out1.xlsx',mode='a',if_sheet_exists='replace') # error,replace,new
df1.to_excel(writer,sheet_name='out3')
writer.save()

---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_14984\1386687888.py in 
      1 # df输出到已存在的文件中
      2 # a模式必须文件已存在
----> 3 writer = pd.ExcelWriter(r'C:\Users\Desktop\测试\out1.xlsx',mode='a',if_sheet_exists='replace') # error,replace,new
      4 df1.to_excel(writer,sheet_name='out3')
      5 writer.save()
D:\RpaStudy\lib\site-packages\pandas\io\excel\_openpyxl.py in __init__(self, path, engine, mode, storage_options, **engine_kwargs)
     29 
     30         super().__init__(
---> 31             path, mode=mode, storage_options=storage_options, **engine_kwargs
     32         )
     33 
D:\RpaStudy\lib\site-packages\pandas\io\excel\_base.py in __init__(self, path, engine, date_format, datetime_format, mode, storage_options, **engine_kwargs)
    798         if not isinstance(path, ExcelWriter):
    799             self.handles = get_handle(
--> 800                 path, mode, storage_options=storage_options, is_text=False
    801             )
    802         self.sheets: Dict[str, Any] = {}
D:\RpaStudy\lib\site-packages\pandas\io\common.py in get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)
    649         else:
    650             # Binary mode
--> 651             handle = open(handle, ioargs.mode)
    652         handles.append(handle)
    653 
FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\\Desktop\\测试\\out1.xlsx'

merge

数据合并

df1 = pd.DataFrame({'id':range(1,6),'name':['a','b','c','d','e']},index=range(5))
df1

df2 = pd.DataFrame({'id':range(2,7),'score':[88,77,67,100,93]},index=range(5))
df2

# outer
df1.merge(df2,how='outer',on='id')

# inner
df1.merge(df2,how='inner',on='id')

# left
pd.merge(df1,df2,how='left',on=['id'])

# right
pd.merge(df1,df2,how='right',on=['id'])

query

df2

df2.query('score > 80') # 类似sql中的where

df2.loc[df2['score']>80]

# 通过变量筛选,使用@表示变量
name = 'b'
df1.query('name == @name')

# 多条件筛选
df2.query('score