python - 在缺少条目的特定列变量上合并两个表/数组 (*.xlsx) 文件

问题描述

所以，我的 MacBook 上有两个要合并的 Excel 表。它们相当大。一个有维度(42912, 6)，另一个有维度(41424,7)。因此，这里有几个例子来说明它们的样子：

表 A

| Country       | Continent         |
|-------------  |---------------    |
| UK            | Europe            |
| France        | Europe            |
| Germany       | Europe            |
| USA           | North America     |
| New Zealand   | Oceania           |

表 B

| Country       | City      | Population    |
|-------------  |---------- |------------   |
| UK            | London    | 8,900,000     |
|               | Bristol   | 53,907        |
| France        | Paris     | 2,141,000     |
| USA           | New York  | 8,623,000     |
| New Zealand   | Auckland  | 1,657,000     |

您可以看到两者之间的一些差异。例如，虽然表 A 列出了 5 个国家，但表 B 缺少其中一个 - Germany。此外。表 B 有 2 个城市UK，其中表 A 只有一行。

基本上，我希望能够合并这两个表，所以它们看起来像这样：

| Country       | Continent         | City      | Population    |
|-------------  |---------------    |---------- |------------   |
| UK            | Europe            | London    | 8,900,000     |
|               |                   | Bristol   | 53,907        |
| France        | Europe            | Paris     | 2,141,000     |
| Germany       | Europe            |           |               |
| USA           | North America     | New York  | 8,623,000     |
| New Zealand   | Oceania           | Auckland  | 1,657,000     |

这两个表由Country它们共有的字符串/列合并，有效地将表 B 中剩余的相关行添加到表 A。

当表 A 有一行而表 B 没有时，这些列只是留空（Germany以 row 为例）。当表 B 在同一国家有多行时，会在表 A 中创建额外的行（UK例如）以容纳。

我真的很感激能帮助我理解如何合并这两个（假设它们每个都有超过 40k 行），而不是在 Excel 中逐行进行，这需要很长时间。

我很高兴使用 Python 或 Matlab 等，甚至更简单 - 一种在 Excel for Mac 中完成的方法。

谢谢！

标签： pythonexcelmatlabdata-analysis

解决方案

您的数据中的一个问题是缺少Country包含的行的条目Bristol。由于您要在Country键上合并，因此合并例程将不知道如何以您的表当前具有的形式合并这些行。如下所述，之前进行一些数据整理会有所帮助。

Python解决方案：

用于pandas读取两个 excel 文件并pd.merge()用于合并。pd.DataFrame.ffill()将解决上述“布里斯托尔”问题。

import pandas as pd


def xls2df_with_ffill(xls_path, fill_key):
    """
    reads xls to dataframe and fills empty `Country` entries with the values
    of the previous row.
    """
    df = pd.read_excel(xls_path)
    df.loc[:, fill_key] = df.loc[:, fill_key].ffill()

    return df


df1 = xls2df_with_ffill('stackoverflow1.xlsx', 'Country')
df2 = xls2df_with_ffill('stackoverflow2.xlsx', 'Country')

# do the merge and write to new excel file
merged_df = pd.merge(df1, df2, how='outer', on='Country')
merged_df.to_excel('stackoverflow_merged.xlsx')

MATLAB解决方案：

MATLAB > 版本 2013b 提供了数据类型，它与 Python 中table的连接目的一样方便。pd.DataFrame为了解决缺失Country值问题，MATLAB 提供了函数fillmissing. 将表与outerjoin.

table1 = xls2table_with_fillmissing('stackoverflow1.xlsx', 'Country');
table2 = xls2table_with_fillmissing('stackoverflow2.xlsx', 'Country');

% do the merge and write to new excel file
merged_table = outerjoin(table1, table2, 'Type', 'Left', ...
    'MergeKeys', true);
writetable(merged_table, 'stackoverflow_merged.xlsx')


function table = xls2table_with_fillmissing(xls_path, fill_key)
% reads xls to table and fills empty `Country` entries with the values of
% the previous row. 
    table = readtable(xls_path);
    table(:, fill_key) = fillmissing(table(:, fill_key), 'previous');
end

在这两种情况下，函数的结果都是一个如下所示的 excel 表：

如您所见，由于ffill()/的应用，行中的列fillmissing被UK填充。除此之外，它符合您的期望。CountryBristol

python - 在缺少条目的特定列变量上合并两个表/数组 (*.xlsx) 文件

问题描述

解决方案

推荐阅读