Create a pandas column based on a lookup value from another dataframe

Question

I have a pandas dataframe that has some data values by hour (which is also the index of this lookup dataframe). The dataframe looks like this:

In [1] print (df_lookup) 
Out[1] 0     1.109248
       1     1.102435
       2     1.085014
       3     1.073487
       4     1.079385
       5     1.088759
       6     1.044708
       7     0.902482
       8     0.852348
       9     0.995912
       10    1.031643
       11    1.023458
       12    1.006961
       ...
       23    0.889541

I want to multiply the values from this lookup dataframe to create a column of another dataframe, which has datetime as index. The dataframe looks like this:

In [2] print (df)
Out[2] 
Date_Label           ID  data-1  data-2    data-3
2015-08-09 00:00:00  1   2513.0    2502     NaN  
2015-08-09 00:00:00  1   2113.0    2102     NaN  
2015-08-09 01:00:00  2   2006.0    1988     NaN  
2015-08-09 02:00:00  3   2016.0    2003     NaN 
...
2018-07-19 23:00:00  33  3216.0    333      NaN  

I want to calculate the data-3 column from data-2 column, where the weight given to 'data-2' column depends on corresponding value in df_lookup. I get the desired values by looping over the index as follows, but that is too slow:

for idx in df.index:
   df.loc[idx,'data-3'] = df.loc[idx, 'data-2']*df_lookup.at[idx.hour]

Is there a faster way someone could suggest?

Answer 1

Using .loc

df['data-2']*df_lookup.loc[df.index.hour].values
Out[275]: 
Date_Label
2015-08-09 00:00:00    2775.338496
2015-08-09 00:00:00    2331.639296
2015-08-09 01:00:00    2191.640780
2015-08-09 02:00:00    2173.283042
Name: data-2, dtype: float64
#df['data-3']=df['data-2']*df_lookup.loc[df.index.hour].values

Answer 2

I'd probably try doing a join.

# Fix column name
df_lookup.columns = ['multiplier']

# Get hour index
df['hour'] = df.index.hour

# Join
df = df.join(df_lookup, how='left', on=['hour'])
df['data-3'] = df['data-2'] * df['multiplier']
df = df.drop(['multiplier', 'hour'], axis=1)