Viewing and Exporting Histograms

[Marcie]

I have several histograms set up to evaluate our process performance based on product criteria.  It works great, except for two things...  Is there a way I can hide histograms like I can with signals and conditions?  I want 14 histograms in my analysis, but with them all displayed at once, it's impossible to see the data.  Secondly, is there a way to export the histogram data to Excel (or anything Excel can manage)?  Right now I am trying to mouse over each bin result and record it, but that is very time consuming.

[Lindsey.Wilcox]

Hi Marcie-

In response to these 2 things:

1. Yes, we are aware that the "dimming" option does not work for histograms.  This is logged in our system as CRAB-13860.  In the meantime, before this item is addressed, I would recommend using Journal links; if you trend each histogram one at a time and add a link to Journal, in the future you will just be able to click through the Journal links to view the histograms.  For more information on adding links to Journal, please refer to this Knowledge Base article: https://seeq12.atlassian.net/wiki/spaces/KB/pages/162201610/Inserting+Seeq+Links

2. Currently the values in a Histogram cannot be exported to Excel.  However, this item is also logged in our system as CRAB-8959.

If you would like to be linked to either of these items (CRAB-13860 and CRAB-8959) please email support@seeq.com; this will ensure that you are updated as we make progress towards their implementation.

Thanks,

Lindsey

[Thorsten Vogt]

Hi Marcie, 

for exporting the data of a Histogram you can use the Seeq REST API / Seeq Server SDK to develop a custom solution. I created a "quick and dirty" sample based on C# for demonstration:

using OfficeOpenXml;
using Seeq.Sdk.Api;
using Seeq.Sdk.Client;
using Seeq.Sdk.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Histogram2Xls
{
    class Program
    {

        private static ApiClient apiClient { get; set; }

        static void Main(string[] args)
        {
            ConnectToSeeq();

            FormulaRunOutputV1 output = GetDataFromSeeq();

            ExportToExcel(output);
        }


        private static void ConnectToSeeq()
        {

            apiClient = new ApiClient(string.Format("{0}api", "http://<your Seeq Server>:34216/"));

            AuthApi authApi = new AuthApi(apiClient);
            AuthInputV1 input = new AuthInputV1();
            input.Username = "<your Seeq Username>";
            input.Password = "<your Seeq Password>";

            authApi.Login(input);
        }

        private static FormulaRunOutputV1 GetDataFromSeeq()
        {
            FormulaRunInputV1 input = new FormulaRunInputV1();
            input.Function = "6ED15A0C-0FBB-4D22-A0A3-D32AF43CC904";
            input.Parameters = new List<string>{
                "condition2=140CB04E-3DC3-4363-8D49-4D950A47FDDA",
                "signalToAggregate=0955829B-BBDC-4130-8204-ABAAB469BDBE"
            };
            input.Fragments = new List<string>{
                "viewCapsule=capsule('2019-04-22T00:00:00Z', '2019-04-29T00:00:00Z')"
            };

            FormulasApi formulasApi = new FormulasApi(apiClient);
            FormulaRunOutputV1 output = formulasApi.RunFormula_0(input);
            return output;
        }

        private static void ExportToExcel(FormulaRunOutputV1 output)
        {
            List<DataItem> items = new List<DataItem>();

            foreach (List<Object> objList in output.Table.Data)
            {
                DataItem item = new DataItem();

                item.Day = Convert.ToInt32(objList[0]);
                item.Stage = objList[1].ToString();
                item.Value = Convert.ToDouble(objList[2]);

                items.Add(item);
            }


            ExcelPackage excelPackage = new ExcelPackage(new System.IO.FileInfo("D:\\Temp\\Histogram.xlsx"));

            ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add("Data");

            int row = 1;
            workSheet.Cells[row, 1].Value = "Day of week";
            workSheet.Cells[row, 2].Value = "OFF";
            workSheet.Cells[row, 3].Value = "STAGE 1";
            workSheet.Cells[row, 4].Value = "STAGE 2";
            workSheet.Cells[row, 5].Value = "TRANSITION";

            List<int> days = items.OrderBy(x => x.Day).Select(x => x.Day).Distinct().ToList();

            foreach (int day in days)
            {
                row++;
                List<DataItem> itemsForDay = items.Where(x => x.Day == day).ToList();
                workSheet.Cells[row, 1].Value = day;
                foreach (DataItem item in itemsForDay)
                {

                    int colIdx = GetColIdx(item.Stage);
                    workSheet.Cells[row, colIdx].Value = item.Value;
                }

            }


            excelPackage.Save();
        }

        private static int GetColIdx(string stage)
        {
            if (stage == "OFF")
                return 2;
            if (stage == "STAGE 1")
                return 3;
            if (stage == "STAGE 2")
                return 4;
            if (stage == "TRANSITION")
                return 5;
            return -1;
        }

    }
    
    public class DataItem
    {
        public int Day { get; set; }
        public string Stage { get; set; }
        public double Value { get; set; }
    }
}

This sample is very static and exports the data of a specific histogram. The configuration is made in the function GetDataFromSeeq():

input.Function takes the ID of the Histogram
input.Parameters takes a list of parameters that the histogram uses for calculation
input.Fragments takes the list of FormulaParameters

All this information can be retrieved from the properties of 
 - the histogram
 - the signal used for calculation
 - the condition used for grouping

The resulting Excel file looks like this:

As mentioned before this is just a sample which is very static and needs some adjustments regarding your needs.

Regards,

Thorsten

[Kristopher Wiggins]

To build on what Thorsten sent, below is a Python adaptation of his code. Rather than manually specify the ids required, this information is pulled from the first two histograms displayed on a worksheet. The next few steps in the code divide the two histograms, makes a graph of the histogram using the plotly or matplotlib library, and exports the results to a csv file. Note: This code was developed based on Seeq R53.3.0. Its common for Seeq to edit its SDK to enable new features so this code may not work for other versions of Seeq. Using the Seeq Python module (SPy) is the only certain way to ensure scripts will work across versions.

workbench_url = "https://explore.seeq.com/workbook/BE0673EA-9DA3-49D7-BA99-33CA77405E7E/worksheet/A4F2F47E-F238-4075-9030-FFDABE34F2DF"

from seeq import sdk
from seeq import spy
import pandas as pd
formAPI = sdk.FormulasApi(spy.client)
analysis_items = spy.search(workbench_url, quiet=True)
analysis_histograms = analysis_items[analysis_items['Type']=='Chart']
analysis_histograms.reset_index(drop=True, inplace=True)
pulled_analysis = spy.workbooks.pull(spy.workbooks.search({"ID":spy.utils.get_workbook_id_from_url(workbench_url)}, quiet=True), quiet=True)
for ws in pulled_analysis[0].worksheets:
    if spy.utils.get_worksheet_id_from_url(workbench_url)==ws.id:
        display_range = ws.display_range

def hist_search(hist_id, display_range):
    hist_info = formAPI.get_function(id=hist_id)
    hist_params = [elt.name + "=" + elt.item.id for elt in hist_info.parameters if elt.name != 'viewCapsule']
#     hist_capsule = [elt.name + "=" + elt.formula for elt in hist_info.parameters if elt.name == 'viewCapsule']
    # Required since the viewCapsule in the formula function isn't always the same as the display range
    hist_capsule = ["viewCapsule=capsule(\""+display_range['Start'].strftime('%Y-%m-%dT%H:%M:%S.%fZ')+ \
    "\", \""+display_range['End'].strftime('%Y-%m-%dT%H:%M:%S.%fZ')+'")']
    output = formAPI.run_formula( function = hist_id, parameters = hist_params, fragments = hist_capsule)
    return output

def extract_hist_data(output):
    headers = [header.name for header in output.table.headers]
    hist_df = pd.DataFrame(columns=headers, data= output.table.data)
    if 'timeCol_Day Of Week' in headers:
        day_week_dict = {1:'Monday', 2:'Tuesday', 3:'Wednesday', 4:'Thursday', 5:'Friday', 6:'Saturday', 7:'Sunday'}
        hist_df['timeCol_Day Of Week'] = hist_df['timeCol_Day Of Week'].apply(lambda x:day_week_dict[int(x)])
    elif 'timeCol_Month' in headers:
        month_year_dict = {1:'January', 2:'February', 3:'March', 4:'April', 5:'May', 6:'June', 7:'July', 8:'August', 9:'September', 10:'October', 11:'November', 12:'December'}
        hist_df['timeCol_Month'] = hist_df['timeCol_Month'].apply(lambda x:month_year_dict[int(x)])
    elif 'timeCol_Quarter' in headers:
        hist_df['timeCol_Quarter'] = hist_df['timeCol_Quarter'].apply(lambda x:'Q'+str(x))
    if 'signalToAggregate' in headers[-1]:
        hist_df.rename(columns={headers[-1]:'signaltoAggregate'}, inplace=True)
        # Condition aggregations still have it shown on the backend as signalToAggregate
    hist_df.set_index(headers[:-1], inplace=True)
    return hist_df

hist_1 = extract_hist_data(hist_search(analysis_histograms.loc[0,"ID"], display_range))
hist_2 = extract_hist_data(hist_search(analysis_histograms.loc[1,"ID"], display_range))
result = hist_1.div(hist_2, axis=1)

matplotlib_fig = result.unstack().plot(kind='bar', y=result.columns[-1], stacked=False)
matplotlib_fig

import plotly.express as px
hold= result.reset_index(drop=False, inplace=False)
plotly_graph = px.bar(hold, x=hold.columns[0], y=hold.columns[-1], color=hold.columns[1], barmode='group')
plotly_graph

hist_1_csv = hist_1.rename(columns={'signaltoAggregate':analysis_histograms.loc[0,"Name"]})
hist_2_csv = hist_2.rename(columns={'signaltoAggregate':analysis_histograms.loc[1,"Name"]})
result_csv = result.rename(columns={'signaltoAggregate':(analysis_histograms.loc[0,"Name"]+"/"+analysis_histograms.loc[1,"Name"])})
result_csv.unstack().transpose().to_csv('TEST.csv')

 

 

.

1863770801_DivideTwoHistogramsOutputtoCSV.ipynb

Edited September 8, 2021 by Kristopher Wiggins