adding info to a data frame

  pandas, python, text

I am looking for a nicer and more "general" way (see first if statement) to separate the headers lines from the bottom lines to create two different dataframes.

The first one is used to create the dataframe df2 and the bottom part is used to create the dataframe df. The goal is to merge the 2 files at the end of the process.

import pandas as pd
import glob
import os

df=[]
df2=[]
d2={}
d={}

path = "C:/MRIdata/CONN_analysis/conn_Ent02/results/secondlevel/SBC_01/Entrepreneurs(1).Managers(-1)/rest/*/"
subdirs2 = glob.glob('%s'%(path))

# Reading all files

for dir in subdirs2:
    seedRegion = os.path.basename(os.path.normpath(dir))
    print(seedRegion)
    new_file = os.path.join(dir, "results.Table.txt")
    print(new_file)
    
    with open(new_file) as test:
        fp=test.readlines()[1:]
        for i in fp:
            print(len(i))
            
# Separating the header from the bottom part of the file for dataframe df and df2 creation
            if len(i) == 94 and (i.find('covering') == -1):
                
                d2['Seed']=seedRegion
                d2['x']=i.split()[0]
                d2['y']=i.split()[1]
                d2['z']=i.split()[2]
                d2['size']=i.split()[3]
                d2['size p-FWE']=i.split()[4]
                d2['size p-FDR']=i.split()[5]
                d2['size p-unc']=i.split()[6]
                d2['peak p-FWE']=i.split()[7]
                d2['peak p-unc']=i.split()[8]                
                df2.append(d2.copy())
                
            # print(df2.copy())
        
            elif (i.find('Cluster') != -1): #find returns -1 if the substring not found

                d['x']=i.split()[1]
                d['y']=i.split()[2]
                d['z']=i.split()[3]

            else:
                if len(i)<=1:
                    continue
                elif (i.find('All') == -1):
                    j=i.split()
                        # print(j)
                    d['voxels']=i.split()[0]
                    d['voxels_pct']=i.split()[2][1:-2]
                    d['covering_pct']=i.split()[4][:-1]
                    d['atlas']=i[i.find('atlas.'):-2]
                    df.append(d.copy())
                
            # else:
                
df2 = pd.DataFrame(df2)
df = pd.DataFrame(df)
df.to_excel('C:/Users/win/Dropbox/1_Mon_Switch/Statistiques/MRI/test.xlsx')
df2.to_excel('C:/Users/win/Dropbox/1_Mon_Switch/Statistiques/MRI/test2.xlsx')

The files are formatted this way. There are 4 files between the 2 blocks.

-44 -58 +36              361     0.049000     0.030607     0.000068      3797.10     0.058000     0.036292     0.000081
-38 -84 +18              344     0.057000     0.030607     0.000079      3386.52     0.071000     0.036292     0.000107
-42 -06 +30              259     0.108000     0.045083     0.000175      3072.19     0.091000     0.036292     0.000141




Cluster -44 -58 +36  :
248 voxels (69%) covering 5% of atlas.sLOC l (Lateral Occipital Cortex, superior division Left)
51 voxels (14%) covering 5% of atlas.AG l (Angular Gyrus Left)
62 voxels (17%) covering 0% of atlas.not-labeled

Cluster -38 -84 +18  :
163 voxels (47%) covering 3% of atlas.sLOC l (Lateral Occipital Cortex, superior division Left)
107 voxels (31%) covering 5% of atlas.iLOC l (Lateral Occipital Cortex, inferior division Left)
25 voxels (7%) covering 1% of atlas.OP l (Occipital Pole Left)
49 voxels (14%) covering 0% of atlas.not-labeled

Cluster -42 -6 +30  :
89 voxels (34%) covering 2% of atlas.PreCG l (Precentral Gyrus Left)
1 voxels (0%) covering 0% of atlas.IFG oper l (Inferior Frontal Gyrus, pars opercularis Left)
169 voxels (65%) covering 0% of atlas.not-labeled

All clusters combined :
411 voxels (43%) covering 8% of atlas.sLOC l (Lateral Occipital Cortex, superior division Left)
107 voxels (11%) covering 5% of atlas.iLOC l (Lateral Occipital Cortex, inferior division Left)
89 voxels (9%) covering 2% of atlas.PreCG l (Precentral Gyrus Left)
51 voxels (5%) covering 5% of atlas.AG l (Angular Gyrus Left)
25 voxels (3%) covering 1% of atlas.OP l (Occipital Pole Left)
1 voxels (0%) covering 0% of atlas.IFG oper l (Inferior Frontal Gyrus, pars opercularis Left)
280 voxels (29%) covering 0% of atlas.not-labeled

All clusters combined :
58 voxels (42%) covering 4% of atlas.AG r (Angular Gyrus Right)
52 voxels (38%) covering 1% of atlas.sLOC r (Lateral Occipital Cortex, superior division Right)
15 voxels (11%) covering 1% of atlas.iLOC r (Lateral Occipital Cortex, inferior division Right)
12 voxels (9%) covering 1% of atlas.pSMG r (Supramarginal Gyrus, posterior division Right)

Thank you for your help.

Fred

Source: Python Questions

LEAVE A COMMENT