常用函数

2026-01-295 min read数据处理

pandas

Pandas

读取Excel

读取文件,解析sheet名为下面的表格,跳过最后2行

python

rateTable = pd.ExcelFile('rate.xlsx').parse(sheet_name='即期汇率', skipfooter=2)

数据过滤

重新赋值,只保留指定的日期

python

rateTable = rateTable[rateTable['日期'] == '2023-04-03']

宽数据转长数据

主要用户存储到mysql中。

python

rateTable = rateTable[rateTable['日期'] == '2023-04-03']
#             日期  USD/CNY  EUR/CNY  ...  CNY/TRY  CNY/MXN  CNY/THB
# 18  2023-04-03   6.8805   7.4381  ...  2.78943   2.6205   4.9746
print(rateTable)

# melt() 函数，将一个宽表格转换为一个长表格，具体解释如下：
# frame pd对象
# id_vars 不变的列，就是将宽数据按照一个维度转换成长数据
# value_vars 那一列的数据,要转换为行数据
# var_name   对values_vars数据新命名一个列名
# value_name 对value_vars的数值新命名一个列名
newTable = pd.melt(frame=rateTable, id_vars=['日期'], value_vars=['USD/CNY', 'CNY/TRY'], var_name='currency',
                   value_name="exchange_rate")
#            日期 currency  exchange_rate
# 0  2023-04-03  USD/CNY        6.88050
# 1  2023-04-03  CNY/TRY        2.78943
print(newTable)

日期列转换

python

# Name: 日期, dtype: object
print(newTable['日期'])

newTable['日期'] = pd.to_datetime(newTable['日期'])
# Name: 日期, dtype: datetime64[ns]
print(newTable['日期'])

获取最大日期

python

max_date = max(newTable['日期'])
# 2023-04-28 00:00:00
print(max_date)

条件过滤

如果币种中包含CNY，~取反

str.replace() 函数的参数说明如下：

pat：要替换的模式，可以是字符串或正则表达式，这里使用了正则表达式 cny|d*|/；
regex：指示 pat 是否为正则表达式，这里设置为 True；
repl：用于替换 pat 匹配的字符串，这里设置为空字符串 ""；
flags：正则表达式的匹配标志，这里设置为 re.IGNORECASE，表示不区分大小写。

python

newTable = newTable[~newTable['currency'].str.startswith(pat='CNY')]

深复制

当我们需要对原数据进行增加列时候要深复制一个,否则会报错。

python

newTable_01 = newTable.copy(deep=True)

增加列

python

newTable_01['year'] = '2023'
newTable_01['month'] = '04'
newTable_01['nature'] = '月末即期汇率'

列重命名

python

#             日期    currency  exchange_rate  year month  nature
# 0   2023-04-28     USD/CNY        6.92400  2023    04  月末即期汇率
# 19  2023-04-28     EUR/CNY        7.63610  2023    04  月末即期汇率
# 38  2023-04-28  100JPY/CNY        5.17230  2023    04  月末即期汇率
newTable_01.rename(columns={"日期": "remark", "currency": "subsidiary_currency"}, inplace=True)
#         remark subsidiary_currency  exchange_rate  year month  nature
# 0   2023-04-28             USD/CNY        6.92400  2023    04  月末即期汇率
# 19  2023-04-28             EUR/CNY        7.63610  2023    04  月末即期汇率
# 38  2023-04-28          100JPY/CNY        5.17230  2023    04  月末即期汇率
# 57  2023-04-28             HKD/CNY        0.88206  2023    04  月末即期汇率

更新到mysql中

python

from sqlalchemy import create_engine
import pyodbc
import numpy as np
import re
import pandas as pd
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/ods?charset=utf8')

# 删除当前账期的数据,所以可以重复执行
sql = "delete from external_rate where year={y} and month={m}".format(y=year,m=month)
engine.execute(sql)

# 更新到数据库
exchange_rate.to_sql(name="external_rate",if_exists="append",con=engine,index=False)

追加

python

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6], 'B': [7, 8]})

# 不使用ignore_index参数
df = df1.append(df2)
print(df)

# 使用ignore_index参数
df = df1.append(df2, ignore_index=True)
print(df)

使用ignore_index=True参数可以忽略所有原来的索引，而使用一个新的索引。这样做可以确保合并后的DataFrame对象具有唯一的索引，并且索引值是从0开始的连续整数。

text

# 不使用ignore_index参数
   A  B
0  1  3
1  2  4
0  5  7
1  6  8

# 使用ignore_index参数
   A  B
0  1  3
1  2  4
2  5  7
3  6  8

列范围筛选

python

finance_period = ['2023年度3月', '调整财政年度 2023']
xsz = xsz[xsz['account_period'].isin(finance_period)]

apply 函数

python

data = {'Alice': [10000, 20000, 15000, 18000], 'Bob': [12000, 18000, 20000, 9000],
        'Charlie': [8000, 12000, 15000, 10000]}
df = pd.DataFrame(data, index=['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen'])
print(df)
#            Alice    Bob  Charlie
# Beijing    10000  12000     8000
# Shanghai   20000  18000    12000
# Guangzhou  15000  20000    15000
# Shenzhen   18000   9000    10000

根据列聚合

python

# 对每一列进行求和操作
total_income_by_person = df.apply(sum, axis=0)
print(total_income_by_person)
# Alice      63000（10000 + 20000 + 15000 + 18000）
# Bob        59000
# Charlie    45000

根据行处理

python

# 对每一行进行求和操作
total_income_by_city = df.apply(sum, axis=1)
print(total_income_by_city)
# Beijing      30000
# Shanghai     50000
# Guangzhou    50000
# Shenzhen     37000

数据合并

python

def concat_dataframes(*dataframes, ignore_index=True):
    """
    合并多个 DataFrame 并返回合并后的结果。

    参数:
    *dataframes (pd.DataFrame): 要合并的多个 DataFrame。
    ignore_index (bool): 是否重置索引，默认为 True。

    返回:
    pd.DataFrame: 合并后的结果 DataFrame。
    """
    result = pd.concat(dataframes, ignore_index=ignore_index)
    return result

Mysql连接

python

from sqlalchemy import create_engine
def reportEngine():
    return create_engine("mssql+pyodbc://root:123#@127.0.0.1:3306/data?driver=SQL+Server",
                         echo=False, echo_pool=False)

VBA + Excel

python

import xlwings as xw
def exeExcelVba(vba_excel_path, current_excel_path, vba_method_name):
    '''
    执行 vba 脚本优化输出文件样式
    :param vba_excel_path:
    :param current_excel_path:
    :param vba_method_name:
    :return:
    '''
    app = xw.App(visible=True, add_book=False)
    vba = app.books.open(vba_excel_path, read_only=False)
    # reportSet
    bk = app.books.open(current_excel_path, read_only=False)
    bk.activate()
    vba.macro(vba_method_name)()
    bk.save()
    bk.close()
    vba.close()
    app.quit()

发邮件

python

import smtplib
from email.mime.text import MIMEText
from email.utils import formataddr
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart


def send_email(email_name: str,
               passwd: str,
               msg_to: list,
               subject: str,
               content: str,
               acc_to: list = None,
               att_file: list = None,
               from_name: str = None) -> bool:
    '''
    发送邮件方法：
    :param email_name:发送者邮件地址
    :param passwd:发送者邮箱密码
    :param msg_to:收件人地址列表，参数类型是列表
    :param subject:邮件主题，字符串类型
    :param content:邮件文本内容，字符串类型
    :param acc_to:抄送人地址列表，参数类型是列表，默认值是None
    :param att_file:附件列表，参数类型是列表，默认值为None
    :param from_name: 显示发件人的名字，字符串类型，默认值为None
    :return bool:成功返回True，失败返回False
    '''
    # 这个是可以发送附件并且包含多样式的内容的对象
    msg = MIMEMultipart()
    # 邮件正文内容
    #     msg.attach(MIMEText(content))
    msg.attach(MIMEText(content, 'html', 'utf-8'))

    # 设置邮件
    msg['Subject'] = subject
    msg['From'] = from_name
    msg['To'] = ';'.join(msg_to)
    msg['Cc'] = ';'.join(acc_to)

    # 判断是否包含附件
    if len(att_file) > 0:
        for file in att_file:
            print(file)
            att = MIMEText(open(file, 'rb').read(), 'plain', 'utf-8')
            att['Content-Type'] = 'application/octet-stream'
            att.add_header("Content-Disposition", 'attachment', filename=('gbk', "", file))
            msg.attach(att)

    # 连接邮件服务器
    s = smtplib.SMTP('smtp.partner.outlook.cn', 587)
    s.ehlo()  # 向邮箱发送SMTP 'ehlo' 命令
    s.starttls()
    # 登陆我的邮箱
    s.login(email_name, passwd)
    # 发送邮箱
    s.sendmail(email_name, msg_to + acc_to, msg.as_string())
    print("发送成功")

Pandas 列

python

from IPython.display import display, HTML
def columns(df: DataFrame):
    colorPrint(df.columns, 'red')
    # 提取列名
    columns = df.columns
    # 创建字典，将列名和数据类型存储起来
    column_data_types = {}
    for column in columns:
        column_data_types[column] = str(df[column].dtype)
    # 转换为DataFrame
    dt_df = pd.DataFrame(list(column_data_types.items()), columns=['列名称', '列类型'])
    # 输出表格
    display(dt_df)

深拷贝

python

def deepCopy(pd: DataFrame):
    '''
    深拷贝
    :param pd:
    :return:
    '''
    return pd.copy(deep=True)

年月

python

from datetime import datetime
# 获取当前时间
def currentDate():
    # 获取当前时间
    current_time = datetime.now()
    # 格式化当前时间
    return current_time.strftime("%Y-%m-%d %H:%M:%S")

去重函数

python

def duplicated(df: DataFrame, cols: []):
    '''
    查询 Pandas 中的重复数据
    :param df:
    :param cols:
    :return:
    '''
    duplicated_rows = df[df.duplicated(subset=cols, keep=False)]
    colorBoldPrint('去重列:{} === 重复行数:{}'.format(','.join(cols), len(duplicated_rows)), 'blue')
    # 打印出重复的行
    print(duplicated_rows.head(3))