Histogram from a large dataset

Question

I have a dataset in csv form which contains entries in the following form:

data in mm/dd/yy format, time in 00:00:00 format, day of the week in 'Monday,...,Sunday' format, latitude, longitude

Its a large data set which deals with certain incidents being recorded in a certain city.

I am trying to plot a 2d histogram which tells us about the number of incidents happening on a certain day (say Friday) and within certain time range (say within 1800 and 2000). I am struggling with this since I don't have much experience with mathematica, especially nested lists with character data.

My attempt:

Build a (sub) data set from the original data set (called 'Data') which only contains information about day and hour.

DataTimeDay = Data[[All, {2, 3}]];
DataHourDay = Apply[{DateValue[#, "Hour"], #2} &, DataTimeDay, {1}];

This gives me a data set which looks like:

{{17, Friday}, {14, Monday}, {13, Sunday}, {10, Thursday},
{16, Friday}, {17, Thursday}, {17, Saturday}, {19, Tuesday},
{13, Wednesday}, {19, Thursday}, {12, Friday},....}

Questions:

(a) How do I plot a 2d-histogram which counts the day and hour of event? For instance it creates a 2d-bin over {hour,day}.

(b) How do I plot a 1d-histogram which counts all the events occurring on a day and a time-range, for instance {Monday,1200-1600},{Monday,1600-2000},.....

I hope this makes sense.

Answer 1

Consider abandoning old-style histograms altogether. If the hourly data is recorded in a relatively continuous manner (i.e., at least to the minute), then a "smooth histogram" (nonparametric density estimate) can better show relationships among the distribution of events among days of the week. (If you're more interested how the days of the week different in the number of events, then that is a different question. It is not clear about that in your question.)

(* Generate some (continous) hourly data for each day of the week:  0 <= hour < 24 *)
monday = 24 Mod[RandomVariate[VonMisesDistribution[1, 2], 100000], 2 π]/(2 π);
tuesday = 24 Mod[RandomVariate[VonMisesDistribution[2, 2], 100000], 2 π]/(2 π);
wednesday = 24 Mod[RandomVariate[VonMisesDistribution[3, 1/2], 100000], 2 π]/(2 π);
thursday = 24 Mod[RandomVariate[VonMisesDistribution[4, 3], 100000], 2 π]/(2 π);
friday = 24 Mod[RandomVariate[VonMisesDistribution[5, 4], 100000], 2 π]/(2 π);
saturday = 24 Mod[RandomVariate[VonMisesDistribution[5.5, 3], 100000], 2 π]/(2 π);
sunday = 24 Mod[RandomVariate[VonMisesDistribution[6, 1], 100000], 2 π]/(2 π);

Edit: the "Bounded" option I used earlier won't work well for circular data such as hour-of-the-day when even a moderate amount of the positive density is near the boundaries. So I've changed that to a better estimator.

(* Find nonparametric density for each day of the week *)
skd = SmoothKernelDistribution[Join[# - 24, #, # + 24], 
  "LeastSquaresCrossValidation"] & /@ {monday, tuesday, wednesday, 
   thursday, friday, saturday, sunday};

(* Plot resulting "smooth histograms" *)
pdfs = Table[PDF[skd[[i]], x], {i, 7}];
Plot[pdfs, {x, 0, 24}, PlotStyle -> Thickness[0.01], Frame -> True, 
 FrameLabel -> {"Hour of the day", "Probability density"}, 
 PlotLegends -> {"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"}]

And, yes, using color is not the greatest way to display given so many different lines. Thick, dotted, and dashed lines would probably be better.

Addition:

If the relative number of observations among days (along with the distribution within a day), then the following might be considered which also uses nonparametric density estimation. (And I've also include the associated histogram which is consistent with the nonparametric density estimate.)

(* Generate some (continous) hourly data for each day of the week with different numbers of samples: 0<=hour<24 *)
monday = 24  Mod[RandomVariate[VonMisesDistribution[1, 2], 5000],  2  π]/(2  π);
tuesday = 24  Mod[RandomVariate[VonMisesDistribution[2, 2], 10000], 2  π]/(2  π);
wednesday = 24  Mod[RandomVariate[VonMisesDistribution[3, 1/2], 20000], 2  π]/(2  π);
thursday = 24  Mod[RandomVariate[VonMisesDistribution[4, 3], 30000], 2  π]/(2  π);
friday = 24  Mod[RandomVariate[VonMisesDistribution[5, 4], 70000], 2  π]/(2  π);
saturday = 24  Mod[RandomVariate[VonMisesDistribution[5.5, 3], 90000], 2  π]/(2  π);
sunday = 24  Mod[RandomVariate[VonMisesDistribution[6, 1], 15000], 2  π]/(2  π);

(* Nonparametric density estimation *)
data = Join[monday, 24 + tuesday, 48 + wednesday, 3*24 + thursday, 
   4*24 + friday, 5*24 + saturday, 6*24 + sunday];
skd = SmoothKernelDistribution[Join[data - 7*24, data, data + 7*24], "LeastSquaresCrossValidation"];

(* Show associated histogram and a nonparametric density estimate *)
Show[Histogram[data, "FreedmanDiaconis", "PDF", AspectRatio -> 1/4, Frame -> True,
  FrameTicks -> {{All, None}, {{{0, "Monday\n12am", {0, 0.01}}, {24, "Tuesday\n12am", {0, 0.01}}, 
  {48, "Wednesday\n12am", {0, 0.01}}, {3*24, "Thursday\n12am", {0, 0.01}},
  {4*24, "Friday\n12am", {0, 0.01}}, {5*24, "Saturday\n12am", {0, 0.01}}, 
  {6*24, "Sunday\n12am", {0, 0.01}}, {7*24, "Monday\n12am", {0, 0.01}}}, None}},
  ImageSize -> Large, FrameLabel -> {"", "Probability density"}, 
  PlotRangeClipping -> False],
Plot[3 PDF[skd, x], {x, 0, 7*24}, PlotRange -> All]]

Answer 2

You can use CategoricalDistribution to directly work with the data from your example:

Generate some fake data:

n = 100;
days = {Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, 
   Sunday};
hours = Range[0, 23];
data = Transpose[{
    RandomChoice[RandomReal[1, 7] -> days, n],
    RandomChoice[RandomReal[1, 24] -> hours, n]
    }];
Take[data, 5]

{{Sunday, 2}, {Monday, 15}, {Monday, 14}, {Saturday, 11}, {Sunday, 9}}

Generate a CategoricalDistribution for the data and show the probability plot:

cd = CategoricalDistribution[{days, hours}, Normal @ Counts @ data];
Information[cd, "ProbabilityPlot"]