Unstack Time Series Data in Origin 2024b

Origin 2024b (released on 5/15/2024) enhances data manipulation of time series data. This blog will focus on the Unstack by Time Interval feature which is the process of converting/reshaping long-format (XY) data into wide-format data (XYXYXY…), particularly when the wide-format structure is organized based on time intervals.

This tool is found in the Restructure menu, and today we will show how to use the new features using a sample data set (Sea Level Honolulu.csv). We packaged this file right into Origin 2024b, so you can play around with the feature right away.

Convert Long Format Data to Wide Format Data

  1. Import Sea Level Honolulu.csv from file path \Samples\Data Manipulation\. This file contains 43494 rows of Time vs. Sea Level data, which is called long format. If plotted like this, it will just show year after year, making it hard to compare one year to another.
  2. To organize it as wide format data, select Restructure: Unstack by Time Interval: Open Dialog.
  3. The long-format data will be unstacked (split) into XYXY pairs for each year with a Year row added to identify each interval.
  4. Time column shows in MMM dd format, e.g. Jan 2. If you’d like to show in MM/dd format, e.g. 01/02, reopen the dialog (click a green lock then Change Parameters…) and set the Output Date/Time Column Format at the bottom of the dialog to MM/dd by typing it in.
  5. Ctrl+A to select the whole sheet and choose Plot: Browser: Stack Lines to plot a graph. Note that you may browse through all of the plots (one from each Y column) using the left panel browser. However, only one plot is displayed at a time.
  6. In the left panel, right click on Short Name header and check Year. This adds a column to the browser displaying the information contained in the Year row.
  7. Single click on the legend to bring up the Mini Toolbar and click the Data Plot Legend Translation Mode button, then choose Year. This will update the source of information displayed in the legend.
  8. Double click on the bottom axis tick labels to bring up the Axis Dialog and change Display to Apr (month only). Click OK.
  9. Click once on the bottom axis and use the red dot on ends of axis to adjust the To and From so they display one whole year. Watch the black data display module at the bottom of the screen, but ignore the specific year it displays.
  10. Your graph should now look like this:
  11. Ctrl+Click to select some random years or Shift+Click to select a range of years on the left to compare yearly data. You can quickly make a custom selection like one plot every ten years by clicking the down arrow on the left and choosing Select Every 10th.
  12. Single click on legend and choose Attach to Plot button from the Mini Toolbar. Now, the legend is distributed to align with each plot, making it easy to locate the corresponding year.
  13. To compare summer months only, again click on bottom axis and use the red dots to adjust the scale.
  14. You can also deselect stacking in the drop-down menu to accurately compare the value changes. E.g., the following graph has Stacked Lines by Y Offset unchecked and shows every 20th Year Data (via Select every Nth… menu). You can clearly see that the summer sea level in 2005 was much higher than in 1905.

Control Start Value and End Value of the Interval

Depending on the study, you may want to unstack your data into different intervals (e.g., start from July) or a subrange of intervals (e.g., only Dec to Feb). Use the Start Value and End Value controls to define the interval range.

Suppose Time Interval is Year, here are some examples

  • Set Start Value as Jul, 7, or 7/1 and leave End Value as <optional> to get yearly data from July to next June (length of whole year).
  • Set Start Value as Jul, 7, or 7/1 and set End Value as <end> to get yearly data from July to Dec (end of the year).
  • Set Start Value as Dec, 12 or 12/1 and End Value as Feb, 2, or 2/28 to get winter data from Dec to Feb. Note: 2/29 data are excluded since it only exists in a leap year.

When Start Value and End Value are specified, Start and End parameter rows will show in the output.

Note: If you want to restrict the unstacking to a range of years you may use Column Filter feature in Origin to limit the range.

Additional Labels in Output

Origin creates metadata/label rows to display information about the parameters used when unstacking by time. These rows are displayed at the top of the output sheet and may include year, month, week, day, etc. For example, in the example above, the Year row displays 1905, 1906, etc.

Click the green lock and Change Parameters…, then change the Time Interval to be Month. Click OK and the Year row will change to become Month row, filled with the corresponding data. Origin prefixes it with the year info. e.g. 1905-Jan.

Let’s say you want the Month data to only show the month without the year, e.g. Jan, so it’s later on it is easier to select all the Jan data. That is possible with the Additional Labels Row(s) field in the parameters dialog to add additional rows and modify the default Interval display.

The syntax is LabelName1 = <date/time format> |LabelName2 = <date format>|…. See the examples in blue text within the dialog window.

If the LabelName matches the Interval name, then it will be used to modify the default interval display.

E.g., if the Time Interval is Month, enter Year = yyyy | Month = MMM and click OK. Now a Year row shows a four-digit year number and the original Month row changed from displaying the full month name to the 3-letter month name.

If you enter Year = yyyy only, it will simply add the Year row without not modifying the original Month row.

Or enter Year= yyyy | Month= MM | Month, Year= MMM, yyyy to add two extra rows, Year and Month, Year, as well as modifying Month to show only month number.

2 Comments on “Unstack Time Series Data in Origin 2024b”

  1. This is awesome! Exactly what I have been looking for. Yes, I could do this before, but it was soooo cumbersome and you added some fantastic features. Well done team Originlab, yet again!

Leave a Reply

Your email address will not be published. Required fields are marked *