예제 #1
0
    def __init__(self):

        # Path
        major_path = os.path.join(data_path, "Chord Flow - Major keys.xlsx")
        minor_path = os.path.join(data_path, "Chord Flow - Minor keys.xlsx")
        major_weight_path = os.path.join(data_path, "major_flow_weight.xlsx")
        minor_weight_path = os.path.join(data_path, "minor_flow_weight.xlsx")

        # Get major data
        self._major = get_data(major_path, start_row=1)["Sheet1"]
        self._majweight = get_data(major_weight_path, start_row=1)["Sheet1"]
        # TODO: now just append a None to the last element to fix the list size. need better solution
        self._major[len(self._major) - 1].append(None)

        # Get minor data
        self._minor = get_data(minor_path, start_row=1)["Sheet1"]
        self._minweight = get_data(minor_weight_path, start_row=1)["Sheet1"]
        self._minor[len(self._minor) - 1].append(None)

        # Create major and minor index
        self._major_index = []
        self._minor_index = []
        self._map = {}
        self._seven = {}
        for row in self._major:
            self._major_index.append(row[0])
        for row in self._minor:
            self._minor_index.append(row[0])

        chord_list = MusicManager.get_instance().make_table_full_extend()
        self.make_map(chord_list[0], "Major", self._major_index,
                      self._majweight)
        self.make_map(chord_list[1], "Minor", self._minor_index,
                      self._minweight)
예제 #2
0
    def __init__(self):

        # Path
        major_path = os.path.join(data_path, "Chord Flow - Major keys.xlsx")
        minor_path = os.path.join(data_path, "Chord Flow - Minor keys.xlsx")
        major_weight_path = os.path.join(data_path, "major_flow_weight.xlsx")
        minor_weight_path = os.path.join(data_path, "minor_flow_weight.xlsx")

        # Get major data
        self._major = get_data(major_path, start_row=1)["Sheet1"]
        self._majweight = get_data(major_weight_path, start_row=1)["Sheet1"]
        # TODO: now just append a None to the last element to fix the list size. need better solution
        self._major[len(self._major)-1].append(None)

        # Get minor data
        self._minor = get_data(minor_path, start_row=1)["Sheet1"]
        self._minweight = get_data(minor_weight_path, start_row=1)["Sheet1"]
        self._minor[len(self._minor) - 1].append(None)

        # Create major and minor index
        self._major_index = []
        self._minor_index = []
        self._map = {}
        for row in self._major:
            self._major_index.append(row[0])
        for row in self._minor:
            self._minor_index.append(row[0])


        self.make_map()     # map data structure: dict(index)-> dict(target index)
예제 #3
0
    def generate_payment_xlsx(self):
        if len(self.get_payment_files()) == len(
                self.customers.get_customer_files()):
            logging.warning('no need to generate payment xlsx')
            return

        fund_account_code = ['0001', '0002']
        fund_account_abstract = [
            u'现金充值', u'其他充值', u'销售返点', u'退款', u'订单付款', u'其他付款'
        ]

        for customer_file in self.customers.get_customer_files():
            customer_data = get_data(customer_file)
            customer_columns = list(customer_data[u'客户数据'])

            fund_account_data = get_data(self.pay_template)
            fund_account_data_columns = list(fund_account_data['Sheet1'])
            for customer in customer_columns[2:]:
                fund_account_data_column = [
                    customer[0],
                    fund_account_code[randrange(0, len(fund_account_code))],
                    randrange(500, 10000), fund_account_abstract[randrange(
                        0, len(fund_account_abstract))]
                ]
                fund_account_data_columns.append(fund_account_data_column)
            new_fund_account_data = OrderedDict()
            new_fund_account_data.update({'Sheet1': fund_account_data_columns})
            save_data(customer_file.replace('customers', 'payment'),
                      new_fund_account_data)
예제 #4
0
    def __init__(self):
        # Create chordlist by chordtype and key for indexing
        # C Major C Minor C Dim ...
        self._chord = []
        for pitch in self.all_pitch_class:
            for chord in self.all_chord_type:
                self._chord.append(" ".join((pitch, chord)))

        # Prepare path
        self._major_model_path = os.path.join(data_path, "majortransmodel.txt")
        self._major_input_path = os.path.join(data_path,
                                              "Chord Flow - Major keys.xlsx")
        self._major_output_path = os.path.join(data_path, "MajorProp.xlsx")

        self._minor_model_path = os.path.join(data_path, "minortransmodel.txt")
        self._minor_input_path = os.path.join(data_path,
                                              "Chord Flow - Minor keys.xlsx")
        self._minor_output_path = os.path.join(data_path, "MinorProp.xlsx")

        # Major get model data
        self._major_file = open(self._major_model_path, "r")
        self._major_map = []
        for line in self._major_file:
            self._major_map.append(float(line))
        self._major_index = get_data(self._major_input_path,
                                     start_row=0)["Sheet2"]

        # Minor get model data
        self._minor_file = open(self._minor_model_path, "r")
        self._minor_map = []
        for line in self._minor_file:
            self._minor_map.append(float(line))
        self._minor_index = get_data(self._minor_input_path,
                                     start_row=0)["Sheet2"]
def build_strings_dict(xlsx_path, logger):
    """
    Will return a dictionary containing all of the strings in the xlsx file.
    The dictionary will look like so:
    {
        "French": {"app_name': "C'est ma vie", "capital": "C"est ma vie", "add_tab": "Jours de la semaine"},
        "German": {"app_name": "Berufe\n", "capital": "Der Weg zur Post", "add_tab":},
        "Hindi": {"app_name": "शब्दावली", "capital": "देशों", "add_tab": "कंबोडिया"}
    }
    """
    from pyexcel_xlsx import get_data
    import json
    raw_workbook_data = get_data(xlsx_path,
                                 start_row=3,
                                 start_column=5,
                                 column_limit=1)
    # data_values = get_data(xlsx_path, start_row=3, end_row=len(output_dict),start_column=1, column_limit=1)

    raw_workbook_dict = json.loads(str(json.dumps(raw_workbook_data)))
    # dict_values = json.loads(str(json.dumps(data_values)))
    xlsx_dict = {}

    # start the workbooks loop
    for language in raw_workbook_dict.keys():
        language_dict = {}

        # scrape the codes list
        codes_list = list(filter(None, raw_workbook_dict[language]))

        # scrape the translated words list
        translated_words_data = get_data(xlsx_path,
                                         start_row=3,
                                         start_column=1,
                                         column_limit=1)[language]
        translated_words_list = translated_words_data[0:len(codes_list)]
        logger.info('Parsing ' + language)
        # loop on all of the codes list (the rightest column)
        for i in range(len(codes_list)):
            if translated_words_list[i] is None or not translated_words_list[i]:
                logger.warning('Seems like the string name \'' +
                               codes_list[i][0] +
                               '\' didn\'t got translated to ' + language +
                               '. Would you like to continue? [yes]')
                to_continue = tools.ask_for_input('')
                if not su.str2bool(to_continue):
                    logger.info('Exiting')
                    return

            else:
                language_dict[codes_list[i][0]] = translated_words_list[i][0]

        xlsx_dict[language] = language_dict
    return xlsx_dict
예제 #6
0
    def print_minorsheet(self):
        # Get data for index
        data = get_data(self._minor_input_path)["Sheet1"]
        full_list = [data[0]]

        # Create result
        for row in self._minor_index:
            list = [row[0]]
            for secrow in self._minor_index:
                if row[1] is None or secrow[1] is None:
                    list.append(None)
                else:
                    current = self._chord.index(row[1])
                    next = self._chord.index(secrow[1])
                    if current == next:
                        list.append(-36)
                    else:
                        list.append(self.getminorvalue(current, next))
            full_list.append(list)
        print(full_list)

        # Save result
        sheetx = {"Sheet1": full_list}
        save_data(self._minor_output_path, sheetx)

        # Calculate Correctness using the Chord Flow file
        data = get_data(self._minor_input_path, start_row=1)["Sheet1"]
        Yessum = 0.0
        Yes = 0
        Nosum = 0.0
        No = 0
        data[len(self._minor_index) - 1].append(None)
        for row in range(0, len(self._minor_index)):
            for column in range(0, len(self._minor_index)):
                if data[row][column + 1] == "Yes":
                    Yes += 1
                    Yessum += exp(full_list[row + 1][column + 1])
                else:
                    No += 1
                    Nosum += exp(full_list[row + 1][column + 1])
        print("Yes: ", Yes, Yessum, Yessum / Yes, "No: ", No, Nosum,
              Nosum / No)

        # Display the correctness of Major and Minor
        self._minorsum = Yessum + Nosum
        self._minorcount = Yes + No
        print(self._majorcount, self._majorsum,
              self._majorsum / self._majorcount, self._minorcount,
              self._minorsum, self._minorsum / self._minorcount)
예제 #7
0
def analysis(read_path):
    # Collect text analysis file
    xlsx_file = './article_text_analysis.xlsx'
    print("Converting:", xlsx_file)
    data = get_data('./article_text_analysis.xlsx',
                    start_row=1,
                    start_column=3,
                    column_limit=1)

    with open("article_text_analysis.txt", "w") as text_file:
        # print(json.dumps(data))
        for key, values in data.items():
            for item in values:
                sentence = str(item)
                sentence = sentence[2:-2]
                text = nltk.word_tokenize(sentence)
                string_text = str(text)
                bi_grams = nltk.bigrams(text)
                pos_tagged = nltk.pos_tag(text)
                pos_line = str(sentence) + "\n" + str(pos_tagged) + "\n\n"
                text_file.write(pos_line)
                print("Text:", text)
                print("Bi-grams:")
                for bigram in bi_grams:
                    print(bigram)
                print("Pos-tags", pos_line)
                print()

                nlp = spacy.load("en_core_web_sm")
                doc = nlp(string_text)
                for ent in doc.ents:
                    print("Entities:", ent.text, ent.start_char, ent.end_char,
                          ent.label_)
                print()
예제 #8
0
    def handle(self, *args, **options):

        path = options['xlsx']
        try:
            data = get_data(path)
        except (AssertionError, NoSupportingPluginFound):
            raise CommandError('Archivo no válido')

        for key in data.keys():
            sheet = data[key]
            keys = sheet[0]
            for row in sheet[1:]:
                row_data = dict(zip(keys, row))
                pk = int(row_data.get('pk', 0))
                if not pk:
                    continue
                calidad = row_data['calidad']
                if calidad in ('A', 'B', 'C'):
                    lat = row_data['lat']
                    lon = row_data['lon']

                    geom = {'type': 'Point', 'coordinates': [lon, lat]}
                    escuela = LugarVotacion.objects.get(id=pk)
                    escuela.geom = geom
                    escuela.longitud = lon
                    escuela.latitud = lat
                    escuela.calidad = calidad
                    escuela.save()
                    self.success(f'actualizada {escuela} con calidad {calidad}')
                else:
                    self.warning(f'{pk} ignorada')
예제 #9
0
파일: graph.py 프로젝트: DrSpez/rtsoft-test
def read_excel_graph(filename, draw=False):
    data = get_data(filename)
    G = nx.DiGraph()
    nodes = set([])
    edges = []

    i = 0
    for line in data.values():
        for v in line:
            i += 1
            if i > 2:
                node_id, void, a, b = v
                nodes.add(node_id)
                edges.append([a, b])

    try:
        nodes.remove(None)
    except:
        pass

    G.add_nodes_from(nodes)
    G.add_edges_from(edges)

    if draw:
        pos = nx.spring_layout(G)
        nx.draw_networkx_nodes(G, pos, node_color='yellow')
        nx.draw_networkx_edges(G, pos, edge_color='black')
        nx.draw_networkx_labels(G, pos, font_size=7)
        plt.show(block=False)

    return G
예제 #10
0
def test_complex_hidden_sheets():
    data = get_data(os.path.join("tests", "fixtures",
                                 "complex_hidden_sheets.xlsx"),
                    skip_hidden_row_and_column=True,
                    library='pyexcel-xlsx')
    expected = [[1, 3, 5, 7, 9], [31, 33, 35, 37, 39], [61, 63, 65, 67]]
    eq_(data['Sheet1'], expected)
예제 #11
0
def as_read_excel(PTE, action, plate, filename, sheetname, conditions=None):
    if conditions:
        for attr, value in conditions.items():
            setattr(PTE, attr, value)
    getattr(PTE, action)(plate)
    getattr(PTE, "close")()
    return get_data(filename)[sheetname]
예제 #12
0
def registerBulkProduct(level1Category,fileName):
  try:
    connection, db, collection = MongoDBconnection('Admin', 'Categories')
    iter = collection.find({'_id':level1Category},{"Categories":True})
    categories = iter[0]['Categories']
    MainCategories = []
    for category in categories:
      MainCategories.append(category.keys()[0])
    records = get_data(fileName)
    i = 1
    while i < len(records) and i<1098:
      product = {'Level1 Category':records[i][1], 'Main Category': records[i][2], 'Sub Category': records[i][3], 'product_name': records[i][5], 'Product Category': records[i][4], 'Quantity': [{'City':'Hyderabad', 'Quantities':[]}]}
      product['_id']='1_'+str(MainCategories.index(records[i][2]))+'_'
      product['_id']='1_' + str(MainCategories.index(records[i][2])) + '_' + str(categories[MainCategories.index(records[i][2])][records[i][2]].index(records[i][3]))
      j = i
      while records[i][5] == records[j][5]:
	quantity = [records[j][8], int(records[j][9]), int(records[j][9]), 0, 'Available']
	product['Quantity'][0]['Quantities'].append(quantity)
	j=j+1
      print json.dumps(product)
      reply, pid = registerProduct(product['Main Category'], product['Sub Category'], str(json.dumps(product)))
      if reply == 'Registered' and pid:
	ProductImagePath(pid)
      print 
      i = j
    connection.close()
    gc.collect()
    return 'Registered'
  except Exception as e:
    return str(e)
    return 'Unable to Register', '[]'
예제 #13
0
def update_many_data():
    excel_data = get_data("69码更新144个---201704201958.xlsx")
    sheet_data = excel_data['Sheet1'][1:]  # 获取Sheet1的第二行之后的数据
    print("excel数据:{}".format(sheet_data))
    sql_data = [tuple(x[::-1]) for x in sheet_data]
    print("sql_data:{}".format(sql_data))
    return sql_data
def read_spreadsheet(request):
    spreadsheet_filename = os.path.join(settings.MEDIA_ROOT, 'NYS Drop Box Locations 2017-01-31.xlsx')
    data = get_data(spreadsheet_filename)

    datadict = {}
    i = 0
    for sheet in data.keys():
        print('DEBUG ', data[sheet][0])
        for item in data[sheet]:
            i += 1
            print('\tDEBUG item=', item)
            datadict[i] = {
                'location_name': item[0],
                'street_address': item[1],
                'city': item[2],
                'zip': item[3],
                'borough': item[4],
                'county': item[5]
            }
            Place.objects.create(
                location_name=item[0],
                street_address=item[1],
                city=item[2],
                zip=item[3],
                borough=item[4],
                county=item[5],

            )
    print('DEBUG datadict={}'.format(datadict))

    return redirect('/admin/location/place/')
def parse_xlsx_files(xl_files_path):
    xl_files = [os.path.join(xl_files_path, filename) for filename in os.listdir(xl_files_path) if '.xlsx' in filename]
    genotype_data = {}
    for xl_file in xl_files:
        data = get_data(xl_file)
        sheet_name = 'HaploView_ped_0'
        data = data.get(sheet_name)
        header = data[0]

        for row in data[1:]:
            if not re.match(r"^ID\d+-P\d+_\d+", row[1]):
                continue
            # if the same sample occurs in a different 'xlsx' files, will be overwriten
            sample_id = row[1].split('-')[-1]
            project_id = sample_id.split('_')[0]
            for rs_id in header[9:]:
                rs_index = header.index(rs_id)
                if project_id not in genotype_data:
                    genotype_data[project_id] = {}
                if sample_id not in genotype_data[project_id]:
                    genotype_data[project_id][sample_id] = {}
                reference, alternative = row[rs_index].split()
                genotype_data[project_id][sample_id][rs_id] = {'gt_allele1': reference, 'gt_allele2': alternative}

        # todo: add try-except
        new_path = os.path.join(xl_files_path, 'archived')
        if not os.path.exists(new_path):
            os.makedirs(new_path)

        filename = os.path.basename(xl_file)
        # shutil.move(xl_file, os.path.join(new_path, filename))

    return genotype_data
예제 #16
0
파일: handle_excel.py 프로젝트: Ggzzhh/ore4
 def init_dept(self):
     data = get_data(self.excel_unit)['单位']
     for dept in data:
         res = Dept.query.filter_by(dept_name=dept[0]).first()
         if res is None:
             system = System.query.filter_by(system_name=dept[1]).first()
             dept_pro = DeptPro.query \
                 .filter_by(dept_pro_name=dept[2]).first()
             _dept = Dept(dept_name=dept[0])
             if system:
                 _dept.system = system
             if dept_pro:
                 _dept.dept_pro = dept_pro
             db.session.add(_dept)
             message = '插入完成'
         else:
             message = '开始更新所属职务属性以及其系统'
             res.dept_pro = DeptPro.query \
                 .filter_by(dept_pro_name=dept[2]).first()
             res.system = System.query \
                 .filter_by(system_name=dept[1]).first()
             if res.order is None:
                 res.order = 10
             db.session.add(res)
             # print(dept[0] + message)
     print('单位分类更新完毕')
예제 #17
0
def get_msfd_reporting_history_from_file(file):
    res = PersistentList()
    sheets = get_data(file)
    env_data = sheets['ENV']

    indx_date_due = env_data[0].index('DateDue')
    indx_date_received = env_data[0].index('DateReceived')

    for row in env_data:
        row = [isinstance(x, basestring) and x.strip() or x for x in row]
        while len(row) < 14:
            row.append('')

        date_due = row[indx_date_due]
        date_received = row[indx_date_received]

        if isinstance(date_due, datetime):
            row[indx_date_due] = date_due.date()

        if isinstance(date_received, datetime):
            row[indx_date_received] = date_received.date()

        res.append(ReportingHistoryENVRow(*row[:14]))

    return res
def add_resection_status(path):
    """add resection status to database """

    conn = sqlite3.connect(util.DB_PATH)

    try:
        conn.execute("alter table Patient add column 'resection' 'INTEGER'")
    except sqlite3.OperationalError:
        pass

    # Add resection status
    data = pyexcel_xlsx.get_data(path + "Biopsy_cases.xlsx")
    case_list = [int(pid[0]) for pid in data['Ark1'][2:]]

    cursor = conn.execute("SELECT pid FROM Patient WHERE study_id LIKE '%GBM_survival_time%'")
    for pid in cursor:
        pid = pid[0]
        if pid in case_list:
            resection_status = 0
        else:
            resection_status = 1
        conn.execute("UPDATE Patient SET resection = ? WHERE pid = ?",
                    (resection_status, pid))
        conn.commit()

    cursor.close()
    conn.close()
예제 #19
0
 def test_is_stack(self):
     self.stack.to_excel("test.xlsx")
     remove_sheet("test.xlsx")
     From = _BioPlateFromExcel("test.xlsx")
     Fr = From.get_BioPlate_object()
     self.assertTrue(
         From.is_stack(get_data("test.xlsx")["plate_representation"]))
예제 #20
0
    def load_excel(self):
        data = dict()
        raw = pyexcel_xlsx.get_data(self.excel_filename)

        data['meta'] = dict()
        try:
            data['meta'][self.SHEET_SETTINGS] = OrderedDict()
            for row in raw.pop(self.SHEET_SETTINGS):
                data['meta'][self.SHEET_SETTINGS][row[0]] = row[1]
        except (KeyError, IndexError) as e:
            print(e)

        try:
            data['meta'][self.SHEET_DECKS] = dict()
            header, *records = raw.pop(self.SHEET_DECKS)
            for record in records:
                data['meta'][self.SHEET_DECKS][record[0]] = OrderedDict(
                    zip(header, record))
        except (KeyError, IndexError) as e:
            print(e)

        data['data'] = OrderedDict()
        for note_type, values in raw.items():
            header, *records = values
            data['data'][note_type] = dict()
            for record in records:
                data['data'][note_type][record[0]] = OrderedDict(
                    zip(header, record))

        return data
예제 #21
0
    def import_translations(self):
        alsoProvides(self.request, IDisableCSRFProtection)

        file_loc = resource_filename(
            'wise.msfd', 'data/MSFDTranslations.xlsx'
        )

        with open(file_loc, 'rb') as file:
            sheets = get_data(file)
            transl_data = sheets['translations']

            for row in transl_data:
                try:
                    lang = row[0]
                    orig = row[1]
                    transl = len(row) > 2 and row[2] or ''
                    approved = len(row) == 4 and int(row[3]) or 0

                    if not transl:
                        delete_translation(orig, lang)

                        continue

                    save_translation(orig, transl, lang, approved=approved)

                except:
                    pass
def write_data(product_price, excel_data, companies):

    rows, columns = excel_data.shape
    j = 0
    i = 0
    new_data = []

    new_data.append([])
    nested_list = new_data[0]
    for companies_name in companies:
        nested_list.append(companies_name)

    while i < rows:
        new_data.append([])
        nested_list = new_data[i + 1]
        while j < columns:
            column = excel_data.columns[j]
            selected_data = excel_data[column].tolist()

            nested_list.append(selected_data[i])
            j += 1
        j = 0
        i += 1

    new_data.append([])
    nested_list = new_data[-1]
    for item in product_price:
        nested_list.append(item)

    data = get_data("Data.xlsx")
    data.update({"Sheet1": new_data})
    save_data("Data.xlsx", data)
예제 #23
0
def fill_refs(apps, schema_editor):
    data = get_data("{0}/{1}.xlsx".format(
        os.path.join(settings.BASE_DIR, 'refs_data'), 'devices'))
    data = data['Отчет']

    DeviceType = apps.get_model('collector', 'DeviceType')
    DeviceBrand = apps.get_model('collector', 'DeviceBrand')
    Device = apps.get_model('collector', 'Device')

    phone = DeviceType.objects.create(category=1)
    tablet = DeviceType.objects.create(category=2)
    other = DeviceType.objects.create(category=3)

    brand_other = DeviceBrand.objects.create(name="Other")

    device_types = {"Смартфоны": phone, "Планшеты": tablet}

    for d in data[1:]:
        brand = d[1].lower()
        if brand == "не определено":
            brand = brand_other

        if d[0] not in device_types.keys():
            continue
        device_type = device_types.get(d[0])

        brand, created = DeviceBrand.objects.get_or_create(name=brand)

        Device.objects.create(device_type=device_type,
                              brand=brand,
                              model=d[2].lower()[0:49])

    Device.objects.create(device_type=other, brand=brand_other, model="other")
예제 #24
0
파일: assessment.py 프로젝트: eea/wise.msfd
def parse_assessments_2012_file():
    f = resource_filename('wise.msfd',
                          'data/Regional_Descriptors_2012_assessment.xlsx')

    res = []

    with open(f, 'rb') as file:
        sheets = get_data(file)
        rows = sheets['Assessment']

        for row in rows[1:]:
            if not row:
                break

            if row[0] == 'NEA':
                row[0] = u'ATL'

            report_by = 'Commission'
            assessment_by = 'Milieu'
            date_assessed = datetime.datetime.strptime('2019-09-20',
                                                       '%Y-%m-%d')
            commission_report = 'http://ec.europa.eu/environment/marine/' \
                                'eu-coast-and-marine-policy/implementation/' \
                                'pdf/national_reports.zip'

            row.extend(
                [report_by, assessment_by, date_assessed, commission_report])

            res.append(Assessment_2012(*row))

    return res
예제 #25
0
    def __generate(self, limit, row_num, filename, order_num, file_count):
        order_data = get_data(self.order_template)
        order_columns = order_data['订单归集']
        for order_num_count in range(limit):
            good_subscript = randrange(
                0,
                len(self.goods_data) - self.goods_in_order_num)
            for goods_count in range(0, self.goods_in_order_num):
                column_data = ['' for j in range(row_num)]
                column_data[0] = order_num + str(order_num_count)
                column_data[5] = self.customer_data[file_count]['name']
                column_data[7] = self.customer_data[file_count]['contactor']
                column_data[8] = '11111111111'
                column_data[9] = self.customer_data[file_count]['area']
                column_data[10] = '北京市'
                column_data[11] = '北京市'
                column_data[12] = '东城区'
                column_data[14] = self.goods_data[good_subscript +
                                                  goods_count]['code']
                column_data[15] = self.goods_data[good_subscript +
                                                  goods_count]['name']
                column_data[17] = self.goods_data[good_subscript +
                                                  goods_count]['marketPrice']
                column_data[18] = randrange(1, 50)
                order_columns.append(column_data)

        new_order_data = OrderedDict()
        new_order_data.update({'订单归集': order_columns})
        save_data(filename, new_order_data)
예제 #26
0
def readexceldata(path_file):
    book = get_data(path_file)
    data_set = np.array(book['filtro'])
    filtro = np.array([row for row in data_set if row[6] <= 2])
    n_filas = filtro.shape[0]

    rangos, filtro2 = [0, 0, 0], []
    for row in filtro[:n_filas - 4]:
        if row[6] == 2:
            valor_selecc = int((row[1] + row[2]) / 2)
        else:
            valor_selecc = int(random.choice(row[1:3]))
        if valor_selecc < 4:
            rangos[0] += 1
            valor_selecc = 1
        elif valor_selecc > 6:
            rangos[2] += 1
            valor_selecc = 3
        else:
            rangos[1] += 1
            valor_selecc = 2

        row[0] = row[0].encode('latin-1', errors='ignore').replace('<<=>>', '')
        filtro2.append((row[0], valor_selecc))
    return filtro2
예제 #27
0
def Unique_Check():
    rd_data = get_data(Excel_filename)
    sv_data = OrderedDict()
    len1 = len(rd_data)
    len2 = len(rd_data.values()[0])
    len3 = len(rd_data.values()[0][0])  # Excel save directory
    sheet_data = [[[0 for y in xrange(len3)] for x in xrange(len2 * 2)]
                  for z in xrange(len1)]  # create a sheet list for Excel
    All_Chipid = []  # restore All Chip ID
    for i in xrange(len1):
        for j in xrange(len2):
            for k in xrange(len3):
                print i, j, k
                All_Chipid += [rd_data[rd_data.keys()[i]][j][k]]
        # print All_Chipid                                                                      # check unique chip_id
    Repe_Chipid = [
        val for val in list(set(All_Chipid)) if All_Chipid.count(val) >= 2
    ]  # search repeated  chip_id
    print "Repeted Chip ID:", Repe_Chipid  # print repeated chip ID
    with open("Uniqueness.txt",
              'w') as uniquenessfile:  # save repeated chip ID to txt file
        for i in xrange(len1):
            for j in xrange(len2):
                for k in xrange(len3):
                    # print i, j, k
                    for id in xrange(len(Repe_Chipid)):
                        if Repe_Chipid[id] == rd_data[rd_data.keys(
                        )[i]][j][k] and Repe_Chipid[id] != 9008:
                            print rd_data.keys()[i], j, k, rd_data[
                                rd_data.keys()[i]][j][k]
                            uniquenessfile.write(
                                "%s %2d %2d %s\n" %
                                (rd_data.keys()[i], j, k,
                                 rd_data[rd_data.keys()[i]][j][k]))
예제 #28
0
def test_reading():
    data = get_data(os.path.join("tests", "fixtures", "date_field.xlsx"),
                    library="pyexcel-xlsx",
                    skip_hidden_row_and_column=False)
    expected = OrderedDict()
    expected.update({
        "Sheet1": [
            ["Date", "Time"],
            [
                datetime(year=2014, month=12, day=25),
                time(hour=11, minute=11, second=11),
            ],
            [
                datetime(2014, 12, 26, 0, 0),
                time(hour=12, minute=12, second=12),
            ],
            [
                datetime(2015, 1, 1, 0, 0),
                time(hour=13, minute=13, second=13),
            ],
            [
                datetime(year=1899, month=12, day=30),
                datetime(1899, 12, 30, 0, 0),
            ],
        ]
    })
    expected.update({"Sheet2": []})
    expected.update({"Sheet3": []})
    eq_(data, expected)
예제 #29
0
def repeated_ber_check():
    Excel_filename = "Sinewave_BER1_Results.xlsx"  # Excel filename
    rd_data = get_data(Excel_filename)
    with open("BER_Record_20181109.txt", 'r') as ber_record_file, open(
            "Repeat_BER_Check.txt", 'w') as reber_file:  # BER Record file
        for row in ber_record_file.readlines():
            chip_id = row.split()[3]  # achieve chip id
            Chip_Test = 0
            Chip_Test1 = 0
            Chip_Result = 0
            result = "BNR"
            for row in xrange(4, len(rd_data[rd_data.keys()[1]]), 1):
                if rd_data[rd_data.keys()[1]][row][0] == int(chip_id):
                    Chip_Test = 1  # Chip test
                # print len(rd_data[rd_data.keys()[1]][row])
                if len(rd_data[rd_data.keys()[1]][row]) >= 25:
                    if rd_data[rd_data.keys()[1]][row][0] == int(chip_id):
                        # print rd_data[rd_data.keys()[1]][row]
                        Chip_Test1 = 1
                        Chip_Result = rd_data[rd_data.keys()[1]][row][24]
            if Chip_Test == 1 and Chip_Test1 == 0:
                result = "BFNT"
            elif Chip_Test1 == 1 and Chip_Result == 1:
                result = "BP"
            elif Chip_Test == 0 and Chip_Test1 == 0:
                result = "BNR"
            else:
                result = "BF"
            print chip_id, result
            reber_file.write("%s %s\n" % (chip_id, result))
예제 #30
0
    def config(self, sheet_path: str) -> "table_extract":
        print(2)
        self.path = sheet_path

        self.sheet_content = get_data(self.path)

        self.json_data = json.loads(
            json.dumps(self.sheet_content, default=json_converter))

        #add a way of gathering all of the page names V

        self.page_selection_list = [
            property_name for property_name in self.json_data
        ]
        self.page_selection_iter = iter(self.page_selection_list)
        self.page_content = {}
        for property_name in self.json_data:

            page = next(self.page_selection_iter)
            self.page_content[page] = [
                property_value if property_value != [] else None
                for property_value in self.json_data[property_name]
            ]

            print(self.pages)
            if page in self.pages:

                self.table_extract(self.page_content[page], False)
예제 #31
0
def read_spreadsheet(request):
    spreadsheet_filename = os.path.join(
        settings.MEDIA_ROOT, 'NYS Drop Box Locations 2017-01-31.xlsx')
    data = get_data(spreadsheet_filename)

    datadict = {}
    i = 0
    for sheet in data.keys():
        print('DEBUG ', data[sheet][0])
        for item in data[sheet]:
            i += 1
            print('\tDEBUG item=', item)
            datadict[i] = {
                'location_name': item[0],
                'street_address': item[1],
                'city': item[2],
                'zip': item[3],
                'borough': item[4],
                'county': item[5]
            }
            Place.objects.create(
                location_name=item[0],
                street_address=item[1],
                city=item[2],
                zip=item[3],
                borough=item[4],
                county=item[5],
            )
    print('DEBUG datadict={}'.format(datadict))

    return redirect('/admin/location/place/')
def parse_xl_file(config, xl_file):
    genotype_data = {}
    data = pyexcel_xlsx.get_data(xl_file)
    data = data.get('HaploView_ped_0') # sheet name
    # getting list of lists
    header = data[0]
    data = data[1:]
    for row in data:
        # row[1] is always sample name. If doesn't match NGI format - skip.
        if len(row) == 0:
            continue
        if not re.match(r"^ID\d+-P\d+_\d+", row[1]):
            continue
        # sample has format ID22-P2655_176
        sample_id = row[1].split('-')[-1]
        # if the same sample occurs twice in the same file, will be overwriten
        if sample_id not in genotype_data:
            genotype_data[sample_id] = {}
        else:
            log.warning('Sample {} has been already parsed from another (or this) file. Overwriting'.format(sample_id))
        # rs positions start from 9 element. hopefully the format won't change
        for rs_id in header[9:]:
            rs_index = header.index(rs_id)
            allele1, allele2 = row[rs_index].split()
            genotype_data[sample_id][rs_id] = [allele1, allele2]
    return genotype_data
예제 #33
0
def parse_xl_file(config, xl_file):
    genotype_data = {}
    data = pyexcel_xlsx.get_data(xl_file)
    data = data.get('HaploView_ped_0')  # sheet name
    # getting list of lists
    header = data[0]
    data = data[1:]
    for row in data:
        # row[1] is always sample name. If doesn't match NGI format - skip.
        if len(row) == 0:
            continue
        if not re.match(r"^ID\d+-P\d+_\d+", row[1]):
            continue
        # sample has format ID22-P2655_176
        sample_id = row[1].split('-')[-1]
        # if the same sample occurs twice in the same file, will be overwriten
        if sample_id not in genotype_data:
            genotype_data[sample_id] = {}
        else:
            log.warning(
                'Sample {} has been already parsed from another (or this) file. Overwriting'
                .format(sample_id))
        # rs positions start from 9 element. hopefully the format won't change
        for rs_id in header[9:]:
            rs_index = header.index(rs_id)
            allele1, allele2 = row[rs_index].split()
            genotype_data[sample_id][rs_id] = [allele1, allele2]
    return genotype_data
예제 #34
0
 def saveStats(self):
     data = get_data(self.currFile)
     save_data(self.oldFile, data)
     namesDict = {}
     for i in range(1, 5):
         namesDict[data['cricStats'][i][0]] = i
     strings = ['BatArchives', 'BatArchives', 'BowlArchives']
     for i in range(len(strings)):
         len1 = len(data[strings[i]][0])
         el = []
         for j in range(len1):
             el.append('')
         data[strings[i]].append(el)
     for i in range(self.noOfInnings):
         data = self.innings[i].writeStats(data, namesDict,
                                           self.batsmanNames[i],
                                           self.bowlerNames[i])
     save_data(self.currFile, data)
     saveData = messagebox.showinfo("Stats Info",
                                    "Stats saved succesfully!!!!")
     self.lead = self.innings[0].retScore() - self.innings[1].retScore()
     if self.selected.get() == 1 and self.inningsCount == 0:
         self.window2 = Toplevel()
         self.window2.title("Innings-2")
         self.window2.geometry('1350x1200')
         self.clickedStartMatch(False)
         self.runsButtons(17, self.window2)
         self.inningsCount += 1
         self.window2.mainloop()
     self.window2.destroy()
     self.window1.destroy()
예제 #35
0
def test_exploration():
    data = get_data(
        get_fixture("merged-sheet-exploration.xlsx"),
        detect_merged_cells=True,
        library="pyexcel-xlsx",
    )
    expected_sheet1 = [
        [1, 1, 1, 1, 1, 1],
        [2],
        [2],
        [2],
        [2],
        [2],
        [2],
        [2],
        [2],
        [2],
    ]
    eq_(data["Sheet1"], expected_sheet1)
    expected_sheet2 = [[3], [3], [3], [3, 4, 4, 4, 4, 4, 4], [3], [3], [3]]
    eq_(data["Sheet2"], expected_sheet2)
    expected_sheet3 = [
        ["", "", "", "", "", 2, 2, 2],
        [],
        [],
        [],
        ["", "", "", 5],
        ["", "", "", 5],
        ["", "", "", 5],
        ["", "", "", 5],
        ["", "", "", 5],
    ]
    eq_(data["Sheet3"], expected_sheet3)
예제 #36
0
def karnofsky_to_db():
    """Convert qol data to database """
    conn = sqlite3.connect(util.DB_PATH)
    cursor = conn.cursor()

    data = pyexcel_xlsx.get_data('/home/dahoiv/disk/data/Segmentations/siste_runde_hgg/Indexverdier_atlas_250117.xlsx')['Ark3']
    try:
        conn.execute("alter table QualityOfLife add column 'karnofsky' 'INTEGER'")
    except sqlite3.OperationalError:
        pass

    k = 0
    for row in data:
        k = k + 1
        if not row:
            continue
        if k < 3:
            continue
        print(row)
        try:
            float(row[0])
        except ValueError:
            continue

        cursor.execute('''UPDATE QualityOfLife SET karnofsky = ? WHERE pid = ?''',
                       (row[1], row[0]))
        conn.commit()

    cursor.close()
    conn.close()
예제 #37
0
def test_merged_cells():
    data = get_data(
        get_fixture("merged-cell-sheet.xlsx"),
        detect_merged_cells=True,
        library="pyexcel-xlsx",
    )
    expected = [[1, 2, 3], [1, 5, 6], [1, 8, 9], [10, 11, 11]]
    eq_(data["Sheet1"], expected)
def qol_to_db(data_type):
    """Convert qol data to database """
    conn = sqlite3.connect(util.DB_PATH)
    cursor = conn.cursor()

#    cursor.executescript('drop table if exists QualityOfLife;')
#    cursor.execute('''CREATE TABLE "QualityOfLife" (
#        `id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
#        `pid`    INTEGER NOT NULL,
#        'Index_value'     REAL,
#        'Global_index'    INTEGER,
#        'Mobility'    INTEGER,
#        'Selfcare'    INTEGER,
#        'Activity'    INTEGER,
#        'Pain'    INTEGER,
#        'Anxiety'    INTEGER,
#        FOREIGN KEY(`pid`) REFERENCES `Patient`(`pid`))''')

    data = pyexcel_xlsx.get_data('/mnt/dokumneter/data/Segmentations/Indexverdier_atlas.xlsx')['Ark2']

    for row in data[4:]:
        print(row)
        if not row:
            continue

        if data_type == "gbm":
            idx1 = 1
            idx2 = 0
            idx3 = 7
        elif data_type == "lgg":
            idx1 = 12
            idx2 = 11
            idx3 = 18
        if len(row) < idx3 - 1:
            continue

        if row[idx1] is None:
            gl_idx = None
        elif row[idx1] > 0.85:
            gl_idx = 1
        elif row[idx1] > 0.50:
            gl_idx = 2
        else:
            gl_idx = 3

        val = [None]*7
        idx = 0
        for _val in row[idx2:idx3]:
            val[idx] = _val
            idx += 1
        cursor.execute('''INSERT INTO QualityOfLife(pid, Index_value, Global_index, Mobility, Selfcare, Activity, Pain, Anxiety) VALUES(?,?,?,?,?,?,?,?)''',
                       (val[0], val[1], gl_idx, val[2], val[3], val[4], val[5], val[6]))
        conn.commit()

    cursor.close()
    conn.close()
def get_convert_table(path):
    """Open xls file and read new pid"""
    xls_data = pyexcel_xlsx.get_data(path)
    convert_table = {}
    data = xls_data['Ark1']
    for row in data:
        if not row:
            continue
        pid = str(row[0])
        case_id = str(row[1])
        convert_table[case_id] = pid
    return convert_table
예제 #40
0
def registerBulkProduct(tableName,fileName):
  try:
    records = get_data(fileName)
    i = 1
    while i < len(records) and i<11:
      product = {'product_name':records[i][0], 'Actual_Price':records[i][1], 'TMS_Price':records[i][2], 'Shop_Name': tableName, '_id':''}
      i=i+1
      reply= registerProduct('TMS', tableName, str(json.dumps(product)))
      #if reply == 'Registered' and pid:
	#ProductImagePath(pid)
    gc.collect()
    return 'Registered'
  except Exception as e:
    return str(e)
    return 'Unable to Register', '[]'
예제 #41
0
def test_complex_merged_cells():
    data = get_data(
        get_fixture("complex-merged-cells-sheet.xlsx"),
        detect_merged_cells=True,
        library="pyexcel-xlsx",
    )
    expected = [
        [1, 1, 2, 3, 15, 16, 22, 22, 24, 24],
        [1, 1, 4, 5, 15, 17, 22, 22, 24, 24],
        [6, 7, 8, 9, 15, 18, 22, 22, 24, 24],
        [10, 11, 11, 12, 19, 19, 23, 23, 24, 24],
        [13, 11, 11, 14, 20, 20, 23, 23, 24, 24],
        [21, 21, 21, 21, 21, 21, 23, 23, 24, 24],
        [25, 25, 25, 25, 25, 25, 25, 25, 25, 25],
        [25, 25, 25, 25, 25, 25, 25, 25, 25, 25],
    ]
    eq_(data["Sheet1"], expected)
예제 #42
0
import datetime
import pytz

# fmt = '%Y-%m-%d %H:%M:%S %Z'
# d = datetime.datetime.now(pytz.timezone("America/New_York"))
# dtz_string = d.strftime(fmt) + ' ' + "America/New_York"

# d_string, tz_string = dtz_string.rsplit(' ', 1)
# d2 = datetime.datetime.strptime(d_string, fmt)
# tz2 = pytz.timezone(tz_string)

# print dtz_string 
# print d2.strftime(fmt) + ' ' + tz_string

import pyexcel as pe
from pyexcel.ext import xlsx

from pyexcel_xlsx import get_data
data = get_data("/home/bharathi/EquipmentDetails/Dec.xlsx")
print(data)
예제 #43
0
#from pyexcel.ext import plugin
from functools import reduce
import os
import pyexcel as pe
import pyexcel.ext.xls # import it to handle xls file
import pyexcel.ext.xlsx # import it to handle xlsx file
from pyexcel_xlsx import get_data
import json
#for rx in range(sh.nrows):
#	print sh.row(rx)

records = get_data("ComparisonMatrix.xlsx")
#data = json.loads(records)
#print (data[0])
#print (json.dumps(records))
print (records['Sheet1'][0][1])
print (records['Sheet1'][1][2])

sheetAtacante = records['Sheet1']

print(sheetAtacante[1][2])





negatives = ['PerdaPosse','GolSofrido','FaltaCometida',
'CartaoAmarelo','CartaoVermelho','Impedimento','GolSofrido']
positives = ['Jogos','FaltaRecebida','Passe','GolPro','Desarme',
'Finalizacao','Defesa','ViradaJogo','Cruzamento','Drible']