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)
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)
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)
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
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)
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()
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')
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
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)
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]
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', '[]'
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
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('单位分类更新完毕')
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()
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"]))
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
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)
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")
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
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)
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
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]))
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)
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))
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)
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
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
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()
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)
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()
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
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', '[]'
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)
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)
#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']
