python 总结常用语句

做ETL半年了，把常用的代码总结以下。

增加一些python常用代码。

20200401，sys.argv

# 获得当前脚本所在路径，包括脚本名称。
import os
import sys
print(sys.argv)     # 返回一个list
print(sys.argv[0])  # 返回当前脚本的路径和名称
print(os.path.dirname(os.path.realpath(sys.argv[0])))   # 返回脚本所在文件夹路径

 返回结果：

['D:/pyCharm2019.1/tensorflow_example/word2vec/test.py']
D:/pyCharm2019.1/tensorflow_example/word2vec/test.py
D:\pyCharm2019.1\tensorflow_example\word2vec

 

# -*- coding: utf-8 -*-
import pandas as pd
import requests
from pymongo import MongoClient
import numpy as np
from timeit import default_timer as timer
import re
from sqlalchemy import create_engine
import platform
import os
import sys
from datetime import datetime
import shutil
from openpyxl import Workbook,load_workbook

# print set
pd.set_option('display.max_columns', 1000)
pd.set_option('display.width', 1000)
pd.set_option('display.max_colwidth', 1000)


###################################
# connect db
# 1. mongodb
user = '##'
password = '##'
ip = '##'
port = 123
database = '##'

# 1.1 用字符串表示连接信息，mongodb://用户名:密码@ip:端口/数据库
conn = MongoClient('mongodb://' + user + ':' + password + '@' + ip + ':' + port + '/' + database)

# 1.2 用参数表示连接信息，参数有ip和端口
conn_2 = MongoClient(host=ip, port=port)

# 要连接的数据库
db = conn.database


# 2. sqlserver
# 初始引擎(数据量大的时候用这种，效率较高)，用字符串表示连接信息，
# mssql+pyodbc://user:password@ip/数据库?driver=ODBC+Driver+17+for+SQL+Server
conn = create_engine('mssql+pyodbc://' + user + ':' + password + '@' + ip + '/' + database +
                     '?driver=ODBC+Driver+17+for+SQL+Server', fast_executemany=True, encoding="utf_16")


###########################
# get data from db
# 1. mongodb
table_collection = db.table
# 第一个花括号里是条件，第二个花括号是我要的字段
df = pd.DataFrame(list(table_collection.find({"columns1": 1, "columns3": 'a'},
                                               {'columns1': 1, 'column2': 1})))

# 稍微发杂一点的条件
df = pd.DataFrame(list(table_collection.find(
        {"$and": [{"columns1": {"$in": ["a", "b"]}}, {"touchless_status": 1}]},
        {'columns1': 1, 'column2': 1})))


# 2. sqlserver
df = pd.read_sql_table('table', conn)


########################################
# insert into db
# 1. to_sql
df.to_sql(table_name, schema='dbo', con=conn, if_exists='append', index=False)

# 2. bcp, need csv
# 分隔符使用横向制表符\t，utf_16 16位为一个编码单位的可变长编码，index是否要索引，header是否要列标题
df.to_csv(csv_name, sep='\t', encoding='utf_16',mode='w', index=False, header=False)

# windows/linux
cmd_str_windows = r'bcp [{database}].[dbo].[{table_name}] in {csv_name} -S {ip} ' \
                  r'-U {user} -P {password} -t "\t" -w '.format(database=sql_server_database,
                                                                table_name=sql_server_table,
                                                                csv_name=csv_name,
                                                                ip=sql_server_ip,
                                                                user=sql_server_user,
                                                                password=sql_server_pass)
cmd_str_linux = r"export PATH='$PATH:/opt/mssql-tools/bin'; " + cmd_str_windows
# cmd_str_linux = r"export PATH='$PATH:/opt/mssql-tools/bin';" + cmd_str_windows + r" -r '\r\n'"
cmd_str = cmd_str_windows if "Windows" in platform.platform() else cmd_str_linux
result = os.popen(cmd_str).read()   # 返回bcp执行结果
print(result)


################################3############
# truncate table in sqlserver
conn_ = conn.raw_connection()
cursor = conn_.cursor()
sql_query = "truncate table dbo.{}".format(table_name)
cursor.execute(sql_query)
cursor.close()
conn_.commit()
conn_.close()


##################################################
# common ETL code
# 删除有关
del df['a'] # 删除一列
df.drop(['a','b'],axis=1,inplace=True)  #删除一/多 行/列，axis指定删除行/列
df.drop_duplicates(['a','b'],keep='last',inplace=True)  # 删除重复项，keep保留第一行或最后一行
df.dropna(axis=0,how='any',inplace=True)    #删除NA，axis指定删除行/列，how=any指只要有NA就删除，how=all指所有是NA才删除


# 合并关联有关
df = pd.merge(df_1,df_2,how='left',left_on=['a'],right_on='b')  # 关联，how控制左右关联，on相关控制关联的字段
df = pd.merge(df_1,df_2,how='left',left_on=['a','b'],right_on=['c','d']).drop(['q','w'],axis=1) #关联后如果有些字段不想要可以直接在drop里指定删除
df = pd.concat([df_1,df_2],axis=0,ignore_index=True,sort=False) # 堆叠，axis控制上下/左右堆叠


# 重命名有关
df.rename({'a':'b','c':'d'},axis=0,inplace=True)    # 写起来费点事，但是最通用，axis控制替换行/列，新旧字段名用字典表示


# 修改列元素有关
df['a'] = np.where(df['b']=='Y','Y','N')    # np.where是我很喜欢用的，根据是否符合指定条件来修改元素
df['a'] = np.where(pd.isnull(df['b']),'null','not null')
df['a'] = df['b'].apply(lambda x: 'N' if x=='Y' else 'Y') # apply+lambda+if,和np.where效果一样
df['a'] = df['a'].apply(lambda x: x.replace(np.nan, 'qwe'))
df_2 = df.applymap(lambda x: np.nan if len(str(x)) < 1 else x)  # applymap对整个df进行操作

df['d'] = np.where(df['a'].apply(lambda x: re.split('_', x, maxsplit=4)[0][-3:]) == 'Y', 'Y', 'N')  # 稍微复杂的修改元素
df['a'] = df['b']/2


# 分组有关
df_2 = df.groupby(['a','b'], as_index=False)['c'].sum() # 按指定字段先分组然后对指定字段求和，as_index指分组后不要索引

df_3 = pd.pivot_table(df,values=['a','b','q'],index=['c','d'],
                      aggfunc={'a':np.sum,'b':np.sum,'q':len},fill_value=0)  # 透视表,字段顺序是英文字母的顺序



# 设置索引相关
df.set_index(['a'], inplace=True)   # 字段设置成索引
df_5 = df.reset_index() # 重置索引


# 按指定顺序排列行
list_1 = ['aaa','bbb','ccc']
list_2 = ['q','w','e']
df['a','b'] = df['a','b'].astype('category')    #设置成“category”数据类型
df['a'].cat.reorder_categories(list_2, inplace=True)  #重新排序,inplace = True，使 recorder_categories生效
df['b'].cat.reorder_categories(list_1, inplace=True)
df.sort_values(['a','b'], inplace=True)  #inplace = True，使 df生效


################################################
# os有关
file_dir = "##############"
for root, dirs, files in os.walk(file_dir):
    for file in files:
        if file[0:4] == '####':
            file_name = file
            source_path = file_dir + "\\" + file_name
des_path = "#############"
shutil.copy(source_path, des_path)  # 复制
shutil.move(source_path, des_path)  # 剪切


######################################################
# TS时间序列有关
df_1 = df.resample('D').ffill()  #If Date is not found, use last day number

df['a'] = np.nansum([df['b'],df['c']],axis=0)   # 非零的按行求和

df_2 = df.set_index('a').groupby(['b','c']).resample('1D')['d'].sum()   # 分组重塑再求和

df_3 = df.groupby(['a']).resample('1D').mean().reset_index(0)   # 分组重塑求平均再重置索引

df['g'] = 0 + np.cumsum(df['a'] - df['b'],axis=0)   # 累计求和

df['a'] = df.groupby(['b'])['c'].rolling(7).mean().reset_index(level=0,drop=True)   # 分组，7个求平均，再重置索引

df = df.fillna(0)   # na全部替换成0

df_6 = df.shift(1).fillna(0)    # 移动数据

df_7 = df.resample('M').ohlc()  # 重采样后索引是层次化索引
df_8 = df_7.T.iloc[[1, 2, 3]].T

df['a'] = df.groupby([df.index.year.values, df.index.month.values]).apply(lambda x: x[x['b'] == x['b'].max()])['b'] # 按索引年月分组并取最大值

# 当前文件不是从1号开始，则从其他文件补数据。需要记录当前文件第一条的日期，其他文件从1号取到当前文件第一条前一天，然后合并即可。


#########################
# truncate xlsx
wb = load_workbook('#######')
ws = wb['Sheet1']
for col in range(ws.max_column):
    for row in range(ws.max_row):
        ws.cell(row=row+2, column=col+1).value = np.nan # 从第2行开始清除
wb.save('#######')  # 保存下文件



###############################
# insert into xlsx
wb = load_workbook('#####3')
ws = wb['Sheet1']
for col in range(df.shape[1]):
    for row in range(df.shape[0]):
        ws.cell(row=row + 2, column=col + 1).value = df.iloc[row, col]  # 从第2行开始插入
    wb.save('#####3')

时间序列相对比较繁琐，主要是时间列要处理成一致的形式，缺失日期的数据用0填补还是用前一天数据填补（重塑或者重采样），按固定周期聚合，行求和要避免NA（用nansum），累加，数据移动等等，这一块的东西还是很多的，如果你是做跟时间有关的ETL，我上面列出来的还不够，要学习的还有很多。

一般的ETL，主要就是关联、合并，聚合，计算，处理列元素，还是很容易上手的。