{ "cells": [ { "cell_type": "markdown", "id": "dbc5bdba-568d-4e8d-aa7b-a6cde302ce57", "metadata": {}, "source": [ "## Series" ] }, { "cell_type": "code", "execution_count": 2, "id": "c738a192-a250-46a0-bb5d-aa5e638d6b6a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 Python\n", "1 C\n", "2 Java\n", "3 JavaScript\n", "4 PHP\n", "5 R\n", "dtype: object" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 导入pandas库\n", "import pandas as pd \n", "# 创建Series类的对象\n", "ser_obj = pd.Series(data=['Python', 'C', 'Java', 'JavaScript', 'PHP', 'R'])\n", "ser_obj" ] }, { "cell_type": "code", "execution_count": 3, "id": "35cf3c5e-9cec-423a-aab4-b8bb508cccc3", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "a Python\n", "b C\n", "c Java\n", "d JavaScript\n", "e PHP\n", "f R\n", "dtype: object" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 创建Series类的对象，并指定标签索引\n", "ser_obj = pd.Series(data=['Python', 'C', 'Java', 'JavaScript', \n", " 'PHP', 'R'], index=['a', 'b', 'c', 'd', 'e', 'f'])\n", "ser_obj" ] }, { "cell_type": "code", "execution_count": 4, "id": "2b7809d8-3d20-4e39-a421-98580ed6405e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "a 2022\n", "b 2023\n", "c 2024\n", "d 2025\n", "e 2026\n", "f 2027\n", "dtype: int64" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "year_dict = {'a':2022, 'b':2023, 'c':2024, 'd':2025, 'e':2026, 'f':2027}\n", "ser_obj = pd.Series(data=year_dict)\n", "ser_obj" ] }, { "cell_type": "markdown", "id": "aa62631c-9745-4b82-a9b9-fc80fddb7ebd", "metadata": {}, "source": [ "## DataFrame" ] }, { "cell_type": "code", "execution_count": 5, "id": "aed1d65e-9557-4359-8355-1db343aa1745", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
01234
012345
1678910
21112131415
31617181920
42122232425
52627282930
\n", "
" ], "text/plain": [ " 0 1 2 3 4\n", "0 1 2 3 4 5\n", "1 6 7 8 9 10\n", "2 11 12 13 14 15\n", "3 16 17 18 19 20\n", "4 21 22 23 24 25\n", "5 26 27 28 29 30" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "import pandas as pd\n", "arr_2d = np.arange(1, 31).reshape((6, 5)) # 创建二维数组\n", "df_obj = pd.DataFrame(data=arr_2d) # 根据二维数组创建DataFrame类的对象\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 6, "id": "a56b9bf6-75e1-4255-81de-00bfad49edfa", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
No1No2No3No4No5
012345
1678910
21112131415
31617181920
42122232425
52627282930
\n", "
" ], "text/plain": [ " No1 No2 No3 No4 No5\n", "0 1 2 3 4 5\n", "1 6 7 8 9 10\n", "2 11 12 13 14 15\n", "3 16 17 18 19 20\n", "4 21 22 23 24 25\n", "5 26 27 28 29 30" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 创建DataFrame类的对象，并给该对象指定列索引\n", "df_obj = pd.DataFrame(data=arr_2d, columns=['No1', 'No2', 'No3', 'No4', 'No5'])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 7, "id": "f7b623f2-d4d3-4b00-87d9-e7e878321cf8", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 2\n", "1 7\n", "2 12\n", "3 17\n", "4 22\n", "5 27\n", "Name: No2, dtype: int32" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result = df_obj.No2 # 获取No2列的数据\n", "result" ] }, { "cell_type": "code", "execution_count": 8, "id": "cc62e22f-9058-4546-95fc-2c95c22d332f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "pandas.core.series.Series" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "type(result) # 查看返回结果的类型" ] }, { "cell_type": "code", "execution_count": 9, "id": "3baee915-27f2-4d6c-a750-fcab2d110dd9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "RangeIndex(start=0, stop=6, step=1)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.index # 获取df_obj的行索引" ] }, { "cell_type": "code", "execution_count": 10, "id": "de85cf1f-b1b2-49b6-ab95-f8012bbe3074", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['No1', 'No2', 'No3', 'No4', 'No5'], dtype='object')" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.columns # 获取df_obj的列索引" ] }, { "cell_type": "code", "execution_count": 11, "id": "04720d18-60bc-467d-9f76-7c82619d5ba6", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "array([[ 1, 2, 3, 4, 5],\n", " [ 6, 7, 8, 9, 10],\n", " [11, 12, 13, 14, 15],\n", " [16, 17, 18, 19, 20],\n", " [21, 22, 23, 24, 25],\n", " [26, 27, 28, 29, 30]])" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.values # 获取df_obj的数据" ] }, { "cell_type": "markdown", "id": "98e1637e-6e2f-4957-b8b7-80187f156f83", "metadata": {}, "source": [ "## 【任务3-1】生成课程目录" ] }, { "cell_type": "code", "execution_count": 1, "id": "b3507a8a-db36-4e00-9f17-137e8c6d27e8", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 Java EE\n", "1 鸿蒙应用开发\n", "2 HTML&JS+前端\n", "3 Python+大数据开发\n", "4 人工智能开发\n", "5 电商视觉设计\n", "6 软件测试\n", "7 新媒体+短视频直播运营\n", "8 产品经理\n", "9 狂野架构师\n", "10 IP短视频带货训练营\n", "dtype: object" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd \n", "# 生成精品课程目录\n", "premium_courses = pd.Series(data=['Java EE', '鸿蒙应用开发', 'HTML&JS+前端', 'Python+大数据开发', \n", " '人工智能开发', '电商视觉设计', '软件测试', '新媒体+短视频直播运营', \n", " '产品经理', '狂野架构师', 'IP短视频带货训练营'])\n", "premium_courses" ] }, { "cell_type": "code", "execution_count": 2, "id": "46272a25-0403-4571-92d1-a8a236ee8e31", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
第1列第2列第3列
第1行课程大纲学费价格优惠活动
第2行免费课程就业薪资教研团队
\n", "
" ], "text/plain": [ " 第1列 第2列 第3列\n", "第1行 课程大纲 学费价格 优惠活动\n", "第2行 免费课程 就业薪资 教研团队" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Java EE课程目录\n", "import numpy as np\n", "arr_2d = np.array([['课程大纲', '学费价格', '优惠活动'], \n", " ['免费课程', '就业薪资', '教研团队']])\n", "java_courses = pd.DataFrame(data=arr_2d, \n", " columns=['第1列', '第2列', '第3列'],\n", " index=['第1行', '第2行'])\n", "java_courses" ] }, { "cell_type": "markdown", "id": "f15ae576-7145-4f49-bc12-c24ab6d7df29", "metadata": {}, "source": [ "## 索引对象" ] }, { "cell_type": "code", "execution_count": 15, "id": "1f887874-f5b1-48ea-8be8-4e2c01c70933", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['a', 'b', 'c', 'd', 'e'], dtype='object')" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "ser_obj = pd.Series(range(5), index=['a', 'b', 'c', 'd', 'e'])\n", "ser_index = ser_obj.index\n", "ser_index" ] }, { "cell_type": "code", "execution_count": 16, "id": "c1ebdaea-d3e2-4e1a-9830-348502e2da78", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'c'" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_index[2] # 获取索引对象中的第3个索引的值" ] }, { "cell_type": "code", "execution_count": 17, "id": "061b27d1-4305-4e74-9b90-53d73f256057", "metadata": {}, "outputs": [ { "ename": "TypeError", "evalue": "Index does not support mutable operations", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mTypeError\u001b[0m Traceback (most recent call last)", "Cell \u001b[1;32mIn[17], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m ser_index[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m2\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mcc\u001b[39m\u001b[38;5;124m'\u001b[39m\n", "File \u001b[1;32m~\\anaconda3\\Lib\\site-packages\\pandas\\core\\indexes\\base.py:5348\u001b[0m, in \u001b[0;36mIndex.__setitem__\u001b[1;34m(self, key, value)\u001b[0m\n\u001b[0;32m 5346\u001b[0m \u001b[38;5;129m@final\u001b[39m\n\u001b[0;32m 5347\u001b[0m \u001b[38;5;28;01mdef\u001b[39;00m \u001b[38;5;21m__setitem__\u001b[39m(\u001b[38;5;28mself\u001b[39m, key, value) \u001b[38;5;241m-\u001b[39m\u001b[38;5;241m>\u001b[39m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m-> 5348\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mIndex does not support mutable operations\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n", "\u001b[1;31mTypeError\u001b[0m: Index does not support mutable operations" ] } ], "source": [ "ser_index['2'] = 'cc'" ] }, { "cell_type": "code", "execution_count": 18, "id": "cf5a34fb-14f7-4627-8e2f-0f21bd529cb1", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['a', 'a', 'c', 'd', 'e'], dtype='object')" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "ser_obj = pd.Series(range(5), index=['a', 'a', 'c', 'd', 'e'])\n", "ser_index = ser_obj.index\n", "ser_index" ] }, { "cell_type": "code", "execution_count": 19, "id": "c75701d1-f23c-42a8-87b8-d937d0c077b1", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "False" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_index.is_unique # 判断索引的值是否是唯一的、不重复的" ] }, { "cell_type": "markdown", "id": "e269f57a-b190-4314-9c55-f8e4934fadf5", "metadata": {}, "source": [ "## 通过索引和切片获取数据" ] }, { "cell_type": "code", "execution_count": 22, "id": "3fa7a75f-e9cd-4c12-984e-edfaf869f976", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\itcast\\AppData\\Local\\Temp\\ipykernel_11836\\3471352156.py:3: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " ser_obj[2] # 通过位置索引获取单个数据\n" ] }, { "data": { "text/plain": [ "30" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "ser_obj = pd.Series([10, 20, 30, 40, 50], index=['one', 'two', 'three', 'four', 'five'])\n", "ser_obj[2] # 通过位置索引获取单个数据" ] }, { "cell_type": "code", "execution_count": 23, "id": "8fb9881a-795a-4b91-b2bc-5b3695a65523", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "30" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj['three'] # 通过标签索引获取单个数据" ] }, { "cell_type": "code", "execution_count": 24, "id": "9b77f25b-6ff1-4722-88ad-f8f320f9a21d", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\itcast\\AppData\\Local\\Temp\\ipykernel_11836\\807117414.py:1: FutureWarning: Series.__getitem__ treating keys as positions is deprecated. In a future version, integer keys will always be treated as labels (consistent with DataFrame behavior). To access a value by position, use `ser.iloc[pos]`\n", " ser_obj[[0, 2, 3]] # 通过位置索引获取多个数据\n" ] }, { "data": { "text/plain": [ "one 10\n", "three 30\n", "four 40\n", "dtype: int64" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj[[0, 2, 3]] # 通过位置索引获取多个数据" ] }, { "cell_type": "code", "execution_count": 25, "id": "48e1e086-b2d6-4b3f-9ada-05265afb934a", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "one 10\n", "three 30\n", "four 40\n", "dtype: int64" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj[['one', 'three', 'four']] # 通过标签索引获取多个数据" ] }, { "cell_type": "code", "execution_count": 27, "id": "9b04606b-1bbf-4d6b-8be8-cadec75b631c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "one False\n", "two False\n", "three True\n", "four True\n", "five True\n", "dtype: bool" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj > 20 # 生成Series类的对象，该对象里面的元素都是布尔类型的值" ] }, { "cell_type": "code", "execution_count": 28, "id": "37488a9a-053a-487a-a42f-33da25808557", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "three 30\n", "four 40\n", "five 50\n", "dtype: int64" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj[ser_obj > 20] # 获取跟True位置对应的数据" ] }, { "cell_type": "code", "execution_count": 29, "id": "1fa505cb-d005-4acd-be98-a6194e2c8148", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "two 20\n", "three 30\n", "dtype: int64" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj[1:3] # 通过位置索引进行切片操作" ] }, { "cell_type": "code", "execution_count": 30, "id": "840a0bf8-a0e8-4835-a794-ebc7397a5d27", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "two 20\n", "three 30\n", "four 40\n", "dtype: int64" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj['two':'four'] # 通过标签索引进行切片操作" ] }, { "cell_type": "code", "execution_count": 31, "id": "aacf260d-4513-4a00-b56a-bff948975984", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_24567
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_2 4 5 6 7\n", "row_3 8 9 10 11" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "import pandas as pd\n", "arr = np.arange(12).reshape(3, 4)\n", "df_obj = pd.DataFrame(arr, index=['row_1', 'row_2', 'row_3'], \n", " columns=['col_1', 'col_2', 'col_3', 'col_4'])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 32, "id": "96953963-7c62-4472-aab3-c2c60219a4ca", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "row_1 1\n", "row_2 5\n", "row_3 9\n", "Name: col_2, dtype: int32" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj['col_2'] # 获取col_2列的数据" ] }, { "cell_type": "code", "execution_count": 33, "id": "1099ab0f-e4f0-4a99-803d-a762782aaa7f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_3
row_102
row_246
row_3810
\n", "
" ], "text/plain": [ " col_1 col_3\n", "row_1 0 2\n", "row_2 4 6\n", "row_3 8 10" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj[['col_1', 'col_3']] # 获取col_1列和col_3列的数据" ] }, { "cell_type": "code", "execution_count": 34, "id": "d88755c2-f1c8-4d80-b920-e987fdd35033", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_24567
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_2 4 5 6 7\n", "row_3 8 9 10 11" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj[1:3] # 通过切片获取row_2行和row_3行的数据" ] }, { "cell_type": "code", "execution_count": 35, "id": "cd48fecd-5235-4d19-975d-27f757b61d7e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_3
row_246
row_3810
\n", "
" ], "text/plain": [ " col_1 col_3\n", "row_2 4 6\n", "row_3 8 10" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj[1:3][['col_1', 'col_3']]" ] }, { "cell_type": "markdown", "id": "81cdb3b7-3a57-4188-94a0-c6ec6b675557", "metadata": {}, "source": [ "## 通过loc和iloc属性获取数据" ] }, { "cell_type": "code", "execution_count": 36, "id": "102fd2e4-b21a-4e26-afbd-cbdda4e67dac", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "20" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "ser_obj = pd.Series([10, 20, 30, 40, 50], \n", " index=['row1', 'row2', 'row3', 'row4', 'row5'])\n", "ser_obj.loc['row2'] # 根据单个标签索引获取单个数据" ] }, { "cell_type": "code", "execution_count": 37, "id": "bfb8afc6-47b3-4a8b-966b-1d65e5de6bad", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "row2 20\n", "row5 50\n", "dtype: int64" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj.loc[['row2', 'row5']] # 根据标签索引构成的列表获取多个数据" ] }, { "cell_type": "code", "execution_count": 38, "id": "fbea6c18-2629-492c-9e57-1e6e2c049823", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "row3 30\n", "row4 40\n", "row5 50\n", "dtype: int64" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj.loc['row3': 'row5'] # 根据基于标签索引的切片获取多个连续数据" ] }, { "cell_type": "code", "execution_count": 39, "id": "9c234599-3fc8-437b-a425-c64d0bcaa35e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "row1 10\n", "row2 20\n", "dtype: int64" ] }, "execution_count": 39, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 根据布尔类型的Series类对象获取符合条件的数据\n", "ser_bool = ser_obj < 30 \n", "ser_obj.loc[ser_bool]" ] }, { "cell_type": "code", "execution_count": 40, "id": "5186fa67-2091-4075-ba01-4a014c5b0e02", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_24567
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_2 4 5 6 7\n", "row_3 8 9 10 11" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "import pandas as pd\n", "arr = np.arange(12).reshape(3, 4)\n", "df_obj = pd.DataFrame(arr, index=['row_1', 'row_2', 'row_3'], \n", " columns=['col_1', 'col_2', 'col_3', 'col_4'])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 41, "id": "1bb8bebf-9746-47e4-8454-e1ae953faf9f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "col_1 0\n", "col_2 1\n", "col_3 2\n", "col_4 3\n", "Name: row_1, dtype: int32" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.loc['row_1'] # 根据单个标签索引获取一行数据" ] }, { "cell_type": "code", "execution_count": 42, "id": "282440c5-bf04-482d-9c68-51688a0e77ce", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_3 8 9 10 11" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.loc[['row_1', 'row_3']] # 根据标签索引构成的列表获取多行数据" ] }, { "cell_type": "code", "execution_count": 43, "id": "2b2167b5-4e62-4912-89b4-9461b41e4f4d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_24567
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_2 4 5 6 7" ] }, "execution_count": 43, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.loc['row_1':'row_2'] # 根据基于标签索引的切片获取连续多行的数据" ] }, { "cell_type": "code", "execution_count": 44, "id": "829ebee3-160f-4fed-8ba3-43fe82dc4126", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_3 8 9 10 11" ] }, "execution_count": 44, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.loc[[True, False, True]] # 根据布尔类型的列表获取符合条件的多行数据" ] }, { "cell_type": "code", "execution_count": 45, "id": "089c224b-a0fd-41c6-976b-46c3703893f6", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "10" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.loc['row_3', 'col_3'] # 根据两个标签索引获取单个数据" ] }, { "cell_type": "code", "execution_count": 46, "id": "bd4bd1db-4a04-4a1a-8779-771e84006448", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_3
row_102
row_246
row_3810
\n", "
" ], "text/plain": [ " col_1 col_3\n", "row_1 0 2\n", "row_2 4 6\n", "row_3 8 10" ] }, "execution_count": 46, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 根据切片和列表获取多列数据\n", "df_obj.loc['row_1':'row_3', ['col_1', 'col_3']] " ] }, { "cell_type": "code", "execution_count": 47, "id": "f7d18221-c0cd-4c19-891c-c59280c68b05", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "col_1 0\n", "col_2 1\n", "col_3 2\n", "col_4 3\n", "Name: row_1, dtype: int32" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.iloc[0] # 根据单个位置索引获取一行数据" ] }, { "cell_type": "code", "execution_count": 48, "id": "bf2a5e63-bddf-4eb2-9fe9-7b81891cfe61", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_3 8 9 10 11" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.iloc[[0, 2]] # 根据位置索引构成的列表获取多行数据" ] }, { "cell_type": "code", "execution_count": 49, "id": "ea73c62b-979c-4028-a18e-488033736eaa", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_24567
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_2 4 5 6 7" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.iloc[0:2] # 根据基于位置索引的切片获取连续多行的数据" ] }, { "cell_type": "code", "execution_count": 50, "id": "6ae4b50e-098e-4a1a-8497-732006e92d3d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_2col_3col_4
row_10123
row_3891011
\n", "
" ], "text/plain": [ " col_1 col_2 col_3 col_4\n", "row_1 0 1 2 3\n", "row_3 8 9 10 11" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 根据布尔类型的列表获取符合条件的多行数据\n", "df_obj.iloc[[True, False, True]] " ] }, { "cell_type": "code", "execution_count": 51, "id": "5f3a9d3c-507f-4ddc-85b1-a5bcab5a9e18", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "10" ] }, "execution_count": 51, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.iloc[2, 2] # 根据两个位置索引获取单个数据" ] }, { "cell_type": "code", "execution_count": 52, "id": "b5a487cd-e6b3-4402-b90b-f6f2f284fd17", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
col_1col_3
row_102
row_246
row_3810
\n", "
" ], "text/plain": [ " col_1 col_3\n", "row_1 0 2\n", "row_2 4 6\n", "row_3 8 10" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.iloc[0:3, [0, 2]] # 根据切片和列表获取多列数据" ] }, { "cell_type": "markdown", "id": "3e0412b8-f857-4885-9938-a869cbdf97e8", "metadata": {}, "source": [ "## 【任务3-2】扫雷游戏" ] }, { "cell_type": "code", "execution_count": 53, "id": "5fb6cdd5-97b2-4656-a6a4-86ef1be49d83", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012345678
012????12??2
1X22212????
21111211
3113??2
41??????2
5122211
61??1
711211
81??1
\n", "
" ], "text/plain": [ " 0 1 2 3 4 5 6 7 8\n", "0 1 2 ?? ?? 1 2 ?? 2\n", "1 X 2 2 2 1 2 ?? ??\n", "2 1 1 1 1 2 1 1\n", "3 1 1 3 ?? 2 \n", "4 1 ?? ?? ?? 2 \n", "5 1 2 2 2 1 1 \n", "6 1 ?? 1 \n", "7 1 1 2 1 1 \n", "8 1 ?? 1 " ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "import pandas as pd \n", "arr_2d = np.array([[1, 2, '??', '??', 1, '', 2, '??', 2], \n", " ['X', 2, 2, 2, 1, '', 2, '??', '??'],\n", " [1, 1, '', '', 1, 1, 2, 1, 1],\n", " ['', '', 1, 1, 3, '??', 2, '', ''],\n", " ['', '', 1, '??', '??', '??', 2, '', ''],\n", " ['', 1, 2, 2, 2, 1, 1, '', ''],\n", " ['', 1, '??', 1, '', '', '', '', ''],\n", " ['', 1, 1, 2, 1, 1, '', '', ''],\n", " ['', '', '', 1, '??', 1, '', '', '']])\n", "df_obj = pd.DataFrame(data=arr_2d)\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 54, "id": "e32ac238-cc4e-4fc9-81be-c5a92e55e900", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012345678
012XX12X2
1X22212X??
21111211
3113X2
41X??X2
5122211
61X1
711211
81X1
\n", "
" ], "text/plain": [ " 0 1 2 3 4 5 6 7 8\n", "0 1 2 X X 1 2 X 2\n", "1 X 2 2 2 1 2 X ??\n", "2 1 1 1 1 2 1 1\n", "3 1 1 3 X 2 \n", "4 1 X ?? X 2 \n", "5 1 2 2 2 1 1 \n", "6 1 X 1 \n", "7 1 1 2 1 1 \n", "8 1 X 1 " ] }, "execution_count": 54, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 获取第0行第2、3列的元素，重新设置为X\n", "df_obj.iloc[0, 2:4] = ['X', 'X']\n", "# 获取第0、1行第7列的元素，重新设置为X\n", "df_obj.iloc[0:2, 7] = ['X', 'X']\n", "# 获取第3、4行第5列的元素，重新设置为X\n", "df_obj.iloc[3:5, 5] = ['X', 'X']\n", "# 获取第4行第3列的元素，重新设置为X\n", "df_obj.iloc[4, 3] = 'X'\n", "# 获取第6行第2列的元素，重新设置为X\n", "df_obj.iloc[6, 2] = 'X'\n", "# 获取第8行第4列的元素，重新设置为X\n", "df_obj.iloc[8, 4] = 'X'\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 55, "id": "ad3e1026-c5cc-4581-b916-f373f59cf062", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012345678
012XX12X2
1X22212X2
21111211
3113X2
41X3X2
5122211
61X1
711211
81X1
\n", "
" ], "text/plain": [ " 0 1 2 3 4 5 6 7 8\n", "0 1 2 X X 1 2 X 2\n", "1 X 2 2 2 1 2 X 2\n", "2 1 1 1 1 2 1 1\n", "3 1 1 3 X 2 \n", "4 1 X 3 X 2 \n", "5 1 2 2 2 1 1 \n", "6 1 X 1 \n", "7 1 1 2 1 1 \n", "8 1 X 1 " ] }, "execution_count": 55, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 获取第1行第8列的元素，重新设置为2\n", "df_obj.iloc[1, 8] = '2'\n", "# 获取第1行第8列的元素，重新设置为3\n", "df_obj.iloc[4, 4] = '3'\n", "df_obj" ] }, { "cell_type": "markdown", "id": "23d259b5-431c-4b50-a2de-f60b22e62bf5", "metadata": {}, "source": [ "## 读写文本文件的数据" ] }, { "cell_type": "code", "execution_count": 56, "id": "1e3a8aca-67be-47da-81df-5d69659cdb48", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
0CNN001小明男行政员工
1CNN002小红女人力资源主管
2CNN003小蓝女销售员工
3CNN004小黑男研发主管
4CNN005小白男财务员工
5CNN006小方女技术员工
6CNN007小梅女员工
7CNN008小刚男市场员工
8CNN009小丽女研发主管
9CNN010小花女技术员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "0 CNN001 小明 男 行政 员工\n", "1 CNN002 小红 女 人力资源 主管\n", "2 CNN003 小蓝 女 销售 员工\n", "3 CNN004 小黑 男 研发 主管\n", "4 CNN005 小白 男 财务 员工\n", "5 CNN006 小方 女 技术 员工\n", "6 CNN007 小梅 女 员工\n", "7 CNN008 小刚 男 市场 员工\n", "8 CNN009 小丽 女 研发 主管\n", "9 CNN010 小花 女 技术 员工" ] }, "execution_count": 56, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df_obj = pd.DataFrame({'编号':['CNN001', 'CNN002', 'CNN003', 'CNN004', \n", " 'CNN005', 'CNN006', 'CNN007', 'CNN008', 'CNN009', 'CNN010'],\n", " '姓名':['小明', '小红', '小蓝', '小黑', '小白', '小方', '小梅', '小刚', '小丽', '小花'],\n", " '性别':['男', '女', '女', '男', '男', '女', '女', '男', '女', '女'],\n", " '部门':['行政', '人力资源', '销售', '研发', '财务', '技术', '', '市场', '研发', '技术'],\n", " '职务':['员工', '主管', '员工', '主管', '员工', '员工', '员工', '员工', '主管', '员工']})\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 57, "id": "462b4f88-a4b9-4b25-aecf-7094781ee7c2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "写入完毕\n" ] } ], "source": [ "# 向当前data目录下的指定文件写入数据，不显示行索引\n", "df_obj.to_csv(r'data\\employee_info.csv', index=False, encoding='gbk')\n", "print('写入完毕')" ] }, { "cell_type": "code", "execution_count": 58, "id": "96b3278e-4788-4002-8278-ebf0ca3b731a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
0CNN001小明男行政员工
1CNN002小红女人力资源主管
2CNN003小蓝女销售员工
3CNN004小黑男研发主管
4CNN005小白男财务员工
5CNN006小方女技术员工
6CNN007小梅女NaN员工
7CNN008小刚男市场员工
8CNN009小丽女研发主管
9CNN010小花女技术员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "0 CNN001 小明 男 行政 员工\n", "1 CNN002 小红 女 人力资源 主管\n", "2 CNN003 小蓝 女 销售 员工\n", "3 CNN004 小黑 男 研发 主管\n", "4 CNN005 小白 男 财务 员工\n", "5 CNN006 小方 女 技术 员工\n", "6 CNN007 小梅 女 NaN 员工\n", "7 CNN008 小刚 男 市场 员工\n", "8 CNN009 小丽 女 研发 主管\n", "9 CNN010 小花 女 技术 员工" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "# 从当前工作路径下的指定文件中读取数据，使用逗号作为分隔符\n", "df_obj = pd.read_csv(r'data\\employee_info.csv', encoding='gbk')\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 59, "id": "62598f9e-1323-4b4a-931f-7c09ab4a91b2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
0CNN001小明男行政员工
1CNN002小红女人力资源主管
2CNN003小蓝女销售员工
3CNN004小黑男研发主管
4CNN005小白男财务员工
5CNN006小方女技术员工
6CNN007小梅女NaN员工
7CNN008小刚男市场员工
8CNN009小丽女研发主管
9CNN010小花女技术员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "0 CNN001 小明 男 行政 员工\n", "1 CNN002 小红 女 人力资源 主管\n", "2 CNN003 小蓝 女 销售 员工\n", "3 CNN004 小黑 男 研发 主管\n", "4 CNN005 小白 男 财务 员工\n", "5 CNN006 小方 女 技术 员工\n", "6 CNN007 小梅 女 NaN 员工\n", "7 CNN008 小刚 男 市场 员工\n", "8 CNN009 小丽 女 研发 主管\n", "9 CNN010 小花 女 技术 员工" ] }, "execution_count": 59, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 从指定文件中读取数据，使用制表符作为分隔符，并指定编码格式为gbk\n", "df_obj = pd.read_table(r'data\\employee_info.txt', encoding='gbk')\n", "df_obj" ] }, { "cell_type": "markdown", "id": "e8a64e8b-4f9d-4cff-9232-e5a293945710", "metadata": {}, "source": [ "## 预览部分数据" ] }, { "cell_type": "code", "execution_count": 60, "id": "8699b349-6c7c-4a0b-b464-0188fa9814d2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
0CNN001小明男行政员工
1CNN002小红女人力资源主管
2CNN003小蓝女销售员工
3CNN004小黑男研发主管
4CNN005小白男财务员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "0 CNN001 小明 男 行政 员工\n", "1 CNN002 小红 女 人力资源 主管\n", "2 CNN003 小蓝 女 销售 员工\n", "3 CNN004 小黑 男 研发 主管\n", "4 CNN005 小白 男 财务 员工" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.head() # 预览前5行数据" ] }, { "cell_type": "code", "execution_count": 61, "id": "5eeddb00-a86f-4cd4-93b3-21953e4f4f4b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
0CNN001小明男行政员工
1CNN002小红女人力资源主管
2CNN003小蓝女销售员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "0 CNN001 小明 男 行政 员工\n", "1 CNN002 小红 女 人力资源 主管\n", "2 CNN003 小蓝 女 销售 员工" ] }, "execution_count": 61, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.head(3) # 预览前3行数据" ] }, { "cell_type": "code", "execution_count": 62, "id": "0d3e8ac6-0726-4d99-a6c0-9bbf86e79d37", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
5CNN006小方女技术员工
6CNN007小梅女NaN员工
7CNN008小刚男市场员工
8CNN009小丽女研发主管
9CNN010小花女技术员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "5 CNN006 小方 女 技术 员工\n", "6 CNN007 小梅 女 NaN 员工\n", "7 CNN008 小刚 男 市场 员工\n", "8 CNN009 小丽 女 研发 主管\n", "9 CNN010 小花 女 技术 员工" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.tail() # 预览后5行数据" ] }, { "cell_type": "code", "execution_count": 63, "id": "f7b10df4-54e3-445c-91b7-370834180176", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
编号姓名性别部门职务
7CNN008小刚男市场员工
8CNN009小丽女研发主管
9CNN010小花女技术员工
\n", "
" ], "text/plain": [ " 编号 姓名 性别 部门 职务\n", "7 CNN008 小刚 男 市场 员工\n", "8 CNN009 小丽 女 研发 主管\n", "9 CNN010 小花 女 技术 员工" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.tail(3) # 预览后3行数据" ] }, { "cell_type": "markdown", "id": "3f54683d-46d5-4b69-80b6-1d2d2bea4f24", "metadata": {}, "source": [ "## 读写Excel文件的数据" ] }, { "cell_type": "code", "execution_count": 1, "id": "15c17925-721c-438b-8379-fdf98d841999", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
手机名称机身内存运行内存颜色价格
0华为 Mate50 Pro256GB8GB曜金黑6799
1华为 畅享50 Pro256GB8GB幻夜黑1799
2华为 P50128GB8GB可可茶金3758
3华为智选 优畅享50128GB8GB月光银999
4华为 P50 Pocket256GB8GB云锦白8188
\n", "
" ], "text/plain": [ " 手机名称 机身内存 运行内存 颜色 价格\n", "0 华为 Mate50 Pro 256GB 8GB 曜金黑 6799\n", "1 华为 畅享50 Pro 256GB 8GB 幻夜黑 1799\n", "2 华为 P50 128GB 8GB 可可茶金 3758\n", "3 华为智选 优畅享50 128GB 8GB 月光银 999\n", "4 华为 P50 Pocket 256GB 8GB 云锦白 8188" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df_obj = pd.DataFrame({'手机名称':['华为 Mate50 Pro', '华为 畅享50 Pro', '华为 P50', '华为智选 优畅享50', '华为 P50 Pocket'],\n", " '机身内存':['256GB', '256GB', '128GB', '128GB', '256GB'],\n", " '运行内存':['8GB', '8GB', '8GB', '8GB', '8GB'],\n", " '颜色':['曜金黑', '幻夜黑', '可可茶金', '月光银', '云锦白'], \n", " '价格（元）':[6799, 1799, 3758, 999, 8188]})\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 65, "id": "5861cc15-89b4-4256-b6c3-7c0277f28e35", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "写入完毕\n" ] } ], "source": [ "# 向当前工作路径data目录下的phones.xlsx文件中写入数据\n", "df_obj.to_excel(r'data\\phones.xlsx')\n", "print('写入完毕')" ] }, { "cell_type": "code", "execution_count": 66, "id": "34eb7a9e-125c-4a3b-a464-c25290af7b0e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Unnamed: 0手机名称机身内存运行内存颜色价格
00华为mate50 Pro256GB8GB曜金黑6799
11华为畅享 50 Pro256GB8GB幻夜黑1799
22华为 P50128GB8GB可可茶金3758
33华为智选 优畅享50128GB8GB月光银999
44华为P50 Pocket256GB8GB云锦白8188
\n", "
" ], "text/plain": [ " Unnamed: 0 手机名称 机身内存 运行内存 颜色 价格\n", "0 0 华为mate50 Pro 256GB 8GB 曜金黑 6799\n", "1 1 华为畅享 50 Pro 256GB 8GB 幻夜黑 1799\n", "2 2 华为 P50 128GB 8GB 可可茶金 3758\n", "3 3 华为智选 优畅享50 128GB 8GB 月光银 999\n", "4 4 华为P50 Pocket 256GB 8GB 云锦白 8188" ] }, "execution_count": 66, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 从文件中读取全部的数据 \n", "df_obj = pd.read_excel(r'data\\phones.xlsx')\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 67, "id": "d4e4adfe-beb5-4f9c-965e-a297a5a58de5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
手机名称机身内存运行内存颜色价格
0华为mate50 Pro256GB8GB曜金黑6799
1华为畅享 50 Pro256GB8GB幻夜黑1799
2华为 P50128GB8GB可可茶金3758
3华为智选 优畅享50128GB8GB月光银999
4华为P50 Pocket256GB8GB云锦白8188
\n", "
" ], "text/plain": [ " 手机名称 机身内存 运行内存 颜色 价格\n", "0 华为mate50 Pro 256GB 8GB 曜金黑 6799\n", "1 华为畅享 50 Pro 256GB 8GB 幻夜黑 1799\n", "2 华为 P50 128GB 8GB 可可茶金 3758\n", "3 华为智选 优畅享50 128GB 8GB 月光银 999\n", "4 华为P50 Pocket 256GB 8GB 云锦白 8188" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 从文件中读取部分列的数据 \n", "df_obj = pd.read_excel(r'data\\phones.xlsx', usecols=[1, 2, 3, 4, 5])\n", "df_obj" ] }, { "cell_type": "markdown", "id": "a92be907-47a9-4be9-89bd-1bd1eaa9f0b8", "metadata": {}, "source": [ "## 重置索引" ] }, { "cell_type": "code", "execution_count": 69, "id": "7e3ea673-be0b-44c9-aadd-aa15361ec6af", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
no1no2
a1.04.0
b2.05.0
c3.06.0
\n", "
" ], "text/plain": [ " no1 no2\n", "a 1.0 4.0\n", "b 2.0 5.0\n", "c 3.0 6.0" ] }, "execution_count": 69, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df_obj = pd.DataFrame({'no1': [1.0, 2.0, 3.0], 'no2': [4.0, 5.0, 6.0]}, index=['a', 'b', 'c'])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 70, "id": "b899c6f0-7fa7-41a9-9206-82126c1a7d1b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
no1no2
a1.04.0
c3.06.0
eNaNNaN
\n", "
" ], "text/plain": [ " no1 no2\n", "a 1.0 4.0\n", "c 3.0 6.0\n", "e NaN NaN" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 重置索引\n", "new_df = df_obj.reindex(index=['a', 'c', 'e'])\n", "new_df" ] }, { "cell_type": "code", "execution_count": 71, "id": "7483d5e4-c254-4cc2-9cdb-bd5d08171d80", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
no1no2
a1.04.0
c3.06.0
e9.09.0
\n", "
" ], "text/plain": [ " no1 no2\n", "a 1.0 4.0\n", "c 3.0 6.0\n", "e 9.0 9.0" ] }, "execution_count": 71, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 重置索引并指定填充的值\n", "new_df = df_obj.reindex(index=['a', 'c', 'e'], fill_value=9)\n", "new_df" ] }, { "cell_type": "code", "execution_count": 72, "id": "5bc5e857-1296-4846-8fe4-2f83547e64a3", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
no1no2
a1.04.0
c3.06.0
e3.06.0
\n", "
" ], "text/plain": [ " no1 no2\n", "a 1.0 4.0\n", "c 3.0 6.0\n", "e 3.0 6.0" ] }, "execution_count": 72, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 重置索引，指定填充方式为前向填充\n", "new_df = df_obj.reindex(index=['a', 'c', 'e'], method ='ffill')\n", "new_df" ] }, { "cell_type": "markdown", "id": "3a7362f4-7988-486e-bd01-dfd7ed85a42a", "metadata": {}, "source": [ "## 数据排序" ] }, { "cell_type": "code", "execution_count": 73, "id": "3005b38e-e422-4b47-8424-ef26cd3dc84c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012
4012
3345
5678
\n", "
" ], "text/plain": [ " 0 1 2\n", "4 0 1 2\n", "3 3 4 5\n", "5 6 7 8" ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "import numpy as np\n", "df_obj = pd.DataFrame(np.arange(9).reshape(3, 3), index=[4, 3, 5])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 74, "id": "f82f9253-ab69-4282-b4f0-4b7bcbff423f", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012
3345
4012
5678
\n", "
" ], "text/plain": [ " 0 1 2\n", "3 3 4 5\n", "4 0 1 2\n", "5 6 7 8" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.sort_index() # 按行索引升序排列" ] }, { "cell_type": "code", "execution_count": 75, "id": "02411cf9-d316-4778-ba24-24fe5655186a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012
5678
4012
3345
\n", "
" ], "text/plain": [ " 0 1 2\n", "5 6 7 8\n", "4 0 1 2\n", "3 3 4 5" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.sort_index(ascending=False) # 按行索引降序排列" ] }, { "cell_type": "code", "execution_count": 76, "id": "30dfbe87-e67f-4d8f-ac31-5754f0de688c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 4.0\n", "1 NaN\n", "2 6.0\n", "3 NaN\n", "4 -3.0\n", "5 2.0\n", "dtype: float64" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj = pd.Series([4, np.nan, 6, np.nan, -3, 2])\n", "ser_obj" ] }, { "cell_type": "code", "execution_count": 77, "id": "f9257c0c-fd15-4812-b9da-e9817fba766b", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4 -3.0\n", "5 2.0\n", "0 4.0\n", "2 6.0\n", "1 NaN\n", "3 NaN\n", "dtype: float64" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj.sort_values() # 按值升序排列" ] }, { "cell_type": "code", "execution_count": 78, "id": "94845202-0c7e-4753-8c91-009e3861cb8d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
0123
00.4-0.1-0.30.0
10.20.6-0.1-0.7
20.80.6-0.50.1
\n", "
" ], "text/plain": [ " 0 1 2 3\n", "0 0.4 -0.1 -0.3 0.0\n", "1 0.2 0.6 -0.1 -0.7\n", "2 0.8 0.6 -0.5 0.1" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj = pd.DataFrame([[0.4, -0.1, -0.3, 0.0], \n", " [0.2, 0.6, -0.1, -0.7],\n", " [0.8, 0.6, -0.5, 0.1]])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 79, "id": "6f93575b-f07f-46db-8df7-1ba7ac16b28b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
0123
20.80.6-0.50.1
00.4-0.1-0.30.0
10.20.6-0.1-0.7
\n", "
" ], "text/plain": [ " 0 1 2 3\n", "2 0.8 0.6 -0.5 0.1\n", "0 0.4 -0.1 -0.3 0.0\n", "1 0.2 0.6 -0.1 -0.7" ] }, "execution_count": 79, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.sort_values(by=2) # 对列索引为2的数据进行排序" ] }, { "cell_type": "markdown", "id": "cd8c07ba-09c8-432d-b4af-28f517c693d8", "metadata": {}, "source": [ "## 【任务3-3】读写会员交易数据" ] }, { "cell_type": "code", "execution_count": 80, "id": "989a7ad6-3bc9-4991-b9bb-31c1def27e68", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
卡号订单号订单类型店铺代码款号尺码消费数量消费金额当前积分
0HS340766JAF6ODLOX6BXX8X2BXBBBBX下单DPX60XBLA267Q3X13AQM23011200.0800
1BL6093039999465603590ODODOXF77X8X2BXBBBB2下单DPX377BLA2651QX14AQC24053332.02531
2BL6093909593939600407ROX8XXFBBBB6BB退单DPS00XTMA27727X5QAQM24011112.5328
3BL6093036096030709394ROX8XXFBBBB6X7退单DPS00XTMA67621X5QBQTM23012260.01038
4BL6093993066943700650ODLOX6BFX8XXFBBBBBX下单DPX603BLA26663X52AQTM23511200.0800
\n", "
" ], "text/plain": [ " 卡号 订单号 订单类型 店铺代码 款号 \\\n", "0 HS340766JAF6 ODLOX6BXX8X2BXBBBBX 下单 DPX60X BLA267Q3X13AQM \n", "1 BL6093039999465603590 ODODOXF77X8X2BXBBBB2 下单 DPX377 BLA2651QX14AQC \n", "2 BL6093909593939600407 ROX8XXFBBBB6BB 退单 DPS00X TMA27727X5QAQM \n", "3 BL6093036096030709394 ROX8XXFBBBB6X7 退单 DPS00X TMA67621X5QBQTM \n", "4 BL6093993066943700650 ODLOX6BFX8XXFBBBBBX 下单 DPX603 BLA26663X52AQTM \n", "\n", " 尺码 消费数量 消费金额 当前积分 \n", "0 230 1 1200.0 800 \n", "1 240 5 3332.0 2531 \n", "2 240 1 1112.5 328 \n", "3 230 1 2260.0 1038 \n", "4 235 1 1200.0 800 " ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df_csv = pd.read_csv(r'data\\member_consumption.csv', encoding='gbk')\n", "df_csv.head()" ] }, { "cell_type": "code", "execution_count": 81, "id": "f34202a7-98c5-4af6-8f59-73a37b758861", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
卡号订单日期订单号订单类型店铺代码款号尺码消费数量消费金额当前积分
28HS693376JAF62024-06-01ODLOX57MX8XXFBBBBB9下单DPX574BLA2612QX5QBQS24011513.52766
27HS693376JAF62024-06-01ODLOX57MX8XXFBBBBB9下单DPX574BLA2612QX5QBQS22511648.52766
20BL60930990975696009342024-06-01ODLOXM62X8XXFBBBBB8下单DPX462BLA26665X6QAQTM22512037.02732
1BL60930399994656035902024-06-01ODODOXF77X8X2BXBBBB2下单DPX377BLA2651QX14AQC24053332.02531
15BL60939906960553003542024-06-01ODODOXM62X8XXFBBBBBM下单DPX462BLA267QQX1QAQTM23563797.02497
\n", "
" ], "text/plain": [ " 卡号 订单日期 订单号 订单类型 店铺代码 \\\n", "28 HS693376JAF6 2024-06-01 ODLOX57MX8XXFBBBBB9 下单 DPX574 \n", "27 HS693376JAF6 2024-06-01 ODLOX57MX8XXFBBBBB9 下单 DPX574 \n", "20 BL6093099097569600934 2024-06-01 ODLOXM62X8XXFBBBBB8 下单 DPX462 \n", "1 BL6093039999465603590 2024-06-01 ODODOXF77X8X2BXBBBB2 下单 DPX377 \n", "15 BL6093990696055300354 2024-06-01 ODODOXM62X8XXFBBBBBM 下单 DPX462 \n", "\n", " 款号 尺码 消费数量 消费金额 当前积分 \n", "28 BLA2612QX5QBQS 240 1 1513.5 2766 \n", "27 BLA2612QX5QBQS 225 1 1648.5 2766 \n", "20 BLA26665X6QAQTM 225 1 2037.0 2732 \n", "1 BLA2651QX14AQC 240 5 3332.0 2531 \n", "15 BLA267QQX1QAQTM 235 6 3797.0 2497 " ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 增加订单日期一列\n", "new_column = ['卡号', '订单日期', '订单号', '订单类型', '店铺代码', \n", " '款号', '尺码', '消费数量', '消费金额', '当前积分']\n", "df_reindex = df_csv.reindex(columns=new_column, \n", " fill_value='2024-06-01')\n", "# 按照当前积分排序\n", "df_sort = df_reindex.sort_values(by='当前积分', ascending=False)\n", "df_sort.head()" ] }, { "cell_type": "code", "execution_count": 82, "id": "95a250e8-e1c5-41bf-b46a-70a10fc360ce", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "写入完毕\n" ] } ], "source": [ "df_sort.to_excel(r'data\\member_consumption.xlsx', index=False)\n", "print('写入完毕')" ] }, { "cell_type": "markdown", "id": "f3e20c83-4d76-4486-af8e-cf42939d1ae0", "metadata": {}, "source": [ "## 读取网页表格的数据" ] }, { "cell_type": "code", "execution_count": 83, "id": "df46e5e8-8e7e-4f9f-b3f9-6f5c48201d98", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
YearWinner
02023C#
12022C++
22021Python
32020Python
42019C
52018Python
62017C
72016Go
82015Java
92014JavaScript
102013Transact-SQL
112012Objective-C
122011Objective-C
132010Python
142009Go
152008C
162007Python
172006Ruby
182005Java
192004PHP
202003C++
\n", "
" ], "text/plain": [ " Year Winner\n", "0 2023 C#\n", "1 2022 C++\n", "2 2021 Python\n", "3 2020 Python\n", "4 2019 C\n", "5 2018 Python\n", "6 2017 C\n", "7 2016 Go\n", "8 2015 Java\n", "9 2014 JavaScript\n", "10 2013 Transact-SQL\n", "11 2012 Objective-C\n", "12 2011 Objective-C\n", "13 2010 Python\n", "14 2009 Go\n", "15 2008 C\n", "16 2007 Python\n", "17 2006 Ruby\n", "18 2005 Java\n", "19 2004 PHP\n", "20 2003 C++" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "# 根据URL读取网页表格的数据\n", "tables = pd.read_html(io='https://www.tiobe.com/tiobe-index/')\n", "# 获取索引3对应的DataFrame类的对象\n", "tables[3]" ] }, { "cell_type": "markdown", "id": "3df19b27-562e-49a2-9c4e-9f1d89391b50", "metadata": {}, "source": [ "## 读写数据库" ] }, { "cell_type": "code", "execution_count": 84, "id": "18bddbc8-621b-4cc8-8eed-184c8481bcaf", "metadata": {}, "outputs": [], "source": [ "from pandas import DataFrame, Series\n", "from sqlalchemy import create_engine\n", "from sqlalchemy.types import *\n", "df = DataFrame({\"年级\":[\"一年级\", \"二年级\", \"三年级\", \"四年级\"],\n", " \"男生人数\":[25, 23, 27, 30],\n", " \"女生人数\":[19, 17, 20, 20]})" ] }, { "cell_type": "code", "execution_count": 85, "id": "9d90aa1e-f973-4efc-8b8e-8af43637250e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "4" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 创建数据库引擎\n", "# mysql+pymysql表示使用PyMySQL作为驱动连接MySQL数据库\n", "# root:123456表示MySQL的用户名是root，密码是123456\n", "# studnets_info表示数据库的名称\n", "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/students_info')\n", "df.to_sql('students', engine)" ] }, { "cell_type": "code", "execution_count": null, "id": "1d5518c0-6311-48e7-8c1d-52579ce4cfbe", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from sqlalchemy import create_engine\n", "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/info')\n", "pd.read_sql('person_info', engine)" ] }, { "cell_type": "code", "execution_count": null, "id": "48afb211-3109-4744-b599-598513d5d658", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from sqlalchemy import create_engine\n", "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/info')\n", "sql = 'select * from person_info where id >3;'\n", "pd.read_sql(sql, engine)" ] }, { "cell_type": "markdown", "id": "ed184c8a-61c5-460f-9315-8c6ce0e392fd", "metadata": {}, "source": [ "## 重命名索引" ] }, { "cell_type": "code", "execution_count": 86, "id": "5a7ffe64-c2f9-4da7-8f86-4090d61cabe0", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AB
New_X14
New_Y25
New_Z36
\n", "
" ], "text/plain": [ " A B\n", "New_X 1 4\n", "New_Y 2 5\n", "New_Z 3 6" ] }, "execution_count": 86, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]}, index=['X', 'Y', 'Z'])\n", "# 重命名行索引\n", "new_index_names = {'X': 'New_X', 'Y': 'New_Y', 'Z': 'New_Z'}\n", "df_new = df.rename(index=new_index_names)\n", "df_new" ] }, { "cell_type": "code", "execution_count": 87, "id": "6f1c641e-95a6-4be9-93b3-b3fc11f72e57", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
New_ANew_B
New_X14
New_Y25
New_Z36
\n", "
" ], "text/plain": [ " New_A New_B\n", "New_X 1 4\n", "New_Y 2 5\n", "New_Z 3 6" ] }, "execution_count": 87, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 重命名列索引\n", "new_columns_names = {'A': 'New_A', 'B': 'New_B'}\n", "df_new = df_new.rename(columns=new_columns_names)\n", "df_new" ] }, { "cell_type": "markdown", "id": "39d8af96-aebf-4576-82b5-d3f36dc73202", "metadata": {}, "source": [ "## 【任务3-4】向数据库存储高考数据" ] }, { "cell_type": "code", "execution_count": 5, "id": "b17d2605-b494-413a-9c19-6792585d1f8b", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[ 0 1 2\n", " 0 本科 普通本科录取控制分数线 434分\n", " 1 本科 特殊类型招生控制分数线 523分\n", " 2 本科 艺术类（不含舞蹈类、戏曲类）本科录取控制分数线 326分\n", " 3 本科 舞蹈类、戏曲类本科录取控制分数线 217分\n", " 4 本科 体育类本科录取控制分数线（体育成绩60分） 400分,\n", " 0 1 2\n", " 0 本科 普通本科录取控制分数线 448分\n", " 1 本科 特殊类型招生控制分数线 527分\n", " 2 本科 艺术类本科录取控制分数线 336分\n", " 3 本科 体育类本科录取控制分数线 （体育成绩60分） 390分,\n", " 0 1 2\n", " 0 本科 普通本科录取控制分数线 425分\n", " 1 本科 特殊类型招生控制分数线 518分\n", " 2 本科 艺术类本科录取控制分数线 319分\n", " 3 本科 体育类本科录取控制分数线（体育成绩60分） 348分,\n", " 0 1 2\n", " 0 本科 普通本科录取控制分数线 400分\n", " 1 本科 特殊类型招生控制分数线 513分\n", " 2 本科 艺术类本科录取控制分数线 300分\n", " 3 本科 体育类本科录取控制分数线（体育成绩60分） 328分,\n", " 0 1 2\n", " 0 本科 普通本科录取控制分数线 436分\n", " 1 本科 特殊类型招生控制分数线 526分\n", " 2 本科 艺术类本科录取控制分数线 316分\n", " 3 本科 体育类本科录取控制分数线 （体育成绩60分） 300分,\n", " 0 1 2 3 4\n", " 0 年份 考生所在地 考生类别 批次 批次分数线\n", " 1 2019 北京 理科 本科普通批（一批二批合并后） 423\n", " 2 2018 北京 理科 本科二批（二批三批合并后） 432\n", " 3 2017 北京 理科 本科二批（二批三批合并后） 439\n", " 4 2016 北京 理科 本科三批 438\n", " 5 2015 北京 理科 本科三批 452\n", " 6 2014 北京 理科 本科三批 466\n", " 7 2013 北京 理科 本科三批 475\n", " 8 2012 北京 理科 本科三批 402,\n", " 0 1 2 3 4\n", " 0 年份 考生所在地 考生类别 批次 批次分数线\n", " 1 2019 北京 文科 本科普通批（一批二批合并后） 480\n", " 2 2018 北京 文科 本科二批（二批三批合并后） 488\n", " 3 2017 北京 文科 本科二批（二批三批合并后） 468\n", " 4 2016 北京 文科 本科三批 488\n", " 5 2015 北京 文科 本科三批 477\n", " 6 2014 北京 文科 本科三批 458\n", " 7 2013 北京 文科 本科三批 454\n", " 8 2012 北京 文科 本科三批 416]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "url = r'http://127.0.0.1:3000'\n", "df_total = pd.read_html(url, encoding='gbk') \n", "df_total" ] }, { "cell_type": "code", "execution_count": 3, "id": "ea0b6bac-63ce-4339-b430-e277dbdb14c1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "写入成功\n" ] } ], "source": [ "from sqlalchemy import create_engine\n", "from sqlalchemy.types import *\n", "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1/college_info')\n", "# 新的列索引\n", "new_column_names = {0: '总分类', 1: '子分类', 2: '分数'}\n", "i = 2024\n", "for df in df_total:\n", " if i == 2019:\n", " break\n", " # 重命名列索引\n", " df_name = df.rename(columns=new_column_names)\n", " # 拼接数据表的名称\n", " table_name = f'college{i}'\n", " i -= 1\n", " # 向数据表写入数据\n", " df_name.to_sql(table_name, engine, index=False)\n", "print('写入成功')" ] }, { "cell_type": "markdown", "id": "eea89db2-a26e-4c7c-96c1-7866d8a2ef01", "metadata": {}, "source": [ "## 算术运算" ] }, { "cell_type": "code", "execution_count": 89, "id": "2c53697f-b2a0-4e77-be4b-946f4069aa2d", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 10\n", "1 11\n", "2 12\n", "dtype: int64" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "obj_one = pd.Series(range(10, 13), index=range(3)) \n", "obj_one" ] }, { "cell_type": "code", "execution_count": 90, "id": "f8bcd13c-0457-4e20-9644-a6620369e287", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 20\n", "1 21\n", "2 22\n", "3 23\n", "4 24\n", "dtype: int64" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "obj_two = pd.Series(range(20, 25), index=range(5))\n", "obj_two" ] }, { "cell_type": "code", "execution_count": 91, "id": "d1b57a5b-1a74-4e08-8784-93d2904c9c27", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 30.0\n", "1 32.0\n", "2 34.0\n", "3 NaN\n", "4 NaN\n", "dtype: float64" ] }, "execution_count": 91, "metadata": {}, "output_type": "execute_result" } ], "source": [ "obj_one + obj_two # 执行相加运算" ] }, { "cell_type": "code", "execution_count": 93, "id": "4a6b0b6b-fdb2-4ac2-8eb1-2e572f9a97f7", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 30.0\n", "1 32.0\n", "2 34.0\n", "3 23.0\n", "4 24.0\n", "dtype: float64" ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" } ], "source": [ "obj_one.add(obj_two, fill_value=0)" ] }, { "cell_type": "markdown", "id": "1b032e76-662c-40b7-a48b-7a59381bcb06", "metadata": {}, "source": [ "## 统计计算" ] }, { "cell_type": "code", "execution_count": 94, "id": "f7c95f60-1647-4531-a229-4c30ffe5c049", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
abcd
00123
14567
2891011
\n", "
" ], "text/plain": [ " a b c d\n", "0 0 1 2 3\n", "1 4 5 6 7\n", "2 8 9 10 11" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj = pd.DataFrame(np.arange(12).reshape(3, 4),\n", " columns=['a', 'b', 'c', 'd'])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 95, "id": "9ecb575b-a06d-4e29-bdda-ce588cc970aa", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "a 12\n", "b 15\n", "c 18\n", "d 21\n", "dtype: int64" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.sum() # 计算每列的和" ] }, { "cell_type": "code", "execution_count": 96, "id": "fe0b7949-e763-45cb-b242-33516a5ce965", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "a 8\n", "b 9\n", "c 10\n", "d 11\n", "dtype: int32" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.max() # 获取每列的最大值" ] }, { "cell_type": "code", "execution_count": 97, "id": "f5e1a163-29a0-4dc4-a38c-4b45acb69159", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 0\n", "1 4\n", "2 8\n", "dtype: int32" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.min(axis=1) # 计算每行的最小值" ] }, { "cell_type": "markdown", "id": "b18ba37e-bae1-47c0-9971-410a90e42201", "metadata": {}, "source": [ "## 统计描述" ] }, { "cell_type": "code", "execution_count": 98, "id": "b67e189c-6866-4c0b-8157-48ab42b5ffb8", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
0123
0126-1119
1-175036
2592328
\n", "
" ], "text/plain": [ " 0 1 2 3\n", "0 12 6 -11 19\n", "1 -1 7 50 36\n", "2 5 9 23 28" ] }, "execution_count": 98, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df_obj = pd.DataFrame([[12, 6, -11, 19], \n", " [-1, 7, 50, 36],\n", " [5, 9, 23, 28]])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 99, "id": "55637578-49e9-47ef-a8d8-ffc4e6ccfabe", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
0123
count3.0000003.0000003.0000003.000000
mean5.3333337.33333320.66666727.666667
std6.5064071.52752530.5668678.504901
min-1.0000006.000000-11.00000019.000000
25%2.0000006.5000006.00000023.500000
50%5.0000007.00000023.00000028.000000
75%8.5000008.00000036.50000032.000000
max12.0000009.00000050.00000036.000000
\n", "
" ], "text/plain": [ " 0 1 2 3\n", "count 3.000000 3.000000 3.000000 3.000000\n", "mean 5.333333 7.333333 20.666667 27.666667\n", "std 6.506407 1.527525 30.566867 8.504901\n", "min -1.000000 6.000000 -11.000000 19.000000\n", "25% 2.000000 6.500000 6.000000 23.500000\n", "50% 5.000000 7.000000 23.000000 28.000000\n", "75% 8.500000 8.000000 36.500000 32.000000\n", "max 12.000000 9.000000 50.000000 36.000000" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.describe() # 输出多个统计指标" ] }, { "cell_type": "markdown", "id": "0a7cad80-871a-49f2-b7cb-77f641218ae9", "metadata": {}, "source": [ "## 【任务3-5】统计产品盈利情况" ] }, { "cell_type": "code", "execution_count": 1, "id": "8bdf515d-0b5d-400d-80e6-f1c67cd6a27e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
产品ID总成本（万元）销售数量（万件）总销售额（万元）
01170807059
12192397991
2376757269
34122349976
4599694267
\n", "
" ], "text/plain": [ " 产品ID 总成本（万元） 销售数量（万件） 总销售额（万元）\n", "0 1 170 80 7059\n", "1 2 192 39 7991\n", "2 3 76 75 7269\n", "3 4 122 34 9976\n", "4 5 99 69 4267" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "cost_data = pd.read_csv(r'data\\cost_data.csv', encoding='gbk')\n", "cost_data.head()" ] }, { "cell_type": "code", "execution_count": 2, "id": "73c88ff5-6fee-402f-adf4-abf8e0f2a33e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
产品ID总成本（万元）销售数量（万件）总销售额（万元）单笔盈利额（万元）
011708070596889
121923979917799
23767572697193
341223499769854
45996942674168
\n", "
" ], "text/plain": [ " 产品ID 总成本（万元） 销售数量（万件） 总销售额（万元） 单笔盈利额（万元）\n", "0 1 170 80 7059 6889\n", "1 2 192 39 7991 7799\n", "2 3 76 75 7269 7193\n", "3 4 122 34 9976 9854\n", "4 5 99 69 4267 4168" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 计算每款产品的单笔盈利，即总销售额减去总成本\n", "cost_data['单笔盈利额（万元）'] = cost_data['总销售额（万元）'] - cost_data['总成本（万元）']\n", "cost_data.head()" ] }, { "cell_type": "code", "execution_count": 4, "id": "41e68e27-c669-4af2-a73b-1994d05bf201", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "总盈利额（万元）： 128636\n" ] } ], "source": [ "# 计算总盈利额\n", "total_profit = cost_data['单笔盈利额（万元）'].sum()\n", "print(\"总盈利额（万元）：\", total_profit)" ] }, { "cell_type": "code", "execution_count": 5, "id": "3cb255ab-c156-4b9a-a10d-fcf8876166f7", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
产品ID总成本（万元）销售数量（万件）总销售额（万元）单笔盈利额（万元）盈利额占比（%）
0117080705968895.355421
1219239799177996.062844
237675726971935.591747
3412234997698547.660375
459969426741683.240151
\n", "
" ], "text/plain": [ " 产品ID 总成本（万元） 销售数量（万件） 总销售额（万元） 单笔盈利额（万元） 盈利额占比（%）\n", "0 1 170 80 7059 6889 5.355421\n", "1 2 192 39 7991 7799 6.062844\n", "2 3 76 75 7269 7193 5.591747\n", "3 4 122 34 9976 9854 7.660375\n", "4 5 99 69 4267 4168 3.240151" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 计算每款产品的盈利额占比\n", "cost_data['盈利额占比（%）'] = (cost_data['单笔盈利额（万元）'] / total_profit * 100)\n", "cost_data.head()" ] }, { "cell_type": "code", "execution_count": 8, "id": "21c58af7-54cf-441a-ab3e-575aae2a7a33", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
总销售额（万元）单笔盈利额（万元）盈利额占比（%）
count20.00000020.00000020.000000
mean6560.6500006431.8000005.000000
std2053.7609992059.6524391.601148
min3795.0000003676.0000002.857676
25%4526.5000004363.7500003.392324
50%6685.0000006574.5000005.110933
75%8219.2500008109.2500006.304028
max9976.0000009854.0000007.660375
\n", "
" ], "text/plain": [ " 总销售额（万元） 单笔盈利额（万元） 盈利额占比（%）\n", "count 20.000000 20.000000 20.000000\n", "mean 6560.650000 6431.800000 5.000000\n", "std 2053.760999 2059.652439 1.601148\n", "min 3795.000000 3676.000000 2.857676\n", "25% 4526.500000 4363.750000 3.392324\n", "50% 6685.000000 6574.500000 5.110933\n", "75% 8219.250000 8109.250000 6.304028\n", "max 9976.000000 9854.000000 7.660375" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cost_data.iloc[:,3:].describe()" ] }, { "cell_type": "markdown", "id": "8cd3a991-444b-4fa8-bc4c-ee980364d600", "metadata": {}, "source": [ "## 创建分层索引" ] }, { "cell_type": "code", "execution_count": 105, "id": "1b1d0afb-c3cb-4d17-b196-a2709842796b", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "MultiIndex([('A', 'A1'),\n", " ('A', 'A2'),\n", " ('B', 'B1'),\n", " ('B', 'B2'),\n", " ('B', 'B3')],\n", " names=['外层索引', '内层索引'])" ] }, "execution_count": 105, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pandas import MultiIndex\n", "# 创建一个包含多个元组的列表\n", "list_tuples = [('A','A1'), ('A','A2'), ('B','B1'),\n", " ('B','B2'), ('B','B3')]\n", "# 根据元组列表创建一个MultiIndex类的对象\n", "multi_index = MultiIndex.from_tuples(tuples=list_tuples, names=['外层索引', '内层索引'])\n", "multi_index" ] }, { "cell_type": "code", "execution_count": 106, "id": "9cd2a89c-6029-4cde-9c34-7b4f0bf4cd30", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "MultiIndex([('A', 'A1'),\n", " ('A', 'A2'),\n", " ('B', 'B1'),\n", " ('B', 'B2'),\n", " ('B', 'B3')],\n", " names=['外层索引', '内层索引'])" ] }, "execution_count": 106, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pandas import MultiIndex\n", "# 创建一个包含多个元组的列表\n", "list_tuples = [('A','A1'), ('A','A2'), ('B','B1'),\n", " ('B','B2'), ('B','B3')]\n", "# 根据元组列表创建一个MultiIndex类的对象\n", "multi_index = MultiIndex.from_tuples(tuples=list_tuples, names=['外层索引', '内层索引'])\n", "multi_index" ] }, { "cell_type": "code", "execution_count": 108, "id": "32b170dd-5ab2-45e5-b12d-b84280640c42", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "MultiIndex([(0, 'green'),\n", " (0, 'purple'),\n", " (1, 'green'),\n", " (1, 'purple'),\n", " (2, 'green'),\n", " (2, 'purple')],\n", " names=['number', 'color'])" ] }, "execution_count": 108, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pandas import MultiIndex\n", "import pandas as pd\n", "numbers = [0, 1, 2]\n", "colors = ['green', 'purple']\n", "multi_product = pd.MultiIndex.from_product(iterables=[numbers, colors], names=['number', 'color'])\n", "multi_product" ] }, { "cell_type": "markdown", "id": "8f941b82-a60f-40f2-8d77-5a275854878c", "metadata": {}, "source": [ "## 创建有分层索引的对象" ] }, { "cell_type": "code", "execution_count": 109, "id": "1fce9cac-40cc-4ef8-a56e-a60c5be1c5d9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "河北省 石家庄市 14530\n", " 唐山市 13829\n", " 邯郸市 12047\n", " 秦皇岛市 7813\n", "河南省 郑州市 7568\n", " 开封市 6239\n", " 洛阳市 15236\n", " 新乡市 8291\n", "dtype: int64" ] }, "execution_count": 109, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import numpy as np\n", "import pandas as pd\n", "mulitindex_series = pd.Series([14530, 13829, 12047, 7813, 7568, 6239, 15236, 8291],\n", " \t index=[['河北省','河北省','河北省','河北省', '河南省','河南省','河南省','河南省'],\n", " ['石家庄市','唐山市','邯郸市','秦皇岛市', '郑州市','开封市','洛阳市','新乡市']])\n", "mulitindex_series" ] }, { "cell_type": "code", "execution_count": 2, "id": "9c5826d6-aece-4be7-975f-fb4360044459", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
河北省石家庄市14530
唐山市13829
邯郸市12047
秦皇岛市7813
河南省郑州市7568
开封市6239
洛阳市15236
新乡市8291
\n", "
" ], "text/plain": [ " \n", "河北省 石家庄市 14530\n", " 唐山市 13829\n", " 邯郸市 12047\n", " 秦皇岛市 7813\n", "河南省 郑州市 7568\n", " 开封市 6239\n", " 洛阳市 15236\n", " 新乡市 8291" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "from pandas import DataFrame, Series\n", "# 占地面积为增加的列索引\n", "mulitindex_df = DataFrame({'': [14530, 13829, 12047,\n", " 7813, 7568, 6239, 15236, 8291]},\n", "\t index=[['河北省','河北省','河北省','河北省',\n", "\t '河南省','河南省','河南省','河南省'],\n", "\t ['石家庄市','唐山市','邯郸市','秦皇岛市',\n", " '郑州市','开封市','洛阳市','新乡市']])\n", "mulitindex_df" ] }, { "cell_type": "code", "execution_count": 112, "id": "34fcf343-1c65-4fff-8289-4dbbdd06cd03", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
01
numbercolor
0green75
purple66
1green31
purple55
2green45
purple53
\n", "
" ], "text/plain": [ " 0 1\n", "number color \n", "0 green 7 5\n", " purple 6 6\n", "1 green 3 1\n", " purple 5 5\n", "2 green 4 5\n", " purple 5 3" ] }, "execution_count": 112, "metadata": {}, "output_type": "execute_result" } ], "source": [ "values = np.array([[7, 5], [6, 6], [3, 1], [5, 5], [4, 5], [5, 3]])\n", "df_product = pd.DataFrame(data=values, index=multi_product)\n", "df_product" ] }, { "cell_type": "markdown", "id": "d1dbbcb4-2760-452f-aa8d-b6b491069aeb", "metadata": {}, "source": [ "## 使用分层索引获取数据" ] }, { "cell_type": "code", "execution_count": 113, "id": "76134cd1-8035-4eee-a54d-69d78a0bcb3e", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "小说 平凡的世界 50\n", " 骆驼祥子 60\n", " 狂人日记 40\n", "散文随笔 皮囊 94\n", " 浮生六记 63\n", " 自在独行 101\n", "传记 曾国藩 200\n", " 老舍自传 56\n", " 知行合一王阳明 45\n", "dtype: int64" ] }, "execution_count": 113, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pandas import Series\n", "ser_obj = Series([50, 60, 40, 94, 63, 101, 200, 56, 45],\n", "\t index=[['小说', '小说', '小说',\n", "\t '散文随笔', '散文随笔', '散文随笔',\n", "\t '传记', '传记', '传记'],\n", "\t ['平凡的世界', '骆驼祥子', '狂人日记',\n", "\t '皮囊', '浮生六记', '自在独行',\n", "\t '曾国藩', '老舍自传', '知行合一王阳明']])\n", "ser_obj" ] }, { "cell_type": "code", "execution_count": 114, "id": "cc5b3421-08cb-4a3d-ab93-232d1f62893d", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "平凡的世界 50\n", "骆驼祥子 60\n", "狂人日记 40\n", "dtype: int64" ] }, "execution_count": 114, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj['小说'] # 获取所有外层索引为“小说”的数据" ] }, { "cell_type": "code", "execution_count": 115, "id": "cbb99f05-b8ad-4527-be9c-4f44c20a8e56", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "50" ] }, "execution_count": 115, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj['小说', '平凡的世界'] # 使用外层索引和内层索引获取数据" ] }, { "cell_type": "code", "execution_count": 116, "id": "2c755952-ad61-4bf6-aa51-40b31bbbc74c", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "散文随笔 101\n", "dtype: int64" ] }, "execution_count": 116, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj[:, '自在独行'] # 获取内层索引对应的数据" ] }, { "cell_type": "markdown", "id": "7dceaf24-0f79-44d0-bfb6-cb2b348fdb4e", "metadata": {}, "source": [ "## 交换索引层级的顺序" ] }, { "cell_type": "code", "execution_count": 117, "id": "096862d7-789c-419c-a018-22e304544dd3", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "平凡的世界 小说 50\n", "骆驼祥子 小说 60\n", "狂人日记 小说 40\n", "皮囊 散文随笔 94\n", "浮生六记 散文随笔 63\n", "自在独行 散文随笔 101\n", "曾国藩 传记 200\n", "老舍自传 传记 56\n", "知行合一王阳明 传记 45\n", "dtype: int64" ] }, "execution_count": 117, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ser_obj.swaplevel() # 交换外层索引与内层索引的位置" ] }, { "cell_type": "markdown", "id": "c4c25ef7-cb27-4567-9c6c-1d10d972f9f8", "metadata": {}, "source": [ "## 分层索引排序" ] }, { "cell_type": "code", "execution_count": 118, "id": "ec9ff94a-5705-418a-ae3f-300f1b14b5a2", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
wordnum
A1a1
3b2
2d4
C3e5
1f3
2k2
B4d6
5s2
8l3
\n", "
" ], "text/plain": [ " word num\n", "A 1 a 1\n", " 3 b 2\n", " 2 d 4\n", "C 3 e 5\n", " 1 f 3\n", " 2 k 2\n", "B 4 d 6\n", " 5 s 2\n", " 8 l 3" ] }, "execution_count": 118, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from pandas import DataFrame\n", "df_obj = DataFrame({'word':['a','b','d','e','f','k','d','s','l'],\n", " 'num':[1, 2, 4, 5, 3, 2, 6, 2, 3]},\n", " index=[['A', 'A', 'A', 'C', 'C', 'C', 'B', 'B', 'B'], [1, 3, 2, 3, 1, 2, 4, 5, 8]])\n", "df_obj" ] }, { "cell_type": "code", "execution_count": 119, "id": "6b90d0a7-1b03-4e75-9e3e-48942b6630ce", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
wordnum
A1a1
2d4
3b2
B4d6
5s2
8l3
C1f3
2k2
3e5
\n", "
" ], "text/plain": [ " word num\n", "A 1 a 1\n", " 2 d 4\n", " 3 b 2\n", "B 4 d 6\n", " 5 s 2\n", " 8 l 3\n", "C 1 f 3\n", " 2 k 2\n", " 3 e 5" ] }, "execution_count": 119, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_obj.sort_index() # 按索引排序" ] }, { "cell_type": "markdown", "id": "80bed352-53fe-48c2-880d-925191f7eaf7", "metadata": {}, "source": [ "## 获取某层级索引的值" ] }, { "cell_type": "code", "execution_count": 120, "id": "c9c8aec3-4206-44fa-980d-df42c9f8c425", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " C D\n", "A B \n", "x 1 0 3\n", "y 2 1 4\n", "z 3 2 5\n" ] } ], "source": [ "import pandas as pd\n", "# 创建一个包含两层级别索引的DataFrame\n", "data = {'C': [0, 1, 2], 'D': [3, 4, 5]}\n", "multi_index = pd.MultiIndex.from_tuples([('x', 1), ('y', 2), ('z', 3)], \n", " names=['A', 'B'])\n", "df_mul = pd.DataFrame(data, index=multi_index)\n", "print(df_mul)" ] }, { "cell_type": "code", "execution_count": 121, "id": "e4008eae-be73-4fed-a37a-8a4f6b55a6f9", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['x', 'y', 'z'], dtype='object', name='A')" ] }, "execution_count": 121, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_mul.index.get_level_values(0) # 根据位置获取外层索引" ] }, { "cell_type": "code", "execution_count": 122, "id": "22452b15-0c70-4a91-9a02-40b3137117cc", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index([1, 2, 3], dtype='int64', name='B')" ] }, "execution_count": 122, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_mul.index.get_level_values(1) # 根据位置获取内层索引" ] }, { "cell_type": "code", "execution_count": 123, "id": "e41e15e8-c1ce-451d-b0fd-c2c7cf63a137", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['x', 'y', 'z'], dtype='object', name='A')" ] }, "execution_count": 123, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_mul.index.get_level_values('A') # 根据名称获取外层索引" ] }, { "cell_type": "markdown", "id": "419c7ccc-e574-45fe-a951-20a247551a8f", "metadata": {}, "source": [ "## 【任务3-6】分析汽车销售数据" ] }, { "cell_type": "code", "execution_count": 14, "id": "86b54eaa-533c-4973-8547-90bbfc36b4a9", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
汽车名称销售数量（辆）销售额（元）
华东王朝系列唐22255455600
王朝系列秦9712590600
王朝系列宋16721676600
王朝系列汉24245931600
王朝系列元37352145400
\n", "
" ], "text/plain": [ " 汽车名称 销售数量（辆） 销售额（元）\n", "华东 王朝系列 唐 222 55455600\n", " 王朝系列 秦 97 12590600\n", " 王朝系列 宋 167 21676600\n", " 王朝系列 汉 242 45931600\n", " 王朝系列 元 373 52145400" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import pandas as pd\n", "df_car = pd.read_excel(r'data\\bydcar.xlsx', index_col=[0, 1])\n", "df_car.head()" ] }, { "cell_type": "code", "execution_count": 15, "id": "23f737dc-99a3-422c-a361-4572dba5a313", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\itcast\\AppData\\Local\\Temp\\ipykernel_13192\\3226330937.py:1: PerformanceWarning: indexing past lexsort depth may impact performance.\n", " df_east_ocean = df_car.loc['华东', '海洋系列']\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
汽车名称销售数量（辆）销售额（元）
华东海洋系列宋PLUS DM-i荣耀版44669486800
海洋系列海鸥38730882600
\n", "
" ], "text/plain": [ " 汽车名称 销售数量（辆） 销售额（元）\n", "华东 海洋系列 宋PLUS DM-i荣耀版 446 69486800\n", " 海洋系列 海鸥 387 30882600" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_east_ocean = df_car.loc['华东', '海洋系列']\n", "df_east_ocean[(df_east_ocean['汽车名称'] == '海鸥') | \n", " (df_east_ocean['汽车名称'] == '宋PLUS DM-i荣耀版')]" ] }, { "cell_type": "code", "execution_count": 16, "id": "9e235e9d-bb11-48a7-a54f-9d141e39a7fe", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\itcast\\AppData\\Local\\Temp\\ipykernel_13192\\3096707248.py:1: PerformanceWarning: indexing past lexsort depth may impact performance.\n", " df_south_ocean = df_car.loc['华南', '海洋系列']\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
汽车名称销售数量（辆）销售额（元）
华南海洋系列海鸥26120827800
海洋系列宋PLUS DM-i荣耀版24337859400
\n", "
" ], "text/plain": [ " 汽车名称 销售数量（辆） 销售额（元）\n", "华南 海洋系列 海鸥 261 20827800\n", " 海洋系列 宋PLUS DM-i荣耀版 243 37859400" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_south_ocean = df_car.loc['华南', '海洋系列']\n", "df_south_ocean[(df_south_ocean['汽车名称'] == '海鸥') | \n", " (df_south_ocean['汽车名称'] == '宋PLUS DM-i荣耀版')]" ] }, { "cell_type": "code", "execution_count": 17, "id": "dd67cc9c-c606-4a37-abd4-075c3b0b37f9", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\itcast\\AppData\\Local\\Temp\\ipykernel_13192\\3684566409.py:1: PerformanceWarning: indexing past lexsort depth may impact performance.\n", " df_north_ocean = df_car.loc['华北', '海洋系列']\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
汽车名称销售数量（辆）销售额（元）
华北海洋系列宋PLUS DM-i荣耀版29245493600
海洋系列海鸥1209576000
\n", "
" ], "text/plain": [ " 汽车名称 销售数量（辆） 销售额（元）\n", "华北 海洋系列 宋PLUS DM-i荣耀版 292 45493600\n", " 海洋系列 海鸥 120 9576000" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_north_ocean = df_car.loc['华北', '海洋系列']\n", "df_north_ocean[(df_north_ocean['汽车名称'] == '海鸥') | \n", " (df_north_ocean['汽车名称'] == '宋PLUS DM-i荣耀版')]" ] }, { "cell_type": "code", "execution_count": 18, "id": "35bb1f6d-89d1-42f2-ba7f-b05db55214e7", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
海洋系列王朝系列
华东204696000187799800
华南142793600204031400
华北235667600148402800
\n", "
" ], "text/plain": [ " 海洋系列 王朝系列\n", "华东 204696000 187799800\n", "华南 142793600 204031400\n", "华北 235667600 148402800" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# 交换索引级别并对索引进行排序\n", "df_car_swapped = df_car.swaplevel().sort_index()\n", "# 创建字典，用于存储所有系列的数据\n", "sales_total = {}\n", "for car_ser in ['海洋系列', '王朝系列']: \n", " # 创建字典，用于存储每个系列的数据\n", " sales_sigal = {}\n", " for car_area in ['华东', '华南', '华北']:\n", " # 根据系列和地区获取相应数据，再计算销售额一列数据的和\n", " sales = df_car_swapped[\n", " (df_car_swapped.index.get_level_values(0) == car_ser) & \n", " (df_car_swapped.index.get_level_values(1) == car_area)\n", " ]['销售额（元）'].sum()\n", " sales_sigal[car_area] = sales\n", " sales_total[car_ser] = sales_sigal\n", "pd.DataFrame(sales_total)" ] }, { "cell_type": "code", "execution_count": null, "id": "e0a39396-6279-4f95-ba9a-75365a962896", "metadata": {}, "outputs": [], "source": [] } ], "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.11.7" } }, "nbformat": 4, "nbformat_minor": 5 }