代码之家 › 专栏 › 技术社区 › Joseph P Nardone

正在检测对数据帧的所有更改

compare dataframe pandas python

Joseph P Nardone · 技术社区 · 4 年前

我期待创建一个程序,将比较两个数据帧,并检测每个数据帧所做的更改。这是一个版本控制应用程序,其中一个原始文件已被用户操作,我试图提取他们对原始文件所做的更改。

尝试的简单解决方案:

import pandas as pd 


# Initalize the data
data_original = [['4', 'NYC','New York'], ['3', 'BOS','Boston'], ['2', 'CHI','Chicago']]
data_new = [['4', 'NYC','New York','50'], ['3', 'Boston','Boston','100'], ['2', 'CHI','Chicago','20'], ['8', 'LA','Los Angeles','30']] 

# Create the dataframes
df_original = pd.DataFrame(data_original, columns = ['Office Number', 'Office Name','Office Location'])
df_new = pd.DataFrame(data_new, columns = ['Office Number', 'Office Name','Office Location','Money'])

df_changes = df_new[ ~df_new.isin(df_original)].dropna()

['8', 'LA','Los Angeles','30']

这个简单的解决方案不是我想要的,因为它没有检测到数据帧第二项中“BOS”到“Boston”的变化。我要找的东西,将表明即使一行中的单个项目已被更改。我可以分别计算出列的加法或减法,但是如何检测诸如“BOSTON”到“BOSTON”之类的元素变化呢?

3 回复 | 直到 4 年前

piRSquared 4 年前

def compare(old, new):
    new_cols = new.columns.difference(old.columns)
    del_cols = old.columns.difference(new.columns)
    new_indx = new.index.difference(old.index)
    del_indx = old.index.difference(new.index)

    # Now that we've checked new and deleted rows and columns
    # `align` the dataframes and check the values
    old, new = old.align(new, 'inner')

    I, J = np.where(old.ne(new))
    c = old.columns
    r = old.index

    changes = pd.DataFrame([
        [r[i], c[j], old.iat[i, j], new.iat[i, j]]
        for i, j in zip(I, J)
    ], columns=['Row', 'Column', 'Old', 'New'])

    return changes, new_cols, del_cols, new_indx, del_indx

示范

获取更改数据

changes, new_cols, del_cols, new_indx, del_indx = compare(df_original, df_new)

print(f"""\
New Columns:
{' '.join(new_cols.astype(str))}

Deleted Columns:
{' '.join(del_cols.astype(str))}

New Rows:
{' '.join(new_indx.astype(str))}

Deleted Rows:
{' '.join(del_indx.astype(str))}

Changes:
{changes}
""")

New Columns:
Money

Deleted Columns:


New Rows:
3

Deleted Rows:


Changes:
   Row       Column  Old     New
0    1  Office Name  BOS  Boston

_______________________________________________________

更简单的解决方案

我们可以放弃查找添加和删除的列和行的细分,而只解释 changes

def compare(old, new):
    old, new = old.align(new)  # Notice I don't use `'inner'` as I did before

    I, J = np.where(old.ne(new))
    c = old.columns
    r = old.index

    changes = pd.DataFrame([
        [r[i], c[j], old.iat[i, j], new.iat[i, j]]
        for i, j in zip(I, J)
    ], columns=['Row', 'Column', 'Old', 'New'])

    return changes

compare(df_original, df_new)

   Row           Column  Old          New
0    0            Money  NaN           50
1    1            Money  NaN          100
2    1      Office Name  BOS       Boston
3    2            Money  NaN           20
4    3            Money  NaN           30
5    3  Office Location  NaN  Los Angeles
6    3      Office Name  NaN           LA
7    3    Office Number  NaN            8

在本例中,唯一的更改由 'Old'

_______________________________________________________

如果你有 np.nan

但是,如果一个数据帧 None 另一个有 . 我将把它作为练习留给未来的读者。

def compare(old, new):
    old, new = old.align(new)

    I, J = np.where(old.ne(new))
    c = old.columns
    r = old.index

    data = []
    for i, j in zip(I, J):
        n = new.iat[i, j]
        o = old.iat[i, j]
        if pd.notna(n) or pd.notna(o):
            data.append([r[i], c[j], o, n])

    return pd.DataFrame(data, columns=['Row', 'Column', 'Old', 'New'])

Jason Chia 4 年前

也许这就是你需要的?

df_changes = df_new[ ~df_new[["Office Number","Office Name","Office Location"]].apply(tuple,1).isin(df_original[["Office Number","Office Name","Office Location"]].apply(tuple,1))].dropna()

不知道现在是否还需要dropna()。您可以将列设置为元组,使其不可变,并且可以跨键进行比较。用你的测试数据测试过了,我想是可行的。

ALollz 4 年前

如果沿着指数进行比较 那我们需要两个 reindexlike True . 生成的数据帧是哪里 df_new df_original .

m = (df_new.reindex_like(df_original)
           .ne(df_original)
           .reindex_like(df_new)
           .fillna(True))

   Office Number  Office Name  Office Location  Money
0          False        False            False   True
1          False         True            False   True
2          False        False            False   True
3           True         True             True   True

# Can slice to see changes
df_new[m]
  Office Number Office Name Office Location Money
0           NaN         NaN             NaN    50
1           NaN      Boston             NaN   100
2           NaN         NaN             NaN    20
3             8          LA     Los Angeles    30