1

I have last column and i am trying to get the max and min for 15minutes. By executing this code. But i am unable to include Type for this code. Because i have many types inside the Type column. I get this Error.

Timestamp        Last           Max            Type
1/20/19 12:15    3071.56                       Ada
1/20/19 12:17    3097.82                       Btc
1/20/19 12:17    3097.82                       Ada
1/20/19 12:18    3095.25                       Ada
1/20/19 12:19    3087.42                       Btc
1/20/19 12:20    3095.29                       Btc
1/20/19 12:21    3095.25                       Btc
1/20/19 12:22    3093.11                       Btc
1/20/19 12:23    3103                          Btc
1/20/19 12:24    3095                          Btc
1/20/19 12:25    3100.6                        Btc
1/20/19 12:26    3099.84                       Ada
1/20/19 12:27    3098.77                       Ada
1/20/19 12:29    3097.24                       Ada
1/20/19 12:29    3090          3103            Ada
1/20/19 12:30    3090          3103            Ada
1/20/19 12:31    3094.29       3103            Ada

I would like to get a column with Ada_max and Btc_max

This is the code i have to calculate Max of Last column for 15minutes.

df['Prev15minMax'] = df['Last'].rolling('15min', min_periods=16).max()
df['Prev15minMin'] = df['Last'].rolling('15min', min_periods=16).min()

Code Tried:

for v in df['Type'].unique():
    df['Prev15minMax_{v}'] = df.loc[df['Type'].eq(v), 'Last_new'].rolling('15min', min_periods=15).max()

Error i get:

ValueError                                Traceback (most recent call last)
<ipython-input-6-43ab5f7d0bc2> in <module>()
      1 
      2 for v in df['Type'].unique():
----> 3     df['Prev60minMax_{v}'] = df.loc[df['Type'].eq(v), 'Last'].rolling('60min', min_periods=60).max()

/usr/local/lib/python3.6/site-packages/pandas/core/frame.py in __setitem__(self, key, value)
   3114         else:
   3115             # set column
-> 3116             self._set_item(key, value)
   3117 
   3118     def _setitem_slice(self, key, value):

/usr/local/lib/python3.6/site-packages/pandas/core/frame.py in _set_item(self, key, value)
   3189 
   3190         self._ensure_valid_index(value)
-> 3191         value = self._sanitize_column(key, value)
   3192         NDFrame._set_item(self, key, value)
   3193 

/usr/local/lib/python3.6/site-packages/pandas/core/frame.py in _sanitize_column(self, key, value, broadcast)
   3361 
   3362         if isinstance(value, Series):
-> 3363             value = reindexer(value)
   3364 
   3365         elif isinstance(value, DataFrame):

/usr/local/lib/python3.6/site-packages/pandas/core/frame.py in reindexer(value)
   3353                     # duplicate axis
   3354                     if not value.index.is_unique:
-> 3355                         raise e
   3356 
   3357                     # other

/usr/local/lib/python3.6/site-packages/pandas/core/frame.py in reindexer(value)
   3348                 # GH 4107
   3349                 try:
-> 3350                     value = value.reindex(self.index)._values
   3351                 except Exception as e:
   3352 

/usr/local/lib/python3.6/site-packages/pandas/core/series.py in reindex(self, index, **kwargs)
   3320     @Appender(generic._shared_docs['reindex'] % _shared_doc_kwargs)
   3321     def reindex(self, index=None, **kwargs):
-> 3322         return super(Series, self).reindex(index=index, **kwargs)
   3323 
   3324     def drop(self, labels=None, axis=0, index=None, columns=None,

/usr/local/lib/python3.6/site-packages/pandas/core/generic.py in reindex(self, *args, **kwargs)
   3683         # perform the reindex on the axes
   3684         return self._reindex_axes(axes, level, limit, tolerance, method,
-> 3685                                   fill_value, copy).__finalize__(self)
   3686 
   3687     def _reindex_axes(self, axes, level, limit, tolerance, method, fill_value,

/usr/local/lib/python3.6/site-packages/pandas/core/generic.py in _reindex_axes(self, axes, level, limit, tolerance, method, fill_value, copy)
   3701             obj = obj._reindex_with_indexers({axis: [new_index, indexer]},
   3702                                              fill_value=fill_value,
-> 3703                                              copy=copy, allow_dups=False)
   3704 
   3705         return obj

/usr/local/lib/python3.6/site-packages/pandas/core/generic.py in _reindex_with_indexers(self, reindexers, fill_value, copy, allow_dups)
   3804                                                 fill_value=fill_value,
   3805                                                 allow_dups=allow_dups,
-> 3806                                                 copy=copy)
   3807 
   3808         if copy and new_data is self._data:

/usr/local/lib/python3.6/site-packages/pandas/core/internals.py in reindex_indexer(self, new_axis, indexer, axis, fill_value, allow_dups, copy)
   4412         # some axes don't allow reindexing with dups
   4413         if not allow_dups:
-> 4414             self.axes[axis]._can_reindex(indexer)
   4415 
   4416         if axis >= self.ndim:

/usr/local/lib/python3.6/site-packages/pandas/core/indexes/base.py in _can_reindex(self, indexer)
   3557         # trying to reindex on an axis with duplicates
   3558         if not self.is_unique and len(indexer):
-> 3559             raise ValueError("cannot reindex from a duplicate axis")
   3560 
   3561     def reindex(self, target, method=None, level=None, limit=None,

ValueError: cannot reindex from a duplicate axis

1 Answer 1

1

Fiter by rows in both sides:

for v in df['Type'].unique():
    mask = df['Type'].eq(v)
    df.loc[mask, f'Prev15minMax_{v}'] = (df.loc[mask,'Last_new']
                                           .rolling('15min',min_periods=15)
                                           .max())
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks life saver :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.