Multiplying multidimensional multiindex dataframe with single index dataframe over time

user_15631890 0
Viewed 74

I am new to Python and looking for help to multiply 2 dataframes over time. Any help to understand the error would be highly appreciated.

First DataFrame (cov)

Date                                 NoDur         Durbl           Manuf
2018-12-27     NoDur                 0.000109      0.000112        0.000118
               Durbl                 0.000112      0.000339        0.000238
               Manuf                 0.000118      0.000238        0.000246
2018-12-28     NoDur                 0.000109      0.000113        0.000117
               Durbl                 0.000113      0.000339        0.000239
               Manuf                 0.000117      0.000239        0.000242
2018-12-31     NoDur                 0.000109      0.000113        0.000118
               Durbl                 0.000113      0.000339        0.000239
               Manuf                 0.000118      0.000239        0.000245

Second DataFrame (w)

Date           NoDur         Durbl           Manuf
2018-12-27     -69.190732    -96.316224      -324.058486    
2018-12-28     -113.831750   30.426696       -410.055587
2018-12-31     -101.365016   -16.613136      -362.232014

the code:

std = np.dot(np.transpose(w) , np.matmul(cov , w))

the error:

ValueError: matmul: Input operand 1 has a mismatch in its core dimension 0, with gufunc signature (n?,k),(k,m?)->(n?,m?) (size 12361 is different from 10)

I only show small extracts from the dataframes. The original cov dataframe is 123610 rows × 10 columns, the w dataframe 12361 rows × 10 columns.

Expected output:

Date           
2018-12-27     44.45574103083
2018-12-28     46.593367859
2018-12-31     45.282932300

Many thanks in advance!!

pandas dataframe numpy python
1 Answers

I think you can use groupby on the 'Date' level and then multiply the weights in w corresponding to the date in the group:

cov.groupby(level='Date').apply(lambda g: w.loc[g.name].dot(g.values@(w.loc[g.name])))

As your data really is better represented by a three dimensional array you could also avoid the implicit loop over the groups in apply and use np.einsum:

reshaped = cov.values.reshape(cov.index.levels[0].nunique(), cov.index.levels[1].nunique(), cov.shape[-1])
np.einsum('ik,ik->i', w.values, np.einsum('ijk,ik->ij', reshaped, w.values))

Performance-wise the second solution appears better:

%timeit cov.groupby(level='Date').apply(lambda g: w.loc[g.name].dot(g.values@(w.loc[g.name])))
4.74 ms ± 614 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%timeit np.einsum('ik,ik->i', w.values, np.einsum('ijk,ik->ij', reshaped, w.values))
35.6 µs ± 5.19 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)

Data:

cov = pd.DataFrame.from_dict({'NoDur': {('2018-12-27', 'NoDur'): 0.000109,
  ('2018-12-27', 'Durbl'): 0.000112,
  ('2018-12-27', 'Manuf'): 0.000118,
  ('2018-12-28', 'NoDur'): 0.000109,
  ('2018-12-28', 'Durbl'): 0.000113,
  ('2018-12-28', 'Manuf'): 0.000117,
  ('2018-12-31', 'NoDur'): 0.000109,
  ('2018-12-31', 'Durbl'): 0.000113,
  ('2018-12-31', 'Manuf'): 0.000118},
 'Durbl': {('2018-12-27', 'NoDur'): 0.000112,
  ('2018-12-27', 'Durbl'): 0.000339,
  ('2018-12-27', 'Manuf'): 0.000238,
  ('2018-12-28', 'NoDur'): 0.000113,
  ('2018-12-28', 'Durbl'): 0.000339,
  ('2018-12-28', 'Manuf'): 0.000239,
  ('2018-12-31', 'NoDur'): 0.000113,
  ('2018-12-31', 'Durbl'): 0.000339,
  ('2018-12-31', 'Manuf'): 0.000239},
 'Manuf': {('2018-12-27', 'NoDur'): 0.000118,
  ('2018-12-27', 'Durbl'): 0.000238,
  ('2018-12-27', 'Manuf'): 0.000246,
  ('2018-12-28', 'NoDur'): 0.000117,
  ('2018-12-28', 'Durbl'): 0.000239,
  ('2018-12-28', 'Manuf'): 0.000242,
  ('2018-12-31', 'NoDur'): 0.000118,
  ('2018-12-31', 'Durbl'): 0.000239,
  ('2018-12-31', 'Manuf'): 0.000245}})

 w = pd.DataFrame.from_dict({'NoDur': {'2018-12-27': -69.190732,
  '2018-12-28': -113.83175,
  '2018-12-31': -101.365016},
 'Durbl': {'2018-12-27': -96.316224,
  '2018-12-28': 30.426696,
  '2018-12-31': -16.613136},
 'Manuf': {'2018-12-27': -324.058486,
  '2018-12-28': -410.055587,
  '2018-12-31': -362.232014}})
user_104665560
Related