Python DataFrame to multiply two time series data frame

Question

I have two data frames. First data frame have one year energy with a time interval of 30 mins. Second data frame is one day price data with 30mins.

How can I multiply two data frames to get the result that 2nd dataframe was repeated multiplied on everyday of the first data frame?

Thank you so much!

Show us a sample of your input data and desired output data. — John Zwinck, Aug 19 '17 at 01:30
Welcome to SO! Please review the following so that others may be better able to help you. https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Alexander, Aug 19 '17 at 01:43

score 0 · Accepted Answer · answered Aug 19 '17 at 02:26

You do something like merge your dataframes on time and multiple, first make sure your date is in the datetimeindex for both dataframes.

MVCE:

df1 = pd.DataFrame({'kilowatts':np.random.randint(100,1000,(2*24*365))},index=pd.date_range('2016-01-01',periods=(2*24*365),freq='30T'))
df2 = pd.DataFrame({'Dol_per_KW':np.random.rand(48)},index=pd.date_range('2016-01-01',periods = 48,freq='30T'))

Create time of day column in each dataframe to for a merge key.

df1['Time'] = df1.index.time
df2['Time'] = df2.index.time

Merge and multiply:

df_out = df1.merge(df2, on='Time',right_index=True)\
            .eval('cost = kilowatts * Dol_per_KW', inplace=False)\
            .sort_index()

Let's print 10:00 and 10:30 every day to verify.

print(df_out.loc[df_out.index.hour == 10].head(10))

Output:

                     kilowatts      Time  Dol_per_KW        cost
2016-01-01 10:00:00        187  10:00:00    0.460365   86.088217
2016-01-01 10:30:00        743  10:30:00    0.572282  425.205644
2016-01-02 10:00:00        364  10:00:00    0.460365  167.572786
2016-01-02 10:30:00        668  10:30:00    0.572282  382.284482
2016-01-03 10:00:00        170  10:00:00    0.460365   78.262016
2016-01-03 10:30:00        682  10:30:00    0.572282  390.296432
2016-01-04 10:00:00        336  10:00:00    0.460365  154.682572
2016-01-04 10:30:00        451  10:30:00    0.572282  258.099254
2016-01-05 10:00:00        215  10:00:00    0.460365   98.978431
2016-01-05 10:30:00        295  10:30:00    0.572282  168.823237

Thank you so much! It seems that it is also possible to do it with DataFrame.mul? — user8486156, Aug 19 '17 at 03:47

Python DataFrame to multiply two time series data frame

1 Answers1