Mapping Respondent Answers and Drop-Down Data in KoboToolbox Exports

[aymohamed]

Hello Kobo Community,

Happy New Year!

I am currently working on a project where we used Kobo Toolbox for the data collection phase. The data collectors used Kobo questionnaires to ask questions, some of which had drop-down options. After exporting the data, the Excel file looks like the following:

1st sheet (sheet[0]): Contains respondent details, such as name, age, address, and answers to some questions.
2nd sheet (sheet[1]) and beyond: Organized by groups.
The central challenge I’m facing is how to map the questions answered by the respondents to their respective answers, which I believe are stored in the group sheets (starting from the second sheet onwards). I noticed that both sheet[0] and the other sheets include a UUID. Is this UUID a unique key that can be used to link the respondent’s questions to their answers?

Additionally, I use automated scripts in my workflow. Are there any libraries or packages available that can help with seamlessly mapping the questions to their corresponding answers?

One more question: Where do the answers for drop-down questions go? Is it possible to download the Kobo-stored files in a way that shows the actual answers to drop-down questions, so that it will be easier to ingest this data into the scripts I developed?

Any Kobo documentation or insights would be greatly appreciated.

Thank you for your help!

[nat]

Greetings @aymohamed and welcome to the community!

I believe I can direct you to the Kobo documentation for a couple of your questions - hope this helps!

@aymohamed

One more question: Where do the answers for drop-down questions go? Is it possible to download the Kobo-stored files in a way that shows the actual answers to drop-down questions, so that it will be easier to ingest this data into the scripts I developed?

For dropdown answers in data exports, you can choose whether to use the variables (that are saved under-the-hood) or the labels (that are displayed onscreen - in any of the languages of the form) - please see Exporting and Downloading Your Data — KoboToolbox documentation -

@aymohamed

The central challenge I’m facing is how to map the questions answered by the respondents to their respective answers, which I believe are stored in the group sheets (starting from the second sheet onwards). I noticed that both sheet[0] and the other sheets include a UUID. Is this UUID a unique key that can be used to link the respondent’s questions to their answers?

Additionally, I use automated scripts in my workflow. Are there any libraries or packages available that can help with seamlessly mapping the questions to their corresponding answers?

The official KoboToolbox documentation says to link responses based on index rather than UUID: To link the repeat group data to the parent data, use the index column from the parent sheet and match it to the parent index column in the repeat group sheet.
https://support.kobotoolbox.org/group_repeat.html?highlight=parent+index#downloading-data-from-repeat-groups
This process is more fully expanded in Merging Individual Data with Roster Data through Power Query in Excel — KoboToolbox documentation (linked from
Exporting and Downloading Your Data — KoboToolbox documentation page), so if your automation is with PowerQuery hopefully that article will help.

[nat]

FYI This forum post outlines what the various different metadata fields are: What are the relation between these columns you get while exporting data in Excel? - #2 by Kal_Lam

NOTE re UUID - it can change if the submission is edited, and also it isn’t always unique on the server (see references in above post) so whilst UUID can often be useful to identify a particular submission there can be problems relying on UUID in certain scenarios.

All the best with your project!