title: Pandas Preprocessing: Labelling time series data using 2 other files
body: <p>I am trying to categorise time series data using categories from another file.
  I have uploaded pictures of the three files, the photos are linked below.</p>
  <p>This has been the bane of my life for the past couple of days.</p>
  <p>I am trying to create a labeled dataset for behavior recognition. I have 2 CVS files to help me label my CSV file.</p>
  <h2>1. Data</h2>
  <p><a href="https://i.stack.imgur.com/4bm8F.png" rel="nofollow noreferrer">Here</a> is what the data frame I need to label looks like. Data was collected 40 times a second, for months. I also have all this data for different individuals, so I have split them into each individual and then into files with 1000000 rows. The CSV file originally had separate columns for date, hour, second, decimal second but I made this into one timestamp, in the hope that I could use this as an index and then do something like:</p>
  <pre><code>df['Label'].iloc[starttime:endtime] = &quot;Grooming&quot;
  </code></pre>
  <p>I was thinking about using unix timestamps as an index also. The dtype of timestamp is object.</p>
  <h2>Files to use in labeling</h2>
  <h3>2.  Behavioural States</h3>
  <p><a href="https://i.stack.imgur.com/O31n2.png" rel="nofollow noreferrer">Here</a> is what the file with each label looks like. So we can see grooming starts at 11:26:48 and ends at 11:28:32. However, there are large gaps in the data from when the animal was not being observed. So we cannot always take the end time from below. This is where our final dataframe comes in.</p>
  <h2>3. Start and end times of observations.</h2>
  <p><a href="https://i.stack.imgur.com/AqBNR.png" rel="nofollow noreferrer">Here</a> is our final dataframe. We have the start and end times of each focal observation, giving us the boundaries for our looping.</p>
  <h2>What I have tried.</h2>
  <p>I initially thought that having nested for loops would be the way to go. Making a copy of the timestamp data from the file that needs labelling, making a labels column then setting it to 0.</p>
  <p>I was then going to get the start and end times from file 3, then while the time in file 2 is between those times, for each activty in file 2, set the label column of my series.</p>
  <p>I was going to use the times like an index as shown above, however since file 1 is 40hz, I cannot just append some zeros onto my timestamps from files 2 and 3 as there may not be a reading for the start of each second. I know <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.between_time.html" rel="nofollow noreferrer">pandas has a between time function</a>, however this would require me to split my data into each day and I am not sure how to do this.</p>