title: reconstructing compressed dataframe information
body: <p>I am collecting binary data using a pygame-based monitoring system which collects state data with a resolution close to but not exactly 0.2seconds. The object will be either on (1) or off (0) and will be monitored one hour, at the end of which  18,000 data-points will have been collected.</p>
  
  <p>My problem is that Students will be reading this dataset using excel, so although it may be considered as a small dataset, viewed in excel it's simply overwhelming. I need this dataset to remain easily viewable and understandable (ie: when in csv form) so when I say I wish to compress dataframe size, I don't mean reduce its filesize using a general file-compression algorithm.</p>
  
  <p>Most of the time the state of the object will be off. Meaning that perhaps 95% of my data-points will be unchanged from one time-point to the next. When there is a state-change to 'on', the 'on' state is usually going remain for much more than just 0.2 seconds.</p>
  
  <p>Here is a short example of a typical dataframe, you can see that from the action column I am easily able to calculate the total time that something remains in an 'on' (or off) state, and matlotlib does a good job at visualising this data using a bar chart. But my issue is that a real the dataframe has a huge number of rows each for every 0.2 second timepoint (approximately).</p>
  
  <p>I thought that I could reduce the dataframe size considerably by just recording the actions which differ from the last time-point. This does reduce the dataframe size, but it now complicates how the data is interpreted. For example, when plotting the new dataset, matplotlib doesn't know how long an 'on' state lasts and neither does my getOnStatePeriods function which is unable to properly measure the time-span for an on state. Please run the code below and look at the original data 'df', then look at my attempt to reduce the dataset size with 'dfSmall'.</p>
  
  <p>This dataset isn't what I would consider to be a timeseries, since pygame is forcing the samples to be made at approximately 0.2 seconds and not EXACTLY at 0.2 seconds. </p>
  
  <p>I'm comfortable with the techniques I'm using to measure on/off states using the full dataset, it just seems so inefficient to store all the timepoint events when nothing is changing. Maybe there is a better compression technique I should be using? Also, it seems that I am forced to use a bar plot rather than a simple 'plot' because a simple plot is giving me diagonal transitions...</p>
  
  <p>Any help is appreciated.</p>
  
  <pre><code>import pandas as pd
  import numpy as np
  import io
  import matplotlib.pyplot as plt
  
  try:
      # for Python2
      from cStringIO import StringIO 
  except ImportError:
      # for Python3
      from io import StringIO
  
  def getOnStatePeriods(df):
      mask = df['action']==0 #mask is True for specified event
      mask[0] = True # maybe worth setting 1st element in event to zero, or: mask[0] = True
      df.loc[mask,'step1'] = df.loc[mask,'time']
      df['step2'] = df['step1'].fillna(method='ffill')
      df['step3'] = df['time']-df['step2']
      df['step4'] = df['step3'].shift(1)
      df.loc[mask,'step5'] = df.loc[mask,'step4']
      df['step6'] = df['step5'].replace(0, np.nan)
      df['step7'] = df['step6'].shift(-1)
      df.rename(columns={'step7': 'actionTime'}, inplace=True)
      longDf = df # Make one detailed longDf and one concise df:
      df = df[['time','action','actionTime']]
      return df
  
  
  df = pd.read_csv(StringIO('''
  time,action
  .203,0
  .401,0
  .605,1
  .802,1
  1.001,0
  1.201,0
  1.403,1
  1.606,1
  1.803,1
  2.004,0
  2.201,0
  2.407,0
  '''.strip()))
  
  dfSmall = pd.read_csv(StringIO('''
  time,action
  .203,0
  .605,1
  1.001,0
  1.403,1
  2.004,0
  '''.strip()))
  
  df = getOnStatePeriods(df) #df based on the ORIGINAL large dataframe
  dfSmall = getOnStatePeriods(dfSmall) # df containing only times of state changes
  
  fig, axes = plt.subplots(4,1, figsize=(6, 6), sharex=True)
  axes[0].set_title("Original df")
  axes[0].bar('time','action',data=df, color='red', align='edge', width=0.2)
  axes[1].plot('time','action',data=df, color='red', alpha=0.5)
  axes[2].set_title("'dfSmall' - where only state changes are recorded.")
  axes[2].bar('time','action',data=dfSmall, color='blue', width=0.2)
  axes[3].plot('time','action',data=dfSmall, color='blue', alpha=0.5)
  
  plt.tight_layout()
  plt.show()
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Jgz7d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Jgz7d.png" alt="My attempt at compressing a dataframe"></a></p>