Import spreadsheet

Solved by Steen.

Task

Can you help develop a process for integrating manual tags from a spreadsheet into Red Hen? If so, write to

and we will try to connect you with a mentor.

More information

Javier Valenzuela and Cristóbal Pagán Cánovas took clips from Red Hen following the usual procedures and annotated timeline gestures by tagging in an Excel spreadsheet. (The csv file for the spreadsheet is attached to this page.) For example, a clip at https://tvnews.sscnet.ucla.edu/edge/video,63eda4b4-c04c-11dd-b6c2-00e0815fe86a,1450 was tagged in the spreadsheet with this row:

from start to finish,"AND IT WAS LIKE AN HOUR, AND I WAS LIKE, ""AN HOUR?"" AND THEN HE CAME OUT AND WAS HILARIOUS FROM START TO FINISH. AND HE'S JUST A FASCINATING GUY. HE WAS REALLY FASCINATED ABOUT COMEDY.","https://tvnews.sscnet.ucla.edu/edge/video,63eda4b4-c04c-11dd-b6c2-00e0815fe86a,1450",KNBC Late Night with Conan OBrien,"Tuesday December 2, 2008 at 12:37 am PST (2008-12-02 08:37 UTC) ",right,lateral,rightward

Regular data like this can easily and automatically be added to Red Hen's core metadata files. The import script converts each row in the spreadsheet into a tag line inside the corresponding .seg file in the Red Hen archive, with the right timestamp. E.g., the converted tag in the .seg file for the spreadsheet row is:

20081202090107.250|20081202090117.333|GES_02|Frame=Time interval|Lexical_cue=from start to finish|Gesture_hand=right|Gesture_axis=lateral|Gesture_direction=rightward

The script also places in the .seg file a credit line like this:

GES_02|2015-10-27 04:21|Method=manual tagging|Source_Person=Cristóbal Pagán Cánovas, Javier Valenzuela Manzanares|Codebook=Frame|Lexical cue|Gesture hand|Gesture axis|Gesture direction

In principle, this bash script could be modified to be used on any other consistently organized spreadsheet to ingest its tags into the .seg file at the appropriate timestamp. Of course, it would be better to standardize the organization of spreadsheets meant to provide tagging data to Red Hen. Alternatively, the Red Hen member providing the spreadsheet would need to modify the bash script to make it work on the new spreadsheet.

The script, cc-integrate-csv, is available on Red Hen's github.