Advertisement
jack06215

[pandas] accumulate column and reset everyday

Jun 16th, 2020
219
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 1.47 KB | None | 0 0
  1. from io import StringIO
  2. import pandas as pd
  3. import numpy as np
  4.  
  5. text = """time       price  quantity  initiator
  6. 2016-07-13 16:19:31   6.20      8000         B
  7. 2016-07-13 16:19:45   6.19      5176         S
  8. 2016-07-13 16:25:08   6.24     15000         NaN
  9. 2016-07-13 16:25:08   6.24      2847         S
  10. 2016-07-13 16:25:08   6.24     39829         B
  11. 2016-07-13 16:25:08   6.24      2398         B
  12. 2016-07-13 16:25:08   6.24      1844         NaN
  13. 2016-07-13 16:25:08   6.24      9538         S
  14. 2016-07-13 16:25:08   6.24       459         B
  15. 2016-07-13 16:25:08   6.24      1082         B
  16. 2016-07-14 16:19:31   6.20      8000         B
  17. 2016-07-14 16:19:45   6.19      5176         S
  18. 2016-07-14 16:25:08   6.24     15000         NaN
  19. 2016-07-14 16:25:08   6.24      2847         S
  20. 2016-07-14 16:25:08   6.24     39829         B
  21. 2016-07-14 16:25:08   6.24      2398         B
  22. 2016-07-14 16:25:08   6.24      1844         NaN
  23. 2016-07-14 16:25:08   6.24      9538         S
  24. 2016-07-14 16:25:08   6.24       459         B
  25. 2016-07-14 16:25:08   6.24      1082         B"""
  26. df = pd.read_csv(StringIO(text), sep='\s{2,}', engine='python', index_col=0, parse_dates=[0])
  27.  
  28. # B: add; S: subtract; NaN: do nothing
  29. """
  30. Use np.where() and notnull to create series of [-1, 0, 1]s.
  31. Multiply this series by df.quantity and use cumsum()
  32. """
  33. def accumulator(df):
  34.     initiator = np.where(df.initiator == 'B', 1, -1) * df.initiator.notnull()
  35.     return pd.DataFrame((df.quantity * initiator).cumsum(), df.index, ['acc_quantity'])
  36.  
  37. pd.concat([df, df.groupby(df.index.strftime('%Y-%m-%d')).apply(accumulator)], axis=1)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement