# 实现按照用户ID分组，然后对其中一列归一化
def ratings_norm(df):
    """
    @param df：每个用户分组的dataframe
    """
    min_value = df["Rating"].min()
    max_value = df["Rating"].max()
    df["Rating_norm"] = df["Rating"].apply(
        lambda x: (x-min_value)/(max_value-min_value))
    return df

ratings = ratings.groupby("UserID").apply(ratings_norm)

ratings[ratings["UserID"]==1].head()

fpath = "./datas/beijing_tianqi/beijing_tianqi_2018.csv"
df = pd.read_csv(fpath)
# 替换掉温度的后缀℃
df.loc[:, "bWendu"] = df["bWendu"].str.replace("℃", "").astype('int32')
df.loc[:, "yWendu"] = df["yWendu"].str.replace("℃", "").astype('int32')
# 新增一列为月份
df['month'] = df['ymd'].str[:7]
df.head()

def getWenduTopN(df, topn):
    """
    这里的df，是每个月份分组group的df
    """
    return df.sort_values(by="bWendu")[["ymd", "bWendu"]][-topn:]

df.groupby("month").apply(getWenduTopN, topn=1).head()

import pandas as pd
import numpy as np
%matplotlib inline

df = pd.read_csv(
    "./datas/movielens-1m/ratings.dat",
    header=None,
    names="UserID::MovieID::Rating::Timestamp".split("::"),
    sep="::",
    engine="python"
)

df.head()

df["pdate"] = pd.to_datetime(df["Timestamp"], unit='s')

df.head()

df.dtypes

UserID                int64
MovieID               int64
Rating                int64
Timestamp             int64
pdate        datetime64[ns]
dtype: object

# 实现数据统计
df_group = df.groupby([df["pdate"].dt.month, "Rating"])["UserID"].agg(pv=np.size)

df_group.head(20)

df_stack = df_group.unstack()
df_stack

df_stack.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1ba09b0ce48>

# unstack和stack是互逆操作
df_stack.stack().head(20)

df_group.head(20)

df_reset = df_group.reset_index()
df_reset.head()

df_pivot = df_reset.pivot("pdate", "Rating", "pv")

df_pivot.head()

df_pivot.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1ba09db6d48>

import pandas as pd
%matplotlib inline

fpath = "./datas/beijing_tianqi/beijing_tianqi_2018.csv"
df = pd.read_csv(fpath)
# 替换掉温度的后缀℃
df.loc[:, "bWendu"] = df["bWendu"].str.replace("℃", "").astype('int32')
df.loc[:, "yWendu"] = df["yWendu"].str.replace("℃", "").astype('int32')
df.head()

df.set_index(pd.to_datetime(df["ymd"]), inplace=True)

df.head()

df.index

DatetimeIndex(['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04',
               '2018-01-05', '2018-01-06', '2018-01-07', '2018-01-08',
               '2018-01-09', '2018-01-10',
               ...
               '2018-12-22', '2018-12-23', '2018-12-24', '2018-12-25',
               '2018-12-26', '2018-12-27', '2018-12-28', '2018-12-29',
               '2018-12-30', '2018-12-31'],
              dtype='datetime64[ns]', name='ymd', length=365, freq=None)

# DatetimeIndex是Timestamp的列表形式
df.index[0]

Timestamp('2018-01-01 00:00:00')

# 筛选固定的某一天
df.loc['2018-01-05']

ymd          2018-01-05
bWendu                3
yWendu               -6
tianqi             多云~晴
fengxiang           西北风
fengli             1-2级
aqi                  50
aqiInfo               优
aqiLevel              1
Name: 2018-01-05 00:00:00, dtype: object

# 日期区间
df.loc['2018-01-05':'2018-01-10']

# 按月份前缀筛选
df.loc['2018-03']

# 按月份前缀筛选
df.loc["2018-07":"2018-09"].index

DatetimeIndex(['2018-07-01', '2018-07-02', '2018-07-03', '2018-07-04',
               '2018-07-05', '2018-07-06', '2018-07-07', '2018-07-08',
               '2018-07-09', '2018-07-10', '2018-07-11', '2018-07-12',
               '2018-07-13', '2018-07-14', '2018-07-15', '2018-07-16',
               '2018-07-17', '2018-07-18', '2018-07-19', '2018-07-20',
               '2018-07-21', '2018-07-22', '2018-07-23', '2018-07-24',
               '2018-07-25', '2018-07-26', '2018-07-27', '2018-07-28',
               '2018-07-29', '2018-07-30', '2018-07-31', '2018-08-01',
               '2018-08-02', '2018-08-03', '2018-08-04', '2018-08-05',
               '2018-08-06', '2018-08-07', '2018-08-08', '2018-08-09',
               '2018-08-10', '2018-08-11', '2018-08-12', '2018-08-13',
               '2018-08-14', '2018-08-15', '2018-08-16', '2018-08-17',
               '2018-08-18', '2018-08-19', '2018-08-20', '2018-08-21',
               '2018-08-22', '2018-08-23', '2018-08-24', '2018-08-25',
               '2018-08-26', '2018-08-27', '2018-08-28', '2018-08-29',
               '2018-08-30', '2018-08-31', '2018-09-01', '2018-09-02',
               '2018-09-03', '2018-09-04', '2018-09-05', '2018-09-06',
               '2018-09-07', '2018-09-08', '2018-09-09', '2018-09-10',
               '2018-09-11', '2018-09-12', '2018-09-13', '2018-09-14',
               '2018-09-15', '2018-09-16', '2018-09-17', '2018-09-18',
               '2018-09-19', '2018-09-20', '2018-09-21', '2018-09-22',
               '2018-09-23', '2018-09-24', '2018-09-25', '2018-09-26',
               '2018-09-27', '2018-09-28', '2018-09-29', '2018-09-30'],
              dtype='datetime64[ns]', name='ymd', freq=None)

# 按年份前缀筛选
df.loc["2018"].head()

# 周数字列表
df.index.week

Int64Index([ 1,  1,  1,  1,  1,  1,  1,  2,  2,  2,
            ...
            51, 51, 52, 52, 52, 52, 52, 52, 52,  1],
           dtype='int64', name='ymd', length=365)

# 月数字列表
df.index.month

Int64Index([ 1,  1,  1,  1,  1,  1,  1,  1,  1,  1,
            ...
            12, 12, 12, 12, 12, 12, 12, 12, 12, 12],
           dtype='int64', name='ymd', length=365)

# 季度数字列表
df.index.quarter

Int64Index([1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
            ...
            4, 4, 4, 4, 4, 4, 4, 4, 4, 4],
           dtype='int64', name='ymd', length=365)

df.groupby(df.index.week)["bWendu"].max().head()

ymd
1    3
2    6
3    7
4   -1
5    4
Name: bWendu, dtype: int32

df.groupby(df.index.week)["bWendu"].max().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x23300b75b88>

df.groupby(df.index.month)["bWendu"].max()

ymd
1      7
2     12
3     27
4     30
5     35
6     38
7     37
8     36
9     31
10    25
11    18
12    10
Name: bWendu, dtype: int32

df.groupby(df.index.month)["bWendu"].max().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x23302dac4c8>

df.groupby(df.index.quarter)["bWendu"].max()

ymd
1    27
2    38
3    37
4    25
Name: bWendu, dtype: int32

df.groupby(df.index.quarter)["bWendu"].max().plot()

<matplotlib.axes._subplots.AxesSubplot at 0x23302e338c8>

import pandas as pd
%matplotlib inline

df = pd.DataFrame({
    "pdate": ["2019-12-01", "2019-12-02", "2019-12-04", "2019-12-05"],
    "pv": [100, 200, 400, 500],
    "uv": [10, 20, 40, 50],
})

df

df.set_index("pdate").plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1a0d908bf48>

df_date = df.set_index("pdate")
df_date

df_date.index

Index(['2019-12-01', '2019-12-02', '2019-12-04', '2019-12-05'], dtype='object', name='pdate')

# 将df的索引设置为日期索引
df_date = df_date.set_index(pd.to_datetime(df_date.index))
df_date

df_date.index

DatetimeIndex(['2019-12-01', '2019-12-02', '2019-12-04', '2019-12-05'], dtype='datetime64[ns]', name='pdate', freq=None)

# 生成完整的日期序列
pdates = pd.date_range(start="2019-12-01", end="2019-12-05")
pdates

DatetimeIndex(['2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04',
               '2019-12-05'],
              dtype='datetime64[ns]', freq='D')

df_date_new = df_date.reindex(pdates, fill_value=0)
df_date_new

df_date_new.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1a0db1ab388>

df

df_new2 = df.set_index(pd.to_datetime(df["pdate"])).drop("pdate", axis=1)
df_new2

df_new2.index

DatetimeIndex(['2019-12-01', '2019-12-02', '2019-12-04', '2019-12-05'], dtype='datetime64[ns]', name='pdate', freq=None)

# 由于采样会让区间变成一个值，所以需要指定mean等采样值的设定方法
df_new2 = df_new2.resample("D").mean().fillna(0)
df_new2

# resample的使用方式
df_new2.resample("2D").mean()

import pandas as pd

# 学生成绩表
df_grade = pd.read_excel("./course_datas/c23_excel_vlookup/学生成绩表.xlsx") 
df_grade.head()

# 学生信息表
df_sinfo = pd.read_excel("./course_datas/c23_excel_vlookup/学生信息表.xlsx") 
df_sinfo.head()

# 只筛选第二个表的少量的列
df_sinfo = df_sinfo[["学号", "姓名", "性别"]]
df_sinfo.head()

df_merge = pd.merge(left=df_grade, right=df_sinfo, left_on="学号", right_on="学号")
df_merge.head()

df_merge.columns

Index(['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别'], dtype='object')

# 将columns变成python的列表形式
new_columns = df_merge.columns.to_list()
new_columns

['班级', '学号', '语文成绩', '数学成绩', '英语成绩', '姓名', '性别']

# 按逆序insert，会将"姓名"，"性别"放到"学号"的后面
for name in ["姓名", "性别"][::-1]:
    new_columns.remove(name)
    new_columns.insert(new_columns.index("学号")+1, name)

new_columns

['班级', '学号', '姓名', '性别', '语文成绩', '数学成绩', '英语成绩']

df_merge = df_merge.reindex(columns=new_columns)
df_merge.head()

df_merge.to_excel("./course_datas/c23_excel_vlookup/合并后的数据表.xlsx", index=False)

import pandas as pd

xlsx_path = "./datas/stocks/baidu_stocks.xlsx"
df = pd.read_excel(xlsx_path, index_col="datetime", parse_dates=True)
df.head()

df.index

DatetimeIndex(['2019-12-03', '2019-12-02', '2019-11-29', '2019-11-27',
               '2019-11-26', '2019-11-25', '2019-11-22', '2019-11-21',
               '2019-11-20', '2019-11-19',
               ...
               '2019-01-15', '2019-01-14', '2019-01-11', '2019-01-10',
               '2019-01-09', '2019-01-08', '2019-01-07', '2019-01-04',
               '2019-01-03', '2019-01-02'],
              dtype='datetime64[ns]', name='datetime', length=227, freq=None)

df.sort_index(inplace=True)
df.head()

# 如果没有安装，使用pip install pyecharts安装
from pyecharts.charts import Line
from pyecharts import options as opts

# 折线图
line = Line()

# x轴
line.add_xaxis(df.index.to_list())

# 每个y轴
line.add_yaxis("开盘价", df["open"].round(2).to_list())
line.add_yaxis("收盘价", df["close"].round(2).to_list())

# 图表配置
line.set_global_opts(
    title_opts=opts.TitleOpts(title="百度股票2019年"),
    tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross")
)

<pyecharts.charts.basic_charts.line.Line at 0x201bd51d088>

# 渲染数据
line.render_notebook()

import pandas as pd

df_train = pd.read_csv("./datas/titanic/titanic_train.csv")
df_train.head()

# 我们只挑选两列，作为预测需要的特征
feature_cols = ['Pclass', 'Parch']
X = df_train.loc[:, feature_cols]
X.head()

# 单独提取是否存活的列，作为预测的目标
y = df_train.Survived
y.head()

0    0
1    1
2    1
3    1
4    0
Name: Survived, dtype: int64

from sklearn.linear_model import LogisticRegression
# 创建模型对象
logreg = LogisticRegression()

# 实现模型训练
logreg.fit(X, y)
//anaconda3/lib/python3.7/site-packages/sklearn/linear_model/logistic.py:432: FutureWarning: Default solver will be changed to 'lbfgs' in 0.22. Specify a solver to silence this warning.
  FutureWarning)

LogisticRegression(C=1.0, class_weight=None, dual=False, fit_intercept=True,
                   intercept_scaling=1, l1_ratio=None, max_iter=100,
                   multi_class='warn', n_jobs=None, penalty='l2',
                   random_state=None, solver='warn', tol=0.0001, verbose=0,
                   warm_start=False)

logreg.score(X, y)

0.6879910213243546

# 找一个历史数据中不存在的数据
X.drop_duplicates().sort_values(by=["Pclass", "Parch"])