100-pandas-puzzles-with-solutions-cn.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.4"},"colab":{"name":"100-pandas-puzzles-with-solutions-cn.ipynb","provenance":[],"collapsed_sections":["a0KpqdoIV59z","kv4dbHVAV5-B","7fqjJgt4V5-X"]}},"cells":[{"cell_type":"markdown","metadata":{"id":"KL1m1vIvV58R"},"source":["# 100 pandas puzzles 中文版\n","\n","受 [numpy-100](https://github.com/rougier/numpy-100) 的启发，本仓库将提供 100 个（仍在更新）小问题用于测试你对 Pandas 的掌握。\n","\n","Pandas 是一个具有非常多专业特性和功能的大型库，由于时间和能力所限，本仓库并不能全面涉及。本仓库设计的练习主要集中在利用 DataFrame 和 Series 对象操作数据的基础上（索引、分组、聚合、清洗）。许多问题的解决方案只需要几行代码，因此选择正确的方法和遵循最佳实践是基本目标。\n","\n","练习题将根据难度等级来划分，当然，这些等级是主观的，但也这些等级也可以作为难度的大致参考，让你对题目难度大致有个数。\n","\n","如果你刚刚接触 Pandas，那么以下是一些不错的学习资源方便你快速上手：\n","\n","- [10 minutes to pandas](http://pandas.pydata.org/pandas-docs/version/0.17.0/10min.html)\n","- [pandas basics](http://pandas.pydata.org/pandas-docs/version/0.17.0/basics.html)\n","- [tutorials](http://pandas.pydata.org/pandas-docs/stable/tutorials.html)\n","- [cookbook and idioms](http://pandas.pydata.org/pandas-docs/version/0.17.0/cookbook.html#cookbook)\n","- [Guilherme Samora's pandas exercises](https://github.com/guipsamora/pandas_exercises)\n","\n","祝你玩的愉快！\n","\n","*关于 Pandas 的 100 题目目前还没有完成！如果你有想反馈或是改进的建议，欢迎提 PR 或是 issue。*"]},{"cell_type":"markdown","metadata":{"id":"LvgXBOh2V58U"},"source":["## 导入 Pandas\n","\n","### 准备好使用 Pandas 吧！\n","\n","难度： *简单* \n","\n","**1.** 以 `pd` 别名导入 pandas 库"]},{"cell_type":"code","metadata":{"id":"csRldte2V58V"},"source":["import pandas as pd"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"zUQyo3ROV58Y"},"source":["**2.** 打印出导入 pandas 库的版本信息"]},{"cell_type":"code","metadata":{"id":"xFOZOdxFV58Y"},"source":["pd.__version__"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"w81DkHCZV58b"},"source":["**3.** 打印 pandas 依赖包及其 *版本* 信息"]},{"cell_type":"code","metadata":{"id":"csegejgKV58b"},"source":["pd.show_versions()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"kxf4Ta2fV58d"},"source":["## DataFrame：基础知识\n","\n","### 使用 DataFrame 进行选择、排序、添加以及聚合数据。\n","\n","难度： *简单*\n","\n","提示： 记得使用如下命令导入 numpy：\n","```python\n","import numpy as np\n","```\n","\n","有如下字典 `data` 以及列表 `labels`:\n","\n","``` python\n","data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],\n","        'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],\n","        'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],\n","        'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}\n","\n","labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']\n","```\n","（这只是以动物和看兽医为主题编造的一些无意义的数据）\n","\n","**4.** 使用数据 `data` 和行索引 `labels` 创建一个 DataFrame `df` "]},{"cell_type":"code","metadata":{"id":"0RbrJv8NV58d"},"source":["import numpy as np\n","\n","data = {'animal': ['cat', 'cat', 'snake', 'dog', 'dog', 'cat', 'snake', 'cat', 'dog', 'dog'],\n","        'age': [2.5, 3, 0.5, np.nan, 5, 2, 4.5, np.nan, 7, 3],\n","        'visits': [1, 3, 2, 3, 2, 3, 1, 1, 2, 1],\n","        'priority': ['yes', 'yes', 'no', 'yes', 'no', 'no', 'no', 'yes', 'no', 'no']}\n","\n","labels = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j']\n","\n","df = pd.DataFrame(data, index=labels)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"UJebCukAV58f"},"source":["**5.** 显示该 DataFrame 及其数据相关的基本信息（*提示：DataFrame 直接调用的方法*）"]},{"cell_type":"code","metadata":{"id":"pLmdysmNV58g"},"source":["df.info()\n","\n","# 或者\n","\n","df.describe()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"K9uvDN3AV58i"},"source":["**6.** 返回 DataFrame `df` 的前三行数据"]},{"cell_type":"code","metadata":{"id":"94xnizJfV58i"},"source":["df.iloc[:3]\n","\n","# 等价于\n","\n","df.head(3)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"t64aNT5rV58k"},"source":["**7.** 从 DataFrame `df` 选择标签为 `animal` 和 `age` 的列"]},{"cell_type":"code","metadata":{"id":"P9U2WIMdV58l"},"source":["df.loc[:, ['animal', 'age']]\n","\n","# 或者\n","\n","df[['animal', 'age']]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"X4_Vj7qbV58n"},"source":["**8.** 在 `[3, 4, 8]` 行中 *且* 列为 `['animal', 'age']` 的数据"]},{"cell_type":"code","metadata":{"id":"Qb3mihdwV58n"},"source":["df.loc[df.index[[3, 4, 8]], ['animal', 'age']]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"eQkovotsV58q"},"source":["**9.** 选择 `visits` 大于 3 的行"]},{"cell_type":"code","metadata":{"id":"WfJ4STgeV58q"},"source":["df[df['visits'] > 3]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"Q2BylA35V58s"},"source":["**10.** 选择 `age` 为缺失值的行，如 `NaN`"]},{"cell_type":"code","metadata":{"id":"MkDKhAPmV58t"},"source":["df[df['age'].isnull()]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"KL1Ma72RV58u"},"source":["**11.** 选择 `animal` 是 cat *且* `age` 小于 3 的行"]},{"cell_type":"code","metadata":{"id":"PTwHJGeMV58v"},"source":["df[(df['animal'] == 'cat') & (df['age'] < 3)]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"ZUUdsSfbV58w"},"source":["**12.** 选择 `age` 在 2 到 4 之间的数据（包含边界值）"]},{"cell_type":"code","metadata":{"id":"a_8VFNQ0V58x"},"source":["df[df['age'].between(2, 4)]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"E-3uwtF8V58z"},"source":["**13.** 将 'f' 行的 `age` 改为 1.5"]},{"cell_type":"code","metadata":{"id":"8V4T0r0-V58z"},"source":["df.loc['f', 'age'] = 1.5"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"nZmhFKP0V581"},"source":["**14.** 计算 `visits` 列的数据总和"]},{"cell_type":"code","metadata":{"id":"_Uy-_5AvV582"},"source":["df['visits'].sum()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"bOuRukSKV583"},"source":["**15.** 计算每种 `animal` `age` 的平均值"]},{"cell_type":"code","metadata":{"id":"UQkBhY0zV584"},"source":["df.groupby('animal')['age'].mean()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"1JdrIWYYV585"},"source":["**16.** 追加一行 k，列的数据自定义，然后再删除新追加的 k 行"]},{"cell_type":"code","metadata":{"id":"SlzhKknIV586"},"source":["df.loc['k'] = [5.5, 'dog', 'no', 2]\n","\n","# 接着删除新添加的 k 行\n","\n","df = df.drop('k')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"7KHfKyHbV588"},"source":["**17.** 计算每种 `animal` 的总数"]},{"cell_type":"code","metadata":{"id":"yTO8u3F8V588"},"source":["df['animal'].value_counts()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"UPEHrJD4V58-"},"source":["**18.** 先根据 `age` 降序排列，再根据 `visits` 升序排列（结果 `i` 列在前面，`d` 列在最后面）"]},{"cell_type":"code","metadata":{"id":"VfMia25bV58-"},"source":["df.sort_values(by=['age', 'visits'], ascending=[False, True])"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"RKU_p2E7V59A"},"source":["**19.** 将 `priority` 列的 `yes` 和 `no` 用 `True` 和 `False` 替换"]},{"cell_type":"code","metadata":{"id":"bkf1aIWOV59A"},"source":["df['priority'] = df['priority'].map({'yes': True, 'no': False})"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"9m3STY0vV59D"},"source":["**20.** 将 `animal` 列的 `snake` 用 `python` 替换"]},{"cell_type":"code","metadata":{"id":"Ia_uy99wV59D"},"source":["df['animal'] = df['animal'].replace('snake', 'python')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"OfzE7-TMV59F"},"source":["**21.** 对于每种 `animal` 和 `visit`，求出平均年龄。换句话说，每一行都是动物，每一列都是访问次数，其值是平均年龄（提示：使用数据透视表）"]},{"cell_type":"code","metadata":{"id":"0X-yAfjQV59G"},"source":["df.pivot_table(index='animal', columns='visits', values='age', aggfunc='mean')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"_UF7d7qVV59I"},"source":["## DataFrame：更上一层楼\n","\n","### 有点困难了！你可能需要结合两种或以上的方法才能得到正确的解答。\n","\n","难度: *中等*\n","\n","上一章节是一些简单但是相当实用的 DataFrame 操作。本章节将介绍如何切割数据，但是并没有开箱即用的方法。"]},{"cell_type":"markdown","metadata":{"id":"8_jaJCQQV59I"},"source":["**22.** 假设现在有一个一列整数 `A` 的 DataFrame `df`，比如：\n","```python\n","df = pd.DataFrame({'A': [1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7]})\n","```\n","\n","你怎么样把重复的数据去除？\n","\n","剩下的数据应该像下面一样：\n","\n","```python\n","1, 2, 3, 4, 5, 6, 7\n","```"]},{"cell_type":"code","metadata":{"id":"zg6TVyj6V59J"},"source":["df = pd.DataFrame({'A': [1, 2, 2, 3, 4, 5, 5, 5, 6, 7, 7]})\n","\n","df.loc[df['A'].shift() != df['A']]\n","\n","# Alternatively, we could use drop_duplicates() here. Note\n","# that this removes *all* duplicates though, so it won't\n","# work as desired if A is [1, 1, 2, 2, 1, 1] for example.\n","\n","df.drop_duplicates(subset='A')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"8b_m9eD_V59L"},"source":["**23.** 给定一组随机数据\n","```python\n","df = pd.DataFrame(np.random.random(size=(5, 3))) # a 5x3 frame of float values\n","```\n","\n","如何使每个元素减去所在行的平均值？"]},{"cell_type":"code","metadata":{"id":"Ngef1JrpV59M"},"source":["df = pd.DataFrame(np.random.random(size=(5, 3)))\n","\n","df.sub(df.mean(axis=1), axis=0)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"nHZxnQDNV59P"},"source":["**24.** 假设你有 10 列实数：\n","\n","```python\n","df = pd.DataFrame(np.random.random(size=(5, 10)), columns=list('abcdefghij'))\n","```\n","返回数字总和最小那列的标签"]},{"cell_type":"code","metadata":{"id":"f_oNz_rZV59P"},"source":["df = pd.DataFrame(np.random.random(size=(5, 10)), columns=list('abcdefghij'))\n","\n","df.sum().idxmin()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"PGi0o8K3V59R"},"source":["**25.** 如何计算一个 DataFrame 有多少唯一的行（即忽略所有重复的行）？\n","\n","```python\n","df = pd.DataFrame(np.random.randint(0, 2, size=(10, 3)))\n","```"]},{"cell_type":"code","metadata":{"id":"Y_eMe336V59R"},"source":["df = pd.DataFrame(np.random.randint(0, 2, size=(10, 3)))\n","\n","len(df) - df.duplicated(keep=False).sum()\n","\n","# or perhaps more simply...\n","\n","len(df.drop_duplicates(keep=False))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"UAAo1-GJV59U"},"source":["后面三题会更加难！做好准备了吗？\n","\n","\n","**26.** \n","给定一个十列的 DataFrame `df`，每行恰好有 5 个 `NaN` 值，从中找出第三个是 `NaN` 值的列标签。\n","返回结果如： `e, c, d, h, d`"]},{"cell_type":"code","metadata":{"id":"M9cFihrMV59U"},"source":["nan = np.nan\n","\n","data = [[0.04,  nan,  nan, 0.25,  nan, 0.43, 0.71, 0.51,  nan,  nan],\n","        [ nan,  nan,  nan, 0.04, 0.76,  nan,  nan, 0.67, 0.76, 0.16],\n","        [ nan,  nan, 0.5 ,  nan, 0.31, 0.4 ,  nan,  nan, 0.24, 0.01],\n","        [0.49,  nan,  nan, 0.62, 0.73, 0.26, 0.85,  nan,  nan,  nan],\n","        [ nan,  nan, 0.41,  nan, 0.05,  nan, 0.61,  nan, 0.48, 0.68]]\n","\n","columns = list('abcdefghij')\n","\n","df = pd.DataFrame(data, columns=columns)\n","\n","\n","(df.isnull().cumsum(axis=1) == 3).idxmax(axis=1)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"qX_x412SV59X"},"source":["**27.** DataFrame 如下 \n","\n","```python\n","df = pd.DataFrame({'grps': list('aaabbcaabcccbbc'), \n","                   'vals': [12,345,3,1,45,14,4,52,54,23,235,21,57,3,87]})\n","```\n","对于每组（即a，b，c 三组），找到三个数相加的最大和\n","```\n","grps\n","a    409\n","b    156\n","c    345\n","```"]},{"cell_type":"code","metadata":{"id":"J2Q3oX8eV59X"},"source":["df = pd.DataFrame({'grps': list('aaabbcaabcccbbc'), \n","                   'vals': [12,345,3,1,45,14,4,52,54,23,235,21,57,3,87]})\n","\n","df.groupby('grps')['vals'].nlargest(3).sum(level=0)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"vPMQkaO0V59Z"},"source":["**28.** \n","`DataFrame` 数据如下，`A` 和 `B` 都是 0-100 之间（包括边界值）的数值，对 `A` 进行分段分组（i.e. `(0, 10]`, `(10, 20]`, ...），求每组内 `B` 的和。输出应该和下述一致：\n","\n","```\n","A\n","(0, 10]      635\n","(10, 20]     360\n","(20, 30]     315\n","(30, 40]     306\n","(40, 50]     750\n","(50, 60]     284\n","(60, 70]     424\n","(70, 80]     526\n","(80, 90]     835\n","(90, 100]    852\n","```"]},{"cell_type":"code","metadata":{"id":"Yq05w91oV59a"},"source":["df = pd.DataFrame(np.random.RandomState(8765).randint(1, 101, size=(100, 2)), columns = [\"A\", \"B\"])\n","\n","df.groupby(pd.cut(df['A'], np.arange(0, 101, 10)))['B'].sum()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"XK8cPx1VV59b"},"source":["## DataFrame：向你发出挑战 \n","\n","### 这里可能需要一点突破常规的思维……\n","\n","但所有这些都可以使用通常的 pandas/NumPy 方法来解决（因此避免使用的`for` 循环来解决）。\n","\n","难度: *困难*"]},{"cell_type":"markdown","metadata":{"id":"LjoymOxpV59b"},"source":["**29.** 假设存在一列整数 `X`\n","```python\n","df = pd.DataFrame({'X': [7, 2, 0, 3, 4, 2, 5, 0, 3, 4]})\n","```\n","对于每一个元素，计算其与上一个 `0` 或者列的开头之间的距离（哪一个更小就取哪个，若自身为 0 则值为 0），结果应该如下：\n","\n","```\n","[1, 2, 0, 1, 2, 3, 4, 0, 1, 2]\n","```\n","\n","生成新列 `Y`"]},{"cell_type":"code","metadata":{"id":"CKcA6KQhV59c"},"source":["df = pd.DataFrame({'X': [7, 2, 0, 3, 4, 2, 5, 0, 3, 4]})\n","\n","izero = np.r_[-1, (df == 0).values.nonzero()[0]]  # 零值的索引\n","idx = np.arange(len(df))\n","y = df['X'] != 0\n","df['Y'] = idx - izero[np.searchsorted(izero - 1, idx) - 1]\n","\n","# http://stackoverflow.com/questions/30730981/how-to-count-distance-to-the-previous-zero-in-pandas-series/\n","# credit: Behzad Nouri"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"58Y279qZV59d"},"source":["基于 [cookbook recipe](http://pandas.pydata.org/pandas-docs/stable/cookbook.html#grouping) 的另一种方法："]},{"cell_type":"code","metadata":{"id":"NJN4EBwgV59e"},"source":["df = pd.DataFrame({'X': [7, 2, 0, 3, 4, 2, 5, 0, 3, 4]})\n","\n","x = (df['X'] != 0).cumsum()\n","y = x != x.shift()\n","df['Y'] = y.groupby((y != y.shift()).cumsum()).cumsum()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"KXX7k6NLV59f"},"source":["使用 groupby 的方法也可以："]},{"cell_type":"code","metadata":{"id":"C7GICRMgV59g"},"source":["df = pd.DataFrame({'X': [7, 2, 0, 3, 4, 2, 5, 0, 3, 4]})\n","\n","df['Y'] = df.groupby((df['X'] == 0).cumsum()).cumcount()\n","\n","# 找到第一组的结束位置，对这个位置之前的所有值都 +1\n","first_zero_idx = (df['X'] == 0).idxmax()\n","df['Y'].iloc[0:first_zero_idx] += 1"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"ol0NFtOyV59h"},"source":["**30.** \n","求出最大的三个值的索引位置，结果应该如下：\n","```\n","[(5, 7), (6, 4), (2, 5)]\n","```"]},{"cell_type":"code","metadata":{"id":"_SecuoqgV59i"},"source":["df = pd.DataFrame(np.random.RandomState(30).randint(1, 101, size=(8, 8)))\n","\n","df.unstack().sort_values()[-3:].index.tolist()\n","\n","# http://stackoverflow.com/questions/14941261/index-and-column-for-the-max-value-in-pandas-dataframe/\n","# credit: DSM"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"r6pXgAAFV59j"},"source":["**31.** 给定如下数据\n","\n","```python\n","df = pd.DataFrame({\"vals\": np.random.RandomState(31).randint(-30, 30, size=15), \n","                   \"grps\": np.random.RandomState(31).choice([\"A\", \"B\"], 15)})\n","```\n","\n","创建一个新列 `patched_values`，当 `vals`为正数时，`patched_value` 的值为`vals` 本身；当 `vals` 为负时，`patched_value` 的值等于所在组的正数的平均值\n","```\n","    vals grps  patched_vals\n","0    -12    A          13.6\n","1     -7    B          28.0\n","2    -14    A          13.6\n","3      4    A           4.0\n","4     -7    A          13.6\n","5     28    B          28.0\n","6     -2    A          13.6\n","7     -1    A          13.6\n","8      8    A           8.0\n","9     -2    B          28.0\n","10    28    A          28.0\n","11    12    A          12.0\n","12    16    A          16.0\n","13   -24    A          13.6\n","14   -12    A          13.6\n","```"]},{"cell_type":"code","metadata":{"id":"9N5IF3CGV59j"},"source":["df = pd.DataFrame({\"vals\": np.random.RandomState(31).randint(-30, 30, size=15), \n","                   \"grps\": np.random.RandomState(31).choice([\"A\", \"B\"], 15)})\n","\n","def replace(group):\n","    mask = group<0\n","    group[mask] = group[~mask].mean()\n","    return group\n","\n","df.groupby(['grps'])['vals'].transform(replace)\n","\n","# http://stackoverflow.com/questions/14760757/replacing-values-with-groupby-means/\n","# credit: unutbu"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"WDBJ8AwgV59l"},"source":["**32.** 对窗口大小为 3 的组实施滚动均值，忽略 NaN 值，给定数据如下\n","\n","```python\n",">>> df = pd.DataFrame({'group': list('aabbabbbabab'),\n","                       'value': [1, 2, 3, np.nan, 2, 3, np.nan, 1, 7, 3, np.nan, 8]})\n",">>> df\n","   group  value\n","0      a    1.0\n","1      a    2.0\n","2      b    3.0\n","3      b    NaN\n","4      a    2.0\n","5      b    3.0\n","6      b    NaN\n","7      b    1.0\n","8      a    7.0\n","9      b    3.0\n","10     a    NaN\n","11     b    8.0\n","```\n","目标是计算该 Series\n","\n","```\n","0     1.000000\n","1     1.500000\n","2     3.000000\n","3     3.000000\n","4     1.666667\n","5     3.000000\n","6     3.000000\n","7     2.000000\n","8     3.666667\n","9     2.000000\n","10    4.500000\n","11    4.000000\n","```\n","\n","例如，第一个大小为 3 的窗口 `b` 组的值为 3.0、NaN 和 3.0。在第三行索引的新列中的值应该是 3.0，而不是 NaN（用两个非 NaN 值来计算平均值（3+3）/2）。"]},{"cell_type":"code","metadata":{"id":"GgI6luixV59l"},"source":["df = pd.DataFrame({'group': list('aabbabbbabab'),\n","                   'value': [1, 2, 3, np.nan, 2, 3, np.nan, 1, 7, 3, np.nan, 8]})\n","\n","g1 = df.groupby(['group'])['value']              # 进行分组 \n","g2 = df.fillna(0).groupby(['group'])['value']    # 填充缺失值为 0\n","\n","s = g2.rolling(3, min_periods=1).sum() / g1.rolling(3, min_periods=1).count() # 计算平均值\n","\n","s.reset_index(level=0, drop=True).sort_index()  # 重置索引\n","\n","# http://stackoverflow.com/questions/36988123/pandas-groupby-and-rolling-apply-ignoring-nans/"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"3pnsEmbFV59n"},"source":["## Series 和 DatetimeIndex\n","\n","### 使用时间数据创建和操作 Series 对象。\n","\n","难度: *简单/中等*\n","\n","pandas 在处理日期与时间方面非常出色，这部分将探索 pandas 这方面的功能。\n"]},{"cell_type":"markdown","metadata":{"id":"pGdhpoSpV59n"},"source":["**33.** 创建一个 Series `s`，其索引为 2015 年的工作日，值可以随机取"]},{"cell_type":"code","metadata":{"id":"qi0ACwvyV59o"},"source":["dti = pd.date_range(start='2015-01-01', end='2015-12-31', freq='B') \n","s = pd.Series(np.random.rand(len(dti)), index=dti)\n","s"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"2K44WUxcV59p"},"source":["**34.** 计算日期是周三（Wednesday）的值的总和"]},{"cell_type":"code","metadata":{"id":"ZdWPanxjV59p"},"source":["s[s.index.weekday == 2].sum() "],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"XXpGmwALV59r"},"source":["**35.** 计算每个月的平均值"]},{"cell_type":"code","metadata":{"id":"N6B1p3orV59s"},"source":["s.resample('M').mean()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"gz6lJHLvV59t"},"source":["**36.** 寻找每个季度内最大值所对应的日期"]},{"cell_type":"code","metadata":{"id":"E_SV2wUVV59u"},"source":["s.groupby(pd.Grouper(freq='4M')).idxmax()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"-5MukJAEV59v"},"source":["**37.** 创建一个 DataTimeIndex，包含 2015 年至 2016 年每个月的第三个星期四"]},{"cell_type":"code","metadata":{"id":"b7J-22_5V59w"},"source":["pd.date_range('2015-01-01', '2016-12-31', freq='WOM-3THU')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"a0KpqdoIV59z"},"source":["## 清理数据\n","\n","### 让 DataFrame 更好协作。\n","\n","难度： *简单/中等*\n","\n","这种情况经常发生：有人给你包含畸形字符串、Python、列表和缺失数据的数据。你如何把它整理好，以便能继续分析？\n","\n","使用下面这些 ”怪兽“ 开始这一章的学习吧：\n","\n","```python\n","df = pd.DataFrame({'From_To': ['LoNDon_paris', 'MAdrid_miLAN', 'londON_StockhOlm', \n","                               'Budapest_PaRis', 'Brussels_londOn'],\n","              'FlightNumber': [10045, np.nan, 10065, np.nan, 10085],\n","              'RecentDelays': [[23, 47], [], [24, 43, 87], [13], [67, 32]],\n","                   'Airline': ['KLM(!)', '<Air France> (12)', '(British Airways. )', \n","                               '12. Air France', '\"Swiss Air\"']})\n","```\n","格式化后，是这样的：\n","\n","```\n","            From_To  FlightNumber  RecentDelays              Airline\n","0      LoNDon_paris       10045.0      [23, 47]               KLM(!)\n","1      MAdrid_miLAN           NaN            []    <Air France> (12)\n","2  londON_StockhOlm       10065.0  [24, 43, 87]  (British Airways. )\n","3    Budapest_PaRis           NaN          [13]       12. Air France\n","4   Brussels_londOn       10085.0      [67, 32]          \"Swiss Air\"\n","```\n","\n","\n","（这是瞎编的一些飞行数据，没有任何实际意义。）\n"]},{"cell_type":"markdown","metadata":{"id":"pHlTXMSbV59z"},"source":["**38.** Some values in the the **FlightNumber** column are missing (they are `NaN`). These numbers are meant to increase by 10 with each row so 10055 and 10075 need to be put in place. Modify `df` to fill in these missing numbers and make the column an integer column (instead of a float column)."]},{"cell_type":"code","metadata":{"id":"aQmBHJl-V59z"},"source":["df = pd.DataFrame({'From_To': ['LoNDon_paris', 'MAdrid_miLAN', 'londON_StockhOlm', \n","                               'Budapest_PaRis', 'Brussels_londOn'],\n","              'FlightNumber': [10045, np.nan, 10065, np.nan, 10085],\n","              'RecentDelays': [[23, 47], [], [24, 43, 87], [13], [67, 32]],\n","                   'Airline': ['KLM(!)', '<Air France> (12)', '(British Airways. )', \n","                               '12. Air France', '\"Swiss Air\"']})\n","\n","df['FlightNumber'] = df['FlightNumber'].interpolate().astype(int)\n","df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"kimo20-HV591"},"source":["**39.** The **From\\_To** column would be better as two separate columns! Split each string on the underscore delimiter `_` to give a new temporary DataFrame called 'temp' with the correct values. Assign the correct column names 'From' and 'To' to this temporary DataFrame. "]},{"cell_type":"code","metadata":{"id":"czd_zYT5V592"},"source":["temp = df.From_To.str.split('_', expand=True)\n","temp.columns = ['From', 'To']\n","temp"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"fEuJtIS3V594"},"source":["**40.** Notice how the capitalisation of the city names is all mixed up in this temporary DataFrame 'temp'. Standardise the strings so that only the first letter is uppercase (e.g. \"londON\" should become \"London\".)"]},{"cell_type":"code","metadata":{"id":"5nUDgFzvV594"},"source":["temp['From'] = temp['From'].str.capitalize()\n","temp['To'] = temp['To'].str.capitalize()\n","temp"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"srthkJK2V596"},"source":["**41.** Delete the From_To column from **41.** Delete the **From_To** column from `df` and attach the temporary DataFrame 'temp' from the previous questions.`df` and attach the temporary DataFrame from the previous questions."]},{"cell_type":"code","metadata":{"id":"gwv744_sV596"},"source":["df = df.drop('From_To', axis=1)\n","df = df.join(temp)\n","df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"op-i5GxcV598"},"source":["**42**. In the **Airline** column, you can see some extra puctuation and symbols have appeared around the airline names. Pull out just the airline name. E.g. `'(British Airways. )'` should become `'British Airways'`."]},{"cell_type":"code","metadata":{"id":"shqFmszIV598"},"source":["df['Airline'] = df['Airline'].str.extract('([a-zA-Z\\s]+)', expand=False).str.strip()\n","# note: using .strip() gets rid of any leading/trailing spaces\n","df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"psSgwg7GV59-"},"source":["**43**. In the **RecentDelays** column, the values have been entered into the DataFrame as a list. We would like each first value in its own column, each second value in its own column, and so on. If there isn't an Nth value, the value should be NaN.\n","\n","Expand the Series of lists into a new DataFrame named 'delays', rename the columns 'delay_1', 'delay_2', etc. and replace the unwanted RecentDelays column in `df` with 'delays'."]},{"cell_type":"code","metadata":{"id":"-2V-OI9iV59-"},"source":["# there are several ways to do this, but the following approach is possibly the simplest\n","\n","delays = df['RecentDelays'].apply(pd.Series)\n","\n","delays.columns = ['delay_{}'.format(n) for n in range(1, len(delays.columns)+1)]\n","\n","df = df.drop('RecentDelays', axis=1).join(delays)\n","\n","df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"9X8vWEIhV5-A"},"source":["The DataFrame should look much better now:\n","\n","```\n","   FlightNumber          Airline      From         To  delay_1  delay_2  delay_3\n","0         10045              KLM    London      Paris     23.0     47.0      NaN\n","1         10055       Air France    Madrid      Milan      NaN      NaN      NaN\n","2         10065  British Airways    London  Stockholm     24.0     43.0     87.0\n","3         10075       Air France  Budapest      Paris     13.0      NaN      NaN\n","4         10085        Swiss Air  Brussels     London     67.0     32.0      NaN\n","```"]},{"cell_type":"markdown","metadata":{"collapsed":true,"id":"kv4dbHVAV5-B"},"source":["## Pandas 多层索引\n","\n","### 为 Pandas 添加多层索引！\n","\n","难度: *中等*\n","\n","Previous exercises have seen us analysing data from DataFrames equipped with a single index level. However, pandas also gives you the possibilty of indexing your data using *multiple* levels. This is very much like adding new dimensions to a Series or a DataFrame. For example, a Series is 1D, but by using a MultiIndex with 2 levels we gain of much the same functionality as a 2D DataFrame.\n","\n","The set of puzzles below explores how you might use multiple index levels to enhance data analysis.\n","\n","To warm up, we'll look make a Series with two index levels. "]},{"cell_type":"markdown","metadata":{"id":"K8QqkW8SV5-B"},"source":["**44**. Given the lists `letters = ['A', 'B', 'C']` and `numbers = list(range(10))`, construct a MultiIndex object from the product of the two lists. Use it to index a Series of random numbers. Call this Series `s`."]},{"cell_type":"code","metadata":{"id":"XzC79bvgV5-B"},"source":["letters = ['A', 'B', 'C']\n","numbers = list(range(10))\n","\n","mi = pd.MultiIndex.from_product([letters, numbers])\n","s = pd.Series(np.random.rand(30), index=mi)\n","s"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"CVtoaumlV5-C"},"source":["**45.** Check the index of `s` is lexicographically sorted (this is a necessary proprty for indexing to work correctly with a MultiIndex)."]},{"cell_type":"code","metadata":{"id":"dA2KTE3AV5-D"},"source":["s.index.is_lexsorted()\n","\n","# or more verbosely...\n","s.index.lexsort_depth == s.index.nlevels"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"I9_q6u-gV5-E"},"source":["**46**. Select the labels `1`, `3` and `6` from the second level of the MultiIndexed Series."]},{"cell_type":"code","metadata":{"id":"cfU9HaleV5-F"},"source":["s.loc[:, [1, 3, 6]]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"0s9p0B6DV5-G"},"source":["**47**. Slice the Series `s`; slice up to label 'B' for the first level and from label 5 onwards for the second level."]},{"cell_type":"code","metadata":{"id":"MCnsRA4QV5-H"},"source":["s.loc[pd.IndexSlice[:'B', 5:]]\n","\n","# or equivalently without IndexSlice...\n","s.loc[slice(None, 'B'), slice(5, None)]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"CKCFm19FV5-J"},"source":["**48**. Sum the values in `s` for each label in the first level (you should have Series giving you a total for labels A, B and C)."]},{"cell_type":"code","metadata":{"id":"qLq1hksdV5-K"},"source":["s.sum(level=0)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"Dxef5oRJV5-L"},"source":["**49**. Suppose that `sum()` (and other methods) did not accept a `level` keyword argument. How else could you perform the equivalent of `s.sum(level=1)`?"]},{"cell_type":"code","metadata":{"id":"pbXK-diBV5-L"},"source":["# One way is to use .unstack()... \n","# This method should convince you that s is essentially just a regular DataFrame in disguise!\n","s.unstack().sum(axis=0)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"rsDUznp-V5-N"},"source":["**50**. Exchange the levels of the MultiIndex so we have an index of the form (letters, numbers). Is this new Series properly lexsorted? If not, sort it."]},{"cell_type":"code","metadata":{"id":"gquxWBlSV5-N"},"source":["new_s = s.swaplevel(0, 1)\n","\n","if not new_s.index.is_lexsorted():\n","    new_s = new_s.sort_index()\n","\n","new_s"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"collapsed":true,"id":"2Y0JZhNIV5-P"},"source":["## 扫雷\n","\n","### 玩过扫雷吗？用 Pandas 生成数据。\n","\n","难度： *中等* 至 *困难*\n","\n","If you've ever used an older version of Windows, there's a good chance you've played with Minesweeper:\n","- https://en.wikipedia.org/wiki/Minesweeper_(video_game)\n","\n","\n","If you're not familiar with the game, imagine a grid of squares: some of these squares conceal a mine. If you click on a mine, you lose instantly. If you click on a safe square, you reveal a number telling you how many mines are found in the squares that are immediately adjacent. The aim of the game is to uncover all squares in the grid that do not contain a mine.\n","\n","In this section, we'll make a DataFrame that contains the necessary data for a game of Minesweeper: coordinates of the squares, whether the square contains a mine and the number of mines found on adjacent squares."]},{"cell_type":"markdown","metadata":{"id":"RRMofr-nV5-P"},"source":["**51**. Let's suppose we're playing Minesweeper on a 5 by 4 grid, i.e.\n","```\n","X = 5\n","Y = 4\n","```\n","To begin, generate a DataFrame `df` with two columns, `'x'` and `'y'` containing every coordinate for this grid. That is, the DataFrame should start:\n","```\n","   x  y\n","0  0  0\n","1  0  1\n","2  0  2\n","...\n","```"]},{"cell_type":"code","metadata":{"id":"rhiJUajdV5-P"},"source":["X = 5\n","Y = 4\n","\n","p = pd.core.reshape.util.cartesian_product([np.arange(X), np.arange(Y)])\n","df = pd.DataFrame(np.asarray(p).T, columns=['x', 'y'])\n","df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"evDkxqeQV5-R"},"source":["**52**. For this DataFrame `df`, create a new column of zeros (safe) and ones (mine). The probability of a mine occuring at each location should be 0.4."]},{"cell_type":"code","metadata":{"id":"0jE-6i9NV5-R"},"source":["# One way is to draw samples from a binomial distribution.\n","\n","df['mine'] = np.random.binomial(1, 0.4, X*Y)\n","df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"XEtpauuJV5-T"},"source":["**53**. Now create a new column for this DataFrame called `'adjacent'`. This column should contain the number of mines found on adjacent squares in the grid. \n","\n","(E.g. for the first row, which is the entry for the coordinate `(0, 0)`, count how many mines are found on the coordinates `(0, 1)`, `(1, 0)` and `(1, 1)`.)"]},{"cell_type":"code","metadata":{"id":"L1jqT38hV5-T"},"source":["# Here is one way to solve using merges.\n","# It's not necessary the optimal way, just \n","# the solution I thought of first...\n","\n","df['adjacent'] = \\\n","    df.merge(df + [ 1,  1, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [ 1, -1, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [-1,  1, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [-1, -1, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [ 1,  0, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [-1,  0, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [ 0,  1, 0], on=['x', 'y'], how='left')\\\n","      .merge(df + [ 0, -1, 0], on=['x', 'y'], how='left')\\\n","       .iloc[:, 3:]\\\n","        .sum(axis=1)\n","        \n","# An alternative solution is to pivot the DataFrame \n","# to form the \"actual\" grid of mines and use convolution.\n","# See https://github.com/jakevdp/matplotlib_pydata2013/blob/master/examples/minesweeper.py\n","\n","from scipy.signal import convolve2d\n","\n","mine_grid = df.pivot_table(columns='x', index='y', values='mine')\n","counts = convolve2d(mine_grid.astype(complex), np.ones((3, 3)), mode='same').real.astype(int)\n","df['adjacent'] = (counts - mine_grid).ravel('F')"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"L88Bduy7V5-U"},"source":["**54**. For rows of the DataFrame that contain a mine, set the value in the `'adjacent'` column to NaN."]},{"cell_type":"code","metadata":{"id":"nMQm09SPV5-U"},"source":["df.loc[df['mine'] == 1, 'adjacent'] = np.nan"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"u0ARBq4cV5-V"},"source":["**55**. Finally, convert the DataFrame to grid of the adjacent mine counts: columns are the `x` coordinate, rows are the `y` coordinate."]},{"cell_type":"code","metadata":{"id":"_-3HmQaAV5-V"},"source":["df.drop('mine', axis=1).set_index(['y', 'x']).unstack()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"7fqjJgt4V5-X"},"source":["## 绘图\n","\n","### 探索 Pandas 的部分绘图功能，了解数据的趋势。\n","\n","难度： *中等*\n","\n","To really get a good understanding of the data contained in your DataFrame, it is often essential to create plots: if you're lucky, trends and anomalies will jump right out at you. This functionality is baked into pandas and the puzzles below explore some of what's possible with the library.\n","\n","**56.** Pandas is highly integrated with the plotting library matplotlib, and makes plotting DataFrames very user-friendly! Plotting in a notebook environment usually makes use of the following boilerplate:\n","\n","```python\n","import matplotlib.pyplot as plt\n","%matplotlib inline\n","plt.style.use('ggplot')\n","```\n","\n","matplotlib is the plotting library which pandas' plotting functionality is built upon, and it is usually aliased to ```plt```.\n","\n","```%matplotlib inline``` tells the notebook to show plots inline, instead of creating them in a separate window.  \n","\n","```plt.style.use('ggplot')``` is a style theme that most people find agreeable, based upon the styling of R's ggplot package.\n","\n","For starters, make a scatter plot of this random data, but use black X's instead of the default markers. \n","\n","```df = pd.DataFrame({\"xs\":[1,5,2,8,1], \"ys\":[4,2,1,9,6]})```\n","\n","Consult the [documentation](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.plot.html) if you get stuck!"]},{"cell_type":"code","metadata":{"scrolled":false,"id":"asuMr3k-V5-X"},"source":["import matplotlib.pyplot as plt\n","%matplotlib inline\n","plt.style.use('ggplot')\n","\n","df = pd.DataFrame({\"xs\":[1,5,2,8,1], \"ys\":[4,2,1,9,6]})\n","\n","df.plot.scatter(\"xs\", \"ys\", color = \"black\", marker = \"x\")"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"7RnCRIHhV5-Y"},"source":["**57.** Columns in your DataFrame can also be used to modify colors and sizes.  Bill has been keeping track of his performance at work over time, as well as how good he was feeling that day, and whether he had a cup of coffee in the morning.  Make a plot which incorporates all four features of this DataFrame.\n","\n","(Hint:  If you're having trouble seeing the plot, try multiplying the Series which you choose to represent size by 10 or more)\n","\n","*The chart doesn't have to be pretty: this isn't a course in data viz!*\n","\n","```\n","df = pd.DataFrame({\"productivity\":[5,2,3,1,4,5,6,7,8,3,4,8,9],\n","                   \"hours_in\"    :[1,9,6,5,3,9,2,9,1,7,4,2,2],\n","                   \"happiness\"   :[2,1,3,2,3,1,2,3,1,2,2,1,3],\n","                   \"caffienated\" :[0,0,1,1,0,0,0,0,1,1,0,1,0]})\n","```"]},{"cell_type":"code","metadata":{"id":"XJfyCyhNV5-Z"},"source":["df = pd.DataFrame({\"productivity\":[5,2,3,1,4,5,6,7,8,3,4,8,9],\n","                   \"hours_in\"    :[1,9,6,5,3,9,2,9,1,7,4,2,2],\n","                   \"happiness\"   :[2,1,3,2,3,1,2,3,1,2,2,1,3],\n","                   \"caffienated\" :[0,0,1,1,0,0,0,0,1,1,0,1,0]})\n","\n","df.plot.scatter(\"hours_in\", \"productivity\", s = df.happiness * 30, c = df.caffienated)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"mfkemI8YV5-a"},"source":["**58.**  What if we want to plot multiple things?  Pandas allows you to pass in a matplotlib *Axis* object for plots, and plots will also return an Axis object.\n","\n","Make a bar plot of monthly revenue with a line plot of monthly advertising spending (numbers in millions)\n","\n","```\n","df = pd.DataFrame({\"revenue\":[57,68,63,71,72,90,80,62,59,51,47,52],\n","                   \"advertising\":[2.1,1.9,2.7,3.0,3.6,3.2,2.7,2.4,1.8,1.6,1.3,1.9],\n","                   \"month\":range(12)\n","                  })\n","```"]},{"cell_type":"code","metadata":{"id":"x5NBAOssV5-a"},"source":["df = pd.DataFrame({\"revenue\":[57,68,63,71,72,90,80,62,59,51,47,52],\n","                   \"advertising\":[2.1,1.9,2.7,3.0,3.6,3.2,2.7,2.4,1.8,1.6,1.3,1.9],\n","                   \"month\":range(12)\n","                  })\n","\n","ax = df.plot.bar(\"month\", \"revenue\", color = \"green\")\n","df.plot.line(\"month\", \"advertising\", secondary_y = True, ax = ax)\n","ax.set_xlim((-1,12))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"MFHu0XfbV5-e"},"source":["Now we're finally ready to create a candlestick chart, which is a very common tool used to analyze stock price data.  A candlestick chart shows the opening, closing, highest, and lowest price for a stock during a time window.  The color of the \"candle\" (the thick part of the bar) is green if the stock closed above its opening price, or red if below.\n","\n","![Candlestick Example](img/candle.jpg)\n","\n","This was initially designed to be a pandas plotting challenge, but it just so happens that this type of plot is just not feasible using pandas' methods.  If you are unfamiliar with matplotlib, we have provided a function that will plot the chart for you so long as you can use pandas to get the data into the correct format.\n","\n","Your first step should be to get the data in the correct format using pandas' time-series grouping function.  We would like each candle to represent an hour's worth of data.  You can write your own aggregation function which returns the open/high/low/close, but pandas has a built-in which also does this."]},{"cell_type":"markdown","metadata":{"id":"VOpZhRrNV5-e"},"source":["The below cell contains helper functions.  Call ```day_stock_data()``` to generate a DataFrame containing the prices a hypothetical stock sold for, and the time the sale occurred.  Call ```plot_candlestick(df)``` on your properly aggregated and formatted stock data to print the candlestick chart."]},{"cell_type":"code","metadata":{"id":"kcGr6Wy5V5-e"},"source":["#This function is designed to create semi-interesting random stock price data\n","\n","import numpy as np\n","def float_to_time(x):\n","    return str(int(x)) + \":\" + str(int(x%1 * 60)).zfill(2) + \":\" + str(int(x*60 % 1 * 60)).zfill(2)\n","\n","def day_stock_data():\n","    #NYSE is open from 9:30 to 4:00\n","    time = 9.5\n","    price = 100\n","    results = [(float_to_time(time), price)]\n","    while time < 16:\n","        elapsed = np.random.exponential(.001)\n","        time += elapsed\n","        if time > 16:\n","            break\n","        price_diff = np.random.uniform(.999, 1.001)\n","        price *= price_diff\n","        results.append((float_to_time(time), price))\n","    \n","    \n","    df = pd.DataFrame(results, columns = ['time','price'])\n","    df.time = pd.to_datetime(df.time)\n","    return df\n","\n","def plot_candlestick(agg):\n","    fig, ax = plt.subplots()\n","    for time in agg.index:\n","        ax.plot([time.hour] * 2, agg.loc[time, [\"high\",\"low\"]].values, color = \"black\")\n","        ax.plot([time.hour] * 2, agg.loc[time, [\"open\",\"close\"]].values, color = agg.loc[time, \"color\"], linewidth = 10)\n","\n","    ax.set_xlim((8,16))\n","    ax.set_ylabel(\"Price\")\n","    ax.set_xlabel(\"Hour\")\n","    ax.set_title(\"OHLC of Stock Value During Trading Day\")\n","    plt.show()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"_g2JBI6xV5-f"},"source":["**59.** Generate a day's worth of random stock data, and aggregate / reformat it so that it has hourly summaries of the opening, highest, lowest, and closing prices"]},{"cell_type":"code","metadata":{"id":"z-J6B4VlV5-f"},"source":["df = day_stock_data()\n","df.head()"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"21HofIyMV5-h"},"source":["df.set_index(\"time\", inplace = True)\n","agg = df.resample(\"H\").ohlc()\n","agg.columns = agg.columns.droplevel()\n","agg[\"color\"] = (agg.close > agg.open).map({True:\"green\",False:\"red\"})\n","agg.head()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"rtvDZlyrV5-i"},"source":["**60.** Now that you have your properly-formatted data, try to plot it yourself as a candlestick chart.  Use the ```plot_candlestick(df)``` function above, or matplotlib's [```plot``` documentation](https://matplotlib.org/api/_as_gen/matplotlib.axes.Axes.plot.html) if you get stuck."]},{"cell_type":"code","metadata":{"scrolled":false,"id":"9ba_SxvJV5-i"},"source":["plot_candlestick(agg)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"gC67sv7xV5-k"},"source":["*More exercises to follow soon...*"]}]}