数据库的整理

（三）数据库的整理

当前应用广泛的统计学软件有很多，Excel电子表格软件具有直观的界面、出色的计算功能和图表工具，是目前最流行的个人计算机数据处理软件。针对基层从事艾滋病防制工作人员的日常工作需要，不需要高深复杂的统计方法，通过通俗易懂、操作性较强、简单实用的Excel电子表格实现数据的整理、数据分析及常用图的制作，解决基层艾滋病疫情管理及分析工作人员的实际问题。本章节基于Excel 2013版进行示例操作。

1.基本变量的处理

（1）数据准备

艾滋病相关基础数据从中国疾病预防控制信息系统“传染病监测系统”历史数据下载获得，包括CARD库（主要是传染病报卡主卡、附卡信息以及部分随访表推送信息）和Flw库（主要是感染者随访信息）。艾滋病基本疫情资料主要来源于CARD库。

（2）基本变量整理

①打开CARD库

CARD库是csv文件（逗号分隔值文件格式），可用Excel打开，但Excel2003仅能打开65534条记录，如果超过该记录数，需使用Excel2007以上版本或者SPSS软件打开。CARD库共包含80个变量，其中有40个用户填写变量，21个系统生成变量，19个随访推送变量（见图1-2）。

图1-2　Card库csv原始文件

②增加新变量

有些变量无法直接用于统计分析，需要增加部分新变量。提示：输入公式时需要将输入法切换到英文状态下。

A.增加年份变量

终审年：在“终审日期”右侧插入空白列，定义变量名为“终审年”，在第二行空格中输入公式“=YEAR”（同行终审日期坐标），按Enter回车键，得到第一条记录的终审年份；鼠标放在该单元格右下角位置，当鼠标变成黑色十字形，双击即可快速获得整列的终审年份结果，再将该变量格式调整为“常规”格式，即可获得所有病例的终审年份（见图1-3）。

图1-3　终审年整理过程

同样的操作方式，根据“录入日期”生成新变量“录入年”，根据“死亡日期”生成新变量“死亡年”。注意：如果病人未死亡，“死亡年”显示的数据为“1900”。

B.标记现存活状态

死亡/存活：新增变量“死亡/存活”，筛选“死亡年”为“1900”的记录，将新增变量“死亡/存活”标记为“存活”；筛选 “死亡年”为非“1900”的全部记录，将新增变量“死亡/存活”标记为“死亡”。注意：先对死亡进行排序，再进行标记，可提高标记运行效率；标记第一行后，鼠标双击该单元格右下角黑色+字形图标可完成整列数据标记（见图1-4）。

图1-4　标记现存活状态过程

C.增加年龄变量

报告年龄：在“录入日期”右侧插入空白列，定义变量名为“报告年龄”，在第二行空格中输入“=DATEDIF（出生日期坐标，录入日期坐标，“Y”）”，按“enter”回车键，得到第一条记录的报告年龄；鼠标放在该单元格右下角位置，当鼠标变成黑色十字形，双击即可以快速获得整列的报告年龄结果，再将该变量格式调整为“数值”格式，即可获得所有病例的报告年龄（见图1-5）。

图1-5　报告年龄整理过程

现存活年龄：插入空白列，定义变量名为“现存活年龄”，在第二行空格中输入“=DATEDIF（出生日期坐标,“2021/12/31”，“Y”）”，即可计算出存活病例到2021年底的存活年龄。

年龄分组：疫情分析过程中，经常需要把年龄进行5岁或者10岁一个年龄段进行分组分析，因此需要增加年龄分组变量。在“报告年龄”右侧插入空白列，定义变量名为“报告年龄10岁分组”，在第二行空格中输入“=ROUNDDOWN （报告年龄坐标/10,0）”，得到报告分组变量。此公式表示将报告年龄除以10后得数舍去小数部分保留整数，“0”表示0～9岁年龄组，“1”表示10～19岁年龄组，“2”表示20～29岁年龄组，以此类推，将报告年龄以10岁一个年龄段进行分组。备注：ROUNDDOWN函数可以替换为INT函数，即取整函数：“=INT（报告年龄坐标/10）”（见图1-6）。

图1-6　报告年龄分组整理过程

同样的操作方式，根据变量“死亡日期”生成新变量“死亡年龄”［（公式为=DATEDIF（出生日期坐标，死亡日期坐标，“Y”）］，进一步生成新变量“死亡年龄10岁分组”（见图1-7）。

图1-7　死亡年龄分组整理过程

D.增加地区变量

Card库中涉及的地区变量有“报告地区”“现住址编码”和“户籍地址编码”，均为8位数的乡镇级国标码，该编码前2位为省级编码，前4位为州市级编码，前6位为县级编码。为了疫情分析的需要，需要生成省级、州（市）级及县（区）级国标码。

现住址省：在“现住址编码”右侧插入空白列，定义变量名为“现住址省”，在第二个空格中输入“=left（现住址编码,2）”，得到现住址省级编码变量。此公式表示取变量“现住址编码”左边两位数值（见图1-8）。

同理，用同一个公式“=left（现住址编码,4）”和“=left（现住址编码,6）”可以获得现住址市、现住址县的编码变量。

图1-8　地区变量整理过程

以此类推，可以根据变量“报告地区”生成新变量“报告地省”“报告地市”和“报告地县”，根据变量“户籍地址变量”生成新变量“户籍地省”“户籍地市”和“户籍地县”。

E.整理感染途径及职业分类

原始的感染途径及职业分类变量，分类较细，不便于数据分析，需要进一步合并分类进行精简，可以用“替换”功能实现。

感染途径整理：“复制”变量“感染途径”的整列单元格，鼠标右键，选择 “插入复制的单元格”，重命名为“感染途径整理”；选中“感染途径整理”整列变量，选择“替换”功能，将“性接触 + 注射毒品”替换为“注射毒品”，将“同性传播”“异性传播”替换为“性传播”，将“采血（浆）”、“不详”“输血/血制品”和“职业暴露”替换为“其他”（见图1-9）。

图1-9　感染途径整理过程

职业分类整理：同理，用“替换”功能，根据变量“人群分类”生成新变量“职业分类整理”，其中“保育员及保姆”“餐饮食品业，海员及长途驾驶员，“教师”“散居儿童”“学生”“医务人员”和“幼托儿童”，替换为“其它”；“公共场所服务员”和“商业服务”，替换为“服务行业”；“家政、家务及待业”替换为“无业”；“民工”“牧民”和“渔（船）民”，替换为“农民”。

F.标记当年新报告的HIV/AIDS病例

根据统计原则，新发现的HIV/AIDS人数：当年新发现的艾滋病感染者人数和艾滋病病人数（不包含当年转化的艾滋病病人数）。(https://www.daowen.com)

选中全部变量，根据变量“疾病名称”“终审年”和“录入年”进行排序,增加新变量“是否2021年新报告HIV/AIDS”，采用“筛选”功能，将“疾病名称”为“HIV”“终审年”为“2021”两个条件同时满足的全部记录，新变量“是否2021年新报告HIV/AIDS”中标记为“新报告HIV”。注意：“录入年”非2021年，但“终审年”为2021年的HIV病例，按照统计原则，仍然被归为2021年新报告的HIV病例（见图1-10）。

图1-10　标记新报告HIV过程

将“疾病名称”为“艾滋病”“终审年”为“2021”“录入年”为“2021”三个条件同时满足的全部记录，新变量“是否2021年新报告HIV/AIDS”中标记为“新报告AIDS”（见图1-11）。

图1-11　标记新报告ADIS

2.获得分析数据库

（1）剔除不符合统计要求的记录

常规艾滋病疫情分析时，需要分析当年新报告HIV/AIDS病例分布状况，以及累计报告病例分布状况，为了便于分析，可以整理出两个数据库，即“当年新报告数据库”和“累计报告总库”。

根据疫情分析统计规则，需要剔除不符合统计原则的记录：

“审核标志”变量中，剔除“已删除卡”和“未终审卡”，保留“已终审卡”。

“病例类型”中剔除“病原携带者” 和“疑似病例”，保留“确诊病例”和“临床诊断病例”。

“地区类别”中剔除“港澳台”和“外籍”，注意：若要统计报告的外籍病例数，此两类不需剔除。

获得分析数据库，可通过Excel透视表功能实现。选择整个数据库，鼠标点击“插入”功能键，出现了“数据透视表”模块，出现工作窗口，点击“确定”就出现一个新的空白数据透视表界面（见图1-12）。

图1-12　插入数据透视表

透视表界面分区如下所示，各功能区的操作将结合示例演示（见图1-13）。

图1-13　数据透视表界面

（2）获得当地当年新报告HIV/AIDS数据库

将报告卡编号“inf_ report_ id”拉进“数值”区域，此变量为每一条记录的唯一编码；将需要进行筛选的变量，如“地区类别”“病例类型”“审核标志”“现住址省”“现住址市”和“是否2021年新报告HIV/AIDS”等变量选中拉进“报表筛选”区域（见图1-14）。

图1-14　数据透视表筛选新报告病例过程1

筛选记录：“地区类别”变量选择“本省其他地市”“本市其他县区”“本县区”和“其他省”的记录；“病例类型”变量选择“临床诊断病例”和“确诊病例”；“审核标志”变量选择“已终审卡”，“现住址省”变量选择“53”；“现住址市”变量选择本地国标；“是否2021年新报告HIV/AIDS”选择“新报告HIV”和“新报告AIDS”（见图1-15）。

图1-15　数据透视表筛选新报告病例过程2

剔除不符合统计规则的记录后，计数项显示的统计值即为现住址为本地的2021年新报告HIV/AIDS病例数，鼠标移动到该单元格，双击左键，即可在新的工作表中显示了新报告病例全部记录（见图1-16）。

图1-16　生成新报告病例表

请注意，CSV格式的文件，如果直接保存，或者“另存”，都只能保存当前打开的工作表（如本示例中的“Sheet2”），其他工作表（如本示例中的“Sheet2”和“CARD”）将会消失。因此保存应慎重。建议新建一个Excel文件，可命名为“2021年新报告HIV/AIDS数据库-现住址统计”，将“Sheet2”中的全部记录复制到该新建文件中，以便于后续数据分析，而整理过变量的原始的“CARD”工作表保存好备用。

注意：

新报告病例数核查：将“是否2021新报告HIV/AIDS”变量拉进透视表工作框“行标签”，则可以统计出新报告HIV及AIDS的记录数，将该统计值与中国疾病预防控制信息系统“传染病监测系统”中的定时统计数据进行核对，如果数值一致，说明数据整理统计过程正确；如果数据不一致，则说明相关变量的剔除过程有误，需进一步检查核对（见图1-17）。

图1-17　数据透视表查看新报告病例数

（3）获得当地累计HIV/AIDS数据库

同理，剔除“审核标志”中变量中的“未审核卡”和“已删除卡”，剔除“病例类型”变量中的“病原携带者”和“疑似病例”，即可获得当地的累计HIV/AIDS病例总记录。新建一个Excel文件，可命名为“截至2021年累计HIV/AIDS数据库”，将“Sheet2”中的全部记录复制到该新建文件中保存，以便于后续数据分析（见图1-18）。

图1-18　数据透视表查看新报告病例数

3.数据库使用原则

累计HIV/AIDS数据库：统计历年死亡数据，统计现存活病例三间分布等。举例：统计1989～2020年历年死亡病例，可以用2021年1月下载的截至2020年底的历史卡片进行统计。

请注意：使用2021年1月下载的历史卡片，根据报告年份直接统计历年每年新报告病例相关数据，这是最常见的错误统计方式，请高度重视，避免此错误做法。

当年新报告HIV/AIDS数据库：统计当年新报告病例三间分布。举例：统计2011～2020年每年新报告病例传播途径，必须用2011～2020年每年下载备份的年度历史卡片数据库（共有10个历史卡片）整理出10个新数据库统计（见图1-19）。

图1-19　历年CARD数据库合并