Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Merge data by time stamp

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 31, 2017 9:20 AM
(6510 views)

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.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

15 REPLIES 15

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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

Thanks for the help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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

-Dave

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Merge data by time stamp

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content