測試環境為 CentOS 8 (虛擬機)
有快速可以對比 SQL 語法成 Python Pandas 程式嗎? 發現這一篇 – https://stackoverflow.com/questions/17071871/how-do-i-select-rows-from-a-dataframe-based-on-column-values
SELECT * FROM table WHERE column_name = some_value
Pandas 處理二維陣列 (資料類似 Excel 或是 RDBMS 關聯式資料庫) , 使用 DataFrame 物件 ,下面可以來看一下怎麼對比成 SQL 語法.
安裝所需模組
[root@localhost ~]# pip install pandas
匯入模組
[root@localhost ~]# python3 Python 3.6.8 (default, Sep 10 2021, 09:13:53) [GCC 8.5.0 20210514 (Red Hat 8.5.0-3)] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import pandas as pd
以以下 JSON 格式 為範例.
>>> data = pd.DataFrame({
"Chinese":
{
"Ben": 68,
"Alex": 86,
"Jeff": 57
},
"English":
{
"Ben": 63,
"Alex": 92,
"Jeff": 83
},
"Math":
{
"Ben": 65,
"Alex": 89,
"Jeff": 77
},
"From":
{
"Ben": "Taipei",
"Alex": "Taichung",
"Jeff": "Tainan"
}
})
>>> data
Chinese English Math From
Ben 68 63 65 Taipei
Alex 86 92 89 Taichung
Jeff 57 83 77 Tainan
- WHERE column=value
可以使用 ==>>> data.loc[data["From"] == "Taipei"] Chinese English Math From Ben 68 63 65 Taipei或是
>>> data.loc[data.From == "Taipei"] Chinese English Math From Ben 68 63 65 Taipei - WERER column IN ()
可以使用 isin>>> data.loc[data["From"].isin(["Taipei","Tainan"])] Chinese English Math From Ben 68 63 65 Taipei Jeff 57 83 77 Tainan - WHERE column > value AND column < value
可以使用 &>>> data.loc[(data["Chinese"] > 60) & (data["Chinese"] < 80)] Chinese English Math From Ben 68 63 65 Taipei - WHERE column != value
可以使用 !=>>> data.loc[data["From"] != "Taipei"] Chinese English Math From Alex 86 92 89 Taichung Jeff 57 83 77 Tainan - WERER column NOT IN ()
可以使用 isin + ~ , isin 回傳 Boolean – True , False , 使用 ~ negate 就可以把 True 轉變成 Fales , False 轉變成 True 了.>>> data.loc[~data['From'].isin(["Taipei","Tainan"])] Chinese English Math From Alex 86 92 89 Taichung - ORDER BY
可以使用 sort_values 函數.>>> data.sort_values(by="Chinese" , ascending=False) Chinese English Math From Alex 86 92 89 Taichung Ben 68 63 65 Taipei Jeff 57 83 77 Tainan>>> data.sort_values(by=["Chinese","English"] , ascending=False) Chinese English Math From Alex 86 92 89 Taichung Ben 68 63 65 Taipei Jeff 57 83 77 Tainan或是 sort_index 函數來針對 Index 來排序.
>>> data.sort_index(axis=0, ascending=True) Chinese English Math From Alex 86 92 89 Taichung Ben 68 63 65 Taipei Jeff 57 83 77 Tainan該函數還可以針對 columns name 做排序.
>>> data.sort_index(axis=1, ascending=True) Chinese English From Math Ben 68 63 Taipei 65 Alex 86 92 Taichung 89 Jeff 57 83 Tainan 77 - 透過 query Method
>>> data.query('Chinese > 80') Chinese English Math From Alex 86 92 89 Taichung >>> data.query('Chinese > 60 & English >90 ' ) Chinese English Math From Alex 86 92 89 Taichung
沒有解決問題,試試搜尋本站其他內容