| 1 | |
|---|
| 2 | import scope |
|---|
| 3 | import dreq |
|---|
| 4 | import xlsxwriter |
|---|
| 5 | from xlsxwriter.utility import xl_rowcol_to_cell |
|---|
| 6 | import collections, os |
|---|
| 7 | import makeTables |
|---|
| 8 | import overviewTabs |
|---|
| 9 | |
|---|
| 10 | ###cell = xl_rowcol_to_cell(1, 2) # C2 |
|---|
| 11 | |
|---|
| 12 | |
|---|
| 13 | class xlsx(object): |
|---|
| 14 | def __init__(self,fn,txtOpts=None): |
|---|
| 15 | self.txtOpts = txtOpts |
|---|
| 16 | self.mcfgNote = 'Reference Volume (1 deg. atmosphere, 0.5 deg. ocean)' |
|---|
| 17 | self.wb = xlsxwriter.Workbook('%s.xlsx' % fn) |
|---|
| 18 | self.hdr_cell_format = self.wb.add_format({'text_wrap': True, 'font_size': 14, 'font_color':'#0000ff', 'bold':1, 'fg_color':'#aaaacc'}) |
|---|
| 19 | self.hdr_cell_format.set_text_wrap() |
|---|
| 20 | self.sect_cell_format = self.wb.add_format({'text_wrap': True, 'font_size': 14, 'font_color':'#0000ff', 'bold':1, 'fg_color':'#ccccbb'}) |
|---|
| 21 | self.sect_cell_format.set_text_wrap() |
|---|
| 22 | self.cell_format = self.wb.add_format({'text_wrap': True, 'font_size': 11}) |
|---|
| 23 | self.cell_format.set_text_wrap() |
|---|
| 24 | |
|---|
| 25 | def newSheet(self,name): |
|---|
| 26 | self.sht = self.wb.add_worksheet(name=name) |
|---|
| 27 | return self.sht |
|---|
| 28 | |
|---|
| 29 | def tabrec(self,j,orec): |
|---|
| 30 | for i in range(len(orec)): |
|---|
| 31 | if orec[i] != '' and type( orec[i] ) == type( '' ) and orec[i][0] == '$': |
|---|
| 32 | self.sht.write_formula(j,i, '{=%s}' % orec[i][1:]) |
|---|
| 33 | else: |
|---|
| 34 | if j == 0: |
|---|
| 35 | self.sht.write( j,i, orec[i], self.hdr_cell_format ) |
|---|
| 36 | else: |
|---|
| 37 | self.sht.write( j,i, orec[i], self.cell_format ) |
|---|
| 38 | |
|---|
| 39 | def close(self): |
|---|
| 40 | self.wb.close() |
|---|
| 41 | |
|---|
| 42 | class vsum(object): |
|---|
| 43 | def __init__(self,sc,odsz,npy,exptFilter=None, odir='xls'): |
|---|
| 44 | idir = dreq.DOC_DIR |
|---|
| 45 | self.sc = sc |
|---|
| 46 | self.odsz=odsz |
|---|
| 47 | self.npy = npy |
|---|
| 48 | self.exptFilter = exptFilter |
|---|
| 49 | self.strSz = dict() |
|---|
| 50 | self.accum = False |
|---|
| 51 | self.odir = odir |
|---|
| 52 | self.efnsfx = '' |
|---|
| 53 | if sc.gridPolicyDefaultNative: |
|---|
| 54 | self.efnsfx = '_dn' |
|---|
| 55 | if not os.path.isdir( odir ): |
|---|
| 56 | print ( 'Creating new directory for xlsx output: %s' % odir ) |
|---|
| 57 | os.mkdir( odir ) |
|---|
| 58 | ii = open( '%s/sfheadings.csv' % idir, 'r' ) |
|---|
| 59 | self.infoRows = [] |
|---|
| 60 | for l in ii.readlines(): |
|---|
| 61 | ll = l.strip().split( '\t' ) |
|---|
| 62 | assert len(ll) == 2, 'Failed to parse info row: %s' % l |
|---|
| 63 | self.infoRows.append( ll ) |
|---|
| 64 | ii.close() |
|---|
| 65 | |
|---|
| 66 | |
|---|
| 67 | def analAll(self,pmax): |
|---|
| 68 | volsmm={} |
|---|
| 69 | volsmmt={} |
|---|
| 70 | volsme={} |
|---|
| 71 | volsue={} |
|---|
| 72 | for m in ['TOTAL',] + self.sc.mips: |
|---|
| 73 | if m != 'TOTAL': |
|---|
| 74 | cmv1 = self.sc.cmvByInvMip(m,pmax=pmax,includeYears=True) |
|---|
| 75 | self.uniqueCmv = self.sc.differenceSelectedCmvDict( cmv1, cmvTotal ) |
|---|
| 76 | self.run( m, '%s/requestVol_%s_%s_%s' % (self.odir,m,self.sc.tierMax,pmax), pmax=pmax ) |
|---|
| 77 | |
|---|
| 78 | self.anal(olab=m,doUnique=True, makeTabs=True) |
|---|
| 79 | ttl = sum( [x for k,x in self.res['vu'].items()] )*2.*1.e-12 |
|---|
| 80 | print ( '%s volume: %8.2fTb' % (m,ttl) ) |
|---|
| 81 | volsmm[m] = self.res['vm'] |
|---|
| 82 | volsmmt[m] = self.res['vmt'] |
|---|
| 83 | volsme[m] = self.res['ve'] |
|---|
| 84 | volsue[m] = self.res['uve'] |
|---|
| 85 | if m == 'TOTAL': |
|---|
| 86 | cmvTotal = self.sc.selectedCmv.copy() |
|---|
| 87 | self.uniqueCmv = {} |
|---|
| 88 | r1 = overviewTabs.r1( self.sc, pmax=pmax, vols=( volsmm, volsme, volsmmt,volsue ) ) |
|---|
| 89 | |
|---|
| 90 | def _analSelectedCmv(self,cmv): |
|---|
| 91 | lex = collections.defaultdict( list ) |
|---|
| 92 | vet = collections.defaultdict( int ) |
|---|
| 93 | vf = collections.defaultdict( int ) |
|---|
| 94 | vu = collections.defaultdict( float ) |
|---|
| 95 | mvol = collections.defaultdict( dict ) |
|---|
| 96 | |
|---|
| 97 | for u in cmv: |
|---|
| 98 | i = self.sc.dq.inx.uid[u] |
|---|
| 99 | if i._h.label != 'remarks': |
|---|
| 100 | npy = self.npy[ i.frequency ] |
|---|
| 101 | isClim = i.frequency.lower().find( 'clim' ) != -1 |
|---|
| 102 | st = self.sc.dq.inx.uid[i.stid] |
|---|
| 103 | c1 = 0 |
|---|
| 104 | for e,g in cmv[u]: |
|---|
| 105 | ee = self.sc.dq.inx.uid[e] |
|---|
| 106 | if ee.mip not in ['SolarMIP']: |
|---|
| 107 | lex[e].append( u ) |
|---|
| 108 | t1, tt = self.sc.getStrSz( g, stid=i.stid, tt=True ) |
|---|
| 109 | np = t1[1]*npy |
|---|
| 110 | if not isClim: |
|---|
| 111 | np = np*cmv[u][(e,g)] |
|---|
| 112 | c1 += cmv[u][(e,g)] |
|---|
| 113 | vet[(e,i.mipTable)] += np |
|---|
| 114 | vf[i.frequency] += np |
|---|
| 115 | vu[u] += np |
|---|
| 116 | else: |
|---|
| 117 | print ('ERROR.obsoleteMip.00001: %s,%s,%s' % (ee.mip,ee.label,ee.uid) ) |
|---|
| 118 | if i.frequency == 'mon': |
|---|
| 119 | mvol[tt][u] = c1 |
|---|
| 120 | |
|---|
| 121 | return lex, vet, vf, vu, mvol |
|---|
| 122 | |
|---|
| 123 | def anal(self,olab=None,doUnique=False,makeTabs=False): |
|---|
| 124 | vmt = collections.defaultdict( int ) |
|---|
| 125 | vm = collections.defaultdict( int ) |
|---|
| 126 | ve = collections.defaultdict( int ) |
|---|
| 127 | uve = collections.defaultdict( int ) |
|---|
| 128 | lm = collections.defaultdict( set ) |
|---|
| 129 | |
|---|
| 130 | lex, vet, vf, vu, mvol = self._analSelectedCmv(self.sc.selectedCmv ) |
|---|
| 131 | if olab != 'TOTAL' and doUnique: |
|---|
| 132 | s_lex, s_vet, s_vf, s_vu, s_mvol = self._analSelectedCmv(self.uniqueCmv ) |
|---|
| 133 | s_lm = set( self.uniqueCmv.keys() ) |
|---|
| 134 | s_cc = collections.defaultdict( int ) |
|---|
| 135 | for e,t in s_vet: |
|---|
| 136 | s_cc[t] += s_vet[(e,t)] |
|---|
| 137 | vm['Unique'] += s_vet[(e,t)] |
|---|
| 138 | vmt[('Unique',t)] += s_vet[(e,t)] |
|---|
| 139 | uve[e] += s_vet[(e,t)] |
|---|
| 140 | |
|---|
| 141 | checkMvol = 1 |
|---|
| 142 | for k in mvol: |
|---|
| 143 | sp = self.sc.dq.inx.uid[k[0]] |
|---|
| 144 | if k not in self.mvol: |
|---|
| 145 | print ( '%s missing from mvol: ' % str(k) ) |
|---|
| 146 | else: |
|---|
| 147 | if checkMvol > 1: |
|---|
| 148 | for u in mvol[k]: |
|---|
| 149 | la = self.sc.dq.inx.uid[u].label |
|---|
| 150 | if self.mvol[k][u] != mvol[k][u]: |
|---|
| 151 | print ( 'MISMATCH IN %s (%s): %s:: %s,%s' % (str(k),sp.label,la,mvol[k][u],self.mvol[k][u]) ) |
|---|
| 152 | |
|---|
| 153 | for e in lex: |
|---|
| 154 | ee = self.sc.dq.inx.uid[e] |
|---|
| 155 | for i in lex[e]: |
|---|
| 156 | lm[ee.mip].add( i ) |
|---|
| 157 | |
|---|
| 158 | for e,t in vet: |
|---|
| 159 | ee = self.sc.dq.inx.uid[e] |
|---|
| 160 | if ee.mip == 'SolarMIP': |
|---|
| 161 | print ('ERROR.solarmip: %s,%s,%s' % (ee.label, ee.title, ee.uid) ) |
|---|
| 162 | vmt[(ee.mip,t)] += vet[(e,t)] |
|---|
| 163 | vm[ee.mip] += vet[(e,t)] |
|---|
| 164 | ve[e] += vet[(e,t)] |
|---|
| 165 | |
|---|
| 166 | ## |
|---|
| 167 | ## makeTab needs: cc[m]: volume summary, by table, lm[m]: list of CMOR variables |
|---|
| 168 | ## |
|---|
| 169 | cc = collections.defaultdict( dict ) |
|---|
| 170 | cct = collections.defaultdict( int ) |
|---|
| 171 | for m,t in vmt: |
|---|
| 172 | cc[m][t] = vmt[(m,t) ] |
|---|
| 173 | ss = set() |
|---|
| 174 | for m in sorted( vm.keys() ): |
|---|
| 175 | if olab != None: |
|---|
| 176 | for t in cc[m]: |
|---|
| 177 | cct[t] += cc[m][t] |
|---|
| 178 | ss = ss.union( lm[m] ) |
|---|
| 179 | if makeTabs: |
|---|
| 180 | makeTables.makeTab(self.sc.dq, subset=lm[m], dest='%s/cmvmm_%s_%s_%s_%s%s' % (self.odir,olab,m,self.sc.tierMax,self.pmax,self.efnsfx), collected=cc[m]) |
|---|
| 181 | |
|---|
| 182 | if olab != None and makeTabs: |
|---|
| 183 | makeTables.makeTab(self.sc.dq, subset=ss, dest='%s/cmvmm_%s_%s_%s_%s%s' % (self.odir,olab,'TOTAL',self.sc.tierMax,self.pmax,self.efnsfx), collected=cct) |
|---|
| 184 | if olab != 'TOTAL' and doUnique: |
|---|
| 185 | makeTables.makeTab(self.sc.dq, subset=s_lm, dest='%s/cmvmm_%s_%s_%s_%s%s' % (self.odir,olab,'Unique',self.sc.tierMax,self.pmax,self.efnsfx), collected=s_cc) |
|---|
| 186 | |
|---|
| 187 | cc = collections.defaultdict( dict ) |
|---|
| 188 | ucc = collections.defaultdict( dict ) |
|---|
| 189 | cct = collections.defaultdict( int ) |
|---|
| 190 | for e,t in vet: |
|---|
| 191 | cc[e][t] = vet[(e,t) ] |
|---|
| 192 | for e in sorted( ve.keys() ): |
|---|
| 193 | if olab != None and makeTabs: |
|---|
| 194 | el = self.sc.dq.inx.uid[e].label |
|---|
| 195 | makeTables.makeTab(self.sc.dq, subset=lex[e], dest='%s/cmvme_%s_%s_%s_%s%s' % (self.odir,olab,el,self.sc.tierMax,self.pmax,self.efnsfx), collected=cc[e]) |
|---|
| 196 | |
|---|
| 197 | if olab != 'TOTAL' and doUnique: |
|---|
| 198 | for e,t in s_vet: |
|---|
| 199 | ucc[e][t] = s_vet[(e,t) ] |
|---|
| 200 | for e in sorted( uve.keys() ): |
|---|
| 201 | if olab != None and makeTabs: |
|---|
| 202 | el = self.sc.dq.inx.uid[e].label |
|---|
| 203 | makeTables.makeTab(self.sc.dq, subset=s_lex[e], dest='%s/cmvume_%s_%s_%s_%s%s' % (self.odir,olab,el,self.sc.tierMax,self.pmax,self.efnsfx), collected=ucc[e]) |
|---|
| 204 | |
|---|
| 205 | self.res = { 'vmt':vmt, 'vet':vet, 'vm':vm, 'uve':uve, 've':ve, 'lm':lm, 'lex':lex, 'vu':vu, 'cc':cc, 'cct':cct} |
|---|
| 206 | cc8 = collections.defaultdict( int ) |
|---|
| 207 | for e,t in vet: |
|---|
| 208 | cc8[t] += vet[(e,t)] |
|---|
| 209 | for f in sorted( vf.keys() ): |
|---|
| 210 | print ( 'Frequency: %s: %s' % (f,vf[f]*2.*1.e-12 ) ) |
|---|
| 211 | |
|---|
| 212 | def csvFreqStrSummary(self,mip,pmax=1): |
|---|
| 213 | sf, c3 = self.sc.getFreqStrSummary(mip,pmax=pmax) |
|---|
| 214 | self.c3 = c3 |
|---|
| 215 | self.pmax = pmax |
|---|
| 216 | lf = sorted( list(sf) ) |
|---|
| 217 | hdr0 = ['','','',''] |
|---|
| 218 | hdr1 = ['','','',''] |
|---|
| 219 | for f in lf: |
|---|
| 220 | hdr0 += [f,'','',''] |
|---|
| 221 | hdr1 += ['','','',str( self.npy.get( f, '****') )] |
|---|
| 222 | orecs = [hdr0,hdr1,] |
|---|
| 223 | crecs = [None,None,] |
|---|
| 224 | self.mvol = collections.defaultdict( dict ) |
|---|
| 225 | self.rvol = dict() |
|---|
| 226 | |
|---|
| 227 | ix = 3 |
|---|
| 228 | for tt in sorted( c3.keys() ): |
|---|
| 229 | s,o,g = tt |
|---|
| 230 | i = self.sc.dq.inx.uid[ s ] |
|---|
| 231 | if o != '': |
|---|
| 232 | msg = '%48.48s [%s]' % (i.title,o) |
|---|
| 233 | else: |
|---|
| 234 | msg = '%48.48s' % i.title |
|---|
| 235 | if g != 'native': |
|---|
| 236 | msg += '{%s}' % g |
|---|
| 237 | szg = self.sc.getStrSz( g, s=s, o=o )[1] |
|---|
| 238 | self.rvol[tt] = szg |
|---|
| 239 | |
|---|
| 240 | rec = [msg,szg,2,''] |
|---|
| 241 | crec = ['','','',''] |
|---|
| 242 | for f in lf: |
|---|
| 243 | if f in c3[tt]: |
|---|
| 244 | nn,ny,ne,labs,expts = c3[tt][f] |
|---|
| 245 | rec += [nn,ny,ne,''] |
|---|
| 246 | clabs = [self.sc.dq.inx.uid[x].label for x in labs.keys()] |
|---|
| 247 | crec += [sorted(clabs),'',expts,''] |
|---|
| 248 | if f.lower().find( 'clim' ) == -1: |
|---|
| 249 | assert abs( nn*ny - sum( [x for k,x in labs.items()] ) ) < .1, 'Inconsistency in year count: %s, %s, %s' % (str(tt),nn,ny) |
|---|
| 250 | if f == 'mon': |
|---|
| 251 | for k in labs: |
|---|
| 252 | self.mvol[tt][k] = labs[k] |
|---|
| 253 | else: |
|---|
| 254 | rec += ['','','',''] |
|---|
| 255 | crec += ['','','',''] |
|---|
| 256 | colr = xl_rowcol_to_cell(0, len(rec)) |
|---|
| 257 | colr = colr[:-1] |
|---|
| 258 | eq = '$SUMPRODUCT(--(MOD(COLUMN(E%(ix)s:%(colr)s%(ix)s)-COLUMN(A%(ix)s)+1,4)=0),E%(ix)s:%(colr)s%(ix)s)' % {'ix':ix,'colr':colr} |
|---|
| 259 | ix += 1 |
|---|
| 260 | rec[3] = eq |
|---|
| 261 | orecs.append( rec ) |
|---|
| 262 | crecs.append( crec ) |
|---|
| 263 | |
|---|
| 264 | return (orecs, crecs) |
|---|
| 265 | |
|---|
| 266 | def byExpt(self): |
|---|
| 267 | for cmv in self.sc.selectedCmv.keys(): |
|---|
| 268 | pass |
|---|
| 269 | |
|---|
| 270 | def run(self,mip='_all_',fn='test',pmax=1): |
|---|
| 271 | if mip == '_all_': |
|---|
| 272 | mip = set(self.sc.mips ) |
|---|
| 273 | self.mip = mip |
|---|
| 274 | self.x = xlsx( fn ) |
|---|
| 275 | self.sht = self.x.newSheet( 'Volume' ) |
|---|
| 276 | orecs, crecs = self.csvFreqStrSummary(mip,pmax=pmax) |
|---|
| 277 | oh = orecs[0] |
|---|
| 278 | self.sht.set_column(0,0,60) |
|---|
| 279 | self.sht.set_column(1,1,15) |
|---|
| 280 | self.sht.set_column(2,2,4) |
|---|
| 281 | self.sht.set_column(3,3,15) |
|---|
| 282 | for k in range( int( (len(oh)-3)/4 ) ): |
|---|
| 283 | self.sht.set_column((k+1)*4,(k+1)*4,4) |
|---|
| 284 | self.sht.set_column((k+1)*4+1,(k+1)*4+1,8) |
|---|
| 285 | self.sht.set_column((k+1)*4+2,(k+1)*4+2,4) |
|---|
| 286 | self.sht.set_column((k+1)*4+3,(k+1)*4+3,12) |
|---|
| 287 | |
|---|
| 288 | oo = [] |
|---|
| 289 | for i in range( len(oh) ): |
|---|
| 290 | oo.append( '' ) |
|---|
| 291 | kk = 0 |
|---|
| 292 | rr1 = 2 |
|---|
| 293 | rr1p = rr1 + 1 |
|---|
| 294 | for ix in range(len(orecs)): |
|---|
| 295 | o = orecs[ix] |
|---|
| 296 | kk += 1 |
|---|
| 297 | if kk > 2: |
|---|
| 298 | for i in range( 7,len(o),4): |
|---|
| 299 | frq = oh[i-3] |
|---|
| 300 | |
|---|
| 301 | cell = xl_rowcol_to_cell(0, i)[:-1] |
|---|
| 302 | ca = xl_rowcol_to_cell(0, i-3)[:-1] |
|---|
| 303 | ##if frq.lower().find( 'clim' ) == -1: |
|---|
| 304 | cb = xl_rowcol_to_cell(0, i-2)[:-1] |
|---|
| 305 | ##else: |
|---|
| 306 | ##cb = xl_rowcol_to_cell(0, i-1)[:-1] |
|---|
| 307 | eq = '$%(cell)s$%(rr1)s*%(cb)s%(kk)s*%(ca)s%(kk)s*$B%(kk)s*$C%(kk)s*0.000000001' % locals() |
|---|
| 308 | o[i] = eq |
|---|
| 309 | self.x.tabrec(kk-1, o ) |
|---|
| 310 | if crecs[ix] != None: |
|---|
| 311 | crec = crecs[ix] |
|---|
| 312 | for j in range(len(crec)): |
|---|
| 313 | if crec[j] != '': |
|---|
| 314 | self.sht.write_comment( kk-1, j, ' '.join( crec[j] ) ) |
|---|
| 315 | else: |
|---|
| 316 | if kk == 1: |
|---|
| 317 | for i in range( 4,len(o),4): |
|---|
| 318 | cell = xl_rowcol_to_cell(0, i)[:-1] |
|---|
| 319 | cell2 = xl_rowcol_to_cell(0, i+3)[:-1] |
|---|
| 320 | self.sht.merge_range('%s1:%s1' % (cell,cell2), 'Merged Range') |
|---|
| 321 | self.x.tabrec(kk-1, o ) |
|---|
| 322 | |
|---|
| 323 | n = len(orecs) |
|---|
| 324 | for i in range( 3,len(oo),4): |
|---|
| 325 | cell = xl_rowcol_to_cell(0, i)[:-1] |
|---|
| 326 | oo[i] = '$SUM(%(cell)s%(rr1p)s:%(cell)s%(n)s)*0.001' % locals() |
|---|
| 327 | for i in range( 5,len(oo),4): |
|---|
| 328 | oo[i] = oh[i-1] |
|---|
| 329 | oo[0] = 'TOTAL VOLUME (Tb)' |
|---|
| 330 | self.x.tabrec(kk, oo ) |
|---|
| 331 | |
|---|
| 332 | n += 2 |
|---|
| 333 | for a,b in self.infoRows: |
|---|
| 334 | self.sht.merge_range('B%s:H%s' % (n+1,n+1), 'Merged Range') |
|---|
| 335 | self.sht.write( n,0, a ) |
|---|
| 336 | self.sht.write( n,1, b ) |
|---|
| 337 | n += 1 |
|---|
| 338 | |
|---|
| 339 | self.x.close() |
|---|
