452 lines
15 KiB
Plaintext
452 lines
15 KiB
Plaintext
{
|
||
"cells": [
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 1,
|
||
"id": "9ec6ebc2",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"import pandas as pd"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 2,
|
||
"id": "413b2f9f",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df = pd.read_excel('学生信息.xlsx',skiprows=1)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"id": "31be9769",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": []
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 7,
|
||
"id": "dd627675",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"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>学号</th>\n",
|
||
" <th>姓名</th>\n",
|
||
" <th>性别</th>\n",
|
||
" <th>年级</th>\n",
|
||
" <th>班级</th>\n",
|
||
" <th>账户名</th>\n",
|
||
" <th>手环号</th>\n",
|
||
" <th>通/住校</th>\n",
|
||
" </tr>\n",
|
||
" </thead>\n",
|
||
" <tbody>\n",
|
||
" <tr>\n",
|
||
" <th>0</th>\n",
|
||
" <td>847613</td>\n",
|
||
" <td>曾笙</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>847613CDSPTSZX</td>\n",
|
||
" <td>3722606215</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>1</th>\n",
|
||
" <td>205240217</td>\n",
|
||
" <td>郭靖</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>0班</td>\n",
|
||
" <td>205240217CDSPTSZX</td>\n",
|
||
" <td>22802380</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>2</th>\n",
|
||
" <td>2024240217</td>\n",
|
||
" <td>黄蓉</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>2024240217CDSPTSZX</td>\n",
|
||
" <td>3108630769</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>3</th>\n",
|
||
" <td>20250011</td>\n",
|
||
" <td>曾曦蕊</td>\n",
|
||
" <td>女</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250011CDSPTSZX</td>\n",
|
||
" <td>22830060</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>4</th>\n",
|
||
" <td>20250012</td>\n",
|
||
" <td>曾星睿</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250012CDSPTSZX</td>\n",
|
||
" <td>3108363489</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>...</th>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" <td>...</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>780</th>\n",
|
||
" <td>20250783</td>\n",
|
||
" <td>彭彦晰</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250783CDSPTSZX</td>\n",
|
||
" <td>1620701492</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>781</th>\n",
|
||
" <td>20250802</td>\n",
|
||
" <td>魏腾一</td>\n",
|
||
" <td>女</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250802CDSPTSZX</td>\n",
|
||
" <td>1618071076</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>782</th>\n",
|
||
" <td>20250805</td>\n",
|
||
" <td>任梓玮</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250805CDSPTSZX</td>\n",
|
||
" <td>1619575092</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>783</th>\n",
|
||
" <td>20250866</td>\n",
|
||
" <td>张羽菲</td>\n",
|
||
" <td>女</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250866CDSPTSZX</td>\n",
|
||
" <td>1620005620</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" <tr>\n",
|
||
" <th>784</th>\n",
|
||
" <td>20250617</td>\n",
|
||
" <td>杨光墨辰</td>\n",
|
||
" <td>男</td>\n",
|
||
" <td>2025级</td>\n",
|
||
" <td>临时班</td>\n",
|
||
" <td>20250617CDSPTSZX</td>\n",
|
||
" <td>3108648785</td>\n",
|
||
" <td>通校</td>\n",
|
||
" </tr>\n",
|
||
" </tbody>\n",
|
||
"</table>\n",
|
||
"<p>785 rows × 8 columns</p>\n",
|
||
"</div>"
|
||
],
|
||
"text/plain": [
|
||
" 学号 姓名 性别 年级 班级 账户名 手环号 通/住校\n",
|
||
"0 847613 曾笙 男 2025级 临时班 847613CDSPTSZX 3722606215 通校\n",
|
||
"1 205240217 郭靖 男 2025级 0班 205240217CDSPTSZX 22802380 通校\n",
|
||
"2 2024240217 黄蓉 男 2025级 临时班 2024240217CDSPTSZX 3108630769 通校\n",
|
||
"3 20250011 曾曦蕊 女 2025级 临时班 20250011CDSPTSZX 22830060 通校\n",
|
||
"4 20250012 曾星睿 男 2025级 临时班 20250012CDSPTSZX 3108363489 通校\n",
|
||
".. ... ... .. ... ... ... ... ...\n",
|
||
"780 20250783 彭彦晰 男 2025级 临时班 20250783CDSPTSZX 1620701492 通校\n",
|
||
"781 20250802 魏腾一 女 2025级 临时班 20250802CDSPTSZX 1618071076 通校\n",
|
||
"782 20250805 任梓玮 男 2025级 临时班 20250805CDSPTSZX 1619575092 通校\n",
|
||
"783 20250866 张羽菲 女 2025级 临时班 20250866CDSPTSZX 1620005620 通校\n",
|
||
"784 20250617 杨光墨辰 男 2025级 临时班 20250617CDSPTSZX 3108648785 通校\n",
|
||
"\n",
|
||
"[785 rows x 8 columns]"
|
||
]
|
||
},
|
||
"execution_count": 7,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 12,
|
||
"id": "ef0a8145",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"'1'"
|
||
]
|
||
},
|
||
"execution_count": 12,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"\" 1\".strip()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"id": "60a3e782",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": []
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 24,
|
||
"id": "4cb9da33",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"df = pd.read_excel('学生信息.xlsx',skiprows=1,dtype=str)\n",
|
||
"df2 = pd.read_excel('2025级学生班级_子1.xlsx',dtype=str)\n",
|
||
"\n",
|
||
"学号字典 = dict(zip(df2['学号'], df2['班级'].astype(\"str\")))\n",
|
||
"\n",
|
||
"# 直接通过map函数添加班级列，不存在的学号用'未找到'填充\n",
|
||
"df['班级'] = df['学号'].map(学号字典).fillna('未找到')"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 31,
|
||
"id": "947a671a",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"name": "stdout",
|
||
"output_type": "stream",
|
||
"text": [
|
||
"df中有但df2中没有的记录：\n",
|
||
" 学号 姓名 性别 年级 班级 账户名 手环号 通/住校\n",
|
||
"0 847613 曾笙 男 2025级 临时班 847613CDSPTSZX 3722606215 通校\n",
|
||
"1 205240217 郭靖 男 2025级 0班 205240217CDSPTSZX 0022802380 通校\n",
|
||
"2 2024240217 黄蓉 男 2025级 临时班 2024240217CDSPTSZX 3108630769 通校\n",
|
||
"17 20250041 陈奕瑾 女 2025级 临时班 20250041CDSPTSZX 3108586721 通校\n",
|
||
"31 20250080 杜雨欣 女 2025级 临时班 20250080CDSPTSZX 3108567745 通校\n",
|
||
"38 20250098 冯雅琳 女 2025级 临时班 20250098CDSPTSZX 3108398001 通校\n",
|
||
"48 20250123 郭明萱 女 2025级 临时班 20250123CDSPTSZX 3108359601 通校\n",
|
||
"96 20250290 刘瑞泽 男 2025级 临时班 20250290CDSPTSZX 3108417121 通校\n",
|
||
"130 20250402 苏子洛 男 2025级 临时班 20250402CDSPTSZX 3108648977 通校\n",
|
||
"143 20250448 王晟骁 男 2025级 临时班 20250448CDSPTSZX 3108604769 通校\n",
|
||
"153 20250475 王雅萱 女 2025级 临时班 20250475CDSPTSZX 3108626049 通校\n",
|
||
"157 20250502 吴睿灿 男 2025级 临时班 20250502CDSPTSZX 3108654369 通校\n",
|
||
"172 20250567 徐周塬 男 2025级 临时班 20250567CDSPTSZX 3107628913 通校\n",
|
||
"233 20250772 李怡辰 女 2025级 临时班 20250772CDSPTSZX 3108433745 通校\n",
|
||
"260 20258671 弋珂 女 2025级 临时班 20258671CDSPTSZX 0022681516 通校\n",
|
||
"261 20258673 马艺诚 男 2025级 临时班 20258673CDSPTSZX 3108571633 通校\n",
|
||
"276 20250021 陈佳逸 女 2025级 临时班 20250021CDSPTSZX 1619715284 通校\n",
|
||
"369 20250169 黄心仪 女 2025级 临时班 20250169CDSPTSZX 1617731892 通校\n",
|
||
"389 20250199 乐思彤 女 2025级 临时班 20250199CDSPTSZX 1619850820 通校\n",
|
||
"419 20250246 李屹然 男 2025级 临时班 20250246CDSPTSZX 1619431076 通校\n"
|
||
]
|
||
}
|
||
],
|
||
"source": [
|
||
"import pandas as pd\n",
|
||
"\n",
|
||
"# 读取Excel文件，确保所有列都是字符串类型\n",
|
||
"df = pd.read_excel('学生信息.xlsx', skiprows=1, dtype=str)\n",
|
||
"df2 = pd.read_excel('2025级学生班级_子1.xlsx', dtype=str)\n",
|
||
"\n",
|
||
"# 为两个DataFrame添加唯一标识列（姓名+性别+学号）\n",
|
||
"# 使用分隔符避免不同字段值拼接后产生歧义（例如\"张三四\"+\"男\" vs \"张三\"+\"四男\"）\n",
|
||
"df['唯一标识'] = df['姓名'] + '|' + df['性别'] + '|' + df['学号']\n",
|
||
"df2['唯一标识'] = df2['学生姓名'] + '|' + df2['性别'] + '|' + df2['学号']\n",
|
||
"\n",
|
||
"# 找出df中有但df2中没有的唯一标识\n",
|
||
"df独有标识 = df[~df['唯一标识'].isin(df2['唯一标识'])]['唯一标识']\n",
|
||
"\n",
|
||
"# 根据独有标识筛选出完整记录\n",
|
||
"结果 = df[df['唯一标识'].isin(df独有标识)]\n",
|
||
"\n",
|
||
"# 输出结果（可以根据需要选择保留或删除'唯一标识'列）\n",
|
||
"print(\"df中有但df2中没有的记录：\")\n",
|
||
"print(结果.drop(columns=['唯一标识'])) # 删除临时的唯一标识列再显示"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 37,
|
||
"id": "a383efe9",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": [
|
||
"import pandas as pd\n",
|
||
"\n",
|
||
"# 读取Excel文件，确保所有列都是字符串类型\n",
|
||
"df = pd.read_excel('学生信息.xlsx',usecols=['姓名','性别','学号'], skiprows=1, dtype=str)\n",
|
||
"df2 = pd.read_excel('2025级学生班级_子1.xlsx',usecols=['学生姓名','性别','学号'], dtype=str)\n",
|
||
"\n",
|
||
"# # 为两个DataFrame添加唯一标识列（姓名+性别+学号）\n",
|
||
"# df['唯一标识'] = df['姓名'] + '|' + df['性别'] + '|' + df['学号']\n",
|
||
"# df2['唯一标识'] = df2['学生姓名'] + '|' + df2['性别'] + '|' + df2['学号']\n",
|
||
"\n",
|
||
"# # 找出df2中有但df中没有的唯一标识\n",
|
||
"# df2独有标识 = df2[~df2['唯一标识'].isin(df['唯一标识'])]['唯一标识']\n",
|
||
"\n",
|
||
"# # 根据独有标识筛选出完整记录\n",
|
||
"# 结果 = df2[df2['唯一标识'].isin(df2独有标识)]\n",
|
||
"\n",
|
||
"# # 输出结果\n",
|
||
"# print(\"df2中有但df中没有的记录：\")\n",
|
||
"# print(结果.drop(columns=['唯一标识'])) # 删除临时的唯一标识列\n",
|
||
"row_strings= set()\n",
|
||
"for _,row in df.iterrows():\n",
|
||
" row_strings.add(f\"{row['姓名']}{row['性别']}{row['学号']}\")\n",
|
||
"row_strings1 = set()\n",
|
||
"for _,row in df2.iterrows():\n",
|
||
" row_strings1.add(f\"{row['学生姓名']}{row['性别']}{row['学号']}\")"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 38,
|
||
"id": "56250fbc",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"{'彭睦雅男20250832', '李沛珊女20250831', '杨槟源男20250830', '杨颜尊女20250833'}"
|
||
]
|
||
},
|
||
"execution_count": 38,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"row_strings1 - row_strings"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 35,
|
||
"id": "34dae11a",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"(769, 4)"
|
||
]
|
||
},
|
||
"execution_count": 35,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df2.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": 36,
|
||
"id": "b8ed5e95",
|
||
"metadata": {},
|
||
"outputs": [
|
||
{
|
||
"data": {
|
||
"text/plain": [
|
||
"(785, 4)"
|
||
]
|
||
},
|
||
"execution_count": 36,
|
||
"metadata": {},
|
||
"output_type": "execute_result"
|
||
}
|
||
],
|
||
"source": [
|
||
"df.shape"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"id": "5a58f936",
|
||
"metadata": {},
|
||
"outputs": [],
|
||
"source": []
|
||
}
|
||
],
|
||
"metadata": {
|
||
"kernelspec": {
|
||
"display_name": "base",
|
||
"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.12.4"
|
||
}
|
||
},
|
||
"nbformat": 4,
|
||
"nbformat_minor": 5
|
||
}
|