9 Replies Latest reply on Nov 20, 2018 3:45 AM by Simon Runc

    New User - Combining Multiple Time Series From Multiple Files?

    nikki.solanki.0

      I am a new Tableau user trying to get some pointers in combining time-series data, would greatly appreciate any assistance.

       

      My dataset:

      • 9 different vitals collected at every 1 second from different wearable devices --> ~2.5M datapoints/vital/month. I have 4-month sample data for one user, both timeline and the number of users will grow soon.
      • Each vital is collected with timestamp + vital value + a unique counter. Each vital is stored in a separate file for a given month --> 9 files/month.
      • I'd like to plot all 9 vitals side-by-side (X-axis) along a 24-hour period (Y-axis).
      • There are gaps in data which vary by vital (depending upon device battery and other factors).

       

      Questions:

      • What is the best way to combine all data (Join, Union, something else?) to display on a single dashboard?
      • Tried to join data but that is severely limiting available data as it is ONLY taking time periods where data is available from ALL 9 devices (a problem as some device or the other always have data gaps on a daily basis).
      • Tried to plot multiple graphs w/o joining but getting an error (to plot on the same Y-axis which is time).
      • With Union, I am getting similar restriction as Join for some reason?

       

      Any pointers? Many thanks.

        • 1. Re: New User - Combining Multiple Time Series From Multiple Files?
          Simon Runc

          Hi Nikki,

           

          So one way that might help will be to use a UNION, with a twist!

           

          In the below GIF I've unioned 2 files (heart rate and blood-pressure) and then used the "Merge Fields" so that I get all the Vitals into a single measure. The Table Name/Sheet (with csv this will be the File Name) can then be used (as a dimension) to split up the different values. If the TimeStamp is also called a different name in each file, you'll also need to merge these so you get a single column of all the datetimes.

           

          Union_Vitals.gif

           

          I've attached a quick example of this plotted out in Tableau. You may also want/need to normalise the values if they are of very different scales, if you want to plot them on a single axis, or you can bring the Table Name/Sheet onto the Columns and create an axis per measure.

           

          Hope that helps,

          1 of 1 people found this helpful
          • 2. Re: New User - Combining Multiple Time Series From Multiple Files?
            nikki.solanki.0

            Simon -  I am a newbie and a student so your animation definitely helped me grasp the concept very quickly! I'll try this later today and let you know.

            • 3. Re: New User - Combining Multiple Time Series From Multiple Files?
              nikki.solanki.0

              Simon - Many thanks, the Union worked as you expected. I created different sets using TableName and that works for creating individual charts, SPO2 set example below.

               

              Union.png

              My goal is to create a series of charts as you see below. My method likely is crude and won't work I am not sure how to assign different sets/filters for each chart. Note the chart below is not accurate as only takes the subset of timeframe as I described above so only including it as an illustration.

               

              Screen Shot 2018-11-10 at 7.08.54 PM.png

              What would be the optimal way to:

              The Table Name/Sheet (with csv this will be the File Name) can then be used (as a dimension) to split up the different values?
              • 4. Re: New User - Combining Multiple Time Series From Multiple Files?
                Simon Runc

                Glad it helped.

                 

                So with regards this there are 2 ways you can go here.

                 

                On the first way (shown in the GIF) I combined both the Heart Rate and Blood Pressure measures into a single column, called values. I can then use the Table Name to split up the measures into separate charts (in my example I just brought this onto the colour shelf to get a colour per Sheet Name, but just add it to the columns to get a chart per Sheet Name)

                 

                Another way, with Unioning the data, but without merging the fields (so I get a measure for each Vital), is shown on "Option 2". Here I've used the (Tableau generates) measure names/ measure values to split the charts up (these a psudo-dimensions and measures that Tableau creates for plotting multiple measures)

                 

                If the scales of the different measures are very different you can also use this option

                 

                 

                So each chart uses it's own Axis range (or you could normalised the measures, say between 0 and 1)

                 

                Hope that helps

                1 of 1 people found this helpful
                • 5. Re: New User - Combining Multiple Time Series From Multiple Files?
                  nikki.solanki.0

                  Simon,

                   

                  Once again, thank you for your assistance and appreciate your patience and coaching.

                   

                  That worked perfectly! Thanks!

                  • 6. Re: New User - Combining Multiple Time Series From Multiple Files?
                    nikki.solanki.0

                    Seeking a bit more assistance to extend the dashboard.

                     

                    I'd like to overlay calendar (as an additional graph) to gain context of activity taking place to better interpret the vitals.

                     

                    Calendar data is Outlook export (start/end time, date, priority etc).

                     

                    While I can get independent graph of vital and calendar, can't seem to get both next to each other. Tried usual Union / Join without success.

                     

                    Any pointers?

                    • 7. Re: New User - Combining Multiple Time Series From Multiple Files?
                      Simon Runc

                      hi Nikki,

                       

                      So it depends what you mean by "next to each other"? If you literally means side by side, then you can just have 2 datasources (vitals and calendar) set up the charts you need in each and then put them side by side in a dashboard. If you want them "joined", this it depends what level you want them joined (just by day, or by time too?). If you let me know the kind of outcome you are looking for, I can hopefully advise

                      • 8. Re: New User - Combining Multiple Time Series From Multiple Files?
                        nikki.solanki.0

                        Simon - Thanks again for your offer to assist!

                         

                        Since some of my vital data have time gaps, putting them side-by-side with a dashboard may yield unexpected behavior?

                         

                        Ideally, I would like to join/union them on the continuous time scale. Using your example above, the calendar would be like any other vital (except the look-and-feel would be like Outlook or Google calendar or time blocks).

                        • 9. Re: New User - Combining Multiple Time Series From Multiple Files?
                          Simon Runc

                          So I don't think joining will work here ...a join (generally*) is looking for exact matches. So for example 01/01/2018 10:00:01 wouldn't join with 01/01/2018 10:00:02

                           

                          *for completeness you can have > and < join conditions, but this would (in this case) likely lead to duplicate rows being generated.

                           

                          So there are a couple of ways to go. The easiest is to just union the calendar, as you have with the others (it will need to be a .csv and in the same folder as the other .csvs), which (assuming the date column name in the Calendar data is different to the date/time stamp in the vitals data) would create an extra date column. You can use the Merge fields to bring these 2 date fields into a single column. Now the Calendar stuff will just be another column in the data (like vitals).

                           

                          The other way, and this might be needed for you to create a calendar style viz, would be to create a "data scaffold". This would be a list of all DateTimes available. How many you need is up to the time level you are using. For example if your data doesn't go any finer than hour, then you need a data source with every date and hour (this is pretty quick to do in Excel, Python or SQL ...to name a few)

                           

                          01/01/2018 00:00:00

                          01/01/2018 01:00:00

                          01/01/2018 02:00:00

                           

                          ...etc

                           

                          You bring this file in first and the left join every other file (on this scaffold date field). So your data may end up looking like...

                            

                          Date ScaffoldHeart RateBlood PressureCalendar Event
                          01/01/2018 00:00nullnullnull
                          01/01/2018 01:0090110null
                          01/01/2018 02:00nullnullnull
                          01/01/2018 03:00null114Hospital Visit
                          01/01/2018 04:0080nullnull

                           

                          The reason you may need this, if you want to create a calendar style view is that if your data only goes from 01/04/2018 to 30/11/2018 you'll only (with out getting super complicated) be able to create a calendar between those dates.

                           

                          Hope that makes sense?