def _parse_base_data(res, match, sheet, sheet_name, re_params_name=_re_params_name): r = {} defaults = {'usage': 'input', 'stage': 'calibration'} if 'type' not in match: match['type'] = 'pa' if 'cycle' not in match else 'ts' match = dsp_utl.combine_dicts(defaults, match) if match['type'] == 'pa': xl_ref = '#%s!B2:C_:["pipe", ["dict", "recurse"]]' % sheet_name data = lasso(xl_ref, sheet=sheet) else: # noinspection PyBroadException try: xl_ref = '#%s!A2(R):.3:RD:["df", {"header": 0}]' % sheet_name data = lasso(xl_ref, sheet=sheet) except: return {} data.dropna(how='all', inplace=True) data.dropna(axis=1, how='all', inplace=True) mask = data.count(0) == len(data._get_axis(0)) # noinspection PyUnresolvedReferences drop = [k for k, v in mask.items() if not v] if drop: msg = 'Columns {} in {} sheet contains nan.\n ' \ 'Please correct the inputs!' raise ValueError(msg.format(drop, sheet_name)) for k, v in parse_values(data, match, re_params_name): co2_utl.get_nested_dicts(r, *k[:-1])[k[-1]] = v n = (match['scope'], 'target') if match['type'] == 'ts' and co2_utl.are_in_nested_dicts(r, *n): t = co2_utl.get_nested_dicts(r, *n) for k, v in co2_utl.stack_nested_keys(t, key=n, depth=2): if 'times' not in v: n = list(k + ('times', )) n[1] = match['usage'] if co2_utl.are_in_nested_dicts(r, *n): v['times'] = co2_utl.get_nested_dicts(r, *n) else: for i, j in co2_utl.stack_nested_keys(r, depth=4): if 'times' in j: v['times'] = j['times'] break co2_utl.combine_nested_dicts(r, depth=5, base=res)
def _parse_plan_data(plans, match, sheet, sheet_name, re_params_name=_re_params_name): # noinspection PyBroadException xl_ref = '#%s!A1(R):._:R:"recurse"' data = lasso(xl_ref % sheet_name, sheet=sheet) try: data = pd.DataFrame(data[1:], columns=data[0]) except IndexError: return None if 'id' not in data: data['id'] = data.index + 1 data.set_index(['id'], inplace=True) data.dropna(how='all', inplace=True) data.dropna(axis=1, how='all', inplace=True) plan = pd.DataFrame() defaults = {'usage': 'input', 'stage': 'calibration'} match = dsp_utl.combine_dicts(defaults, match) for k, v in parse_values(data, match, re_params_name): k = k[-1] if k[-1] in ('base', 'defaults') else '.'.join(k[1:]) plan[k] = v plans.append(plan)
def main(): f = "DT_MT_par.xlsx" fin = "test_in.csv" #"DT_MT_in.csv" fout = "DT_MT_out_Python.csv" dfins = pd.read_csv(fin) dfins = dfins.ix[:1000, :] dfsplit = np.array_split(dfins, 1)#100) sh = shts[0] dFL = pd.DataFrame(columns = shts) startTime = datetime.now() for sh in shts: ysc = pd.DataFrame(xleash.lasso("%s#%s!%s"%(f, sh, refs['Ysc']))) ssc = pd.DataFrame(xleash.lasso("%s#%s!%s"%(f, sh, refs['SSc']))) theta = pd.DataFrame(xleash.lasso("%s#%s!%s"%(f, sh, refs['theta']))) beta = pd.DataFrame(xleash.lasso("%s#%s!%s"%(f, sh, refs['beta']))) gamma = pd.DataFrame(xleash.lasso("%s#%s!%s"%(f, sh, refs['gamma']))) s = pd.DataFrame(xleash.lasso("%s#%s!%s"%(f, sh, refs['S']))) dS = pd.Series() for df in dfsplit: r = KrigingCo2mpas(df, ysc, ssc, theta, beta, gamma, s) dS = dS.append(r) dFL[sh] = dS print(datetime.now() - startTime) dFL.to_csv(fout, index=False)
def test_collect_tables(self, case): exp_ref_path, exp_ref_sheet = 'datasync.xlsx', 'Sheet1' (ref_xlref, *sync_xlrefs), exp_xlrefs = case required_labels = ('x', 'y1') tables = datasync.Tables(required_labels, _shfact) tables.collect_tables(ref_xlref, *sync_xlrefs) self.assertEqual(tables.ref_fpath, exp_ref_path) self.assertEqual(tables.ref_sh_name, exp_ref_sheet) self.assertEquals(len(tables.tables), len(exp_xlrefs)) self.assertEquals(len(tables.headers), len(exp_xlrefs)) for d, xlref, in zip(tables.tables, exp_xlrefs): df = xleash.lasso('%s:"df"' % xlref, sheets_factory=_shfact) npt.assert_array_almost_equal(d, df.values, err_msg=xlref)
def _consume_next_xlref(self, xlref, lasso): """ :param str xlref: an xlref that may not contain hash(`#`); in that case, it is taken as *file-part* or as *fragment-part* depending on the existence of prev lasso's `url_file`. :param Lasso lasso: reuses `url_file` & `sheet` if missing from xlref """ xlref = _guess_xlref_without_hash(xlref, bias_on_fragment=bool( lasso.url_file)) lasso = xleash.lasso(xlref, sheets_factory=self._sheets_factory, url_file=lasso.url_file, sheet=lasso.sheet, return_lasso=True) values = lasso.values if values: # Skip blank sheets. # TODO: Convert column monkeybiz into pure-pandas using xleash. str_row_indices = [ i for i, r in enumerate(values) if any( isinstance(v, str) for v in r) ] req_labels = IndexedSet(self.required_labels) for k in str_row_indices: if set(values[k]) >= req_labels: break else: raise CmdException( "Columns %r not found in table of sheet(%r) in book(%r)!" % (self.required_labels, lasso.sheet._sheet.name, lasso.sheet.book_fname)) ix = values[k] i = max(str_row_indices, default=0) + 1 h = pd.DataFrame(values[:i], columns=ix) self.headers.append((sheet_name(lasso), k, h)) values = pd.DataFrame(values[i:], columns=ix) values.dropna(how='all', inplace=True) values.dropna(axis=1, how='any', inplace=True) if values.empty: log.warning("Empty table of sheet(%r) in book (%r)!" % (lasso.sheet._sheet.name, lasso.sheet.book_fname)) else: self.tables.append(values) return lasso
def _extract_dice_report_from_output(self, fpath): import pandas as pd from pandalone import xleash df = xleash.lasso(self.dice_report_xlref, url_file=fpath) if not isinstance(df, pd.DataFrame): raise ValueError( "The param '%s.%s' must resolve to a DataFrame, not type(%r): %s" % (type(self).__name__, self.dice_report_xlref, type(df), df)) df = df.where((pd.notnull(df)), None) vfid = df.at[self.output_vfid_coords] return vfid, df
def extract_output_tables(self, fpath): from pandalone import xleash #fpath = pndlu.convpath(fpath) master_xlrefs = xleash.lasso(self.output_master_xlref, url_file=fpath) assert isinstance(master_xlrefs, Mapping), ( "The `output_master_xlref(%s) must resolve to a dictionary, not type(%r): %s" % (self.output_master_xlref, type(master_xlrefs), master_xlrefs)) tables = [] for tab_key in self.output_table_keys: assert tab_key in master_xlrefs, ( "The `output_table_key` %r were not found in *master-xlref* dictionary: %s" % (tab_key, master_xlrefs)) tab_xlref = master_xlrefs[tab_key] tab = xleash.lasso(tab_xlref, url_file=fpath) assert isinstance(tab, pd.DataFrame), ( "The `output_master_xlref` key (%r --> %r) must resolve to a DataFrame, not type(%r): %s" % (tab_key, tab_xlref, type(tab), tab)) tables.append(tab) self.output_tables = tables return self.output_tables
def _parse_sheet(match, sheet, sheet_name, res=None): if res is None: res = {} sh_type = _get_sheet_type(**match) # noinspection PyBroadException try: import pandalone.xleash as xleash data = xleash.lasso(_xl_ref[sh_type] % sheet_name, sheet=sheet) except Exception: return res if sh_type == 'pl': try: import pandas as pd data = pd.DataFrame(data[1:], columns=data[0]) except IndexError: return None if 'id' not in data: data['id'] = data.index + 1 data.set_index(['id'], inplace=True) data.dropna(how='all', inplace=True) data.dropna(axis=1, how='all', inplace=True) elif sh_type == 'ts': data.dropna(how='all', inplace=True) data.dropna(axis=1, how='all', inplace=True) mask = data.count(0) == len(data._get_axis(0)) # noinspection PyUnresolvedReferences drop = [k for k, v in mask.items() if not v] if drop: msg = 'Columns {} in {} sheet contains nan.\n ' \ 'Please correct the inputs!' raise ValueError(msg.format(drop, sheet_name)) data = data.to_dict('list') else: data = {k: v for k, v in data.items() if k} for k, v in _parse_values(data, match, "in sheet '%s'" % sheet_name): sh.get_nested_dicts(res, *k[:-1])[k[-1]] = v return res
# %% vehdb.print_nodes(h5fname) # %% # a=xleash.lasso('VehData/calculation_parameter_all.15092019_prog_code_dev.xlsx#calculation_parameter_all!::["df"]') # b=xleash.lasso('VehData/calculation_parameter_all.20092019.xlsx#calculation_parameter_all!::["df"]') # bad_cols = (a == b).all() # bad_cols[~bad_cols] # %% veh_inputs_excel = ( Path("VehData/calculation_parameter_all.20092019.xlsx"), "calculation_parameter_all", ) specs = xleash.lasso('%s#%s!::["df"]' % veh_inputs_excel) wots_excel = (Path("VehData/TB_Pwot.20092019.xlsx"), "TB_Pwot") pwots = xleash.lasso('%s#%s!::["df"]' % wots_excel) # %% [markdown] # ## PRE 20190728 COLUMNS # ``` # vehicle_no rated_power v_max ndv_6 no_of_gears v_max_4 v_max_10 n_min_drive_set n95_high at_s # comments kerb_mass ndv_1 ndv_7 ng_vmax v_max_5 v_s_max n_min_wot n_max1 above_s # pmr_km test_mass ndv_2 ndv_8 v_max_ext v_max_6 n_vmax f_dsc_req n_max2 vmax_determined_by_n_lim # pmr_tm rated_speed ndv_3 ndv_9 v_max_1 v_max_7 f0 Pres_130 n_max3 # IDclass idling_speed ndv_4 ndv_10 v_max_2 v_max_8 f1 Pres_130_Prated n_max_wot # class v_max_declared ndv_5 v_max_transmission v_max_3 v_max_9 f2 n95_low below_s # # Index(['no_engine', 'n', 'Pwot', 'Twot', 'Pwot_norm', 'Twot_norm', 'SM', 'ASM',
def extract_input_params(self, fpath): from pandalone import xleash #fpath = pndlu.convpath(fpath) self.input_params = xleash.lasso(self.input_xlref, url_file=fpath) return self.input_params
def prepare_data(raw_data, variation, input_file_name, overwrite_cache, output_folder, timestamp, type_approval_mode, modelconf): """ Prepare the data to be processed. :param raw_data: Raw data from the input file. :type raw_data: dict :param variation: Variations to be applied. :type variation: dict :param input_file_name: Input file name. :type input_file_name: str :param overwrite_cache: Overwrite saved cache? :type overwrite_cache: bool :param output_folder: Output folder. :type output_folder: str :param timestamp: Run timestamp. :type timestamp: str :param type_approval_mode: Is launched for TA? :type type_approval_mode: bool :param modelconf: Path of modelconf that has modified the defaults. :type modelconf: str :return: Prepared data. :rtype: dict """ has_plan = 'plan' in raw_data and (not raw_data['plan'].empty) match = { 'scope': 'plan' if has_plan else 'base', } r = {} sheets_factory = xleash.SheetsFactory() from co2mpas.io import check_xlasso for k, v in excel._parse_values(variation, match, "in variations"): if isinstance(v, str) and check_xlasso(v): v = xleash.lasso(v, sheets_factory, url_file=input_file_name) dsp_utl.get_nested_dicts(r, *k[:-1])[k[-1]] = v if 'plan' in r: if has_plan: plan = raw_data['plan'].copy() for k, v in dsp_utl.stack_nested_keys(r['plan'], 4): plan['.'.join(k)] = v else: gen = dsp_utl.stack_nested_keys(r['plan'], 4) plan = pd.DataFrame([{'.'.join(k): v for k, v in gen}]) excel._add_index_plan(plan, input_file_name) r['plan'] = plan has_plan = True if 'base' in r: r['base'] = dsp_utl.combine_nested_dicts(raw_data.get('base', {}), r['base'], depth=4) if 'flag' in r: r['flag'] = dsp_utl.combine_nested_dicts(raw_data.get('flag', {}), r['flag'], depth=1) data = dsp_utl.combine_dicts(raw_data, r) if type_approval_mode: variation, has_plan = {}, False if not schema._ta_mode(data): return {}, pd.DataFrame([]) flag = data.get('flag', {}).copy() if 'run_base' not in flag: flag['run_base'] = not has_plan if 'run_plan' not in flag: flag['run_plan'] = has_plan flag['type_approval_mode'] = type_approval_mode flag['output_folder'] = output_folder flag['overwrite_cache'] = overwrite_cache if modelconf: flag['modelconf'] = modelconf if timestamp is not None: flag['timestamp'] = timestamp flag = schema.validate_flags(flag) if flag is dsp_utl.NONE: return {}, pd.DataFrame([]) schema.check_data_version(flag) res = { 'flag': flag, 'variation': variation, 'input_file_name': input_file_name, } res = dsp_utl.combine_dicts(flag, res) base = dsp_utl.combine_dicts(res, {'data': data.get('base', {})}) plan = dsp_utl.combine_dicts(res, {'data': data.get('plan', pd.DataFrame([]))}) return base, plan