import json
import pandas as pd
import pyxnat

c = pyxnat.Interface(config='/home/grg/.xnat_bsc.cfg')

# Collect experiments
experiments = json.load(open('/home/grg/Downloads/mri_list.json'))

# Querying FreeSurfer resource -if any- from each experiment
tables = [[], [], []]
for e in experiments:
    s = c.array.experiments(experiment_id=e, columns=['subject_label']).data[0]['subject_label']
    r = c.select.experiment(e).resource('FREESURFER6')
    if not r.exists(): continue
    volumes = [r.hippoSfVolumes(), r.aseg(), r.aparc()]

    for each, table in zip(volumes, tables):
        each['subject'] = s
        table.append(each)

# Convert to dataframes
hippoSfVolumes = pd.concat(tables[0]).set_index('subject').sort_index()
aseg = pd.concat(tables[1]).set_index('subject').sort_index()
aparc = pd.concat(tables[2]).set_index('subject').sort_index()

# Creating pivot tables
aparc.to_excel('/tmp/aparc_JH.xlsx')

import pandas as pd
t = pd.read_excel('/tmp/aparc_JH.xlsx').set_index('subject')
pd.pivot_table(t,
               values='value',
               columns='region',
               index=t.index).head()