forming json data for d3 for stackable and nested charts

Question

I have json data that looks like this:

[{"DateText":"Apr 2016","Agent":"wade","Talk":2},
{"DateText":"Apr 2016","Agent":"winop4","Talk":0},
{"DateText":"Apr 2016","Agent":"amy","Talk":2},
{"DateText":"Apr 2016","Agent":"GARY","Talk":0},
{"DateText":"Apr 2016","Agent":"kmahoney","Talk":0}]

I need it to look like this for d3:

["DateText":"Apr 2016", "Agent":"wade","Talk":2}, "Agent":"winop4","Talk":0}, 
"Agent":"amy","Talk":2}, "Agent":"GARY","Talk":0}, 
"Agent":"kmahoney","Talk":0}
"DateText":"May 2016", "Agent":"wade","Talk":5}, 
"Agent":"winop4","Talk":200}, "Agent":"amy","Talk":400}, 
"Agent":"GARY","Talk":900}, "Agent":"kmahoney","Talk":3}]

I would like to do this in JavaScript or even in d3. can anyone offer examples of good ways to do this?

Your question is a little broad. Are you looking for a SQL solution, C# solution, frontend JavaScript solution? For example, [here's an answer](http://stackoverflow.com/a/10404455/16363) detailing a dynamic sql pivot that would solve your problem. — Mark, Apr 28 '17 at 12:54
sorry, hoping not to have to pivot tables in sql. that's my last resort — keepTrackOfYourStack, Apr 28 '17 at 12:56
Well that's kinda the problem with your question and why it'll probably eventually be closed. What sort of solution are you looking for? I can think of 3 different ways off the top of my head. Which way is `the best way`, is a matter of opinion and off-topic for Stack Overflow. — Mark, Apr 28 '17 at 14:33
For me the best option would be to handle it in d3 or jquery. But since I use a DataContext I could even deal with it tn c# What I would like to avoid is a dynamic query even though I realize it is a possible solution. As stated in the question, it is not easy for me to maintain a pivot query for each stacked chart I might need. — keepTrackOfYourStack, Apr 28 '17 at 14:51

score 0 · Answer 1 · answered Apr 28 '17 at 21:53

I think you want to make your date of the main iterators, and then be able to access those agents.

[{
    "DateText": "Apr 2016",
    [{
      "Agent": "wade",
      "Talk": 2
    }, {
      "Agent": "winop4",
      "Talk": 0
    }, {
      "Agent": "amy",
      "Talk": 2
    }, {
      "Agent": "GARY",
      "Talk": 0
    }, {
      "Agent": "kmahoney",
      "Talk": 0
    }]
  }, {
    "DateText": "May 2016",
    [{
        "Agent": "wade",
        "Talk": 5
      }, {
        "Agent": "winop4",
        "Talk": 200
      }, {
        "Agent": "amy",
        "Talk": 400
      }, {
        "Agent": "GARY",
        "Talk": 900
      }, {
        "Agent": "kmahoney",
        "Talk": 3
      }
    }
  ]
}]

keepTrackOfYourStack · Accepted Answer · 2017-05-06T17:46:58.793

I did it this way and it does most of what I need without 'messy' sql.

  // all agents in set
  List<object> ops = m.GroupBy(x => x.Agent).ToList()
  .ConvertAll(c => (object)new { Agent = c.First().Agent});
  list = m
  .GroupBy(x => string.Format(@"""DateText"": {0}", x.DateText ))
  .ToDictionary(x => x.Key, x => x.ToList()
  .ConvertdictionaryAll(c => (object)new { Datetext = c.DateText,  Agent 
  c.Agent, Talk = c.Talk  })));
  list.Add("keys", ops);

The keys for the Agents are in the last key of the dictionary.

forming json data for d3 for stackable and nested charts

2 Answers2