This blog highlights some improvements in Origin 2020b that make it easy to create multiple graphs using different data filter condition on the same source worksheet. We will use data provided by the COVID Tracking Project : https://covidtracking.com/api/v1/states/daily.csv
Please first download the zip file which contains three project files. Please also download Origin2020b from www.originlab.com if you haven’t done so already.
First open covid US states.opju. This project is connected to the web data mentioned above, with COVID-19 cases recorded up to 4/19/2020. There are 6 graphs showing historical daily percent increase in cases in 6 different US states as column plots, with the columns colored by the test positivity rate. As is shown in the video below, you can click the tool bar button Imported All Connected Data to get data from the web and see the graphs updated to show the current status for these 6 states.
Explanation on Key Features Employed in this Project
The following features were used in this project:
- Copy Columns – We select some of the imported data and duplicate them into a new book for further processing. This allows us to keep the connected web data sheet unchanged, and thus be independent of our data manipulations.
- Locking Filter Condition on the Copy Columns sheet. This feature was actually introduced in Origin 9.1. What is new in 2020b is to show the filter condition on the output sheet, and the ability to then show this on a graph.
Simple Exercise About Duplicating a Book and Switching to a Different State
Next we will walk you through the process to start from a graph on one US state, and make more copies so that we can examine other states.
1. First open the project covid 1 state.opju which contains two workbooks. The Source workbook contains the source data, a filter is added to col(B) that only shows the daily data for the state of Arkansas (AK). Four columns in the Source book are copied to Book2 by Copy Column To so they are linked with the source book. Two new columns are calculated from the copied data and are used to generate the data for the embedded graph. If you click on the green locks on the column header, you see that Worksheet Filters: Locked is checked.
Activate Book2, click the Duplicate button in toolbar to make a copy of Book2. When a book with an embedded graph is duplicated, Origin will check if the data in the graph are entirely from that book, and if so, data in the duplicated graph will be all switched to the data in the newly-duplicated book, which is Book3 in this case. Because the book is duplicated, the filter condition is the same for the two workbooks.
2. Go back to Source workbook, change the Filter condition on col(B) to TX. Since the two Copy Columns output books have their filters locked, the data and graphs stay the same.
3. Now let’s apply this new filter condition to our duplicated graph, which is Book3. Click on that book and click on the green lock on the column header and select Worksheet Filters: Reload.
4. Filter condition is updated on Book3 and graph also gets updated.
Note: The text in the graph is also updated. Double-click on the text to check that the syntax for the state is %(1,@LF,A), which refers to the filter condition on col(A). This syntax is newly-added in Origin 2020b.
5. Repeat the steps above to generate workbooks to view the daily cases for other states. Save the project once finished. As shown at the beginning of the blog, you can open the project at any time and click Imported All Connected Data button to check the current data.
Learning How to Make the Original Graph
You may ask at this point on why bother to use filter locking on the output sheet from Copy Columns, why not just add a filter on this output sheet? It is certainly more straightforward to add filter on each output sheet, but we are showing this filtering on the source and locking of the output for the following two benefits:
- Avoids data duplication, as only the small amount of data from the filter result is duplicated.
- In our example, we need to use cell formula to compute percent increase, which requires dividing the previous day’s value by today’s. Whenever a cell formula needs to reference data on a different row, using a data filter renders that problematic.
You can watch this YouTube video to learn the details on how the covid 1 state.opju was made, and how the calculations were done.
Visualizing New Cases and Testing Status on a US Map
Next you can open up the 3rd file in the zip, COVID States Google Map.opju. There is button on the top right of the graph Last 30 Days, you can click this button to see an animation, as this file was made on April 19, you can see the daily percentage increase was much higher in March.
You can also go ahead to click that toolbar button mentioned above to bring in new data, and hopefully you will see the situation in the US has been improved.
The button Last 30 Days basically run the following LabTalk script:
int nDays = 10;
win -o Source {
for(int ii=nDays; ii>=0; ii--) {
wks.col1.FILTER$="x=col(A)[1]-$(ii)";
wks.runfilter();run -p au;
sec -p 0.5;
}
}
Using Substitution Notation on Graph Label
One detail worth mentioning on this map graph is how the date was shown and gets updated in the animation, which you can click to see it as
Covid-19 Test Positivity and Case Increase Rate by US states for %(Source,@WL,A[$(Source!wks.GetNextVisibleRow(0))], D)
The first part is the substitution notation to refer to the Source book’s cell value. The basic syntax of this notation is as follows:
%([Book]Sheet, @WL, Column[Row], Formatting)
When referring to the active sheet, you can directly use the book’s short name without using square brackets. Like in our case, can be just %(source, @WL, A[1],D), where ‘D’ is one of the formatting characters, and they are the same as those used in the numeric to text substitution, the $() notation. In our case we want to show the value as the default date format, so the ‘D’ character is used.
Next we come to the tricky issue about data filtering in the source book. So if we just use %(source, @WL, A[1],D) then we are not going to get the actual value since first row may have been filtered out. What we need is the row that we can see, essentially the first row not being filtered, and there is LabTalk function to get that, namely wks.GetNextVisibleRow. Since we need to use this as row index, the $() notation is used to evaluate this function, and since this evaluation takes place on a graph, we need to add Source! to refer to our worksheet.
Converting from US State Names to Coordinates
Another thing that needed explanation is how the bubbles for different states are placed on the google map. A web data source was used to map state capitals to longitudes and latitudes, which you can see in the the book name States. It has a CSV data connector mapped to that web data source. Book1 was constructed in similar way as we discussed above using Copy Columns to select the needed data from the book name Source, which connects to historical states data on the web. New columns with column formula were added to generate the daily increase of cases and test positivity rates. The X Y coordinates were then obtained using column formula with lookup function using the two letter state names to map from the States book for the longitudes and latitudes.
Hope the info provided here is useful.
English 
Japanese