Merge data by time stamp

[#1]

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.

[#2]

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?

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

[#3]

---

@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.

[#4]

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

Thanks for the help.

[#5]

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,

M

[#6]

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

[#7]

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 ;)

[#8]

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.

[#9]

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.

[#10 · Accepted solution]

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?