带有嵌套的带有匹配/更新条件的Python循环

Question

我正在用python编写一个脚本，该脚本使两个表彼此相对。如果满足条件，则脚本将对从属表进行更新。

到目前为止我的python代码：

def updatedata():

    for y in range(updatetable.shape[0]):

        for x in range(mastertable.shape[0]):

            if updatetable[y].s_date <= mastertable[x].index <= updatetable[y].e_date:

                mastertable[x].field2 = updatetable[y]. field2

                mastertable[y].field3 = updatetable[y]. field3

我也有这种迭代技术：

for index, row in mastertable.iterrows():

    print (row['Value'], index)

for index, row in updatetable.iterrows():

    print (row['field1'], row['field2'])

我正在遵循如何在VBA中编写此代码：

For x = 1 to lastrow_update

    for y = 1 to lastrow_master

        if update(x,1) <= master(y,1) and master(y,1) <= update(x,2) then

        master (y,2) = update(x,3)

我在使用python代码时遇到了错误。1）如何为“ for循环”创建两个控制变量2）如何在匹配后减少内部循环以减少运行时间

Answer 1

使用整数索引按行建立索引时，您需要.iloc：if&nbsp;updatetable.iloc[y].s_date&nbsp;<=&nbsp;mastertable.iloc[x].index&nbsp;<=&nbsp;updatetable.iloc[y].e_date:语法updatetable.iloc [y]的意思是“获取名为y的列”，在这种情况下，y应该是“ president”或您拥有列的另一个字符串。

Answer 2

考虑大熊猫的merge_asof（即，“间隔合并”）使用用于向后方向tookoffice用或等效正向leftoffice：merge_df = pd.merge_asof(value_df, pres_df, left_on='Date', right_on='tookoffice',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;suffixes=['','_'], direction='backward')merge_df = pd.merge_asof(value_df, pres_df, left_on='Date', right_on='leftoffice',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;suffixes=['','_'], direction='forward')下面是使用随机数据的示例，该镜像镜像了已发布的数据。对于以下解决方案，必须完成两件事：总统数据框的营业地点和营业地点必须进行排序；值数据框的索引应重置为将Date作为数据框的一列（设置在末尾）。数据from io import StringIOimport numpy as npimport pandas as pdtxt = '''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;president tookoffice leftoffice&nbsp; &nbsp; &nbsp; &nbsp;party0&nbsp; &nbsp; &nbsp; "Lyndon B. Johnson" "1963-11-22" "1969-01-20"&nbsp; Democratic1&nbsp; "Franklin D. Roosevelt" "1933-03-04" "1945-04-12"&nbsp; Democratic2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"Herbert Hoover" "1929-03-04" "1933-03-04"&nbsp; Republican3&nbsp; &nbsp; &nbsp; "Warren G. Harding" "1921-03-04" "1923-08-02"&nbsp; Republican4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"Barack Obama" "2009-01-20" "2017-01-20"&nbsp; Democratic'''pres_df = pd.read_table(StringIO(txt), sep="\s+", index_col=[0],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; parse_dates=['tookoffice', 'leftoffice'])pres_df = pres_df.sort_values(['tookoffice', 'leftoffice'])np.random.seed(7012018)&nbsp; &nbsp;# SEEDED FOR REPRODUCIBILITYvalue_df = pd.DataFrame({'Value': 4 + abs(np.random.randn(1765)),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'president': 'president',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'party_of_president': 'party_of_president'},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; columns=['Value', 'president', 'party_of_president'],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; index=pd.date_range('1871-01-01', '2018-01-01', freq='MS'))\&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.rename_axis('Date')value_df = value_df.reset_index()合并merge_df = pd.merge_asof(value_df, pres_df, left_on='Date', right_on='tookoffice',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;suffixes=['','_'], direction='backward')# UPDATE NEEDED COLUMNS TO ADJACENT COLUMNSmerge_df['president'] = merge_df['president_']merge_df['party_of_president'] = merge_df['party']merge_df['president'] = merge_df['president_']merge_df['party_of_president'] = merge_df['party']# CLEAN UP (IN CASE PRESIDENT DF IS NOT EXHAUSTIVE BETWEEN 1871-2018)mask = ~merge_df['Date'].between(merge_df['tookoffice'], merge_df['leftoffice'])merge_df.loc[mask, 'president'] = np.nanmerge_df.loc[mask, 'party_of_president'] = np.nan# SUBSET FIRST 4 COLUMNS AND SET INDEXmerge_df = merge_df[merge_df.columns[:4]].set_index('Date')输出print(merge_df.shape)&nbsp; &nbsp; # SAME SHAPE AS ORIGINAL value_df# (1765, 3)# FIRST 20 RECORDSprint(merge_df.head(20))&nbsp; &nbsp;#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Value president party_of_president# Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;# 1871-01-01&nbsp; 4.859688&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-02-01&nbsp; 4.309355&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-03-01&nbsp; 5.003074&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-04-01&nbsp; 4.769772&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-05-01&nbsp; 5.765133&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-06-01&nbsp; 5.408663&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-07-01&nbsp; 4.177684&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-08-01&nbsp; 5.980318&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-09-01&nbsp; 5.029296&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-10-01&nbsp; 4.604133&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-11-01&nbsp; 4.691276&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1871-12-01&nbsp; 5.387712&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-01-01&nbsp; 4.387162&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-02-01&nbsp; 4.002513&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-03-01&nbsp; 6.105690&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-04-01&nbsp; 5.604589&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-05-01&nbsp; 4.860393&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-06-01&nbsp; 4.776127&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-07-01&nbsp; 4.280952&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# 1872-08-01&nbsp; 4.886334&nbsp; &nbsp; &nbsp; &nbsp;NaN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NaN# FIRST NON-NULL VALUESprint(merge_df[~pd.isnull(merge_df['president'])].head(20))#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; president party_of_president# Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;# 1921-04-01&nbsp; 5.713479&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-05-01&nbsp; 4.542561&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-06-01&nbsp; 5.148667&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-07-01&nbsp; 4.949704&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-08-01&nbsp; 5.138469&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-09-01&nbsp; 5.797446&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-10-01&nbsp; 4.498131&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-11-01&nbsp; 4.216718&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1921-12-01&nbsp; 6.110533&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-01-01&nbsp; 5.179318&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-02-01&nbsp; 4.808477&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-03-01&nbsp; 4.466641&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-04-01&nbsp; 4.307025&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-05-01&nbsp; 4.337476&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-06-01&nbsp; 4.396854&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-07-01&nbsp; 4.391316&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-08-01&nbsp; 4.748302&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-09-01&nbsp; 5.468115&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-10-01&nbsp; 4.295268&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican# 1922-11-01&nbsp; 5.432448&nbsp; Warren G. Harding&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Republican