pandas_learning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "         pandas的使用\n",
    "            - Series对象构建及使用\n",
    "            - pandas日期处理\n",
    "            - DataFrame对象构建及使用\n",
    "            - DataFrame对象API的使用\n",
    "            - DataFrame对象的常用属性\n",
    "            - pandas描述性统计\n",
    "            - pandas排序\n",
    "\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Series([], dtype: float64) <class 'pandas.core.series.Series'> float64\n",
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "4    5\n",
      "dtype: int32\n",
      "1\n",
      "a    1\n",
      "b    2\n",
      "c    3\n",
      "dtype: int64\n",
      "a    5\n",
      "b    5\n",
      "c    5\n",
      "dtype: int64\n",
      "5\n",
      "a    5\n",
      "b    5\n",
      "dtype: int64\n",
      "5\n",
      "5\n",
      "a    5\n",
      "c    5\n",
      "dtype: int64\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:9: DeprecationWarning: The default dtype for empty Series will be 'object' instead of 'float64' in a future version. Specify a dtype explicitly to silence this warning.\n",
      "  if __name__ == '__main__':\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    - Series对象\n",
    "        - 创建Series对象\n",
    "            - pd.Series()\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "s = pd.Series()\n",
    "print(s,type(s),s.dtype)\n",
    "\n",
    "#通过ndarray创建Series对象\n",
    "array = np.array([1,2,3,4,5])\n",
    "s = pd.Series(array)\n",
    "print(s)\n",
    "#创建Series对象时，指定index行级索引标签\n",
    "s = pd.Series(array,index=[\"a\",\"b\",\"c\",\"d\",\"e\"])\n",
    "print(s[\"a\"])\n",
    "#从字典创建一个系列\n",
    "dic = {\"a\":1,\"b\":2,\"c\":3}\n",
    "s = pd.Series(dic)\n",
    "print(s)\n",
    "\n",
    "#从标创建Series\n",
    "s = pd.Series(5,index=[\"a\",\"b\",\"c\"])\n",
    "print(s)\n",
    "\n",
    "#访问Series中的数据\n",
    "#使用索引检索数据\n",
    "print(s[0])\n",
    "print(s[:2])\n",
    "print(s[\"a\"])\n",
    "print(s[\"c\"])\n",
    "print(s[[\"a\",\"c\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    2011\n",
      "1    2011\n",
      "2    2011\n",
      "3    2011\n",
      "4    2011\n",
      "5    2011\n",
      "dtype: int64\n",
      "0    1\n",
      "1    2\n",
      "2    3\n",
      "3    4\n",
      "4    5\n",
      "5    6\n",
      "dtype: int64\n",
      "0    1\n",
      "1    1\n",
      "2    1\n",
      "3    1\n",
      "4    1\n",
      "5    1\n",
      "dtype: int64\n",
      "0    0\n",
      "1    0\n",
      "2    0\n",
      "3    0\n",
      "4    1\n",
      "5    0\n",
      "dtype: int64\n",
      "0    0\n",
      "1    0\n",
      "2    0\n",
      "3    0\n",
      "4    1\n",
      "5    0\n",
      "dtype: int64\n",
      "0    0\n",
      "1    0\n",
      "2    0\n",
      "3    0\n",
      "4    1\n",
      "5    0\n",
      "dtype: int64\n",
      "0    0\n",
      "1    0\n",
      "2    0\n",
      "3    0\n",
      "4    0\n",
      "5    0\n",
      "dtype: int64\n",
      "0    52\n",
      "1     5\n",
      "2     9\n",
      "3    13\n",
      "4    17\n",
      "5    22\n",
      "dtype: int64\n",
      "0    5\n",
      "1    1\n",
      "2    1\n",
      "3    4\n",
      "4    6\n",
      "5    2\n",
      "dtype: int64\n",
      "0    5\n",
      "1    1\n",
      "2    1\n",
      "3    4\n",
      "4    6\n",
      "5    2\n",
      "dtype: int64\n",
      "0    1\n",
      "1    1\n",
      "2    1\n",
      "3    2\n",
      "4    2\n",
      "5    2\n",
      "dtype: int64\n",
      "0    True\n",
      "1    True\n",
      "2    True\n",
      "3    True\n",
      "4    True\n",
      "5    True\n",
      "dtype: bool\n",
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "5    False\n",
      "dtype: bool\n",
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "5    False\n",
      "dtype: bool\n",
      "0    31\n",
      "1    28\n",
      "2    31\n",
      "3    30\n",
      "4    31\n",
      "5    30\n",
      "dtype: int64\n",
      "DatetimeIndex(['2019-08-21', '2019-08-22', '2019-08-23', '2019-08-24',\n",
      "               '2019-08-25'],\n",
      "              dtype='datetime64[ns]', freq='D')\n",
      "DatetimeIndex(['2019-08-31', '2019-09-30', '2019-10-31', '2019-11-30',\n",
      "               '2019-12-31'],\n",
      "              dtype='datetime64[ns]', freq='M')\n",
      "DatetimeIndex(['2017-11-01 00:00:00', '2017-11-01 16:00:00',\n",
      "               '2017-11-02 08:00:00', '2017-11-03 00:00:00',\n",
      "               '2017-11-03 16:00:00', '2017-11-04 08:00:00',\n",
      "               '2017-11-05 00:00:00'],\n",
      "              dtype='datetime64[ns]', freq=None)\n",
      "DatetimeIndex(['2021-01-31', '2021-02-28', '2021-03-31', '2021-04-30',\n",
      "               '2021-05-31', '2021-06-30', '2021-07-31'],\n",
      "              dtype='datetime64[ns]', freq='M')\n",
      "DatetimeIndex(['2021-01-01', '2021-01-04', '2021-01-05', '2021-01-06',\n",
      "               '2021-01-07', '2021-01-08', '2021-01-11'],\n",
      "              dtype='datetime64[ns]', freq='B')\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:68: FutureWarning: Series.dt.weekofyear and Series.dt.week have been deprecated.  Please use Series.dt.isocalendar().week instead.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:85: FutureWarning: The pandas.datetime class is deprecated and will be removed from pandas in a future version. Import from datetime module instead.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:86: FutureWarning: The pandas.datetime class is deprecated and will be removed from pandas in a future version. Import from datetime module instead.\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    - pandas日期处理\n",
    "        - pd.to_datetime([dates]) 转换日期数据类型\n",
    "        - timedelta64[ns]类型时\n",
    "            - timedelta.dt.days          获取天数\n",
    "            - timedelta.dt.seconds       获取秒数\n",
    "        - datetime64[ns]类型时\n",
    "            - datetime.dt.year                  年份\n",
    "            - datetime.dt.month                 月份\n",
    "            - datetime.dt.days                  天数\n",
    "            - datetime.dt.hour                  小时\n",
    "            - datetime.dt.minute                分钟\n",
    "            - datetime.dt.second                秒数\n",
    "            - datetime.dt.microsecond           微秒数\n",
    "            - datetime.dt.week                  一年中第几周。\n",
    "            - datetime.dt.weekofyear            一年中第几周。\n",
    "            - datetime.dt.dayofweek             一周内的星期数,范围：Monday=0, Sunday=6。\n",
    "            - datetime.dt.weekday               一周内的星期数,范围：Monday=0, Sunday=6。\n",
    "            - datetime.dt.dayofyear             一年中的第几天\n",
    "            - datetime.dt.quarter               一年中的季度\n",
    "            - datetime.dt.is_month_start        日期是否为每月的第一天\n",
    "            - datetime.dt.is_month_end          日期是否为该月的最后一天\n",
    "            - datetime.dt.is_quarter_start      日期是否为一个季度的第一天。\n",
    "            - datetime.dt.is_quarter_end        日期是否为一个季度的最后一天\n",
    "            - datetime.dt.is_year_start         日期是否为一年的第一天。\n",
    "            - datetime.dt.is_leap_year          日期是否属于闰年\n",
    "            - datetime.dt.days_in_month         一个月中的天数\n",
    "    - DateTimeIndex\n",
    "            - date_range(start=None,end=None,periods=None,freq=None,tz=None)\n",
    "                - start                         开始日期\n",
    "                - end                           结束日期\n",
    "                - periods                       要生成的周期数。\n",
    "                - freq                          默认频率为D[天],可选参数M(月份)\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "\n",
    "dates = pd.Series([\"2011\",\"2011-02\",\"2011-03-01\",\"2011/04/01\",\"2011/05/01 01:01:01\",\"01Jun 2011\"])\n",
    "dates = pd.to_datetime(dates)\n",
    "# print(dates,type(dates),dates.dtype)\n",
    "# datetime类型数据支持日期运算\n",
    "delta = dates - pd.to_datetime('2010-01-01')  #日期偏移量\n",
    "# 获取天数数值\n",
    "# print(delta.dt.days)\n",
    "# 获取秒数数值\n",
    "# print(delta.dt.seconds)\n",
    "\n",
    "\n",
    "# print(dates.dtype,66)   #datetime64[ns]\n",
    "# print(delta.dtype)      #timedelta64[ns]\n",
    "#\n",
    "print(dates.dt.year)\n",
    "print(dates.dt.month)\n",
    "print(dates.dt.day)\n",
    "print(dates.dt.hour)\n",
    "print(dates.dt.minute)\n",
    "print(dates.dt.second)\n",
    "print(dates.dt.microsecond)\n",
    "print(dates.dt.week)\n",
    "print(dates.dt.weekday)\n",
    "print(dates.dt.dayofweek)\n",
    "print(dates.dt.quarter)\n",
    "\n",
    "print(dates.dt.is_month_start)\n",
    "print(dates.dt.is_year_end)\n",
    "print(dates.dt.is_leap_year)\n",
    "print(dates.dt.days_in_month)\n",
    "\n",
    "# 以日为频率\n",
    "datelist = pd.date_range('2019/08/21', periods=5)\n",
    "print(datelist)\n",
    "# 以月为频率\n",
    "datelist = pd.date_range('2019/08/21', periods=5,freq='M')\n",
    "print(datelist)\n",
    "# 构建某个区间的时间序列\n",
    "start = pd.datetime(2017, 11, 1)\n",
    "end = pd.datetime(2017, 11, 5)\n",
    "dates = pd.date_range(start, end,periods=7)\n",
    "print(dates)\n",
    " #修改频率为M\n",
    "dates = pd.date_range(\"2021/01/01\",periods=7,freq=\"M\")\n",
    "print(dates)\n",
    "#生成时间序列(工作日时间序列)\n",
    "dates = pd.bdate_range(\"2021/1/1\",periods=7) #跳过周六日\n",
    "print(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      0   1\n",
      "a   Tom  13\n",
      "b  Jack  11\n",
      "c  Andy  20\n",
      "******************************\n",
      "   0\n",
      "0  1\n",
      "1  2\n",
      "2  3\n",
      "3  4\n",
      "4  5\n",
      "5  6\n",
      "6  7\n",
      "7  8\n",
      "******************************\n",
      "   a   b     c\n",
      "0  1   2   NaN\n",
      "1  5  10  20.0\n",
      "******************************\n",
      "   a   b   c\n",
      "0  5  10  20\n",
      "******************************\n",
      "   name  age\n",
      "0   Tom   20\n",
      "1  Jack   17\n",
      "2  Andy   30\n",
      "******************************\n",
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "c  3.0    9\n",
      "d  NaN   10\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    DataFrame\n",
    "        - DataFrame是一个类似于表格的数据类型，可以理解为一个二维数组，索引有两个维度，可更改。DataFrame具有以下特点：\n",
    "            - 潜在的列是不同的类型\n",
    "            - 大小可变\n",
    "            - 标记轴(行和列)\n",
    "            - 可以对行和列执行算术运算\n",
    "        - np.DataFrame()\n",
    "        创建方式:\n",
    "            - 通过列表创建\n",
    "                - 一位列表:[](生成一列数据)\n",
    "                - 二维列表:[[],[],...](生成二维行列数据,取最长长度的一位列表为二维列数)\n",
    "                - 二维列表:[{},{},...](生成二维行列数据,取最长长度的字典为二维列数)\n",
    "            - 通过字典创建\n",
    "                - 二维字典:{\"key1\":[],\"key2\":[],....}\n",
    "                - 二维字典:{\"key1\":Series,\"key2\":Series,....}\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.DataFrame()\n",
    "# df\n",
    "# 通过列表创建DataFrame\n",
    "\n",
    "data = [[\"Tom\",13],[\"Jack\",11],[\"Andy\",20]]\n",
    "df = pd.DataFrame(data,index=[chr(i) for i in range(97,100)])\n",
    "print(df)\n",
    "print(\"*\"*30)\n",
    "\n",
    "data = np.arange(1,9)\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "\n",
    "print(\"*\"*30)\n",
    "\n",
    "#通过字典创建DataFrame\n",
    "data = [{'a': 1, 'b': 2},{'a': 5, 'b': 10,\"c\":20}]\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "\n",
    "print(\"*\"*30)\n",
    "\n",
    "data = {'a': 5, 'b': 10,\"c\":20}\n",
    "df = pd.DataFrame(data,index=[0])\n",
    "print(df)\n",
    "print(\"*\"*30)\n",
    "\n",
    "data = {\"name\":['Tom',\"Jack\",\"Andy\"],\"age\":[20,17,30]}\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "print(\"*\"*30)\n",
    "data = {\"one\":pd.Series([1,2,3],index=['a','b','c']),\"two\":pd.Series([7,8,9,10],index=['a','b','c','d'])}\n",
    "df = pd.DataFrame(data)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "c  3.0    9\n",
      "d  NaN   10\n",
      "a    1.0\n",
      "b    2.0\n",
      "c    3.0\n",
      "d    NaN\n",
      "Name: one, dtype: float64\n",
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "c  3.0    9\n",
      "d  NaN   10\n",
      "******************************\n",
      "   one  two  three\n",
      "a  1.0    7      1\n",
      "b  2.0    8      2\n",
      "c  3.0    9      3\n",
      "d  NaN   10      7\n",
      "******************************\n",
      "   three\n",
      "a      1\n",
      "b      2\n",
      "c      3\n",
      "d      7\n",
      "******************************\n",
      "   three\n",
      "a      4\n",
      "b      6\n",
      "c      7\n",
      "d      9\n",
      "******************************\n",
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "c  3.0    9\n",
      "d  NaN   10\n",
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "one    1.0\n",
      "two    7.0\n",
      "Name: a, dtype: float64\n",
      "   one  two\n",
      "a  1.0    7\n",
      "c  3.0    9\n",
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "c  3.0    9 252\n",
      "one    3.0\n",
      "two    9.0\n",
      "Name: c, dtype: float64\n",
      "   one  two\n",
      "a  1.0    7\n",
      "d  NaN   10\n",
      "1.0\n",
      "******************************\n",
      "   one  two\n",
      "a  1.0    7\n",
      "b  2.0    8\n",
      "c  3.0    9\n",
      "e  2.0    5\n",
      "******************************\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:65: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    },
    {
     "data": {
      "text/plain": "   one  two\na  3.0    7\nb  2.0    8\nc  3.0    9\nd  NaN   10\ne  2.0    5",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>one</th>\n      <th>two</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>3.0</td>\n      <td>7</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>2.0</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>3.0</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>NaN</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>e</th>\n      <td>2.0</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "    DataFrame操作\n",
    "        - df.index                  拿到所有的行级索引标签\n",
    "        - df.columns                拿到所有的列级索引标签\n",
    "        - df.copy(deep)             复制DataFrame数据，默认deep为True，深拷贝\n",
    "        - df.loc[\"索引名\"]           获得行索引数据,索引名为index\n",
    "        - df.iloc[\"索引下标\"]         获得行索引数据,索引名为index\n",
    "        - df.append(DataFrame)      添加行数据,并返回一个新的DataFrame,不改变原DataFrame数据\n",
    "        - df.drop(index)            根据行索引名删除行数据,并返回一个新的DataFrame,不改变原DataFrame数据\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "data = {\"one\":pd.Series([1,2,3],index=['a','b','c']),\"two\":pd.Series([7,8,9,10],index=['a','b','c','d'])}\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "'''列操作'''\n",
    "#按列索引\n",
    "print(df[\"one\"])\n",
    "print(df[[\"one\",\"two\"]])\n",
    "print(\"*\"*30)\n",
    "\n",
    "#添加列数据(不写index,则全部是NaN)\n",
    "df[\"three\"] = pd.Series([1,2,3,7],index=[\"a\",'b','c','d'])\n",
    "df[\"three\"] = pd.Series([1,2,3,7],index=df.index)\n",
    "print(df)\n",
    "print(\"*\"*30)\n",
    "\n",
    "#删除列数据\n",
    "df.pop(\"one\")\n",
    "del df[\"two\"]\n",
    "print(df)\n",
    "print(\"*\"*30)\n",
    "\n",
    "#修改列数据\n",
    "df[\"three\"] = pd.Series([4,6,7,9],index=df.index)\n",
    "print(df)\n",
    "print(\"*\"*30)\n",
    "\n",
    "'''行操作'''\n",
    "data = {\"one\":pd.Series([1,2,3],index=['a','b','c']),\"two\":pd.Series([7,8,9,10],index=['a','b','c','d'])}\n",
    "df = pd.DataFrame(data)\n",
    "#按切片行索引\n",
    "print(df)\n",
    "print(df[0:2])\n",
    "\n",
    "#按索引名获取行数据\n",
    "print(df.loc[\"a\"])\n",
    "print(df.loc[[\"a\",\"c\"]])\n",
    "print(df.loc[\"a\":\"c\",:])\n",
    "\n",
    "#按索引获取行数据\n",
    "print(df.iloc[2])\n",
    "print(df.iloc[[0,3]])\n",
    "print(df.iloc[0,0])     #获得标量数据\n",
    "#行添加\n",
    "row_data = {\"one\":[2],\"two\":[5]}\n",
    "df2 = pd.DataFrame(row_data,index=[\"e\"])\n",
    "\n",
    "df = df.append(df2)\n",
    "df\n",
    "#行删除\n",
    "print(\"*\"*30)\n",
    "print(df.drop(\"d\"))\n",
    "#修改行数据\n",
    "print(\"*\"*30)\n",
    "df[\"one\"][0] = 3\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Index(['s1', 's2', 's3', 's4'], dtype='object'), Index(['name', 'age'], dtype='object')]\n",
      "name    object\n",
      "age      int64\n",
      "dtype: object object\n",
      "False\n",
      "2\n",
      "8\n",
      "[['Tom' 20]\n",
      " ['Jack' 23]\n",
      " ['Steve' 21]\n",
      " ['Ricky' 26]]\n",
      "    name  age\n",
      "s1   Tom   20\n",
      "s2  Jack   23\n",
      "     name  age\n",
      "s3  Steve   21\n",
      "s4  Ricky   26\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    DataFrame常用属性\n",
    "       - axes                       返回 行/列 标签（index）列表\n",
    "       - dtype                      返回对象的数据类型(dtype)。\n",
    "       - dtypes                     返回DataFrame的数据类型(dtype)。\n",
    "       - empty                      如果系列为空，则返回True\n",
    "       - ndim                       返回底层数据的维度，默认定义:1\n",
    "       - size                       返回基础数据中的元素数\n",
    "       - values                     将系数作为ndarray返回\n",
    "       - head()                     返回前n行\n",
    "       - tail()                     返回最后n行\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "data = {\"name\":[\"Tom\",'Jack',\"Steve\",\"Ricky\"],\"age\":[20,23,21,26]}\n",
    "df = pd.DataFrame(data,index=['s1','s2','s3','s4'])\n",
    "print(df.axes)\n",
    "print(df.dtypes,df[\"name\"].dtype)\n",
    "print(df.empty)\n",
    "print(df.ndim)\n",
    "print(df.size)\n",
    "print(df.values)\n",
    "print(df.head(2))\n",
    "print(df.tail(2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Name  Age  Rating\n",
      "0      Tom   25    4.23\n",
      "1    James   26    3.24\n",
      "2    Ricky   25    3.98\n",
      "3      Vin   23    2.56\n",
      "4    Steve   30    3.20\n",
      "5    Minsu   29    4.60\n",
      "6     Jack   23    3.80\n",
      "7      Lee   34    3.78\n",
      "8    David   40    2.98\n",
      "9   Gasper   30    4.80\n",
      "10  Betina   51    4.10\n",
      "11  Andres   46    3.65\n",
      "Name      12\n",
      "Age       12\n",
      "Rating    12\n",
      "dtype: int64\n",
      "Name      TomJamesRickyVinSteveMinsuJackLeeDavidGasperBe...\n",
      "Age                                                     382\n",
      "Rating                                                44.92\n",
      "dtype: object\n",
      "Age       31.833333\n",
      "Rating     3.743333\n",
      "dtype: float64\n",
      "Age       29.50\n",
      "Rating     3.79\n",
      "dtype: float64\n",
      "Age       9.232682\n",
      "Rating    0.661628\n",
      "dtype: float64\n",
      "Name      Andres\n",
      "Age           23\n",
      "Rating      2.56\n",
      "dtype: object\n",
      "Name      Vin\n",
      "Age        51\n",
      "Rating    4.8\n",
      "dtype: object\n",
      "    Age  Rating\n",
      "0    25    4.23\n",
      "1    26    3.24\n",
      "2    25    3.98\n",
      "3    23    2.56\n",
      "4    30    3.20\n",
      "5    29    4.60\n",
      "6    23    3.80\n",
      "7    34    3.78\n",
      "8    40    2.98\n",
      "9    30    4.80\n",
      "10   51    4.10\n",
      "11   46    3.65\n",
      "Age       7.158408e+17\n",
      "Rating    6.320128e+06\n",
      "dtype: float64\n",
      "                                                 Name  Age  Rating\n",
      "0                                                 Tom   25    4.23\n",
      "1                                            TomJames   51    7.47\n",
      "2                                       TomJamesRicky   76   11.45\n",
      "3                                    TomJamesRickyVin   99   14.01\n",
      "4                               TomJamesRickyVinSteve  129   17.21\n",
      "5                          TomJamesRickyVinSteveMinsu  158   21.81\n",
      "6                      TomJamesRickyVinSteveMinsuJack  181   25.61\n",
      "7                   TomJamesRickyVinSteveMinsuJackLee  215   29.39\n",
      "8              TomJamesRickyVinSteveMinsuJackLeeDavid  255   32.37\n",
      "9        TomJamesRickyVinSteveMinsuJackLeeDavidGasper  285   37.17\n",
      "10  TomJamesRickyVinSteveMinsuJackLeeDavidGasperBe...  336   41.27\n",
      "11  TomJamesRickyVinSteveMinsuJackLeeDavidGasperBe...  382   44.92\n",
      "                   Age        Rating\n",
      "0                   25  4.230000e+00\n",
      "1                  650  1.370520e+01\n",
      "2                16250  5.454670e+01\n",
      "3               373750  1.396395e+02\n",
      "4             11212500  4.468465e+02\n",
      "5            325162500  2.055494e+03\n",
      "6           7478737500  7.810877e+03\n",
      "7         254277075000  2.952512e+04\n",
      "8       10171083000000  8.798485e+04\n",
      "9      305132490000000  4.223273e+05\n",
      "10   15561756990000000  1.731542e+06\n",
      "11  715840821540000000  6.320128e+06\n",
      "             Age     Rating\n",
      "count  12.000000  12.000000\n",
      "mean   31.833333   3.743333\n",
      "std     9.232682   0.661628\n",
      "min    23.000000   2.560000\n",
      "25%    25.000000   3.230000\n",
      "50%    29.500000   3.790000\n",
      "75%    35.500000   4.132500\n",
      "max    51.000000   4.800000\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:31: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:32: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:33: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:37: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas描述性统计\n",
    "        数值型数据的描述性统计主要包括了计算数值型数据的完整情况、最小值、均值、中位 数、最大值、四分位数、极差、标准差、方差、\n",
    "        协方差等。在NumPy库中一些常用的统计学函数也可用于对数据框进行描述性统计。\n",
    "        - df.count()                                非空观测数量\n",
    "        - df.sum(axis=None)                         所有值之和\n",
    "        - df.mean(axis=None)                        所有值的平均值\n",
    "        - df.median(axis=None)                      所有值的中位数\n",
    "        - df.std(axis=None)                         值的标准偏差\n",
    "        - df.min(axis=None)                         所有值中的最小值\n",
    "        - df.max(axis=None)                         所有值中的最大值\n",
    "        - df.abs(axis=None)                         绝对值\n",
    "        - df.prod(axis=None)                        数组元素的乘积\n",
    "        - df.cumsum(axis=None)                      累计总和\n",
    "        - df.cumprod(axis=None)                     累计乘积\n",
    "        - df.describe(include)                      拿到所有统计数据(如果为number,拿到所有数值型数据,如果为object,则拿到所有字符型数据)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "# 创建DataFrame\n",
    "d = {'Name':pd.Series(['Tom','James','Ricky','Vin','Steve','Minsu','Jack',\n",
    "   'Lee','David','Gasper','Betina','Andres']),\n",
    "   'Age':pd.Series([25,26,25,23,30,29,23,34,40,30,51,46]),\n",
    "   'Rating':pd.Series([4.23,3.24,3.98,2.56,3.20,4.6,3.8,3.78,2.98,4.80,4.10,3.65])}\n",
    "\n",
    "df = pd.DataFrame(d)\n",
    "print(df)\n",
    "print(df.count())\n",
    "print(df.sum())\n",
    "print(df.mean())\n",
    "print(df.median())\n",
    "print(df.std())\n",
    "print(df.min())\n",
    "print(df.max())\n",
    "print(df[[\"Age\",\"Rating\"]].abs())\n",
    "print(df.prod())\n",
    "print(df.cumsum())\n",
    "print(df[[\"Age\",\"Rating\"]].cumprod())\n",
    "print(df.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Name  Age  Rating\n",
      "11  Andres   46    3.65\n",
      "10  Betina   51    4.10\n",
      "9   Gasper   30    4.80\n",
      "8    David   40    2.98\n",
      "7      Lee   34    3.78\n",
      "6     Jack   23    3.80\n",
      "5    Minsu   29    4.60\n",
      "4    Steve   30    3.20\n",
      "3      Vin   23    2.56\n",
      "2    Ricky   25    3.98\n",
      "1    James   26    3.24\n",
      "0      Tom   25    4.23\n",
      "******************************\n",
      "    Age    Name  Rating\n",
      "0    25     Tom    4.23\n",
      "1    26   James    3.24\n",
      "2    25   Ricky    3.98\n",
      "3    23     Vin    2.56\n",
      "4    30   Steve    3.20\n",
      "5    29   Minsu    4.60\n",
      "6    23    Jack    3.80\n",
      "7    34     Lee    3.78\n",
      "8    40   David    2.98\n",
      "9    30  Gasper    4.80\n",
      "10   51  Betina    4.10\n",
      "11   46  Andres    3.65\n",
      "******************************\n",
      "      Name  Age  Rating\n",
      "3      Vin   23    2.56\n",
      "6     Jack   23    3.80\n",
      "0      Tom   25    4.23\n",
      "2    Ricky   25    3.98\n",
      "1    James   26    3.24\n",
      "5    Minsu   29    4.60\n",
      "4    Steve   30    3.20\n",
      "9   Gasper   30    4.80\n",
      "7      Lee   34    3.78\n",
      "8    David   40    2.98\n",
      "11  Andres   46    3.65\n",
      "10  Betina   51    4.10\n",
      "******************************\n",
      "      Name  Age  Rating\n",
      "6     Jack   23    3.80\n",
      "3      Vin   23    2.56\n",
      "0      Tom   25    4.23\n",
      "2    Ricky   25    3.98\n",
      "1    James   26    3.24\n",
      "5    Minsu   29    4.60\n",
      "9   Gasper   30    4.80\n",
      "4    Steve   30    3.20\n",
      "7      Lee   34    3.78\n",
      "8    David   40    2.98\n",
      "11  Andres   46    3.65\n",
      "10  Betina   51    4.10\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas排序\n",
    "        - pandas有两种排序方式，它们分别是按标签与按实际值排序。\n",
    "            - 按标签排序\n",
    "                - df.sort_index(axis,ascending）\n",
    "                    - axis                          0:按行排序 1:按列排序\n",
    "                    - ascending                     True:升序 False:降序\n",
    "            - 按实际值排序\n",
    "                - df.sort_values(by)\n",
    "                    - by                            指定具体的标签名\n",
    "                    - axis                          0:按行排序 1:按列排序\n",
    "                    - ascending                     True:升序 False:降序\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "import pandas as pd\n",
    "d = {'Name':pd.Series(['Tom','James','Ricky','Vin','Steve','Minsu','Jack',\n",
    "   'Lee','David','Gasper','Betina','Andres']),\n",
    "   'Age':pd.Series([25,26,25,23,30,29,23,34,40,30,51,46]),\n",
    "   'Rating':pd.Series([4.23,3.24,3.98,2.56,3.20,4.6,3.8,3.78,2.98,4.80,4.10,3.65])}\n",
    "unsorted_df = pd.DataFrame(d)\n",
    "#按行标签排序\n",
    "print(unsorted_df.sort_index(ascending=False))\n",
    "print(\"*\"*30)\n",
    "#按列标签排序\n",
    "print(unsorted_df.sort_index(axis=1))           #axis=1:按照列标签进行排序\n",
    "print(\"*\"*30)\n",
    "#按实际值排序\n",
    "print(unsorted_df.sort_values(by=\"Age\"))\n",
    "print(\"*\"*30)\n",
    "print(unsorted_df.sort_values(by=[\"Age\",\"Rating\"],ascending=[True,False]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "           Rank    Year      Points\ncount  4.000000     4.0    4.000000\nmean   2.500000  2014.0  795.250000\nstd    1.290994     0.0   87.439026\nmin    1.000000  2014.0  701.000000\n25%    1.750000  2014.0  731.000000\n50%    2.500000  2014.0  802.000000\n75%    3.250000  2014.0  866.250000\nmax    4.000000  2014.0  876.000000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Rank</th>\n      <th>Year</th>\n      <th>Points</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>4.000000</td>\n      <td>4.0</td>\n      <td>4.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>2.500000</td>\n      <td>2014.0</td>\n      <td>795.250000</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>1.290994</td>\n      <td>0.0</td>\n      <td>87.439026</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>1.000000</td>\n      <td>2014.0</td>\n      <td>701.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>1.750000</td>\n      <td>2014.0</td>\n      <td>731.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>2.500000</td>\n      <td>2014.0</td>\n      <td>802.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>3.250000</td>\n      <td>2014.0</td>\n      <td>866.250000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>4.000000</td>\n      <td>2014.0</td>\n      <td>876.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas分组\n",
    "        - agg_obj = df.groupby(by=None,axis: Axis = 0)          返回DataFrameGroupBy object\n",
    "             - by                            指定具体的标签名\n",
    "             - axis                          0:按行排序 1:按列排序\n",
    "\n",
    "        -  agg_obj.groups                  返回改组的行级索引\n",
    "        -  agg_obj.get_group(组名)          返回指定组名的数据\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "\n",
    "ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',\n",
    "         'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],\n",
    "         'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],\n",
    "         'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],\n",
    "         'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}\n",
    "df = pd.DataFrame(ipl_data)\n",
    "agg_obj = df.groupby(by=\"Year\")\n",
    "# agg_obj.groups\n",
    "# for year,group in agg_obj:\n",
    "#     print(year,group)\n",
    "group = agg_obj.get_group(2014)\n",
    "\n",
    "#分组聚合\n",
    "group.describe()\n",
    "\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Team      4\n",
      "Rank      4\n",
      "Year      4\n",
      "Points    4\n",
      "dtype: int64\n",
      "Team      RidersDevilsKingsRoyals\n",
      "Rank                           10\n",
      "Year                         8056\n",
      "Points                       3181\n",
      "dtype: object\n",
      "Rank         2.50\n",
      "Year      2014.00\n",
      "Points     795.25\n",
      "dtype: float64\n",
      "Rank         2.5\n",
      "Year      2014.0\n",
      "Points     802.0\n",
      "dtype: float64\n",
      "Rank       1.290994\n",
      "Year       0.000000\n",
      "Points    87.439026\n",
      "dtype: float64\n",
      "Team      Devils\n",
      "Rank           1\n",
      "Year        2014\n",
      "Points       701\n",
      "dtype: object\n",
      "Team      Royals\n",
      "Rank           4\n",
      "Year        2014\n",
      "Points       876\n",
      "dtype: object\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:31: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:32: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n",
      "D:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\ipykernel_launcher.py:33: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "bad operand type for abs(): 'str'",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mTypeError\u001B[0m                                 Traceback (most recent call last)",
      "\u001B[1;32m~\\AppData\\Local\\Temp/ipykernel_11536/724123596.py\u001B[0m in \u001B[0;36m<module>\u001B[1;34m\u001B[0m\n\u001B[0;32m     34\u001B[0m \u001B[0mprint\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mmin\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0maxis\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;32mNone\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m     35\u001B[0m \u001B[0mprint\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mmax\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0maxis\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;32mNone\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m---> 36\u001B[1;33m \u001B[0mprint\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mabs\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m     37\u001B[0m \u001B[0mprint\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mprod\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0maxis\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;32mNone\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m     38\u001B[0m \u001B[0mprint\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mgroup\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mcumsum\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0maxis\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;32mNone\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32mD:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\pandas\\core\\generic.py\u001B[0m in \u001B[0;36mabs\u001B[1;34m(self)\u001B[0m\n\u001B[0;32m   9771\u001B[0m         \u001B[1;31m# error: Incompatible return value type (got \"ndarray[Any, dtype[Any]]\",\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m   9772\u001B[0m         \u001B[1;31m# expected \"FrameOrSeries\")\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m-> 9773\u001B[1;33m         \u001B[1;32mreturn\u001B[0m \u001B[0mnp\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mabs\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mself\u001B[0m\u001B[1;33m)\u001B[0m  \u001B[1;31m# type: ignore[return-value]\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m   9774\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m   9775\u001B[0m     \u001B[1;33m@\u001B[0m\u001B[0mfinal\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32mD:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\pandas\\core\\generic.py\u001B[0m in \u001B[0;36m__array_ufunc__\u001B[1;34m(self, ufunc, method, *inputs, **kwargs)\u001B[0m\n\u001B[0;32m   2030\u001B[0m         \u001B[0mself\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mufunc\u001B[0m\u001B[1;33m:\u001B[0m \u001B[0mnp\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mufunc\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mmethod\u001B[0m\u001B[1;33m:\u001B[0m \u001B[0mstr\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m*\u001B[0m\u001B[0minputs\u001B[0m\u001B[1;33m:\u001B[0m \u001B[0mAny\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m**\u001B[0m\u001B[0mkwargs\u001B[0m\u001B[1;33m:\u001B[0m \u001B[0mAny\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m   2031\u001B[0m     ):\n\u001B[1;32m-> 2032\u001B[1;33m         \u001B[1;32mreturn\u001B[0m \u001B[0marraylike\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0marray_ufunc\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mself\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mufunc\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mmethod\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m*\u001B[0m\u001B[0minputs\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m**\u001B[0m\u001B[0mkwargs\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m   2033\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m   2034\u001B[0m     \u001B[1;31m# ideally we would define this to avoid the getattr checks, but\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32mD:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\pandas\\core\\arraylike.py\u001B[0m in \u001B[0;36marray_ufunc\u001B[1;34m(self, ufunc, method, *inputs, **kwargs)\u001B[0m\n\u001B[0;32m    370\u001B[0m             \u001B[1;31m# take this path if there are no kwargs\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    371\u001B[0m             \u001B[0mmgr\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0minputs\u001B[0m\u001B[1;33m[\u001B[0m\u001B[1;36m0\u001B[0m\u001B[1;33m]\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0m_mgr\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m--> 372\u001B[1;33m             \u001B[0mresult\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mmgr\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mapply\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mgetattr\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mufunc\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mmethod\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    373\u001B[0m         \u001B[1;32melse\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    374\u001B[0m             \u001B[1;31m# otherwise specific ufunc methods (eg np.<ufunc>.accumulate(..))\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32mD:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001B[0m in \u001B[0;36mapply\u001B[1;34m(self, f, align_keys, ignore_failures, **kwargs)\u001B[0m\n\u001B[0;32m    323\u001B[0m             \u001B[1;32mtry\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    324\u001B[0m                 \u001B[1;32mif\u001B[0m \u001B[0mcallable\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mf\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m--> 325\u001B[1;33m                     \u001B[0mapplied\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mb\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mapply\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mf\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m**\u001B[0m\u001B[0mkwargs\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    326\u001B[0m                 \u001B[1;32melse\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    327\u001B[0m                     \u001B[0mapplied\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mgetattr\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mb\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mf\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;33m**\u001B[0m\u001B[0mkwargs\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32mD:\\PycharmProjects\\dataAnalysis\\venv\\lib\\site-packages\\pandas\\core\\internals\\blocks.py\u001B[0m in \u001B[0;36mapply\u001B[1;34m(self, func, **kwargs)\u001B[0m\n\u001B[0;32m    379\u001B[0m         \"\"\"\n\u001B[0;32m    380\u001B[0m         \u001B[1;32mwith\u001B[0m \u001B[0mnp\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0merrstate\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mall\u001B[0m\u001B[1;33m=\u001B[0m\u001B[1;34m\"ignore\"\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m--> 381\u001B[1;33m             \u001B[0mresult\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mfunc\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mself\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mvalues\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m**\u001B[0m\u001B[0mkwargs\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    382\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    383\u001B[0m         \u001B[1;32mreturn\u001B[0m \u001B[0mself\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0m_split_op_result\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mresult\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;31mTypeError\u001B[0m: bad operand type for abs(): 'str'"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas分组聚合\n",
    "        - group.count()                                非空观测数量\n",
    "        - group.sum(axis=None)                         所有值之和\n",
    "        - group.mean(axis=None)                        所有值的平均值\n",
    "        - group.median(axis=None)                      所有值的中位数\n",
    "        - group.std(axis=None)                         值的标准偏差\n",
    "        - group.min(axis=None)                         所有值中的最小值\n",
    "        - group.max(axis=None)                         所有值中的最大值\n",
    "        - group.abs()                                  绝对值\n",
    "        - group.prod(axis=None)                        数组元素的乘积\n",
    "        - group.cumsum(axis=None)                      累计总和\n",
    "        - group.cumprod(axis=None)                     累计乘积\n",
    "        - group.describe(include)                      拿到所有统计数据(如果为number,拿到所有数值型数据,如果为object,则拿到所有字符型数据)\n",
    "        - group[\"列标签\"].agg([np.sum,np.mean,np.std])  拿到某列的聚合数据\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',\n",
    "         'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],\n",
    "         'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],\n",
    "         'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],\n",
    "         'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}\n",
    "df = pd.DataFrame(ipl_data)\n",
    "agg_obj = df.groupby(by=\"Year\")\n",
    "group = agg_obj.get_group(2014)\n",
    "\n",
    "#分组聚合\n",
    "print(group.count())\n",
    "print(group.sum(axis=None))\n",
    "print(group.mean(axis=None))\n",
    "print(group.median(axis=None))\n",
    "print(group.std(axis=None))\n",
    "print(group.min(axis=None))\n",
    "print(group.max(axis=None))\n",
    "print(group.abs())\n",
    "print(group.prod(axis=None))\n",
    "print(group.cumsum(axis=None))\n",
    "print(group.cumprod(axis=None))\n",
    "print(group.describe())\n",
    "print(group[\"Points\"].agg([np.sum, np.mean, np.std]))\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    student_id student_name  class_id gender   age  score class_name\n",
      "0          1.0         Alex         1      M  20.0   98.0     ClassA\n",
      "1          2.0          Amy         1      M  21.0   74.0     ClassA\n",
      "2          3.0        Allen         1      F  22.0   67.0     ClassA\n",
      "3         11.0         Emma         1      F  20.0   52.0     ClassA\n",
      "4         12.0        Marry         1      F  21.0   38.0     ClassA\n",
      "5         13.0        Allen         1      M  22.0   26.0     ClassA\n",
      "6          4.0        Alice         2      F  20.0   38.0     ClassB\n",
      "7          5.0       Ayoung         2      M  21.0   65.0     ClassB\n",
      "8          6.0        Billy         2      M  22.0   29.0     ClassB\n",
      "9         14.0         Jean         2      M  23.0   89.0     ClassB\n",
      "10        15.0         Rose         2      F  20.0   68.0     ClassB\n",
      "11        16.0        David         2      F  21.0   46.0     ClassB\n",
      "12        20.0       Andrew         2      F  22.0   87.0     ClassB\n",
      "13         7.0        Brian         3      F  23.0   32.0     ClassC\n",
      "14         8.0         Bran         3      F  20.0   34.0     ClassC\n",
      "15         9.0        Bryce         3      M  21.0   85.0     ClassC\n",
      "16        17.0          Tom         3      M  22.0   32.0     ClassC\n",
      "17        18.0         Jack         3      M  20.0   78.0     ClassC\n",
      "18        19.0       Daniel         3      F  21.0   79.0     ClassC\n",
      "19         NaN          NaN         5    NaN   NaN    NaN     ClassE\n",
      "******************************\n",
      "    student_id student_name  class_id gender   age  score class_name\n",
      "0          1.0         Alex         1      M  20.0   98.0        NaN\n",
      "1          2.0          Amy         1      M  21.0   74.0        NaN\n",
      "2          3.0        Allen         1      F  22.0   67.0        NaN\n",
      "3          4.0        Alice         2      F  20.0   38.0        NaN\n",
      "4          5.0       Ayoung         2      M  21.0   65.0        NaN\n",
      "5          6.0        Billy         2      M  22.0   29.0        NaN\n",
      "6          7.0        Brian         3      F  23.0   32.0        NaN\n",
      "7          8.0         Bran         3      F  20.0   34.0        NaN\n",
      "8          9.0        Bryce         3      M  21.0   85.0        NaN\n",
      "9         10.0        Betty         4      M  22.0   64.0        NaN\n",
      "10        11.0         Emma         1      F  20.0   52.0        NaN\n",
      "11        12.0        Marry         1      F  21.0   38.0        NaN\n",
      "12        13.0        Allen         1      M  22.0   26.0        NaN\n",
      "13        14.0         Jean         2      M  23.0   89.0        NaN\n",
      "14        15.0         Rose         2      F  20.0   68.0        NaN\n",
      "15        16.0        David         2      F  21.0   46.0        NaN\n",
      "16        17.0          Tom         3      M  22.0   32.0        NaN\n",
      "17        18.0         Jack         3      M  20.0   78.0        NaN\n",
      "18        19.0       Daniel         3      F  21.0   79.0        NaN\n",
      "19        20.0       Andrew         2      F  22.0   87.0        NaN\n",
      "0          NaN          NaN         1    NaN   NaN    NaN     ClassA\n",
      "1          NaN          NaN         2    NaN   NaN    NaN     ClassB\n",
      "2          NaN          NaN         3    NaN   NaN    NaN     ClassC\n",
      "3          NaN          NaN         5    NaN   NaN    NaN     ClassE\n",
      "******************************\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas 表关联\n",
    "        - pd.merge(left,right)\n",
    "            left                        左表 (DataFrame | Series)\n",
    "            right                       右表 (DataFrame | Series)\n",
    "            how                         left/right/outer/inner\n",
    "        - pd.concat(objs, axis=0, join='outer)\n",
    "            objs                        多个DataFrame对象构成的数组\n",
    "            axis                        0:index 1:columns, default 0\n",
    "            join                        inner、outer(默认outer)\n",
    "\n",
    "        -df.join(df1)\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({\n",
    "         'student_id':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20],\n",
    "         'student_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung', 'Billy', 'Brian', 'Bran', 'Bryce', 'Betty', 'Emma', 'Marry', 'Allen', 'Jean', 'Rose', 'David', 'Tom', 'Jack', 'Daniel', 'Andrew'],\n",
    "         'class_id':[1,1,1,2,2,2,3,3,3,4,1,1,1,2,2,2,3,3,3,2],\n",
    "         'gender':['M', 'M', 'F', 'F', 'M', 'M', 'F', 'F', 'M', 'M', 'F', 'F', 'M', 'M', 'F', 'F', 'M', 'M', 'F', 'F'],\n",
    "         'age':[20,21,22,20,21,22,23,20,21,22,20,21,22,23,20,21,22,20,21,22],\n",
    "         'score':[98,74,67,38,65,29,32,34,85,64,52,38,26,89,68,46,32,78,79,87]})\n",
    "right = pd.DataFrame(\n",
    "         {'class_id':[1,2,3,5],\n",
    "         'class_name': ['ClassA', 'ClassB', 'ClassC', 'ClassE']})\n",
    "# 合并两个DataFrame\n",
    "data = pd.merge(left,right,how=\"right\")\n",
    "print(data)\n",
    "print(\"*\"*30)\n",
    "data = pd.concat([left,right],join=\"outer\")\n",
    "print(data)\n",
    "print(\"*\"*30)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    student_id student_name  class_id gender  age  score class_name\n",
      "0            1         Alex         1      M   20     98     ClassA\n",
      "1            2          Amy         1      M   21     74     ClassA\n",
      "2            3        Allen         1      F   22     67     ClassA\n",
      "3           11         Emma         1      F   20     52     ClassA\n",
      "4           12        Marry         1      F   21     38     ClassA\n",
      "5           13        Allen         1      M   22     26     ClassA\n",
      "6            4        Alice         2      F   20     38     ClassB\n",
      "7            5       Ayoung         2      M   21     65     ClassB\n",
      "8            6        Billy         2      M   22     29     ClassB\n",
      "9           14         Jean         2      M   23     89     ClassB\n",
      "10          15         Rose         2      F   20     68     ClassB\n",
      "11          16        David         2      F   21     46     ClassB\n",
      "12          20       Andrew         2      F   22     87     ClassB\n",
      "13           7        Brian         3      F   23     32     ClassC\n",
      "14           8         Bran         3      F   20     34     ClassC\n",
      "15           9        Bryce         3      M   21     85     ClassC\n",
      "16          17          Tom         3      M   22     32     ClassC\n",
      "17          18         Jack         3      M   20     78     ClassC\n",
      "18          19       Daniel         3      F   21     79     ClassC\n",
      "******************************\n",
      "                       age      score  student_id\n",
      "class_id gender                                  \n",
      "1        F       21.000000  52.333333    8.666667\n",
      "         M       21.000000  66.000000    5.333333\n",
      "2        F       20.750000  59.750000   13.750000\n",
      "         M       22.000000  61.000000    8.333333\n",
      "3        F       21.333333  48.333333   11.333333\n",
      "         M       21.000000  65.000000   14.666667\n",
      "******************************\n",
      "                     score\n",
      "class_id gender           \n",
      "1        F       52.333333\n",
      "         M       66.000000\n",
      "2        F       59.750000\n",
      "         M       61.000000\n",
      "3        F       48.333333\n",
      "         M       65.000000\n",
      "******************************\n",
      "                score                  \n",
      "age                20    21    22    23\n",
      "class_id gender                        \n",
      "1        F       52.0  38.0  67.0   NaN\n",
      "         M       98.0  74.0  26.0   NaN\n",
      "2        F       53.0  46.0  87.0   NaN\n",
      "         M        NaN  65.0  29.0  89.0\n",
      "3        F       34.0  79.0   NaN  32.0\n",
      "         M       78.0  85.0  32.0   NaN\n",
      "******************************\n",
      "                     score                             \n",
      "age                     20    21    22    23        All\n",
      "class_id gender                                        \n",
      "1        F       52.000000  38.0  67.0   NaN  52.333333\n",
      "         M       98.000000  74.0  26.0   NaN  66.000000\n",
      "2        F       53.000000  46.0  87.0   NaN  59.750000\n",
      "         M             NaN  65.0  29.0  89.0  61.000000\n",
      "3        F       34.000000  79.0   NaN  32.0  48.333333\n",
      "         M       78.000000  85.0  32.0   NaN  65.000000\n",
      "All              61.333333  64.5  48.2  60.5  58.789474\n",
      "******************************\n",
      "                score                      \n",
      "age                20    21    22    23 All\n",
      "class_id gender                            \n",
      "1        F       52.0  38.0  67.0   NaN  67\n",
      "         M       98.0  74.0  26.0   NaN  98\n",
      "2        F       68.0  46.0  87.0   NaN  87\n",
      "         M        NaN  65.0  29.0  89.0  89\n",
      "3        F       34.0  79.0   NaN  32.0  79\n",
      "         M       78.0  85.0  32.0   NaN  85\n",
      "All              98.0  85.0  87.0  89.0  98\n",
      "gender     F  M  All\n",
      "class_id            \n",
      "1          3  3    6\n",
      "2          4  3    7\n",
      "3          3  3    6\n",
      "All       10  9   19\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas透视表与交叉表\n",
    "        透视表(pivot table)是各种电子表格程序和其他数据分析软件中一种常见的数据汇总工具。\n",
    "            它根据一个或多个键对数据进行分组聚合，并根据每个分组进行数据汇总。\n",
    "                - pd.pivot_table(data,values=None,index=None,columns=None,aggfunc=\"mean\")\n",
    "                - df.pivot_table(values=None,index=None,columns=None,aggfunc=\"mean\")\n",
    "                    - values                 要聚合的列或列的列表\n",
    "                    - index                  透视表索引上进行分组的键。如果传入一个数组，则以与列值相同的方式使用它\n",
    "                    - columns                透视表列上进行分组的键。如果传入一个数组，则以与列值相同的方式使用它\n",
    "                    - aggfunc                用于聚合的Unction，默认为np.mean\n",
    "                    - margins                True/False 是否增加小计\n",
    "        交叉表(cross-tabulation, 简称crosstab)是一种用于计算分组频率的特殊透视表\n",
    "                pd.crosstab(index, columns, values=None, rownames=None, colnames=None, aggfunc=None, margins=False)\n",
    "                    - values                 根据因素进行聚合的值数组。需要指定aggfunc\n",
    "                    - index                  在行中分组的值\n",
    "                    - columns                在列中分组的值\n",
    "                    - aggfunc                用于聚合的Unction，默认为np.mean\n",
    "                    - margins                True/False 是否增加小计\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "left = pd.DataFrame({\n",
    "         'student_id':[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20],\n",
    "         'student_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung', 'Billy', 'Brian', 'Bran', 'Bryce', 'Betty', 'Emma', 'Marry', 'Allen', 'Jean', 'Rose', 'David', 'Tom', 'Jack', 'Daniel', 'Andrew'],\n",
    "         'class_id':[1,1,1,2,2,2,3,3,3,4,1,1,1,2,2,2,3,3,3,2],\n",
    "         'gender':['M', 'M', 'F', 'F', 'M', 'M', 'F', 'F', 'M', 'M', 'F', 'F', 'M', 'M', 'F', 'F', 'M', 'M', 'F', 'F'],\n",
    "         'age':[20,21,22,20,21,22,23,20,21,22,20,21,22,23,20,21,22,20,21,22],\n",
    "         'score':[98,74,67,38,65,29,32,34,85,64,52,38,26,89,68,46,32,78,79,87]})\n",
    "right = pd.DataFrame(\n",
    "         {'class_id':[1,2,3,5],\n",
    "         'class_name': ['ClassA', 'ClassB', 'ClassC', 'ClassE']})\n",
    "# 合并两个DataFrame\n",
    "data = pd.merge(left,right)\n",
    "print(data)\n",
    "print(\"*\"*30)\n",
    "# 以class_id与gender做分组汇总数据，默认聚合统计所有列\n",
    "data1 = data.pivot_table(index=[\"class_id\",\"gender\"])\n",
    "print(data1)\n",
    "print(\"*\"*30)\n",
    "# 以class_id与gender做分组汇总数据，聚合统计score列\n",
    "data2 = data.pivot_table(index=['class_id', 'gender'], values=['score'])\n",
    "print(data2)\n",
    "print(\"*\"*30)\n",
    "# 以class_id与gender做分组汇总数据，聚合统计score列，针对age的每个值列级分组统计\n",
    "data3 = data.pivot_table(index=[\"class_id\",\"gender\"],values=[\"score\"],columns=['age'])\n",
    "print(data3)\n",
    "print(\"*\"*30)\n",
    "# 以class_id与gender做分组汇总数据，聚合统计score列，针对age的每个值列级分组统计，添加行、列小计\n",
    "data4 = data.pivot_table(index=[\"class_id\",\"gender\"],values=[\"score\"],columns=[\"age\"],margins=True)\n",
    "print(data4)\n",
    "print(\"*\"*30)\n",
    "# 以class_id与gender做分组汇总数据，聚合统计score列，针对age的每个值列级分组统计，添加行、列小计\n",
    "data5 = data.pivot_table(index=[\"class_id\",\"gender\"],values=[\"score\"],columns=[\"age\"],margins=True,aggfunc=\"max\")\n",
    "print(data5)\n",
    "\n",
    "#交叉表\n",
    "# 按照class_id分组，针对不同的gender，统计数量\n",
    "data6=pd.crosstab(index=data.class_id,columns=data.gender,margins=True)\n",
    "print(data6)\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [
    {
     "data": {
      "text/plain": "<Figure size 432x288 with 1 Axes>",
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAD1CAYAAABA+A6aAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/YYfK9AAAACXBIWXMAAAsTAAALEwEAmpwYAAATA0lEQVR4nO3dfZDdVX3H8feXEFgjGCEJDrKJm44BFghIWB6moZIq2iBOEMFKFBofM2Ol2rE6TRsHM1Q72DJ1cCa0zQgCtpgCU2gYglBEqsiDCYLEkEQiRrJgAQPGB0CIfvvHvens3uxmb9i7v3v35P2a2cn9Pew53+ze/ezZc8/vdyMzkSSNf/u0uwBJUmsY6JJUCANdkgphoEtSIQx0SSqEgS5Jhdi3XR1PnTo1e3p62tW9JI1LDzzwwM8zc9pQx9oW6D09Paxdu7Zd3UvSuBQRPx3umFMuklQIA12SCmGgS1Ih2jaHLknt8vLLL9Pf38+LL77Y7lKG1dXVRXd3NxMnTmz6c0YM9Ii4Engn8HRmHjPE8QAuA94BPA98IDO/33QFklSx/v5+DjzwQHp6eqhFWGfJTLZt20Z/fz8zZ85s+vOamXK5Cpi/m+NnALPqH4uBf266d0lqgxdffJEpU6Z0ZJgDRARTpkzZ478gRgz0zPw28OxuTjkLuCZr7gNeGxGH7lEVklSxTg3znV5Jfa14UfQwYOuA7f76vl1ExOKIWBsRa5955pkWdC1J2qnSF0UzcwWwAqCvr8931tC41bPklkHbWy45c9D27KtnD9pet2jdmNekV67x+zlajc+HqrRihP4EMH3Adnd9nyRpGO9617s44YQTOProo1mxYkVL2mzFCH0VcGFErAROBrZn5s9a0K4kFevKK6/k4IMP5oUXXuDEE0/knHPOYcqUKaNqs5lli18H5gFTI6If+BwwESAz/wVYTW3J4mZqyxY/OKqKJGkv8OUvf5kbb7wRgK1bt/Loo4+OfaBn5sIRjifw8VFVIUl7kbvuuos77riDe++9l0mTJjFv3ryWXOTkpf+SVLHt27dz0EEHMWnSJDZu3Mh9993XknYNdEmq2Pz589mxYwe9vb0sWbKEU045pSXtei8XSXu9qpcZ7r///tx6660tb9cRuiQVYnyN0JdNbtje3p46JDXNi6yqM74CXR3NH1ypvZxykaRCGOiSVAgDXZIKYaBLUsW2bNnCMcfs8gZwo+aLopLUuIJu1O21ZwWeI3RJaoMdO3bw/ve/n97eXs4991yef/75UbfpCH0cGOrm++26gb6k1ti0aRNXXHEFc+fO5UMf+hCXX345n/70p0fVpiN0Sa21bPLgDw1p+vTpzJ07F4Dzzz+fu+++e9RtGuiS1AaNbwLdijetNtAlqQ0ef/xx7r33XgCuvfZaTj311FG3aaBLUhscccQRLF++nN7eXp577jk+9rGPjbpNXxSVpIqXGfb09LBx48aWt+sIXZIKYaBLUiEMdEkqhIEuSYXwRVGpAhuO7N1lX+/GDW2oRCVzhC5JhTDQJakQTrlI2us1vh/uaLXr/XQdoUtSG1xzzTUce+yxHHfccVxwwQUtadMRuiRVbP369Xz+85/nnnvuYerUqTz77LMtadcRuiRV7M477+Q973kPU6dOBeDggw9uSbsGuiQVwkCXpIq95S1v4frrr2fbtm0ALZtycQ5dkip29NFHs3TpUk477TQmTJjA8ccfz1VXXTXqdpsK9IiYD1wGTAC+kpmXNByfAVwNvLZ+zpLMXD3q6iSpAu1YZrho0SIWLVrU0jZHnHKJiAnAcuAM4ChgYUQc1XDaZ4HrMvN44Dzg8pZWKUkaUTNz6CcBmzPzscx8CVgJnNVwTgKvqT+eDDzZuhIlSc1oZsrlMGDrgO1+4OSGc5YBt0fEXwCvBk5vSXWSOl7PklsGbW/palMhatkql4XAVZnZDbwD+FpE7NJ2RCyOiLURsfaZZ55pUdeSJGgu0J8Apg/Y7q7vG+jDwHUAmXkv0AVMbWwoM1dkZl9m9k2bNu2VVSxJGlIzgb4GmBURMyNiP2oveq5qOOdx4K0AEdFLLdAdgktShUYM9MzcAVwI3AZsoLaaZX1EXBwRC+qn/RXw0Yj4AfB14AOZmWNVtCSVZNmyZVx66aWjbqepdej1NeWrG/ZdNODxI8DcUVcjSW0w1DtKjUa73o3KS/8lqQ2+8IUvcPjhh3PqqaeyadOmlrQ5ri/9b7wpfbtuKi9Je+KBBx5g5cqVPPTQQ+zYsYM5c+ZwwgknjLrdcR3okjQefec73+Hss89m0qRJACxYsGCEz2iOgS5JY+XJBwdvv/74Me3OOXRJqtib3/xmbrrpJl544QV+9atfcfPNN7ekXUfoklSxOXPm8N73vpfjjjuOQw45hBNPPLEl7RrokvZ67VhmuHTpUpYuXdrSNp1ykaRCOEJX03a5q94lZ7apEklDcYQuSYUw0CXtlTr9dlOvpD4DXdJep6uri23btnVsqGcm27Zto6trz94txDl0SXud7u5u+vv7GfM32vnF04O3tze/mqarq4vu7u496s5Al7TXmThxIjNnzhz7jpad0rC9fUy769hAb1xRAb5XoSTtjnPoklQIA12SCmGgS1IhDHRJKoSBLkmFMNAlqRAGuiQVwkCXpEIY6JJUCANdkgphoEtSIQx0SSqEgS5JhTDQJakQBrokFaJj74feKXyne0njhSN0SSqEgS5JhWgq0CNifkRsiojNEbFkmHP+NCIeiYj1EXFta8uUJI1kxDn0iJgALAfeBvQDayJiVWY+MuCcWcDfAHMz87mIOGSsCpYkDa2ZEfpJwObMfCwzXwJWAmc1nPNRYHlmPgeQmU+3tkxJ0kiaCfTDgK0Dtvvr+wY6HDg8Ir4bEfdFxPxWFShJak6rli3uC8wC5gHdwLcjYnZm/mLgSRGxGFgMMGPGjBZ1LUmC5kboTwDTB2x31/cN1A+sysyXM/MnwI+oBfwgmbkiM/sys2/atGmvtGZJ0hCaCfQ1wKyImBkR+wHnAasazrmJ2uiciJhKbQrmsdaVKUkayYiBnpk7gAuB24ANwHWZuT4iLo6IBfXTbgO2RcQjwLeAz2TmtrEqWpK0q6bm0DNzNbC6Yd9FAx4n8Kn6hySpDbxSVJIKYaBLUiEMdEkqhIEuSYUw0CWpEL7BhSRVZPbVswdtr1u0rqXtO0KXpEIY6JJUCANdkgphoEtSIXxRdLxaNrlhe3v7a5jpLZE7zVi/CKfO4ghdkgphoEtSIQx0SSpEUXPoG47sHbTdu3FDmyqRpOo5QpekQhjoklQIA12SClHUHLoktVPPklsGbW/pqrZ/R+iSVAgDXZIK4ZSLJLVJ41JrGN1ya0foklQIA12SCmGgS1IhnEPfU51w21pJGoIjdEkqhIEuSYUw0CWpEAa6JBXCQJekQhjoklSIppYtRsR84DJgAvCVzLxkmPPOAW4ATszMtS2rUpL2ROPyYtgrlhiPOEKPiAnAcuAM4ChgYUQcNcR5BwKfBO5vdZGSpJE1M+VyErA5Mx/LzJeAlcBZQ5z3d8AXgRdbWJ8kqUnNBPphwNYB2/31ff8vIuYA0zNz8N3dJUmVGfWl/xGxD/BPwAeaOHcxsBhgxowZo+26I7X6dpiS1KxmRuhPANMHbHfX9+10IHAMcFdEbAFOAVZFRF9jQ5m5IjP7MrNv2rRpr7xqSdIumgn0NcCsiJgZEfsB5wGrdh7MzO2ZOTUzezKzB7gPWOAqF0mq1ohTLpm5IyIuBG6jtmzxysxcHxEXA2szc9XuW5CqNfvq2YO21y1a16ZKpGo1NYeemauB1Q37Lhrm3HmjL0uStKe8H7rGv8aLSGaW+YJ7o54luy4q23LJmW2oRJ3CS/8lqRAGuiQVwkCXpEI4hy7tRRovfPOit7I4QpekQhjoklQIA12SCmGgS1IhDHRJKoSrXArh/UskOUKXpEIY6JJUCANdkgphoEtSIXxRVMXzcnftLRyhS1IhHKFrXBnyTR262lCI1IEcoUtSIQx0SSqEgS5JhTDQJakQBrokFcJAl6RCuGxxlBrvcnhdm+qQxovGC73Ai71axRG6JBXCQJekQhjoklQI59AL5Q2ppL2PI3RJKoSBLkmFMNAlqRAGuiQVoqlAj4j5EbEpIjZHxJIhjn8qIh6JiIcj4psR8YbWlypJ2p0RAz0iJgDLgTOAo4CFEXFUw2kPAn2ZeSxwA/APrS5UkrR7zYzQTwI2Z+ZjmfkSsBI4a+AJmfmtzHy+vnkf0N3aMiVJI2km0A8Dtg7Y7q/vG86HgVtHU5Qkac+19MKiiDgf6ANOG+b4YmAxwIwZM1rZtSTt9ZoZoT8BTB+w3V3fN0hEnA4sBRZk5m+HaigzV2RmX2b2TZs27ZXUK0kaRjOBvgaYFREzI2I/4Dxg1cATIuJ44F+phfnTrS9TkjSSEQM9M3cAFwK3ARuA6zJzfURcHBEL6qf9I3AAcH1EPBQRq4ZpTpI0RpqaQ8/M1cDqhn0XDXh8eovrkiTtIe+2KGmv0PjuYusWrWtTJWPHS/8lqRAGuiQVwkCXpEIY6JJUCANdkgphoEtSIQx0SSqE69AljXs9S24ZtL2lq02FtJkjdEkqhIEuSYUw0CWpEAa6JBXCQJekQrjKRdJeacORvYO2ezduaFMlreMIXZIKYaBLUiGccpFKsmzy4O2ZM9pTh9rCEbokFcJAl6RCGOiSVAjn0DVmGpeFQRlLw6RO5QhdkgphoEtSIQx0SSqEgS5JhTDQJakQBrokFcJAl6RCGOiSVAgDXZIKYaBLUiEMdEkqRFOBHhHzI2JTRGyOiCVDHN8/Iv6jfvz+iOhpeaWSpN0aMdAjYgKwHDgDOApYGBFHNZz2YeC5zHwj8CXgi60uVJK0e82M0E8CNmfmY5n5ErASOKvhnLOAq+uPbwDeGhHRujIlSSOJzNz9CRHnAvMz8yP17QuAkzPzwgHn/LB+Tn99+8f1c37e0NZiYHF98whg0yjrnwr8fMSzxlYn1ACdUUcn1ACdUUcn1ACdUUcn1ACdUUcranhDZk4b6kCl90PPzBXAila1FxFrM7OvVe2N1xo6pY5OqKFT6uiEGjqljk6ooVPqGOsamplyeQKYPmC7u75vyHMiYl9gMrCtFQVKkprTTKCvAWZFxMyI2A84D1jVcM4qYFH98bnAnTnSXI4kqaVGnHLJzB0RcSFwGzABuDIz10fExcDazFwFXAF8LSI2A89SC/0qtGz6ZhQ6oQbojDo6oQbojDo6oQbojDo6oQbojDrGtIYRXxSVJI0PXikqSYUw0CWpEAa6JBWi0nXooxERR1K7IvWw+q4ngFWZuaF9VbVP/etxGHB/Zv56wP75mfmNimo4CcjMXFO/HcR8YGNmrq6i/2FquiYz/6xd/ddrOJXaFdY/zMzbK+rzZGBDZv4yIl4FLAHmAI8Af5+Z2yuq4xPAjZm5tYr+hqlh52q8JzPzjoh4H/CHwAZgRWa+XGEtfwC8m9qy7t8BPwKuzcxfjkl/4+FF0Yj4a2AhtdsO9Nd3d1P7pq3MzEvaVdtOEfHBzPxqRX19Avg4tSfom4BPZuZ/1Y99PzPnVFDD56jd32df4L+Bk4FvAW8DbsvML1RQQ+Py2QD+GLgTIDMXjHUN9Tq+l5kn1R9/lNr35kbg7cDNVTw/I2I9cFx9VdoK4Hnqt+Go73/3WNdQr2M78Bvgx8DXgesz85kq+h5Qw79Te15OAn4BHAD8J7WvRWTmouE/u6V1fAJ4J/Bt4B3Ag/V6zgb+PDPvanmnmdnxH9R+q00cYv9+wKPtrq9ey+MV9rUOOKD+uAdYSy3UAR6ssIYJ1H5ofgm8pr7/VcDDFdXwfeDfgHnAafV/f1Z/fFqF348HBzxeA0yrP341sK6iGjYM/Lo0HHuoyq8Ftanct1NbzvwM8A1q16kcWFEND9f/3Rd4CphQ346qnpv1/tYN6HsScFf98Yyx+jkdL1MuvwdeD/y0Yf+h9WOViIiHhzsEvK6qOoB9sj7NkplbImIecENEvKFeSxV2ZObvgOcj4sdZ/xMyM1+IiKq+J33AJ4GlwGcy86GIeCEz/6ei/nfaJyIOohZkkfURaWb+JiJ2VFTDDwf8lfiDiOjLzLURcThQ2RQDtSm43wO3A7dHxERqf8ktBC4FhrwHSYvtU592eTW1IJ1M7fqY/YGJFfQ/0L7Uplr2p/aXApn5eP3rMiadjQd/CXwzIh4Fds7NzQDeCFw43CeNgdcBfwI817A/gHsqrOOpiHhTZj4EkJm/joh3AlcCsyuq4aWImJSZzwMn7NwZEZOp6JdsPTi+FBHX1/99ivY8pycDD1B7HmREHJqZP4uIA6juF+xHgMsi4rPUbv50b0Rspfbz8pGKaoCG/2/W5qtXAasiYlJFNVwBbKT2F+RS4PqIeAw4hdq0bVW+AqyJiPuBP6J+W/GImEbtF0zLjYs5dICI2IfaC00DXxRdUx8lVlXDFcBXM/PuIY5dm5nvq6iObmoj5P8d4tjczPxuBTXsn5m/HWL/VODQzFw31jUM0feZwNzM/Nuq+x5KPcBel5k/qbDP1wAzqf1i68/Mp6rqu97/4Zn5oyr7HKaO1wNk5pMR8VrgdGrTot+ruI6jgV5qL5BvHPP+xkugS5J2z3XoklQIA12SCmGgS1IhDHRJKoSBLkmF+D/vUNQvAGJypgAAAABJRU5ErkJggg==\n"
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "\"\"\"\n",
    "    pandas可视化        中文官网:http://www.pypandas.cn/docs/user_guide/visualization.html#other-plots\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from matplotlib import pyplot as plt\n",
    "df = pd.DataFrame(np.random.rand(10,4),columns=['a','b','c','d'])\n",
    "df.plot.bar()\n",
    "# df.plot.bar(stacked=True)\n",
    "plt.show()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}