Creating a timeline from timestamps

[kourosh kalayehParticipant]

Hi,

I have bunch of timestamps that I am trying to make timeline from. The start and end of the events are both in the same column. Using mask command I have successfully extracted the start and end times but they are not continuous columns (i.e., there are empty rows in between as a result of masking), see the screenshot. What is the easiest way to make these two new columns (start and end) continuous with the same size?

Thanks
Kourosh

[dgteamModerator]

There are two ways you could do this.  One uses a Pivot command and the other uses the indices in the data table.

Here is a the Pivot example.  

1 – Move the index column you created to the left of the time stamps.

2 – Select all three columns using the column headers.

3 – Click the Pivot command shortcut

4 – Click the gear menu on top right of the Pivot command and select “Extract all pivot columns”.

You will see something like this.

By default the command will order by category name.  If you want to specify the order, use the settings in the Pivot command.

For example, there is a built-in setting for sorting based on the sum.  This will also change the order of the output in the data table.

One thing to keep in mind is that if you have a lot of categories, the Pivot command may be slow to draw.  Then you can click the Hide or Exclude button on the command.  Then it will still do the data manipulation but it won’t slow down the program.

Here is the second approach that only uses data columns.

Use an expression to refer to the odd (#*2-1) and even (#*2) indices for the column.

If you also wanted to show the category, could map it but you need a unique ID column for the text.  You can do that dynamically using the Values menu option in the text column, then use an expression to make that a separate number column showing those values.

Here is what is would look like.

The second group column is created using a Map column, to pull the text label for each row.

Hope this helps!

 

[kourosh kalayehParticipant]

Thanks, this is great.

The second method works like a charm.

For the first method, my timestamps are in this format: “Date HH:MM:SS”.  After extracting the pivot columns, I was not able to find an easy way to reformat it to HH:MM:SS, am I missing something?

Thanks,
Kourosh

[dgteamModerator]

I see what you mean.  The date is not being reported properly from the Pivot command.

We will check into that!  Thanks for letting us know.

 

[kourosh kalayehParticipant]

Great, thanks.

 

[Author]

Posts