Alignment of annotations in elan export to excel

Dear ELANers,
I hope there’s some easy solution to this but we have a problem when we export ELAN to excel in terms of what appears as aligned with what. So, we have a tier (called SENTENCE PARTS) which we want all other tiers below to be aligned with when we export to excel - it’s for the part of the sentence (most of the time it’s parts like beginning, middle and end, but let’s call them A, B, C), then we have other tiers in which we annotate for the presence of facial features like eyeblink or eyebrow raise within these sentence parts. We aligned everything with the tier for sentence parts - for example, when there was an eyeblink it was perfectly aligned with part A of the sentence (meaning the blink annotation has the same start and end time as part A annotation of the sentence exactly) - since we want to see which face features appear with which parts of the sentence. The problem is that sometimes the face feature aligns with two parts of the sentence, part A and B so we stretched the annotation to be aligned to parts A and B but now according to excel, this is NOT aligned to any sentence part any more. In excel they appear as blanks in the SENTENCE PARTS column. I understand why - because the start of the eyeblink aligns ONLY with the start of part A and the end of the eyeblink aligns ONLY with the end of part B but I don’t know how to overcome this. If we align everything to each sentence part - like 1 annotation for eyeblink on part A and 1 annotation for eyeblink on part B then this will be an overcount of the facial features so we don’t want to do this option. If we use a parent tier then it doesn’t let us do several annotations in the child tier (from what I could see!).
We ultimately want to see in excel all the face features which align with Parts A and all of the face features which align also with both Parts A and B - not sure if that is possible.
Any ideas?

Hello Rose,

I assume you tried all combinations of options in the tab-delimited text export (e.g. “repeat values etc.” and “sliced annotation output etc.”) and none produces the output you like to have. And it is like that: if an annotation overlaps multiple other annotations, the export either contains empty cells or contains repeated values (and counts are not reliable). There is no real solution to that; if a “blink” overlaps both part A and part B, the export could maybe place “blink” in the row for either A or B depending on the amount of overlap and leave the other one empty (in which case the overlap with the other one is lost in excel), or it can place “blink” next to both A and B and the count is unreliable.

It seems you would like the SENTENCE PART tier to be ‘leading’ in the export. The tab-delimited text export doesn’t provide that but the multiple file export labeled “Annotation Overlaps Information…” does. It allows to select a reference tier and a set of other tiers to export along with it. The output will contain many columns with information about annotations after the reference annotation, which you can all delete, but there are also columns with information about number and value(s) of overlapping annotations. Still not exactly what you want, but you could have a look at it.

The structured multiple file search might be helpful in finding e.g. all co-occurrences of a face feature and part A or B, but filtering out features that overlap multiple parts will not be trivial.


Thank you, Han! :slight_smile:
This was very helpful.