将数据集中的值替换为另一个数据集中的值的有效方法

Question

我有这个密码：

for index, row in df.iterrows():
  for index1, row1 in df1.iterrows():
    if df['budget'].iloc[index] == 0:
        if df['production_companies'].iloc[index] == df1['production_companies'].iloc[index1]
            and df['release_date'].iloc[index].year == df1['release_year'].iloc[index1] :
                df['budget'].iloc[index] = df1['mean'].iloc[index1]

这是可行的，但要花很长时间才能完成。我怎样才能让它跑得更快？ 我也试过：

df.where((df['budget'] != 0 and df['production_companies'] != df1['production_companies']
    and df['release_date'] != df1['release_year']),
        other = pd.replace(to_replace = df['budget'],
            value = df1['mean'],  inplace = True))

应该快一点，但不行。我如何做到这一点？ 谢谢您！你知道吗

df看起来像这样：

budget; production_companies;   release_date    ;title    
0;  Villealfa Filmproduction Oy ;10/21/1988;    Ariel    
0;  Villealfa Filmproduction Oy ;10/16/1986;    Shadows in Paradise   
4000000;    Miramax Films;  12/25/1995; Four Rooms   
0;  Universal Pictures; 10/15/1993; Judgment Night   
42000;  inLoops ;1/1/2006;  Life in Loops (A Megacities RMX)   
...          

和df1：

production_companies;   release_year;   mean;
Metro-Goldwyn-Mayer (MGM);  1998;   17500000    
Metro-Goldwyn-Mayer (MGM);  1999;   12500000   
Metro-Goldwyn-Mayer (MGM);  2000;   12000000   
Metro-Goldwyn-Mayer (MGM)   ;2001   ;43500000  
Metro-Goldwyn-Mayer (MGM);  2002    ;12000000  
Metro-Goldwyn-Mayer (MGM)   ;2003;  36000000  
Metro-Goldwyn-Mayer (MGM);  2004    ;27500000   
...   

如果年份和生产公司相同，我想用来自df的“mean”vealue替换来自df1的值0。你知道吗

Answer 1

您可以使用前面的if语句快速删除一个循环：

for index, row in df.iterrows():
  if df['budget'].iloc[index] == 0:
      for index1, row1 in df1.iterrows():
        if df['production_companies'].iloc[index] == df1['production_companies'].iloc[index1] and df['release_date'].iloc[index].year == df1['release_year'].iloc[index1] :
            df['budget'].iloc[index] = df1['mean'].iloc[index1]

Answer 2

除去所有的循环，您可以通过合并有效地完成这一点。这里我提供了一些示例数据，因为您提供的所有数据实际上都不会合并。如果df中的release_date不是日期时间，则需要确保它是日期时间。你知道吗

import pandas as pd
import numpy as np
df = pd.DataFrame({'budget': [0, 100, 0, 1000, 0],
                   'production_company': ['Villealfa Filmproduction Oy', 'Villealfa Filmproduction Oy',
                                      'Villealfa Filmproduction Oy', 'Miramax Films', 'Miramax Films'],
                   'release_date': ['10/21/1988', '10/18/1986', '12/25/1955', '1/1/2006', '4/13/2017'],
                   'title': ['AAA', 'BBB', 'CCC', 'DDD', 'EEE']})

df1 = pd.DataFrame({'production_companies': ['Villealfa Filmproduction Oy', 'Villealfa Filmproduction Oy', 
    'Villealfa Filmproduction Oy', 'Miramax Films', 'Miramax Films'],
                'release_year': [1988, 1986, 1955, 2006, 2017],
                   'mean': [1000000, 2000000, 30000000, 4000000, 5000000]})

df['release_date'] = pd.to_datetime(df.release_date, format='%m/%d/%Y')

#   budget           production_company release_date title
#0       0  Villealfa Filmproduction Oy   1988-10-21   AAA
#1     100  Villealfa Filmproduction Oy   1986-10-18   BBB
#2       0  Villealfa Filmproduction Oy   1955-12-25   CCC
#3    1000                Miramax Films   2006-01-01   DDD
#4       0                Miramax Films   2017-04-13   EEE

然后，如果生产公司和年份匹配，您需要将预算值为0的地方替换为平均值。作为合并，这是：

df.loc[df.budget==0, 'budget'] = (df.merge(df1, left_on=['production_company', 
    df.release_date.dt.year], right_on=['production_companies', 'release_year'], how='left')
    .loc[df.budget==0, 'mean'])

#     budget           production_company release_date title
#0   1000000  Villealfa Filmproduction Oy   1988-10-21   AAA
#1       100  Villealfa Filmproduction Oy   1986-10-18   BBB
#2  30000000  Villealfa Filmproduction Oy   1955-12-25   CCC
#3      1000                Miramax Films   2006-01-01   DDD
#4   5000000                Miramax Films   2017-04-13   EEE

如果没有给定生产公司和年份的mean数据，budget中的0将替换为np.NaN，因此您可以保留它们，也可以根据需要将它们替换回0。你知道吗

Answer 3

不要对此任务使用循环

熊猫的主要优点是矢量化的功能。你知道吗

矢量化计算的一种方法是对齐索引，然后使用pd.DataFrame.index.map。要提取年份，首先需要转换为datetime。你知道吗

来自@ALollz的数据。你知道吗

# convert release_date to datetime and calculate year
df['release_date'] = pd.to_datetime(df['release_date'])
df['year'] = df['release_date'].dt.year

# create mapping from df1
s = df1.set_index(['production_companies', 'release_year'])['mean']

# use map on selected condition
mask = df['budget'] == 0
df.loc[mask, 'budget'] = df[mask].set_index(['production_company', 'year']).index.map(s.get)

print(df)

#      budget           production_company release_date title  year
# 0   1000000  Villealfa Filmproduction Oy   1988-10-21   AAA  1988
# 1       100  Villealfa Filmproduction Oy   1986-10-18   BBB  1986
# 2  30000000  Villealfa Filmproduction Oy   1955-12-25   CCC  1955
# 3      1000                Miramax Films   2006-01-01   DDD  2006
# 4   5000000                Miramax Films   2017-04-13   EEE  2017