topic Re: Merge data by time stamp in Discussions

Merge data by time stamp

mmcclusk0 — Tue, 31 Jan 2017 17:20:02 GMT

I have a data set with a Column of Date/Time (m/d/yy h:m:s) and two colums of data.

I am trying to move/combine/correct the numbers in each data colum to match the Date Time stamp.

Here is a sample of two rows that shold have the columns combined:

Notice the time stamps are the same, yet I cannot get them to combine correctly on the same row.

I have about 5K+ lines of data, but I work with more, and this type of thing all the time.

I am sure there is an easy way to get this to combine, but I cannot figure this out.

I am using JMP 12 and I'm not versed in JSL. If there is a step by step using the menus, that would help me.

Thanks for any help that can be provided.

Re: Merge data by time stamp

martindemel — Tue, 31 Jan 2017 17:35:33 GMT

Is it true that for each time stamp you have two rows analog to the ones in the screenshot? So there is always just one value per column per timestamp?

If so you might wanna use SUMMARY:

Data Table( "Untitled 6" ) << Summary(
	Group( :Column 1 ),
	Sum( :Column 2 ),
	Sum( :Column 3 ),
	Freq( "None" ),
	Weight( "None" )
)

Untitled 6 is the table attached with Column 1 would be your time stamp column, Column 2 and 3 the data columns.

And the result would look like this you'll see after executing the script in the table

Re: Merge data by time stamp

mmcclusk0 — Tue, 31 Jan 2017 19:05:53 GMT

@martindemel wrote:
Hi
Is it true that for each time stamp you have two rows analog to the ones in the screenshot? So there is always just one value per column per timestamp? Yes, this is true.

If so you might wanna use SUMMARY:
Data Table( "Untitled 6" ) << Summary(
	Group( :Column 1 ),
	Sum( :Column 2 ),
	Sum( :Column 3 ),
	Freq( "None" ),
	Weight( "None" )
)
Untitled 6 is the table attached with Column 1 would be your time stamp column, Column 2 and 3 the data columns.
And the result would look like this you'll see after executing the script in the table

I got your example to work, but adapting it to my data table, not so much.

Thanks.

Re: Merge data by time stamp

mmcclusk0 — Tue, 31 Jan 2017 19:14:14 GMT

I did a little playing around with the Summary function and I think I may have it.

Thanks for the help.

Re: Merge data by time stamp

MikeD_Anderson — Tue, 31 Jan 2017 22:46:25 GMT

You could also do a similar operation by using Tables > Summary. Going this route has the advantage of writing the JSL for your specific case, so you can recycle the code later if you need it.

Best,

Re: Merge data by time stamp

David_Burnham — Thu, 02 Feb 2017 15:56:59 GMT

He didn't want code, so that is an added advantage ;)

Re: Merge data by time stamp

martindemel — Wed, 08 Feb 2017 15:08:59 GMT

Oh I missed to say that the code was generated by Mike's approach. I should have mentioned the steps to get to the skript, Sorry. In the data tabel I just saved the script from the interactive work. next time I'll do better ;)

Re: Merge data by time stamp

KellyK — Tue, 14 Feb 2017 16:35:50 GMT

How would I run a similar script but not have the values of "column 2" and "column 3" added together? I would still like to have "column 1" grouped together, but would like to have the values from "column 2" and "column 3" in different columns, but the same row.

Re: Merge data by time stamp

martindemel — Fri, 17 Feb 2017 01:06:36 GMT

Hi KellyK,

may I do not get your question right, but here is the original and the resulting table as a picture::

The column "Sum(Column 2)" is only the sum of Column 2 and not of 2 and 3. The only other thing I could suggest what you mean is that you want to have the left table and the missing values filled. This will create duplicate values but keep the four rows.

Can you describe in more detail what you want to achieve, thx.

Re: Merge data by time stamp

KellyK — Fri, 17 Feb 2017 16:11:03 GMT

Sorry, I did not do a good job explaining my question. I have a table that contains two different ID tags in the same column. Each ID tag correlates with a sample value and a timestamp in the next two columns. I applied a round formula to my timestamps so that they record every 10 minutes. I would like to separate the ID tag column so that each ID has its own column associated with the timestamp. I would also like to have the sample value for the ID tag and timestamp in the same row. There are multiple timestamps with the same time so I would like to have a mean of the sample value in the row. I've split the Table by TagID and splitting the columns by TagID, SampleValue, and the rounded timestamp. I then ran the script you posted, but substituted mean for sum. The table was almost exactly what I wanted, but the mean values are off a bit since the timestamps for each tag aren't the exact same. Any ideas on how to fix this issue?

Re: Merge data by time stamp

martindemel — Wed, 22 Feb 2017 20:11:52 GMT

Hi Kelly,

I'm glad that the summary function was of help. You mentioned you calculate the mean over the rounded timestamps. therefore you get the means just for those rounded timestamps. What I'm confused about is your comment on "the means are off a bit because the timestamps do not align". What do you expect from the mean to look like? Do you have comparison values or do you mean it would be good to have a mean for every exact time stamp?

Thanks,

Martin

Re: Merge data by time stamp

KellyK — Wed, 22 Feb 2017 20:29:17 GMT

I'm happy with how the table looks. The numbers in the columns are off though. TagID 3322 does not have a timestamp at 4:10 PM, but when I do the mean function a SampleValue is in the 4:10 PM row. For the 4:20 PM timestamp, the SampleValue mean is not correct for the 3322 Tag. It averages the values for one of the 4:20 timestamps and one of the 4:30 timestamps.

Re: Merge data by time stamp

martindemel — Wed, 22 Feb 2017 21:27:45 GMT

I think I know what's going on here. The first column name is "Column 4 3317" which is not what you should have. There should be just "Column 4". Then you will see a empty value in Mean (SampleValue, 3322).
Please test the following in the table menu "Summary":
Group -> Column 4
Mean -> SampleValue
Subgroup -> TagID

Does that fix your issue?

Re: Merge data by time stamp

KellyK — Wed, 22 Feb 2017 21:38:02 GMT

That worked! Thank you!

Re: Merge data by time stamp

martindemel — Wed, 22 Feb 2017 21:41:09 GMT

Re: Merge data by time stamp

martindemel — Wed, 22 Feb 2017 21:42:04 GMT

Great :)