"Barcode" style graphs from spreadsheet?

March 2, 2015 11:48 AM

For a film music analysis I would like to create a graph from four criteria: cue name, time in/out, whether "underscore" (beneath dialogue) or standalone, and a brief description of the scene against which the cues play. In my research I've come across good looking examples - e.g. this graph from this table. Would anyone be so kind as to explain how I might do this, or share their experience of a similar task?

Might you know what this type of graph is called? As for now, I'm going to collate the information in Excel. I figure I can then focus on wrestling it into (barograph) shape from there. To cite, the example in question is from James Wierzbicki's "Psycho-analysis" (Hayward, Philip, ed. Terror Tracks: Music, Sound and Horror Cinema. Genre, Music and Sound. London ; Oakville, CT: Equinox, 2009.)

posted by alexandermatheson to Writing & Language (4 answers total)

Best answer: You could trick Excel in creating this kind of chart by making a "2D area graph" with specially prepared data. You'd prepare it by making two points for your starts and two points for your ends. For example, if a cue starts at 5:00 and ends at 5:33, you table would be like this:

time       value
5:00       0
5:00.0001  100
5:33       100
5:33.0001  0

You'd add more columns for the other types of cues in different colors or shades, adding rows as needed to cover all the start/stops.

(This is pretty hacky, and there's probably an existing package in R that will do this the right way. But I'm describing anyway, since you mention Excel, and I don't happen to have experience making this exact thing in R.)
posted by paper chromatographologist at 12:11 PM on March 2, 2015

Best answer: I was going to suggest the same method as paper chromatographologist, but you can do it without adding .0001 and just repeat the same number above it if you change your horizontal axis to a date axis instead of a text axis. At the same time you'll want to keep your time column as integers, because they'll be interpreted as days in the Excel dialogs and I don't think Excel likes plotting in fraction of days for that type of chart.
posted by noneuclidean at 2:15 PM on March 2, 2015

Best answer: I'd just do a stacked bar chart.
Add a column to calculate the length of each piece and then graph that.
It's possible to colour code with some overlapping dummy series too.
Drop me an email with a few rows of sample data and I can throw a template together if you'd like.
posted by chrispy108 at 2:37 PM on March 2, 2015 [1 favorite]

Response by poster: Thank you all! Meta never ceases to amaze with knowledge and generosity. I'm marking this one answered!
posted by alexandermatheson at 12:49 PM on March 3, 2015