Pandas

import numpy as np
import pandas as pd

s = pd.Series([1, 3, 5, np.nan, 6, 8])

dates = pd.date_range('20130101', periods=6)
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list('ABCD'))

df2 = pd.DataFrame({ 'A': 1., 'B': pd.Timestamp('20130102'),
    'C': pd.Series(1, index=list(range(4)), dtype='float32'),
    'D': np.array([3] * 4, dtype='int32'),
    'E': pd.Categorical(["test", "train", "test", "train"]), 'F': 'foo' })
df2.dtypes

df.head()              # 头部数据，默认 5 行
df.tail(3)             # 尾部数据
df.index               # 索引
df.columns             # 列名
df.to_numpy()          # 底层 NumPy 数组
df.describe()          # 统计摘要
df.T                   # 转置
df.sort_index(axis=1, ascending=False)  # 按轴排序
df.sort_values(by='B')                  # 按值排序

df['A']                              # 选择单列
df[0:3]                              # 行切片
df.loc[dates[0]]                     # 按标签提取行
df.loc[:, ['A', 'B']]                # 选择多列
df.loc['20130102':'20130104', ['A', 'B']]  # 标签切片
df.iloc[3]                           # 按位置选择
df.iloc[3:5, 0:2]                    # 整数切片
df.iloc[[1, 2, 4], [0, 2]]           # 整数列表
df[df.A > 0]                         # 布尔索引
df[df > 0]                           # 条件筛选
df2[df2['E'].isin(['two', 'four'])]  # isin 筛选

df['F'] = s1                         # 新增列
df.at[dates[0], 'A'] = 0             # 按标签赋值
df.iat[0, 1] = 0                     # 按位置赋值
df.loc[:, 'D'] = np.array([5] * len(df))
df2[df2 > 0] = -df2                  # 条件赋值

df1 = df.reindex(index=dates[0:4], columns=list(df.columns) + ['E'])
df1.dropna(how='any')                # 删除含缺失值的行
df1.fillna(value=5)                  # 填充缺失值
pd.isna(df1)                         # 布尔掩码

df.mean()                            # 每列均值
df.mean(1)                           # 每行均值
df.apply(np.cumsum)                  # Apply 函数
df.apply(lambda x: x.max() - x.min())
s.value_counts()                     # 直方图
s.str.lower()                        # 字符串方法

pd.concat([df[:3], df[3:7], df[7:]])  # Concat
pd.merge(left, right, on='key')        # Join

df.groupby('A').sum()
df.groupby(['A', 'B']).sum()

stacked = df2.stack()
stacked.unstack()

pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])

ts.resample('5Min').sum()            # 重采样
ts.tz_localize('UTC')                # 时区设置
ts.tz_convert('US/Eastern')          # 时区转换
ts.to_period()                       # 转时间段

df["grade"] = df["raw_grade"].astype("category")
df["grade"].cat.categories = ["very good", "good", "very bad"]

ts = pd.Series(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))
ts = ts.cumsum()
ts.plot()

df.to_csv('foo.csv')                 # CSV
pd.read_csv('foo.csv')
df.to_hdf('foo.h5', 'df')            # HDF5
pd.read_hdf('foo.h5', 'df')
df.to_excel('foo.xlsx')              # Excel
pd.read_excel('foo.xlsx', 'Sheet1')

s = pd.Series(data, index=index)

s = pd.Series(np.random.randn(5), index=['a', 'b', 'c', 'd', 'e'])
s.index
# Index(['a', 'b', 'c', 'd', 'e'], dtype='object')

d = {'b': 1, 'a': 0, 'c': 2}
pd.Series(d)

pd.Series(d, index=['b', 'c', 'd', 'a'])
# b    1.0, c    2.0, d    NaN, a    0.0

pd.Series(5., index=['a', 'b', 'c', 'd', 'e'])

s[0]                    # 按位置索引
s[:3]                   # 切片
s[s > s.median()]       # 布尔选择
s[[4, 3, 1]]            # 数组索引
np.exp(s)               # NumPy 函数
s.array                 # 提取扩展数组
s.to_numpy()            # 提取 NumPy 数组

s['a']                  # 用标签提取值
s['e'] = 12.            # 设置值
'e' in s                # 成员检查
s.get('f')              # 安全提取，不存在返回 None

s + s                   # 矢量加法
s * 2                   # 标量乘法
s[1:] + s[:-1]          # 自动基于标签对齐，不重叠的标签结果为 NaN

s = pd.Series(np.random.randn(5), name='something')
s.name
s2 = s.rename("different")

df = pd.DataFrame(data, index=index, columns=columns)

d = {'one': pd.Series([1., 2., 3.], index=['a', 'b', 'c']),
     'two': pd.Series([1., 2., 3., 4.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(d)
pd.DataFrame(d, index=['d', 'b', 'a'], columns=['two', 'three'])

d = {'one': [1., 2., 3., 4.], 'two': [4., 3., 2., 1.]}
pd.DataFrame(d)
pd.DataFrame(d, index=['a', 'b', 'c', 'd'])

data2 = [{'a': 1, 'b': 2}, {'a': 5, 'b': 10, 'c': 20}]
pd.DataFrame(data2)

pd.DataFrame({('a', 'b'): {('A', 'B'): 1, ('A', 'C'): 2}})

df['one']                    # 提取列
df['three'] = df['one'] * df['two']  # 新增列
del df['two']                # 删除列
df.pop('three')              # 弹出列
df.insert(1, 'bar', df['one'])  # 指定位置插入

iris.assign(sepal_ratio=iris['SepalWidth'] / iris['SepalLength']).head()
iris.assign(sepal_ratio=lambda x: (x['SepalWidth'] / x['SepalLength'])).head()

df + df2                          # 自动对齐列与行标签
df - df.iloc[0]                   # DataFrame 与 Series 广播
df.sub(row, axis='columns')       # 显式控制广播方向

df[:5].T
np.exp(df)
np.asarray(df)

baseball.info()
pd.set_option('display.width', 40)
pd.set_option('display.max_colwidth', 30)

long_series.head()      # 默认 5 条
long_series.tail(3)     # 指定数量

df.shape                # 形状
df.columns              # 列名
df.index                # 行索引
s.array                 # 扩展数组
s.to_numpy()            # NumPy 数组

df.sub(row, axis='columns')   # 按列广播
df.sub(column, axis='index')  # 按行广播
df.add(df2, fill_value=0)     # 填充缺失值运算

df.apply(np.cumsum)
df.apply(lambda x: x.max() - x.min())
df.applymap(lambda x: '%.2f' % x)  # 逐元素

df.reindex(index=dates[0:4], columns=list(df.columns) + ['E'])

for col in df:           # 迭代列名
for idx, row in df.iterrows():  # 迭代行
for col_name, series in df.items():  # 迭代列

s = pd.Series(pd.date_range('20130101 09:10:12', periods=4))
s.dt.hour
s.dt.day
s.dt.tz_localize('US/Eastern')
s.dt.strftime('%Y/%m/%d')

s = pd.Series(['A', 'B', 'C', 'Aaba', 'Baca', np.nan, 'CABA', 'dog', 'cat'])
s.str.lower()
s.str.upper()

df.sort_index()                    # 按索引排序
df.sort_values(by='two')           # 按值排序
df.sort_values(by=['one', 'two'])  # 多列排序

shallow = df.copy()               # 深复制
deep = df.copy(deep=True)

df3.astype('float32')              # 转换所有列
dft.astype({'a': np.bool, 'c': np.float64})  # 字典指定
pd.to_numeric(m)                   # 转换为数值型
pd.to_datetime(m)                  # 转换为 datetime
pd.to_timedelta(m)                 # 转换为 timedelta
pd.to_numeric(m, errors='coerce')  # 强制转换
df.select_dtypes(include=[bool])   # 基于 dtype 选择列

url = 'https://raw.github.com/pandas-dev/pandas/master/pandas/tests/data/tips.csv'
tips = pd.read_csv(url)

SELECT total_bill, tip, smoker, time FROM tips LIMIT 5;

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

SELECT * FROM tips WHERE time = 'Dinner' LIMIT 5;

tips[tips['time'] == 'Dinner'].head(5)

SELECT * FROM tips WHERE time = 'Dinner' AND tip > 5.00;

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

SELECT * FROM tips WHERE size >= 5 OR total_bill > 45;

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

frame[frame['col2'].isna()]      # IS NULL
frame[frame['col1'].notna()]     # IS NOT NULL

SELECT sex, count(*) FROM tips GROUP BY sex;

tips.groupby('sex').size()

SELECT day, AVG(tip), COUNT(*) FROM tips GROUP BY day;

tips.groupby('day').agg({'tip': np.mean, 'day': np.size})

SELECT smoker, day, COUNT(*), AVG(tip) FROM tips GROUP BY smoker, day;

tips.groupby(['smoker', 'day']).agg({'tip': [np.size, np.mean]})

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)})

pd.merge(df1, df2, on='key')              # INNER JOIN
pd.merge(df1, df2, on='key', how='left')  # LEFT JOIN
pd.merge(df1, df2, on='key', how='right') # RIGHT JOIN
pd.merge(df1, df2, on='key', how='outer') # FULL JOIN

pd.concat([df1, df2])                     # UNION ALL
pd.concat([df1, df2]).drop_duplicates()   # UNION

SELECT * FROM tips ORDER BY tip DESC LIMIT 10 OFFSET 5;

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

UPDATE tips SET tip = tip*2 WHERE tip < 2;

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

DELETE FROM tips WHERE tip > 9;

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