Joined up analysis?

 “So,” I said brightly. “Moving on: what about your analysis of the electronic monitoring records? What have you discovered?”

“Well not much I suppose,” said Ms D with unusual coyness. “Trouble is I organised my data by time, not person. That is, all the camera sitings are presented in time order rather than being grouped up by person and then time. I guess I could restructure the data but it will take a bit of time to do.”

Seems my ego was to take another battering: I had committed one of the most basic errors by not agreeing analysis output deliverable formats up front with Ms D. Of course she was going to come up with a different way of presenting her analysis findings – there must be a gazzilion+1 ways to present the same information and if you don’t agree which one to use up front you will end up trying to join up marmite with jam. It can be done of course, but it does not taste good and a little forethought would have removed this unnecessary hurdle (or should that be hurl?).

It was time (again!) to eat humble pie (jam and marmite!) as I had been directing this little fiasco. “No problem, I should have agreed with you at the start how we should present this stuff. Let’s have a look anyway and see if we can join it up.”

Camera locations

1.     External

a.     Gatehouse

                                          i.    pointing out to road

                                         ii.    pointing up drive

b.    Main house front main entrance

                                          i.    pointing to gatehouse

                                         ii.    pointing along dining room wall

                                        iii.    Main house front pointing along reception room wall

c.     Main house back

                                          i.    pointing along kitchen wall

                                         ii.    pointing along staff room wall

d.    Main house side

                                          i.    pointing back to front along kitchen wall

                                         ii.    pointing back to front along staff room wall

2.     Internal

a.     Ground floor

                                          i.    Main hall way pointing from stairs to front door

                                         ii.    Main hall way pointing from front door to stairs

b.    Dining room

                                          i.    door way pointing to far corner

                                         ii.    far corner pointing to doorway

c.     Reception room

                                          i.    door way pointing to far corner

                                         ii.    far corner pointing to doorway

d.    Kitchen

                                          i.    door way pointing to far corner

                                         ii.    far corner pointing to doorway

e.     Staff room

                                          i.    door way pointing to far corner

                                         ii.    far corner pointing to doorway

f.     First floor

                                          i.    landing pointing from stairs to far wall

                                         ii.    landing pointing from far wall to stairs

g.    Second floor

                                          i.    landing corner next to Erica’s bedroom pointing stairs from first floor

                                         ii.    landing corner next to Erica’s bedroom pointing ladder to attic

h.     Attic

                                          i.    landing pointing from ladder to far wall

                                         ii.    landing pointing from far wall to ladder

Time	Who	Doing what	Seen by (location)
18:02	Lord Todd and Danny D’Eath and Arthur Court	Enter dining room	2.a.i and 2.a.ii
18:02 – 18:51	Lord Todd and Danny D’Eath	Evening meal	2.b.i and 2.b.ii
18:06	Arthur Court	Goes from dining room to kitchen, picks up starters course tray and returns to dining room	2.b.i 2.b.ii 2.a.i 2.a.ii 2.d.i 2.d.ii
Etc…

This was a big table and needed to be to record the interactions of 8 people, 16 rooms, 4 corridors and 25 cameras over 15 hours!

How could this be sensibly joined to what I had produced?

An answer that seemed to fit with what we were trying to achieve (identification of attacker) and where we were (mis-matched data)  was validation of the witness statements I had got.

“This is great detail,” I said. “Fantastic, objective detail. Trouble is there is too much for my brain to process. Could we – well, could you – join it to the witness statements I got?

The process would be something like

1.     for each witness statement line (which has a rough time against it so you can go to the right part of your data)

a.     identify 1 or more entries in your table that corroborated it with evidence from cameras.

b.    Highlight any that don’t have a corroborating camera siting (unless it is inside a bedroom).

2.     Ideally, check for any camera sitings that could not be reasonably (in your judgement) be accounted for by witness statements

The join between my table and yours must be on the witness name. Luckily our lists DO use the same set of witness names!”

“That’s great,” she replied looking like she thought it was anything but. She didn’t add “it would have saved a shed-load of time if you had asked for that up front” – but then she didn’t need to.