九色国产,午夜在线视频,新黄色网址,九九色综合,天天做夜夜做久久做狠狠,天天躁夜夜躁狠狠躁2021a,久久不卡一区二区三区

一、加載數(shù)據(jù)

Copy
import pandas as pdimport numpy as npurl = ('https://raw.github.com/pandas-dev/pandas/master/pandas/tests/data/tips.csv')tips = pd.read_csv(url)output = tips.head()

Output:

Copy
   total_bill   tip     sex smoker  day    time  size0       16.99  1.01  Female     No  Sun  Dinner     21       10.34  1.66    Male     No  Sun  Dinner     32       21.01  3.50    Male     No  Sun  Dinner     33       23.68  3.31    Male     No  Sun  Dinner     24       24.59  3.61  Female     No  Sun  Dinner     4

二、SELECT 的使用方式

sql 語句： SELECT total_bill, tip, smoker, time FROM tips LIMIT 5;。

Copy
output = tips[['total_bill', 'tip', 'smoker', 'time']].head(5)

Output:

Copy
   total_bill   tip smoker    time0       16.99  1.01     No  Dinner1       10.34  1.66     No  Dinner2       21.01  3.50     No  Dinner3       23.68  3.31     No  Dinner4       24.59  3.61     No  Dinner

三、WHERE 的使用方式

1. 舉個栗子

sql 語句： SELECT * FROM tips WHERE time = 'Dinner' LIMIT 5;

Copy
output = tips[tips['time'] == 'Dinner'].head(5)# 或者output = tips.query("time == 'Dinner'").head(5)

Output:

Copy
   total_bill   tip     sex smoker  day    time  size0       16.99  1.01  Female     No  Sun  Dinner     21       10.34  1.66    Male     No  Sun  Dinner     32       21.01  3.50    Male     No  Sun  Dinner     33       23.68  3.31    Male     No  Sun  Dinner     24       24.59  3.61  Female     No  Sun  Dinner     4

2. 比較運算符：等于 ==、 大于 >、 大于等于 >=、小于等于 <=、不等于 !=

2.1 等于 ==

sql 語句：SELECT * FROM tips WHERE time = 'Dinner';。

Copy
output = tips[(tips['time'] == 'Dinner')]

2.2 大于 >

sql 語句：SELECT * FROM tips WHERE tip > 5.00;。

Copy
output = tips[(tips['tip'] > 5.00)]

2.3 大于等于 >=

sql 語句：SELECT * FROM tips WHERE tip >= 5.00;。

Copy
output = tips[(tips['size'] >= 5)]

2.4 小于等于 <=

sql 語句：SELECT * FROM tips WHERE tip <= 5.00;。

Copy
output = tips[(tips['size'] <= 5)]

2.5 不等于 !=

sql 語句：SELECT * FROM tips WHERE tip <> 5.00;。

Copy
output = tips[(tips['size'] != 5)]

3. 邏輯運算符：且 &、或 |、非 -

3.1 且 &

sql 語句：SELECT * FROM tips WHERE time = 'Dinner' AND tip > 5.00;

Copy
output = tips[(tips['time'] == 'Dinner') & (tips['tip'] > 5.00)]

3.2 或 |

sql 語句：SELECT * FROM tips WHERE size >= 5 OR total_bill > 45;。

Copy
output = tips[(tips['size'] >= 5) | (tips['total_bill'] > 45)]

3.3 非 -

sql 語句：SELECT * FROM tips WHERE not (size <> 5 AND size > 4);

Copy
output = df[-((df['size'] != 5) & (df['size'] > 4))]

4. Null 的判斷

這里重新定義一個包含 NaN 數(shù)據(jù)的 DataFrame。

Copy
frame = pd.DataFrame({                        'col1': ['A', 'B', np.NaN, 'C', 'D'],                        'col2': ['F', np.NaN, 'G', 'H', 'I']                    })output = frame

Output:

Copy
  col1 col20    A    F1    B  NaN2  NaN    G3    C    H4    D    I

4.1 判斷列是 Null

sql 語句：SELECT * FROM frame WHERE col2 IS NULL;。

Copy
output = frame[frame['col2'].isna()]

Output:

Copy
  col1 col21    B  NaN

4.2 判斷列不是 Null

sql 語句：SELECT * FROM frame WHERE col1 IS NOT NULL;。

Copy
output = frame[frame['col1'].notna()]

Output:

Copy
  col1 col20    A    F1    B  NaN3    C    H4    D    I

5. In、Like 操作

5.1 In

sql 語句：SELECT * FROM tips WHERE siez in (5, 6);。

Copy
output = tips[tips['size'].isin([2, 5])]

5.2 Like

sql 語句：SELECT * FROM tips WHERE time like 'Din%';。

Copy
output = tips[tips.time.str.contains('Din*')]

四、GROUP BY 的使用方式

sql 語句：SELECT sex, count(*) FROM tips GROUP BY sex;

Copy
output = tips.groupby('sex').size()# 獲取相應的結(jié)果output['Male']output['Female']

Copy
output = tips.groupby('sex').count()# 獲取相應的結(jié)果output['tip']['Female']

Copy
output = tips.groupby('sex')['total_bill'].count()# 獲取相應的結(jié)果output['Male']output['Female']

sql 語句：SELECT day, AVG(tip), COUNT(*) FROM tips GROUP BY day;

Copy
output = tips.groupby('day').agg({'tip': np.mean, 'day': np.size})# 獲取相應的結(jié)果output['day']['Fri']output['tip']['Fri']

sql 語句：SELECT smoker, day, COUNT(*), AVG(tip) FROM tips GROUP BY smoker, day;

Copy
output = tips.groupby(['smoker', 'day']).agg({'tip': [np.size, np.mean]})# 獲取相應的結(jié)果output['tip']['size']['No']['Fri']

sql 語句：SELECT tip, count(distinct sex) FROM tips GROUP BY tip;

Copy
output = tips.groupby('tip').agg({'sex': pd.Series.nunique})

五、JOIN 連接的使用方式

定義兩個 DataFrame。

Copy
df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value': np.random.randn(4)})df2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'], 'value': np.random.randn(4)})

1. 內(nèi)連接 Inner Join

sql 語句：SELECT * FROM df1 INNER JOIN df2 ON df1.key = df2.key;

Copy
output = pd.merge(df1, df2, on='key')# 或indexed_df2 = df2.set_index('key')pd.merge(df1, indexed_df2, left_on='key', right_index=True)

2. 左連接 Left Outer Join

sql 語句：SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.key = df2.key;

Copy
output = pd.merge(df1, df2, on='key', how='left')# 或output = df1.join(df2, on='key', how='left')

3. 右連接 Right Join

sql 語句：SELECT * FROM df1 RIGHT OUTER JOIN df2 ON df1.key = df2.key;

Copy
output = pd.merge(df1, df2, on='key', how='right')

4. 全連接 Full Join

sql 語句：SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.key = df2.key;

Copy
output = pd.merge(df1, df2, on='key', how='outer')

五、UNION 的使用方式

Copy
df1 = pd.DataFrame({'city': ['Chicago', 'San Francisco', 'New York City'], 'rank': range(1, 4)})df2 = pd.DataFrame({'city': ['Chicago', 'Boston', 'Los Angeles'], 'rank': [1, 4, 5]})

sql 語句：SELECT city, rank FROM df1 UNION ALL SELECT city, rank FROM df2;

Copy
output = pd.concat([df1, df2])

sql 語句：SELECT city, rank FROM df1 UNION SELECT city, rank FROM df2;

Copy
output = pd.concat([df1, df2]).drop_duplicates()

六、與 SQL 等價的其他語法

1. 去重 Distinct

sql 語句：SELECT DISTINCT sex FROM tips;

Copy
output = tips.drop_duplicates(subset=['sex'], keep='first', inplace=False)

2. 修改列別名 As

sql 語句：SELECT total_bill AS total, sex AS xes FROM tips;

Copy
output = tips.rename(columns={'total_bill': 'total', 'sex': 'xes'}, inplace=False)

3. Limit 與 Offset

sql 語句：SELECT * FROM tips ORDER BY tip DESC LIMIT 10 OFFSET 5;

Copy
output = tips.nlargest(10 + 5, columns='tip').tail(10)

4. 每個 Group 的前幾行

sql 語句：

Copy
SELECT * FROM (  SELECT    t.*,    ROW_NUMBER() OVER(PARTITION BY day ORDER BY total_bill DESC) AS rn  FROM tips t)WHERE rn < 3ORDER BY day, rn;

Copy
output = tips.assign(rn=tips.sort_values(['total_bill'], ascending=False).                     groupby(['day']).cumcount() + 1).    query('rn < 3').    sort_values(['day', 'rn'])

七、Update 的使用方式

sql 語句：UPDATE tips SET tip = tip*2 WHERE tip < 2;

Copy
output = tips.loc[tips['tip'] < 2, 'tip'] *= 2

八、Delete 的使用方式

sql 語句：DELETE FROM tips WHERE tip > 9;

Copy
output = tips = tips.loc[tips['tip'] <= 9]

九、參考文章

• 【Python實戰(zhàn)】Pandas：讓你像寫SQL一樣做數(shù)據(jù)分析（一）
• Comparison with SQL