Delete specific time window?!

Malvina_A · August 18, 2015, 1:51pm

Hello,

I am trying to work out some time - series data, and i would like to delete the time window (-5 min and + 5min) when the specific rule occurs in my data. Is it possible?

For example:

IF Freq=0 THAN I would like to delete the Time Stamp rows +/- 5 mins

Cheers and Thanks for help!

Iris · August 27, 2015, 9:38am

Hi Malvina_A,

this can be done, but to help you I would need some more details how your data looks like. My approach would be to first generate a list of rows where Freq=0 and than use this list to filter the rows.

Best, Iris

Malvina_A · August 27, 2015, 12:18pm

Hello Iris,

Thanks for help!

ok so i have the time data with 201 columns and 100000. For example it includes 6 sensors and some sort of PositionSequence column.

Time_Stamp Sensor1 Sensor2 Sensor3 Sensor4...PosSeq

02.01.2015 11:59 0,015 0,015 0,020 0,021 0

02.01.2015 12:00 0,015 0,015 0,020 0,021 0

02.01.2015 12:02 0,015 0,015 0,020 0,021 0

02.01.2015 12:04 0,015 0,015 0,020 0,021 0

02.01.2015 12:06 0,015 0,015 0,020 0,021 0

02.01.2015 12:08 0,015 0,015 0,020 0,021 0

02.01.2015 12:10 0,015 0,015 0,020 0,021 0

02.01.2015 12:12 0,015 0,015 0,020 0,021 0

02.01.2015 12:14 0,015 0,015 0,020 0,021 0

02.01.2015 12:16 0,015 0,015 0,020 0,021 1

02.01.2015 12:18 0,015 0,015 0,020 0,021 1

02.01.2015 12:20 0,015 0,015 0,020 0,021 1

02.01.2015 12:22 0,015 0,015 0,020 0,021 1

02.01.2015 12:24 0,015 0,015 0,020 0,021 2

02.01.2015 12:26 0,015 0,015 0,020 0,021 2 ....

I need to delete from Time_Stamp +/- 5 mins when the PosSeq begin 0.

So for example with the above data i need to filter only those 4 rows:

Time_Stamp Sensor1 Sensor2 Sensor3 Sensor4...PosSeq

02.01.2015 11:59 0,015 0,015 0,020 0,021 0

02.01.2015 12:00 0,015 0,015 0,020 0,021 0

02.01.2015 12:02 0,015 0,015 0,020 0,021 0

02.01.2015 12:04 0,015 0,015 0,020 0,021 0

and the rest of PosSeq 0 should stay. Is there any way to filter my data in this way?

I would really apprecieate Your help!

Cheers

Malvina

I am not yet that fit with R, thats why it can be bit problematic for me.

Malvina_A · August 31, 2015, 8:00am

I have sent here the sample of my data, but since 4 days it hasnt appear here as a comment? why is that?

Malvina_A · August 31, 2015, 8:18am

i will try again. so i have data from 6 sensors, time stamp and smthg called seq_position

Time Stamp Pos_Seq Sensor1 Sensor2 Sensor3 Sensor4 Sensor5 Sensor6

02.01.2015 11:59	1	0.15	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:01	1	0.15	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:03	1	0.15	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:05	1	0.15	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:07	1	0.15	0.15	0.15	0.14	0.15	0.15

02.01.2015 12:09	1	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:11	1	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:13	1	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:15	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:17	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:19	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:21	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:23	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:25	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:27	2	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:29	3	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:31	3	0.14	0.15	0.15	0.14	0.15	0.15
02.01.2015 12:33	3	0.14	0.15	0.34	0.31	0.15	0.15
02.01.2015 12:35	3	0.14	0.15	0.34	0.31	0.15	0.15
02.01.2015 12:37	3	0.14	0.15	0.34	0.30	0.15	0.15

		<p>So i need to filter the data when Pos_Seq starts to be 1, then from above data the rows below have to be deleted:</p>

		<p>&nbsp;</p>

		<table>
			<tbody>
				<tr>
					<td>02.01.2015 11:59</td>
					<td>1</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.14</td>
					<td>0.15</td>
					<td>0.15</td>
				</tr>
				<tr>
					<td>02.01.2015 12:01</td>
					<td>1</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.14</td>
					<td>0.15</td>
					<td>0.15</td>
				</tr>
				<tr>
					<td>02.01.2015 12:03</td>
					<td>1</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.14</td>
					<td>0.15</td>
					<td>0.15</td>
				</tr>
				<tr>
					<td>02.01.2015 12:05</td>
					<td>1</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.15</td>
					<td>0.14</td>
					<td>0.15</td>
					<td>0.15</td>
				</tr>
			</tbody>
		</table>
		</td>
	</tr>
	<tr>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td>
		<p>But the rest of Pos_Seq 1 must stay!</p>

		<p>&nbsp;</p>

		<p>Thanks for help and i hope this time the comment will go through</p>
		</td>
	</tr>
	<tr>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td>&nbsp;</td>
	</tr>
</tbody>

Iris · August 31, 2015, 9:15am

I am sorry your post was somehow caught by our spamfilter. I published them.

The solution is a little bit more complex. I made you a workflow(attached). The main difficulty is to find the interval. Than we can filter using the binner.

Take a look at the workflow and let me know if you have any further questions

Best, Iris

timefilter.zip

Malvina_A · August 31, 2015, 3:06pm

Thank You so much for help, it works great!

Just last question:

I do need to delete -5 minutes time window. However i have noticed that Pos_seq goes from 1 - 3 and than starts 1 - 2 and so on. The time stamps for the first pair 1 - 3 is different than 1-2 (might be different day etc). But i must delete the 5 minutes window at the end of each pair (and i have over 1000000 rows, with different block numbers, so it ends up sometimes at pos_seq=2 or 3 or 4 ...). Is there any way to delete for example 10 rows (which represent -5 minutes window) to the back when Pos_Seq=0 ?

Cheers

Iris · August 31, 2015, 4:48pm

Hi Malvina,

okay, so you want to delete the 10 rows before pos_seq = 0 ? or including the first occurence of pos_seq = 0?

Malvina_A · September 1, 2015, 7:39am

delete 10 rows before Pos_seq=0 so for example:

Time_Stamp Sensor1 Sensor2 Sensor3 Sensor4...PosSeq

02.01.2015 11:59 0,015 0,015 0,020 0,021 3

02.01.2015 12:00 0,015 0,015 0,020 0,021 3

02.01.2015 12:02 0,015 0,015 0,020 0,021 3

02.01.2015 12:04 0,015 0,015 0,020 0,021 3

02.01.2015 12:06 0,015 0,015 0,020 0,021 3

02.01.2015 12:08 0,015 0,015 0,020 0,021 0

02.01.2015 12:10 0,015 0,015 0,020 0,021 0

02.01.2015 12:12 0,015 0,015 0,020 0,021 0

02.01.2015 12:14 0,015 0,015 0,020 0,021 0

02.01.2015 12:16 0,015 0,015 0,020 0,021 1

02.01.2015 12:18 0,015 0,015 0,020 0,021 1

02.01.2015 12:20 0,015 0,015 0,020 0,021 1

in this case i would delete these rows:

02.01.2015 11:59 0,015 0,015 0,020 0,021 3

02.01.2015 12:00 0,015 0,015 0,020 0,021 3

02.01.2015 12:02 0,015 0,015 0,020 0,021 3

02.01.2015 12:04 0,015 0,015 0,020 0,021 3

02.01.2015 12:06 0,015 0,015 0,020 0,021 3

Thank You so much for any tipp!

Malvina_A · September 1, 2015, 7:46am

i need to delete 10 rows to the back when the first occurence of Pos_Seq=0

Iris · September 1, 2015, 8:29am

Hi

I made you another flow which works on the basis of a generated counter (for identifying rows) . It always deletes the 10 rows before 0 starts.

Best, Iris

timefilter.zip

Malvina_A · September 1, 2015, 9:04am

Thanks so much! You are great!

I guess i am bombarding You with questions, but my Java knowledge is quite bad. If you could at last help me with the code in Java snippet to get the last row in counting block?

That would be amazing!

and I am very sorry for so many questions!

Cheers