I am trying to multiply the value of the 'Data' column by the value of the cell in the same column above it, i.e. Data * Data(t-1), so have constructed a loop as below, however I am getting an error that I am not sure how to fix - is anyone please able to see where I went wrong?
df.head()
Date Data
04/01/2016 -0.466844
05/01/2016 -0.477460
06/01/2016 -0.477849
13/01/2016 -0.499518
14/01/2016 -0.491221
df['Data_sq'] = df['Data']**2
df['Data_x_Data_t1'] = ""
#data*data(t-1) column
i = 1
for i in range(1, len(df)):
df.loc[i, 'Data_x_Data_t1'] = df.loc[i-1, 'Data'] * df.loc[i, 'Data']
The KeyError: 0 that I am receiving:
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
2656 try:
-> 2657 return self._engine.get_loc(key)
2658 except KeyError:
pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
KeyError: 0
During handling of the above exception, another exception occurred:
KeyError Traceback (most recent call last)
<ipython-input-15-5d8034885c09> in <module>
6 i = 1
7 for i in range(1, len(df)):
----> 8 df.loc[i, 'Data_x_Data_t1'] = df.loc[i-1, 'Data'] * df.loc[i, 'Data']
9
10 #df.head()
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in __getitem__(self, key)
1492 except (KeyError, IndexError, AttributeError):
1493 pass
-> 1494 return self._getitem_tuple(key)
1495 else:
1496 # we by definition only have the 0th axis
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _getitem_tuple(self, tup)
866 def _getitem_tuple(self, tup):
867 try:
--> 868 return self._getitem_lowerdim(tup)
869 except IndexingError:
870 pass
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _getitem_lowerdim(self, tup)
986 for i, key in enumerate(tup):
987 if is_label_like(key) or isinstance(key, tuple):
--> 988 section = self._getitem_axis(key, axis=i)
989
990 # we have yielded a scalar ?
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _getitem_axis(self, key, axis)
1911 # fall thru to straight lookup
1912 self._validate_key(key, axis)
-> 1913 return self._get_label(key, axis=axis)
1914
1915
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _get_label(self, label, axis)
139 raise IndexingError('no slices here, handle elsewhere')
140
--> 141 return self.obj._xs(label, axis=axis)
142
143 def _get_loc(self, key, axis=None):
~/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py in xs(self, key, axis, level, drop_level)
3583 drop_level=drop_level)
3584 else:
-> 3585 loc = self.index.get_loc(key)
3586
3587 if isinstance(loc, np.ndarray):
~/anaconda3/lib/python3.7/site-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
2657 return self._engine.get_loc(key)
2658 except KeyError:
-> 2659 return self._engine.get_loc(self._maybe_cast_indexer(key))
2660 indexer = self.get_indexer([key], method=method, tolerance=tolerance)
2661 if indexer.ndim > 1 or indexer.size > 1:
pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()
pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.Int64HashTable.get_item()
KeyError: 0
Try instead of the for loop:
df['Data_x_Data_t1'] = df['Data'].shift(1).mul(df['Data'])
Ref: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.shift.html