excel - 使用 Pandas 在 Python 中复制 Excel 索引匹配

Question

我有一个经常更新的 Excel 电子表格(每天 2-3 次)。此更新需要运行索引匹配以从另一个电子表格的表中提取值并将它们写入第一个电子表格中的列。这些值会覆盖旧值，而不是创建新列。

我想使用 pandas 自动化这个过程(和 xlwings 将数据写入电子表格，但我对那部分没有任何问题)。第一步是用 pandas 复制 excel 的 INDEXMATCH()。总的来说，该功能应该:

接受的参数是要索引的列的字符串标题、要覆盖的列以及包含用于匹配读取和写入列的值的列

向下迭代写列；在每次迭代中，在读取列中搜索其对应的匹配列值与写入列的匹配列值匹配的值

如果没有匹配值，则将 NaN 或 '#N/A' 写入数据帧(区分 0 和不匹配很重要)

我希望在 pandas 中有一个原生的 vlookup/indexmatch 功能，但我唯一能找到的就是加入或合并数据帧，这不是我想要做的——我想覆盖数据帧中的各个值，并以任意索引顺序执行此操作。

我已经设法让它与一个非常难看的特定于脚本的函数一起工作，但我认为尝试将函数推广到其他用途会很有用。经过一些清理和重写后，我得到了以下内容:

##Index Match in Python with pandas
#Remember that dataframes start at 0, excel starts at 1
#This only works if both DFs have the same indices (integers, strings, whatever)
import numpy as np
import pandas as pd

#sample dataframes
d = {'Match Column' : [0.,1.,2.,3.,4.,7.,'string'],
     'Read Column' : ['zero','one','two','three','four','seven','string']}

dfRead = pd.DataFrame(d)

d2 = {'Match Column' : [0.,1.,2.,3.,4.,5.,6.,7.,'8'],
      'Write Column' : [0,0,0,0,0,0,0,0,'0']}

dfWrite = pd.DataFrame(d2)

#test arguments
ReadColumn = 'Read Column'
WriteColumn = 'Write Column'
ReadMatchColumn = 'Match Column'
WriteMatchColumn = 'Match Column'

def indexmatch(dfRead, dfWrite, ReadColumn, WriteColumn, ReadMatchColumn, WriteMatchColumn, skiprows=0):
#convert the string inputs to a column number for each dataframe
    RCNum = np.where(dfRead.columns == ReadColumn)[0][0]
    WCNum = np.where(dfWrite.columns == WriteColumn)[0][0]
    RMCNum = np.where(dfRead.columns == ReadMatchColumn)[0][0]
    WMCNum = np.where(dfWrite.columns == WriteMatchColumn)[0][0]

    for i in range(skiprows,len(dfWrite.index),1):
        match = dfWrite.loc[dfWrite.index[i]][WMCNum] #the value we're using to match the columns    
        try:
            matchind = dfRead.index[np.where(dfRead[ReadMatchColumn] == match)[0][0]]
            value = dfRead.fillna('#N/A').loc[matchind][RCNum] #replaces DF NaN values with excel's #N/A, optional method
            dfWrite.set_value(dfWrite.index[i],WriteColumn,value)
        except KeyError:
            dfWrite.set_value(dfWrite.index[i],WriteColumn,np.nan) #if there is no match, write NaN to the 'cell'
        except IndexError:
            dfWrite.set_value(dfWrite.index[i],WriteColumn,np.nan)

这可行，但它并不漂亮，并且它不考虑何时要将列与另一个数据框的索引匹配(例如，将数据框与数据透视表数据框匹配)。

有没有更健壮、更简洁的方法来做到这一点？

根据要求，预期的输入和输出:

In [2]: dfRead
Out[2]: 
  Match Column Read Column
0            0        zero
1            1         one
2            2         two
3            3       three
4            4        four
5            7       seven
6       string      string

In [3]: dfWrite
Out[3]: 
  Match Column Write Column
0            0            0
1            1            0
2            2            0
3            3            0
4            4            0
5            5            0
6            6            0
7            7            0
8            8            0

In [4]: indexmatch(dfRead, dfWrite, 'Read Column', 'Write Column', 'Match Column', 'Match Column')
In [5]: dfWrite
Out[7]: 
  Match Column Write Column
0            0         zero
1            1          one
2            2          two
3            3        three
4            4         four
5            5          NaN
6            6          NaN
7            7        seven
8            8          NaN

Answer 1

pd.Series.map将 Series 作为参数，将其视为以索引为键的字典。

应用在这里，看起来像

dfWrite['Write Column'] = dfWrite['Match Column'].map(dfRead.set_index('Match Column')['Read Column'])

dfWrite
Out[409]: 
  Match Column Write Column
0            0         zero
1            1          one
2            2          two
3            3        three
4            4         four
5            5          NaN
6            6          NaN
7            7        seven
8            8          NaN

给出相同的输出

indexmatch(dfRead, dfWrite, 'Read Column', 'Write Column', 'Match Column', 'Match Column')

dfWrite
Out[413]: 
  Match Column Write Column
0            0         zero
1            1          one
2            2          two
3            3        three
4            4         four
5            5          NaN
6            6          NaN
7            7        seven
8            8          NaN

匹配 dfRead 的索引，跳过 .set_index(...)步。匹配 dfWrite 的索引, 替换 dfWrite['Match Column'].map与 dfWrite.index.to_series().map