❶ 挑戰sql：圖解pandas的數據合並merge函數
大家好，我是Peter~
在實際的業務需求中，我們的數據可能存在於不同的庫表中。很多情況下，我們需要進行多表的連接查詢來實現數據的提取，通過SQL的join，比如left join、left join、inner join等來實現。
在pandas中也有實現合並功能的函數，比如：concat、append、join、merge。本文中重點介紹的是 merge函數 ，也是pandas中最為重要的一個實現數據合並的函數。
看完了你會放棄SQL嗎？
目前Pandas系列文章已經更新了13篇，文章都是以案例+圖解的風格，歡迎訪問閱讀。有很多個人推薦的文章：
官網學習地址： https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html#
參數的具體解釋為：
我們創建了4個DataFrame數據框；其中df1和df2、df3是具有相同的鍵userid；df4有類似的鍵userid1，取值也是ac，和df1或df2的userid取值有相同的部分。
left、how就是需要連接的兩個數據幀，一般有兩種寫法：
圖解過程如下：
inner稱之為 內連接 。它會直接根據相同的列屬性userid進行關聯，取出屬性下面相同的數據信息a、c
⚠️上面的圖解過程就是默認的使用how="inner"
outer稱之為外連接，在拼接的過程中會取兩個數據框中鍵的並集進行拼接
圖解過程如下：
以左邊數據框中的鍵為基準；如果左邊存在但是右邊不存在，則右邊用NaN表示
圖解過程如下：
以右邊數據框中的鍵的取值為基準；如果右邊存在但是左邊不存在，則左邊用NaN表示
圖解過程如下：
笛卡爾積：兩個數據框中的數據交叉匹配，出現 n1*n2 的數據量
笛卡爾積的圖解過程如下：
如果待連接的兩個數據框有相同的鍵，則默認使用該相同的鍵進行聯結。
上面的所有圖解例子的參數on默認都是使用相同的鍵進行聯結，所以有時候可省略。
再看個例子：
還可以將left和right的位置進行互換：
上面的兩個例子都是針對數據框只有具有相同的一個鍵，如果不止通過一個鍵進行聯結，該如何處理？通過一個來自官網的例子來解釋，我們先創建兩個DataFrame：df5、df6
現在進行兩個數據框的合並：
合並的圖解過程如下：
在看一個通過how="outer"進行連接的案例：
看看圖解的過程：
上面在連接合並的時候，兩個數據框之前都是有相同的欄位，比如userid或者key1和key2。但是如何兩個數據框中沒有相同的鍵，但是這些鍵中的取值有相同的部分，比如我們的df1、df3：
在這個時候我們就使用left_on和right_on參數，分別指定兩邊的連接的鍵：
如果我們不指定，系統就會報錯，因為這兩個數據框是沒有相同的鍵，本身是無法連接的：
如果連接之後結果有相同的欄位出現，默認後綴是 _x_、_y 。這個參數就是改變我們默認的後綴。我們回顧下笛卡爾積的形成；
現在我們可以指定想要的後綴：
這個參數的作用是表明生成的一條記錄是來自哪個DataFrame：both、left_only、right_only
如果帶上參數會顯示一個新欄位 _merge ：
不帶上參數的話，默認是不會顯示來源的，看默認的情況：
merge函數真的是非常強大，在工作中也使用地很頻繁，完全可以實現SQL中的join效果。希望本文的圖解能夠幫助讀者理解這個合並函數的使用。同時pandas還有另外幾個與合並相關的函數，比如：join、concat、append，會在下一篇文中統一講解。
❷ 基於Pandas的數據分析平台，數據連接該不該用SqlAlchemy的ORM
、始使用：
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
DB_CONNECT_STRING = 'mysql+mysqldb://root:123@localhost/ooxx?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
DB_Session = sessionmaker(bind=engine)
session = DB_Session()
DB_CONNECT_STRING 連接資料庫路徑mysql+mysqldb指定使用 MySQL-Python 連接root123別用戶名密碼localhost資料庫域名ooxx使用資料庫名（省略）charset指定連接使用字元集（省略）
create_engine() 返資料庫引擎echo 參數 True 顯示每條執行 SQL 語句產環境關閉
sessionmaker() 資料庫類類實例資料庫連接同記錄些查詢數據並決定候執行 SQL 語句由於 SQLAlchemy 自維護資料庫連接池（默認 5 連接）初始化銷並 Tornado 言 BaseHandler initialize() 初始化：
class BaseHandler(tornado.web.RequestHandler):
def initialize(self):
self.session = models.DB_Session()
def on_finish(self):
self.session.close()
其 Web 伺服器說使用 sqlalchemy.orm.scoped_session能保證每線程獲 session 象都唯 Tornado 本身單線程使用非同步式能現問題並沒使用
拿 session 執行 SQL ：
session.execute('create database abc')
print session.execute('show databases').fetchall()
session.execute('use abc')
# 建 user 表程略
print session.execute('select * from user where id = 1').first()
print session.execute('select * from user where id = :id', {'id': 1}).first()
直接使用 MySQL-Python 沒啥區別；ORM 式採用 SQLAlchemy 唯原
於定義表：
from sqlalchemy import Column
from sqlalchemy.types import CHAR, Integer, String
from sqlalchemy.ext.declarative import declarative_base
BaseModel = declarative_base()
def init_db():
BaseModel.metadata.create_all(engine)
def drop_db():
BaseModel.metadata.drop_all(engine)
class User(BaseModel):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(CHAR(30)) # or Column(String(30))
init_db()
declarative_base() 創建 BaseModel 類類類自與表關聯
User 類例 __tablename__ 屬性資料庫該表名稱 id name 兩欄位別整型 30 定字元Column 些其參數我解釋
BaseModel.metadata.create_all(engine) 找 BaseModel 所類並資料庫建立些表；drop_all() 則刪除些表
接著始使用表吧：
from sqlalchemy import func, or_, not_
user = User(name='a')
session.add(user)
user = User(name='b')
session.add(user)
user = User(name='a')
session.add(user)
user = User()
session.add(user)
session.commit()
query = session.query(User)
print query # 顯示SQL 語句
print query.statement # 同
for user in query: # 遍歷查詢
print user.name
print query.all() # 返類似列表象
print query.first().name # 記錄存first() 返 None
# print query.one().name # 存或行記錄拋異
print query.filter(User.id == 2).first().name
print query.get(2).name # 主鍵獲取等效於句
print query.filter('id = 2').first().name # 支持字元串
query2 = session.query(User.name)
print query2.all() # 每行元組
print query2.limit(1).all() # 返 1 條記錄
print query2.offset(1).all() # 第 2 條記錄始返
print query2.order_by(User.name).all()
print query2.order_by('name').all()
print query2.order_by(User.name.desc()).all()
print query2.order_by('name desc').all()
print session.query(User.id).order_by(User.name.desc(), User.id).all()
print query2.filter(User.id == 1).scalar() # 記錄返第條記錄第元素
print session.query('id').select_from(User).filter('id = 1').scalar()
print query2.filter(User.id > 1, User.name != 'a').scalar() # and
query3 = query2.filter(User.id > 1) # 拼接 filter and
query3 = query3.filter(User.name != 'a')
print query3.scalar()
print query2.filter(or_(User.id == 1, User.id == 2)).all() # or
print query2.filter(User.id.in_((1, 2))).all() # in
query4 = session.query(User.id)
print query4.filter(User.name == None).scalar()
print query4.filter('name is null').scalar()
print query4.filter(not_(User.name == None)).all() # not
print query4.filter(User.name != None).all()
print query4.count()
print session.query(func.count('*')).select_from(User).scalar()
print session.query(func.count('1')).select_from(User).scalar()
print session.query(func.count(User.id)).scalar()
print session.query(func.count('*')).filter(User.id > 0).scalar() # filter() 包含 User需要指定表
print session.query(func.count('*')).filter(User.name == 'a').limit(1).scalar() == 1 # 用 limit() 限制 count() 返數
print session.query(func.sum(User.id)).scalar()
print session.query(func.now()).scalar() # func 跟任意函數名要該資料庫支持
print session.query(func.current_timestamp()).scalar()
print session.query(func.md5(User.name)).filter(User.id == 1).scalar()
query.filter(User.id == 1).update({User.name: 'c'})
user = query.get(1)
print user.name
user.name = 'd'
session.flush() # 寫資料庫並提交
print query.get(1).name
session.delete(user)
session.flush()
print query.get(1)
session.rollback()
print query.get(1).name
query.filter(User.id == 1).delete()
session.commit()
print query.get(1)
二、進階知識
1）何批量插入批數據
使用非 ORM 式：
session.execute(
User.__table__.insert(),
[{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]
)
session.commit()
何批量插入批數據
使用非 ORM 式：
session.execute(
User.__table__.insert(),
[{'name': `randint(1, 100)`,'age': randint(1, 100)} for i in xrange(10000)]
)
session.commit()
面批量插入 10000 條記錄半秒內執行完； ORM 式花掉間
2）何讓執行 SQL 語句增加前綴
使用 query 象 prefix_with() ：
session.query(User.name).prefix_with('HIGH_PRIORITY').all()
session.execute(User.__table__.insert().prefix_with('IGNORE'), {'id': 1, 'name': '1'})
3）何替換已主鍵記錄
使用 session.merge() 替代 session.add()其實 SELECT + UPDATE：
user = User(id=1, name='ooxx')
session.merge(user)
session.commit()
或者使用 MySQL INSERT … ON DUPLICATE KEY UPDATE需要用 @compiles 裝飾器點難懂自看吧：《SQLAlchemy ON DUPLICATE KEY UPDATE》 sqlalchemy_mysql_ext
4）何使用符號整數
使用 MySQL 言：
from sqlalchemy.dialects.mysql import INTEGER
id = Column(INTEGER(unsigned=True), primary_key=True)
5）模型屬性名需要表欄位名辦
發遇奇怪需求其系統表包含from欄位 Python 關鍵字於能處理：
from_ = Column('from', CHAR(10))
6）何獲取欄位度
Column 復雜象想獲取度比較麻煩 User.name 例：
User.name.property.columns[0].type.length
7）何指定使用 InnoDB及使用 UTF-8 編碼
簡單式修改資料庫默認配置非要代碼指定：
class User(BaseModel):
__table_args__ = {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8'
}
MySQL 5.5 始支持存儲 4 位元組 UTF-8 編碼字元iOS 自帶 emoji（ ?? 字元）屬於種
表設置面代碼 utf8 改 utf8mb4DB_CONNECT_STRING charset 更改
庫或欄位設置則自寫 SQL 語句比較便具體細節參考《How to support full Unicode in MySQL databases》
建議全用 utf8mb4 代替 utf8前者更慢索引佔用更空間
8）何設置外鍵約束
from random import randint
from sqlalchemy import ForeignKey
class User(BaseModel):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
age = Column(Integer)
class Friendship(BaseModel):
__tablename__ = 'friendship'
id = Column(Integer, primary_key=True)
user_id1 = Column(Integer, ForeignKey('user.id'))
user_id2 = Column(Integer, ForeignKey('user.id'))
for i in xrange(100):
session.add(User(age=randint(1, 100)))
session.flush() # 或 session.commit()執行完user 象 id 屬性才訪問（ id 自增）
for i in xrange(100):
session.add(Friendship(user_id1=randint(1, 100), user_id2=randint(1, 100)))
session.commit()
session.query(User).filter(User.age < 50).delete()
執行段代碼應該遇錯誤：
sqlalchemy.exc.IntegrityError: (IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`ooxx`.`friendship`, CONSTRAINT `friendship_ibfk_1` FOREIGN KEY (`user_id1`) REFERENCES `user` (`id`))') 'DELETE FROM user WHERE user.age < %s' (50,)原刪除 user 表數據能導致 friendship 外鍵指向真實存記錄默認情況MySQL 拒絕種操作 RESTRICTInnoDB 允許指定 ON DELETE CASCADE SET NULL前者刪除 friendship 效記錄者些記錄外鍵設 NULL
除刪除能更改主鍵導致 friendship 外鍵失效於相應 ON UPDATE 其 CASCADE 變更新相應外鍵刪除
SQLAlchemy 處理：
class Friendship(BaseModel):
__tablename__ = 'friendship'
id = Column(Integer, primary_key=True)
user_id1 = Column(Integer, ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'))
user_id2 = Column(Integer, ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE'))
9）何連接表
from sqlalchemy import distinct
from sqlalchemy.orm import aliased
Friend = aliased(User, name='Friend')
print session.query(User.id).join(Friendship, User.id == Friendship.user_id1).all() # 所朋友用戶
print session.query(distinct(User.id)).join(Friendship, User.id == Friendship.user_id1).all() # 所朋友用戶（掉重復）
print session.query(User.id).join(Friendship, User.id == Friendship.user_id1).distinct().all() # 同
print session.query(Friendship.user_id2).join(User, User.id == Friendship.user_id1).order_by(Friendship.user_id2).distinct().all() # 所別朋友用戶
print session.query(Friendship.user_id2).select_from(User).join(Friendship, User.id == Friendship.user_id1).order_by(Friendship.user_id2).distinct().all() # 同join 向相反 STRAIGHT_JOIN所 MySQL 自選擇順序
print session.query(User.id, Friendship.user_id2).join(Friendship, User.id == Friendship.user_id1).all() # 用戶及其朋友
print session.query(User.id, Friendship.user_id2).join(Friendship, User.id == Friendship.user_id1).filter(User.id < 10).all() # id 於 10 用戶及其朋友
print session.query(User.id, Friend.id).join(Friendship, User.id == Friendship.user_id1).join(Friend, Friend.id == Friendship.user_id2).all() # 兩 join由於使用相同表需要別名
print session.query(User.id, Friendship.user_id2).outerjoin(Friendship, User.id == Friendship.user_id1).all() # 用戶及其朋友（朋友則 None使用左連接）
-
-
❸ 資料庫間如何自動拿數據
詳情如下：
1、連接資料庫，不同資料庫python有對應的第三方庫，用賬號密碼ip地址直接連接。
2、執行sql語句，可以用pandas裡面的read_sql_query，也可以用資料庫第三方庫的fetchall。
3、獲取結果，read_sql_query直接出來帶列名的DataFrame，但fetchall這種還要另外處理成DataFrame，還有其他數據處理的，在這一步加上就是了。
4、保存結果，pandas裡面有一個to_pickle的函數，可以把數據序列化保存在本地文件，需要用到的時候再read_pickle反序列拿出來用，比不停地執行sql要方便。
5、將以上四步所有的操作代碼封裝成函數，作為scheler的其中一個作業，設置執行周期和執行時間，到點就會自動獲取數據保存在本地文件了
❹ 如何從postgresq獲取數據到pandas(不通過pandas 的 tosql函數)
直接用pandas讀取PostgreSQL數據不行嗎
from sqlalchemy import create_engine
engine = create_engine('postgresql://user@localhost:5432/mydb')
df = pd.read_sql_query('select * from "Stat_Table"',con=engine)
❺ pandas 可以載入sql語句嗎
各種資料庫有相應的軟體包，SQL Server有pyodbc，Oracle有cx_Oracle，MySQL有MySQLdb，各種包有相應的調用方法。 需要根據需求選擇合適的資料庫類型，然後選擇訪問資料庫的包進一步細化後續的工作。
❻ 基於Pandas的數據分析平台，數據連接該不該用SqlAlchemy的ORM
當然可以自己寫代碼連接到資料庫，並操作之。使用Sqlalchmy的目的主要是ROM吧。工作重心可以放在業務處理上，而不用太操心資料庫的具體操作和sql語法，特別是多表的join～Flask的文檔里，就有說怎麼直接通過Python操作資料庫的內容。
❼ python數據分析需要哪些庫
Python數據分析需要安裝的第三方擴展庫有：Numpy、Pandas、SciPy、Matplotlib、Scikit-Learn、Keras、Gensim、Scrapy等，以下是對該第三方擴展庫的簡要介紹：
1. Pandas
Pandas是Python強大、靈活的數據分析和探索工具，包含Series、DataFrame等高級數據結構和工具，安裝Pandas可使Python中處理數據非常快速和簡單。
Pandas是Python的一個數據分析包，Pandas最初被用作金融數據分析工具而開發出來，因此Pandas為時間序列分析提供了很好的支持。
Pandas是為了解決數據分析任務而創建的，Pandas納入了大量的庫和一些標準的數據模型，提供了高效的操作大型數據集所需要的工具。Pandas提供了大量是我們快速便捷的處理數據的函數和方法。Pandas包含了高級數據結構，以及讓數據分析變得快速、簡單的工具。它建立在Numpy之上，使得Numpy應用變得簡單。
帶有坐標軸的數據結構，支持自動或明確的數據對齊。這能防止由於數據結構沒有對齊，以及處理不同來源、採用不同索引的數據而產生的常見錯誤。
使用Pandas更容易處理丟失數據。
合並流行資料庫（如：基於SQL的資料庫）
Pandas是進行數據清晰/整理的最好工具。
2. Numpy
Python沒有提供數組功能，Numpy可以提供數組支持以及相應的高效處理函數，是Python數據分析的基礎，也是SciPy、Pandas等數據處理和科學計算庫最基本的函數功能庫，且其數據類型對Python數據分析十分有用。
Numpy提供了兩種基本的對象：ndarray和ufunc。ndarray是存儲單一數據類型的多維數組，而ufunc是能夠對數組進行處理的函數。Numpy的功能：
•N維數組，一種快速、高效使用內存的多維數組，他提供矢量化數學運算。
•可以不需要使用循環，就能對整個數組內的數據進行標准數學運算。
•非常便於傳送數據到用低級語言編寫(C\C++)的外部庫,也便於外部庫以Numpy數組形式返回數據。
Numpy不提供高級數據分析功能，但可以更加深刻的理解Numpy數組和面向數組的計算。
#一般以np作為numpy的別名
import numpy as np
#創建數組
a = np.array([2,1,0,5])
print(a)
print(a[:3])
print(a.min())
a.sort()
b = np.array([1,2,3],[4,5,6])
print(b*b)
3. Matplotlib
Matplotlib是強大的數據可視化工具和作圖庫，是主要用於繪制數據圖表的Python庫，提供了繪制各類可視化圖形的命令字型檔、簡單的介面，可以方便用戶輕松掌握圖形的格式，繪制各類可視化圖形。
Matplotlib是Python的一個可視化模塊，他能方便的只做線條圖、餅圖、柱狀圖以及其他專業圖形。
使用Matplotlib，可以定製所做圖表的任一方面。他支持所有操作系統下不同的GUI後端，並且可以將圖形輸出為常見的矢量圖和圖形測試，如PDF SVG JPG PNG BMP GIF.通過數據繪圖，我們可以將枯燥的數字轉化成人們容易接收的圖表。
Matplotlib是基於Numpy的一套Python包，這個包提供了吩咐的數據繪圖工具，主要用於繪制一些統計圖形。
Matplotlib有一套允許定製各種屬性的默認設置，可以控制Matplotlib中的每一個默認屬性：圖像大小、每英寸點數、線寬、色彩和樣式、子圖、坐標軸、網個屬性、文字和文字屬性。
4. SciPy
SciPy是一組專門解決科學計算中各種標准問題域的包的集合，包含的功能有最優化、線性代數、積分、插值、擬合、特殊函數、快速傅里葉變換、信號處理和圖像處理、常微分方程求解和其他科學與工程中常用的計算等，這些對數據分析和挖掘十分有用。
Scipy是一款方便、易於使用、專門為科學和工程設計的Python包，它包括統計、優化、整合、線性代數模塊、傅里葉變換、信號和圖像處理、常微分方程求解器等。Scipy依賴於Numpy，並提供許多對用戶友好的和有效的數值常式，如數值積分和優化。
Python有著像Matlab一樣強大的數值計算工具包Numpy；有著繪圖工具包Matplotlib;有著科學計算工具包Scipy。
Python能直接處理數據，而Pandas幾乎可以像SQL那樣對數據進行控制。Matplotlib能夠對數據和記過進行可視化，快速理解數據。Scikit-Learn提供了機器學習演算法的支持，Theano提供了升讀學習框架（還可以使用CPU加速）。
5. Keras
Keras是深度學習庫，人工神經網路和深度學習模型，基於Theano之上，依賴於Numpy和Scipy，利用它可以搭建普通的神經網路和各種深度學習模型，如語言處理、圖像識別、自編碼器、循環神經網路、遞歸審計網路、卷積神經網路等。
6. Scikit-Learn
Scikit-Learn是Python常用的機器學習工具包，提供了完善的機器學習工具箱，支持數據預處理、分類、回歸、聚類、預測和模型分析等強大機器學習庫，其依賴於Numpy、Scipy和Matplotlib等。
Scikit-Learn是基於Python機器學習的模塊，基於BSD開源許可證。
Scikit-Learn的安裝需要Numpy S Matplotlib等模塊，Scikit-Learn的主要功能分為六個部分，分類、回歸、聚類、數據降維、模型選擇、數據預處理。
Scikit-Learn自帶一些經典的數據集，比如用於分類的iris和digits數據集，還有用於回歸分析的boston house prices數據集。該數據集是一種字典結構，數據存儲在.data成員中，輸出標簽存儲在.target成員中。Scikit-Learn建立在Scipy之上，提供了一套常用的機器學習演算法，通過一個統一的介面來使用，Scikit-Learn有助於在數據集上實現流行的演算法。
Scikit-Learn還有一些庫，比如：用於自然語言處理的Nltk、用於網站數據抓取的Scrappy、用於網路挖掘的Pattern、用於深度學習的Theano等。
7. Scrapy
Scrapy是專門為爬蟲而生的工具，具有URL讀取、HTML解析、存儲數據等功能，可以使用Twisted非同步網路庫來處理網路通訊，架構清晰，且包含了各種中間件介面，可以靈活的完成各種需求。
8. Gensim
Gensim是用來做文本主題模型的庫，常用於處理語言方面的任務，支持TF-IDF、LSA、LDA和Word2Vec在內的多種主題模型演算法，支持流式訓練，並提供了諸如相似度計算、信息檢索等一些常用任務的API介面。
以上是對Python數據分析常用工具的簡單介紹，有興趣的可以深入學習研究一下相關使用方法!