Answer field is not captured while creating dataframe from QuestionnaireResponse parquet files using fhir-views.

Answer field is not captured while creating dataframe from QuestionnaireResponse parquet files using fhir-views.

Code for creating dataframe

import pandas
import json

from sqlalchemy import dialects
from sqlalchemy import engine

from google.fhir.views import r4
from google.fhir.views import spark_runner

fhir_dataset = 'default'
analysis_dataset = 'statin_analysis_example'

dialects.registry.register('hive', 'pyhive.sqlalchemy_hive', 'HiveDialect')

query_engine = engine.create_engine('hive://localhost:10001/default')

runner = spark_runner.SparkRunner(
query_engine=query_engine,
fhir_dataset=fhir_dataset,
view_dataset=analysis_dataset,
snake_case_resource_tables=True,
)

#### Load views based on the base FHIR R4 profile definitions.
views = r4.base_r4()
qrs = views.view_of('QuestionnaireResponse')

QuestionnaireResponse_df = runner.to_dataframe(
qrs.select(
{
"id": qrs.id,
"tag": qrs.meta.tag.code,
"patient_id":qrs.subject.reference,
"encounter_id": qrs.encounter.reference,
"questionnaire": qrs.questionnaire,
"item":qrs.item
}
)
)

when I do QuestionnaireResponse_df.iloc[0].item
[dataframe_json.zip](https://github.com/google/fhir-py/files/12158266/dataframe_json.zip)

when I query for the same data using sql

Please refer the attached Json file
[qr.zip](https://github.com/google/fhir-py/files/12158067/qr.zip)

Clearly the nested items and answer field are not captured in the dataframe. For example if I take the first object of the dataframe json and sql output

##Dataframe output
[
{
"id": null,
"linkId": "vitals",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.1",
"definition": null,
"text": "ANC service provided at *"
},
{
"id": null,
"linkId": "service-type-details",
"definition": null,
"text": null
},
{
"id": null,
"linkId": "machine-available",
"definition": null,
"text": "Is BP Machine Available? *"
},
{
"id": null,
"linkId": "3.2",
"definition": null,
"text": null
},
{
"id": null,
"linkId": "3.3",
"definition": null,
"text": null
},
{
"id": null,
"linkId": "3.4",
"definition": null,
"text": null
},
{
"id": null,
"linkId": "3.5",
"definition": null,
"text": null
}
]

##Sql output
[
{
"id": null,
"linkId": "vitals",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.1",
"definition": null,
"text": "ANC service provided at *",
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": null,
"version": null,
"code": "home",
"display": "Home",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "service-type-details",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "service-type",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "https://iprdgroup.com/custom-codes",
"version": null,
"code": "anc-visit",
"display": "ANC Visit",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
}
]
},
{
"id": null,
"linkId": "machine-available",
"definition": null,
"text": "Is BP Machine Available? *",
"answer": [
{
"id": null,
"value": {
"boolean": false,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": null,
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.2",
"definition": null,
"text": null,
"answer": null,
"item": null
},
{
"id": null,
"linkId": "3.3",
"definition": null,
"text": null,
"answer": null,
"item": null
},
{
"id": null,
"linkId": "3.4",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.4.1",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "weight",
"definition": null,
"text": "Weight (in Kgs) *",
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": 79.0,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": null,
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.4.1.2",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": "Kgs",
"uri": null,
"attachment": null,
"coding": null,
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
}
]
},
{
"id": null,
"linkId": "3.4.2",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.4.2.1",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "http://fhir.org/guides/who/anc-cds/CodeSystem/anc-custom-codes",
"version": null,
"code": "ANC.B8.DE3",
"display": "Current weight",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.4.2.2",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "http://hl7.org/fhir/observation-status",
"version": null,
"code": "preliminary",
"display": "Preliminary",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.4.2.3",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "http://hl7.org/fhir/ValueSet/observation-category",
"version": null,
"code": "vital-signs",
"display": "Vital Signs",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
}
]
}
]
},
{
"id": null,
"linkId": "3.5",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.5.1",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.5.1.1",
"definition": null,
"text": "Height (in cms) *",
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": 100.0,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": null,
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.5.1.2",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": "cms",
"uri": null,
"attachment": null,
"coding": null,
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
}
]
},
{
"id": null,
"linkId": "3.5.2",
"definition": null,
"text": null,
"answer": null,
"item": [
{
"id": null,
"linkId": "3.5.2.1",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "http://fhir.org/guides/who/anc-cds/CodeSystem/anc-custom-codes",
"version": null,
"code": "ANC.B8.DE1",
"display": "Height",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.5.2.2",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "http://hl7.org/fhir/observation-status",
"version": null,
"code": "registered",
"display": "Registered",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
},
{
"id": null,
"linkId": "3.5.2.3",
"definition": null,
"text": null,
"answer": [
{
"id": null,
"value": {
"boolean": null,
"decimal": null,
"integer": null,
"date": null,
"dateTime": null,
"time": null,
"string": null,
"uri": null,
"attachment": null,
"coding": {
"system": "http://hl7.org/fhir/ValueSet/observation-category",
"version": null,
"code": "vital-signs",
"display": "Vital Signs",
"userSelected": null
},
"quantity": null,
"reference": null
},
"item": null
}
],
"item": null
}
]
}
]
}
]

Reproduce the report using google.fhir.views.r4, spark_runner.SparkRunner, and the qrs.select call shown in the issue, comparing QuestionnaireResponse_df with the attached SQL output. Trace how nested item and answer data are handled by to_dataframe; done means the dataframe preserves the nested answers and values shown in the SQL result.

Written by the indexing model from the issue text.