Linqpad Charting. Combination of Column and StackedColumn

Question

I am trying to do some charting in LinqPad. I have some logs from an api and i know, what api was called and if the request was cached (in our real case we resolve address with coordinates from bing api or we get addres from cache table if we have cached it)

i use this linqpad script:

var startDate = new DateTime(2019, 1,1);

var Requests = new[]
{
    new {Date=startDate, Name = "Api1", Cached=true },  
    new {Date=startDate, Name = "Api2", Cached=true },  
    new {Date=startDate, Name = "Api3", Cached=true },  
    new {Date=startDate, Name = "Api1", Cached=true },  
    new {Date=startDate, Name = "Api1", Cached=false }, 
    new {Date=startDate, Name = "Api2", Cached=false }, 
    new {Date=startDate, Name = "Api3", Cached=false }, 
    new {Date=startDate, Name = "Api1", Cached=false }, 
    new {Date=startDate.AddDays(1), Name = "Api3", Cached=true },   
    new {Date=startDate.AddDays(1), Name = "Api1", Cached=false },  
    new {Date=startDate.AddDays(1), Name = "Api2", Cached=true },   
    new {Date=startDate.AddDays(1), Name = "Api2", Cached=false },  
    new {Date=startDate.AddDays(1), Name = "Api1", Cached=true },   
    new {Date=startDate.AddDays(1), Name = "Api1", Cached=false },  
    new {Date=startDate.AddDays(1), Name = "Api3", Cached=true },   
};

Requests.GroupBy(x=>x.Date).Chart (c => c.Key)
    .AddYSeries (c => c.Count(x=>x.Name=="Api1"),name:"Api1")
    .AddYSeries (c => c.Count(x=>x.Name=="Api2"),name:"Api2")
    .AddYSeries (c => c.Count(x=>x.Name=="Api3"),name:"Api3")
    .AddYSeries (c => c.Count(x=>x.Name=="Api1" && x.Cached),name: "Api1 Cached")
    .AddYSeries (c => c.Count(x=>x.Name=="Api2" && x.Cached),name: "Api2 Cached")
    .AddYSeries (c => c.Count(x=>x.Name=="Api3" && x.Cached),name: "Api3 Cached")   
    .Dump();

That is the result:

Actually i want to have only 3, columns per day but they have to be slacked (to show in one column both total and cached values)

if i switch to SlackedColumns i have all values in one column together and is not what i want:

Any ideas, how to do it?

Update:

What i want is something like this (but i prefer, that it is grouped on date, like linqpad is doing that):

Koonce

Answer 1

I was not able to do it with linqpad, so i have used external library to get desire output in html:

So it looks like this:

void Main()
{
    var startDate = new DateTime(2019, 1,1);

    var Requests = new[]
    {
        new {Date=startDate, Name = "Api1", Cached=true },  
        new {Date=startDate, Name = "Api2", Cached=true },  
        new {Date=startDate, Name = "Api3", Cached=true },  
        new {Date=startDate, Name = "Api1", Cached=true },  
        new {Date=startDate, Name = "Api1", Cached=false }, 
        new {Date=startDate, Name = "Api2", Cached=false }, 
        new {Date=startDate, Name = "Api3", Cached=false }, 
        new {Date=startDate, Name = "Api1", Cached=false }, 
        new {Date=startDate.AddDays(1), Name = "Api3", Cached=true },   
        new {Date=startDate.AddDays(1), Name = "Api1", Cached=false },  
        new {Date=startDate.AddDays(1), Name = "Api2", Cached=true },   
        new {Date=startDate.AddDays(1), Name = "Api2", Cached=false },  
        new {Date=startDate.AddDays(1), Name = "Api1", Cached=true },   
        new {Date=startDate.AddDays(1), Name = "Api1", Cached=false },  
        new {Date=startDate.AddDays(1), Name = "Api3", Cached=true },   
    };



    var data = new Dictionary<Tuple<string, bool>,List<int>>(); 

    foreach (var val in Requests.GroupBy(x=>x.Date.ToShortDateString()))
        {
            var keyCached = Tuple.Create(val.Key, true);
            var keyNotCached = Tuple.Create(val.Key, false);

            if (!data.ContainsKey(keyCached))
            {
                data.Add(keyCached, new List<int>());
            }           
            if (!data.ContainsKey(keyNotCached))
            {
                data.Add(keyNotCached, new List<int>());
            }                       

            data[keyCached].Add(val.Count(x=>x.Cached));
            data[keyNotCached].Add(val.Count(x=>!x.Cached));            
        }



    var columns = Requests.Select(c=>c.Date.ToShortDateString());       
    var rawData= data.Select(x=>new {name =x.Key.Item1 + " " + ( x.Key.Item2 ? "Cached":"Not Cached"), stack = x.Key.Item1, data = x.Value});       
    Util.RawHtml(createHtml(columns, Newtonsoft.Json.JsonConvert.SerializeObject(rawData))).Dump();         

}

private string createHtml(IEnumerable<string> columns, string serializedData)
{
var columnsString = Newtonsoft.Json.JsonConvert.SerializeObject(columns);
var s = @"<script src=""https://code.highcharts.com/highcharts.js""></script>
<script src=""https://code.highcharts.com/modules/exporting.js""></script>
<script src=""https://code.highcharts.com/modules/export-data.js""></script>

<div id=""container"" style=""min-width: 310px; height: 400px; margin: 0 auto""></div>

<script>
Highcharts.chart('container', {

    chart: {
        type: 'column'
    },

    title: {
        text: 'Total'
    },

    xAxis: {
        categories:"+columnsString+@"
    },

    yAxis: {
        allowDecimals: false,
        min: 0,
        title: {
            text: 'Number of calls'
        }
    },

    tooltip: {
        formatter: function () {
            return '<b>' + this.x + '</b><br/>' +
                this.series.name + ': ' + this.y + '<br/>' +
                'Total: ' + this.point.stackTotal;
        }
    },

    plotOptions: {
        column: {
            stacking: 'normal'
        }
    },

    series: "+serializedData+@"
});
</script>";

return s;

}

Koonce

Answer 2

as i understood you want something like this ?

Requests.GroupBy(x => x.Date).Chart(c => c.Key)
    .AddYSeries(c => c.Count(x => x.Name == "Api1"), LINQPad.Util.SeriesType.StackedColumn, "Api1", false)
    .AddYSeries(c => c.Count(x => x.Name == "Api2"), name: "Api2")
    .AddYSeries(c => c.Count(x => x.Name == "Api3"), name: "Api3")
    .AddYSeries(c => c.Count(x => x.Name == "Api1" && x.Cached), LINQPad.Util.SeriesType.StackedColumn, "Api1 Cached", true)
    .AddYSeries(c => c.Count(x => x.Name == "Api2" && x.Cached), LINQPad.Util.SeriesType.StackedColumn, "Api2 Cached", true)
    .AddYSeries(c => c.Count(x => x.Name == "Api3" && x.Cached), LINQPad.Util.SeriesType.StackedColumn, "Ap3 Cached", true)
    .Dump();

Ruffled

Not really, i have updated my question with sample chart i have done in excel. – Koonce

in this case there is a software limitation. but you can use System.Windows.Forms.DataVisualization.Charting example – Ruffled