22、数据合并与整理：实用技巧与操作指南

数据合并与整理：实用技巧与操作指南

在数据处理过程中，数据合并和整理是常见且重要的操作。下面将详细介绍数据合并例程的开发、重复行的删除以及多对多关系的修复等内容。

1. 开发数据合并例程

在进行数据合并时，我们可以采用一种通用的方法。这里以将国家数据与全球历史气候学网络综合数据库中的位置数据进行左连接为例。

1.1 准备工作

我们的目标是将国家数据和位置数据进行左连接。

1.2 操作步骤

• 导入库并加载数据 ：

import pandas as pd
countries = pd.read_csv("data/ltcountries.csv")
locations = pd.read_csv("data/ltlocations.csv")

• 检查合并列的匹配情况 ：

def checkmerge(dfleft, dfright, mergebyleft, mergebyright):
    dfleft['inleft'] = "Y"
    dfright['inright'] = "Y"
    dfboth = pd.merge(dfleft[[mergebyleft,'inleft']],
                      dfright[[mergebyright,'inright']], left_on=[mergebyleft],
                      right_on=[mergebyright], how="outer")
    dfboth.fillna('N', inplace=True)
    print(pd.crosstab(dfboth.inleft, dfboth.inright))
    print(dfboth.loc[(dfboth.inleft=='N') | (dfboth.inright=='N')].head(20))

checkmerge(countries.copy(), locations.copy(), "countryid", "countryid")

运行上述代码后，会输出如下结果：

inright  N      Y
inleft           
N        0      1
Y        2  27472
      countryid inleft inright
9715         LQ      Y       N
13103        ST      Y       N
27474        FO      N       Y

• 合并国家和位置数据 ：

stations = pd.merge(countries, locations, left_on=["countryid"], right_on=["countryid"], how="left")
stations[['locationid','latitude','stnelev','country']].head(10)

输出结果如下：
| locationid | latitude | stnelev | country |
| — | — | — | — |
| ACW00011604 | 58 | 18 | Antigua and Barbuda |
| AE000041196 | 25 | 34 | United Arab Emirates |
| AEM00041184 | 26 | 31 | United Arab Emirates |
| AEM00041194 | 25 | 10 | United Arab Emirates |
| AEM00041216 | 24 | 3 | United Arab Emirates |
| AEM00041217 | 24 | 27 | United Arab Emirates |
| AEM00041218 | 24 | 265 | United Arab Emirates |
| AF000040930 | 35 | 3,366 | Afghanistan |
| AFM00040911 | 37 | 378 | Afghanistan |
| AFM00040938 | 34 | 977 | Afghanistan |

stations.shape

输出结果为 (27474, 7) ，这表明我们得到了左连接预期的行数，其中 27472 行的合并列值在两个数据框中都存在，2 行的合并列值仅存在于左数据框中。

1.3 工作原理

在大多数数据合并场景中，上述步骤 2 和 3 的逻辑都很有效。 checkmerge 函数增加了一个参数，允许为左右数据框指定不同的合并列。在合并之前调用该函数，可以让我们清楚不同连接类型下的预期结果，包括内连接、外连接、左连接或右连接将返回的行数，以及新缺失值的生成位置。虽然这会增加一定的计算成本，但有助于我们思考合并操作。

在步骤 3 中，我们采用了首选的语法，始终将左数据框作为第一个参数，右数据框作为第二个参数，并明确设置 left_on 和 right_on ，即使合并列相同也如此，这样在合并列不同时无需更改语法。同时，默认采用左连接，因为左数据框的行通常代表分析单元，避免因右数据框中缺少合并列值而删除分析单元的行。

下面是数据合并的流程图：

graph TD;
    A[导入库并加载数据] --> B[检查合并列匹配情况];
    B --> C[合并国家和位置数据];

2. 整理和重塑数据

在处理数据时，我们经常会遇到数据结构不规整的情况。不规整的数据通常具有以下特点：
- 合并列关系不清晰
- 一对多关系中某一侧的数据冗余
- 多对多关系导致的数据冗余
- 列名中存储值
- 一个变量值中存储多个值
- 数据未按分析单元进行结构化

为了处理这些问题，我们可以使用以下强大的工具：
- 去除重复行
- 修复多对多关系
- 使用 stack 和 melt 将数据从宽格式转换为长格式
- 融化多组列
- 使用 unstack 和 pivot 将数据从长格式转换为宽格式

3. 去除重复行

在分析单元层面可能会出现数据重复的情况，原因如下：
- 现有数据框可能是一对多合并的结果，其中“一”的一侧是分析单元。
- 数据框是重复测量或面板数据合并成的平面文件，这是第一种情况的特殊情况。
- 我们可能正在处理一个分析文件，其中多个一对多关系被扁平化，形成了多对多关系。

当“一”的一侧是分析单元时，“多”的一侧的数据可能需要进行某种形式的合并。例如，在分析大学学生的结果时，学生是分析单元，但我们可能有每个学生的课程注册数据。在进行分析之前，我们可能需要先统计每个学生的课程数量、总学分或计算 GPA，最终使每个学生只有一行数据。

3.1 准备工作

我们将使用 COVID - 19 每日病例数据，该数据每行代表每个国家每天的情况，包含当天的新增病例数、新增死亡数，以及每个国家的人口统计数据和病例与死亡的累计总数，每个国家的最后一行提供了总病例数和总死亡数。

3.2 操作步骤

• 导入库并加载数据 ：

import pandas as pd
covidcases = pd.read_csv("data/covidcases720.csv")

• 创建列列表 ：

dailyvars = ['casedate','new_cases','new_deaths']
totvars = ['location','total_cases','total_deaths']
demovars = ['population','population_density','median_age',
            'gdp_per_capita','hospital_beds_per_thousand','region']
covidcases[dailyvars + totvars + demovars].head(3).T

• 创建仅包含每日数据的数据框 ：

coviddaily = covidcases[['location'] + dailyvars]
coviddaily.shape
coviddaily.head()

• 为每个国家选择一行数据 ：

covidcases.location.nunique()
coviddemo = covidcases[['casedate'] + totvars + demovars].\
    sort_values(['location','casedate']).\
    drop_duplicates(['location'], keep='last').\
    rename(columns={'casedate':'lastdate'})
coviddemo.shape
coviddemo.head(3).T

• 对每个组的值进行求和 ：

covidtotals = covidcases.groupby(['location'], as_index=False).\
    agg({'new_cases':'sum','new_deaths':'sum','median_age':'last',
         'gdp_per_capita':'last','region':'last','casedate':'last',
         'population':'last'}).\
    rename(columns={'new_cases':'total_cases',
                    'new_deaths':'total_deaths','casedate':'lastdate'})
covidtotals.head(3).T

3.3 工作原理

COVID 数据每行代表每个国家每天的情况，但实际上只有 casedate 、 new_cases 和 new_deaths 可以视为每日数据。其他列显示的是累计病例和死亡数或人口统计数据。累计数据是冗余的，因为我们有 new_cases 和 new_deaths 的实际值。人口统计数据在每个国家的所有日期中都是相同的。

我们可以通过创建一个包含每日数据的数据框和另一个包含人口统计数据的数据框来恢复隐含的一对多关系。当需要计算各国的总数时，我们可以自己生成这些数据，而不是存储冗余数据。同时，累计总数变量并非完全无用，我们可以用它们来检查总病例数和总死亡数的计算结果。

下面是去除重复行的流程图：

graph TD;
    A[导入库并加载数据] --> B[创建列列表];
    B --> C[创建仅包含每日数据的数据框];
    C --> D[为每个国家选择一行数据];
    D --> E[对每个组的值进行求和];

综上所述，通过上述方法，我们可以有效地处理数据合并和整理过程中遇到的各种问题，提高数据的质量和可用性。在实际应用中，我们可以根据具体的数据情况选择合适的方法进行处理。

数据合并与整理：实用技巧与操作指南

4. 修复多对多关系

在数据处理过程中，我们有时需要处理由多对多合并创建的数据表。多对多关系是指合并列的值在左右两侧都存在重复。这种情况通常是由于多个一对多关系中“一”的一侧被移除导致的。

例如，我们有来自克利夫兰艺术博物馆馆藏的数据，包括创作者文件和媒体引用文件。创作者文件记录了博物馆馆藏每件物品的创作者，每件物品可能有多个创作者，因此每行代表一个创作者。引用文件记录了每件物品的引用（如报纸、新闻电台、期刊等），每件物品可能有多个引用，因此每行代表一个引用。但我们没有一个包含每件物品唯一标识符的馆藏文件，这就导致了创作者和引用数据集之间存在多对多关系。

4.1 准备工作

我们将使用克利夫兰艺术博物馆馆藏的数据，该 CSV 文件包含通过标识馆藏物品的 id 列合并的创作者和引用数据。每件物品可能有一个或多个引用和创作者的行。

4.2 操作步骤

• 导入库并加载数据 ：

import pandas as pd
cma = pd.read_csv("data/cmacollections.csv")

• 查看博物馆馆藏数据 ：

cma.shape
cma.head(2).T

同时查看 id 、 citation 和 creator 的唯一值数量：

cma.id.nunique()
cma.drop_duplicates(['id','citation']).id.count()
cma.drop_duplicates(['id','creator']).id.count()

• 展示具有重复引用和创作者的馆藏物品 ：

cma.set_index(['id'], inplace=True)
cma.loc[124733, ['title','citation','creator','birth_year']].head(14)

• 创建馆藏 DataFrame ：

collectionsvars = ['title','collection','type']
cmacollections = cma[collectionsvars].\
    reset_index().\
    drop_duplicates(['id']).\
    set_index(['id'])
cmacollections.shape
cmacollections.head()
cmacollections.loc[124733]

• 创建引用 DataFrame ：

cmacitations = cma[['citation']].\
    reset_index().\
    drop_duplicates(['id','citation']).\
    set_index(['id'])
cmacitations.loc[124733]

• 创建创作者 DataFrame ：

creatorsvars = ['creator','birth_year','death_year']
cmacreators = cma[creatorsvars].\
    reset_index().\
    drop_duplicates(['id','creator']).\
    set_index(['id'])
cmacreators.loc[124733]

• 统计有创作者出生于 1950 年之后的馆藏物品数量 ：

cmacreators['birth_year'] = cmacreators.birth_year.str.findall("\d+").str[0].astype(float)
youngartists = cmacreators.loc[cmacreators.birth_year>1950, ['creator']].assign(creatorbornafter1950='Y')
youngartists.shape[0]==youngartists.index.nunique()
youngartists

4.3 工作原理

通过将原始的多对多关系数据拆分为馆藏、引用和创作者三个 DataFrame，我们恢复了隐含的多个一对多关系。在处理数据结构变化时，需要注意某些列的含义可能会发生变化，例如在这个例子中， casedate 变成了每个国家最后一行的日期，我们将其重命名为 lastdate 。

下面是修复多对多关系的流程图：

graph TD;
    A[导入库并加载数据] --> B[查看博物馆馆藏数据];
    B --> C[展示具有重复引用和创作者的馆藏物品];
    C --> D[创建馆藏 DataFrame];
    D --> E[创建引用 DataFrame];
    E --> F[创建创作者 DataFrame];
    F --> G[统计有创作者出生于 1950 年之后的馆藏物品数量];

总结

在数据处理过程中，数据合并、整理和重塑是非常重要的环节。我们介绍了数据合并例程的开发，包括如何检查合并列的匹配情况、选择合适的连接类型进行数据合并。同时，针对不规整数据的特点，我们学习了去除重复行、修复多对多关系等方法。

在去除重复行时，根据是否需要在合并“多”的一侧数据之前进行聚合操作，我们可以选择使用 drop_duplicates 或 groupby 方法。而在修复多对多关系时，关键是识别合适的合并列，将多对多关系拆分为多个一对多关系。

通过这些方法，我们可以有效地处理各种数据问题，提高数据的质量和可用性，为后续的数据分析和挖掘工作打下坚实的基础。在实际应用中，我们需要根据具体的数据情况灵活选择合适的方法进行处理。

以下是不同数据处理方法的总结表格：
| 处理方法 | 适用场景 | 关键操作 |
| — | — | — |
| 数据合并 | 需要将多个数据框根据合并列进行连接 | 检查合并列匹配，选择合适连接类型（如左连接） |
| 去除重复行 | 分析单元层面存在数据重复 | 根据是否需要聚合选择 drop_duplicates 或 groupby |
| 修复多对多关系 | 数据存在多对多关系 | 识别合并列，拆分多对多关系为一对多关系 |

希望这些方法能帮助你更好地处理数据，解决实际工作中遇到的问题。