<jupyter_start><jupyter_text>配置选项<jupyter_code>import numpy as np
import pandas as pd

pd.set_option('display.max_rows',500)
pd.set_option('display.max_columns',500)
pd.set_option('display.width',1000)
pd.set_option('display.max_colwidth',1000)<jupyter_output><empty_output><jupyter_text>数据导入导出<jupyter_code>df1 = pd.read_csv("data/pcs_users_1.csv", "\t")
df1
df2 = pd.read_csv("data/pcs_users_2.csv", "\t")
df2
df3 = pd.read_csv("data/pcs_appstore.csv", "\t")
df3
print(df1.shape), print(df1.shape[0]), print(df1.shape[1])<jupyter_output>(4, 4)
4
4<jupyter_text>行列截取<jupyter_code>df1[["pkg", "cert", "size"]]
df1.loc[1:2]
df1.iloc[1:2]
df1[1:2]
df1[1:2][["pkg", "cert"]]
df1.iloc[1:2, 0:2]
df1.loc[1:2, ("pkg", "cert")]<jupyter_output><empty_output><jupyter_text>数据拼接<jupyter_code># 拼接
df = df1.append(df2)
df
# 拼接
pd.concat([df1, df2])<jupyter_output><empty_output><jupyter_text>去重<jupyter_code># 去重
df = df.drop_duplicates()
df
# 重置索引（默认会增加"index"列，drop=True则去掉该列）
df = df.reset_index(drop=True)
df
df.groupby(by="pkg").sum()
df.groupby(by="pkg").count()
ser = df.groupby(by=["pkg", "cert"]).sum()
ser
ser.loc["com.tencent.mobileqq"]
ser.loc["com.tencent.mobileqq", "a6b745bf24a2c277527716f6f36eb68d"]
ser.loc["com.tencent.mobileqq", "a6b745bf24a2c277527716f6f36eb68d"]["users"]
ser = df.groupby(by="pkg")["users"].sum()
ser
ser["com.tencent.mm"]
# 累计
df.groupby(by="pkg")["users"].describe()
df.groupby(by="pkg")["users"].describe()[["mean", "std"]]
# 求交集
df1.merge(df2, how="inner")
# 关联两张不同列的表
df1.merge(df3, on=["pkg", "cert", "size"], how="inner")
df1.merge(df3, left_on=["pkg", "cert", "size"], right_on=["pkg", "cert", "size"], how="inner")
df1.merge(df3, on=["pkg", "cert", "size"], how="left")
df1.merge(df3, on=["pkg", "cert", "size"], how="right")
df13_outer = df1.merge(df3, on=["pkg", "cert", "size"], how="outer")
df13_outer
df13_outer.isnull().any()
df13_outer[df13_outer.isnull()]
df13_outer[df13_outer["users"].isnull()]
df13_outer[df13_outer.notnull()]
df13_outer[df13_outer.users.notnull()]
df13_outer.fillna(0)
# 默认会剔除任何包含缺失值的整行数据，how默认是any
# df13_outer.dropna(inplace=True)
df13_outer.dropna()
# 剔除全部是缺失值的行或列，
df13_outer.dropna(axis="rows", how="all")
df13_outer.dropna(axis="columns", how="all")
# 指定列为空才删除
df13_outer.dropna(subset=["users"])
df1.aggregate( ["min", "max", "sum"])
df1.aggregate({"users": ["min", "max", "sum"]})
df1.aggregate({"cert": "count",  "size": "max", "users": "sum"})
df1.groupby("pkg").aggregate({"cert": "count",  "size": "max", "users": np.sum})
df1.groupby(["pkg", "cert"]).aggregate({"cert": "count",  "size": "max", "users": np.sum})
df1.groupby(["pkg"]).std()
def filter_func(x):
    return x["size"].std() > 5000

df1.groupby("pkg").filter(filter_func)
df1.groupby("pkg").transform(lambda x: x - x.mean())
def apply_func(x):
    # x是一个分组数据的DataFrame
    x["isTencent"] = x.pkg.str.contains("tencent")
    x["users"] /= x["users"].max()
    return x

df1.groupby("pkg").apply(apply_func)
df1[df1.pkg.isin(df2.pkg)&df1.cert.isin(df2.cert)&df1["size"].isin(df2["size"])&df1.users.isin(df2.users)]
df1["pcs"] = df1.pkg.str.cat(df1.cert, sep="|").str.cat(df1["size"].astype(str), sep="|")
df1
df1.pcs.str.split("|", 2)
df1["p"], df1["c"], df1["s"] = df1.pcs.str.split("|", 2).str
df1
df1["isTencent"] = df1.pkg.str.contains("tencent")
df1
# one-hot
df1["pkg"].str.get_dummies(".")
df1+df2
df1.drop(columns=["pkg", "users"])
[x for x in range(10) if x>5]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.24556299098050627, 'fraction_numerical': 0.055280768111725345, 'mean_word_length': 7.92987012987013, 'pattern_counts': {'":': 10, '<': 20, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '526446', 'license': 'Apache-2.0', 'n_tokens_mistral': 1686, 'n_tokens_neox': 1643, 'n_words': 202, 'path': '_docs/pandas/pandas_test.ipynb', 'repo_name': 'sbnote/sbnote.github.io', 'size': '3437'}