Spaces:
Sleeping
Sleeping
| # -*- coding: utf-8 -*- | |
| """Google Sheet to XML.ipynb | |
| Automatically generated by Colaboratory. | |
| Original file is located at | |
| https://colab.research.google.com/drive/1T-b1N8Gq6wwbBurODzJIdhRQNeRbyBnz | |
| """ | |
| from googleapiclient.discovery import build | |
| from google.oauth2 import service_account | |
| import pygsheets | |
| import ast | |
| import re | |
| import pandas as pd | |
| from google.oauth2 import service_account | |
| from googleapiclient.discovery import build | |
| import pygsheets | |
| import re | |
| def authorizeLegend(): | |
| SCOPES = [ | |
| 'https://www.googleapis.com/auth/spreadsheets', | |
| 'https://www.googleapis.com/auth/drive', | |
| 'https://www.googleapis.com/auth/drive.metadata' | |
| ] | |
| credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES) | |
| spreadsheet_service = build('sheets', 'v4', credentials=credentials) | |
| drive_service = build('drive', 'v3', credentials=credentials) | |
| gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json') | |
| return spreadsheet_service,drive_service,gc | |
| spreadsheet_service,drive_service,gc=authorizeLegend() | |
| def create_new_sheet(path): | |
| spreadsheet_details = { | |
| 'properties': { | |
| 'title': path | |
| } | |
| } | |
| sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, fields='spreadsheetId').execute() | |
| spreadsheetId = sheet.get('spreadsheetId') | |
| permission1 = { | |
| 'type': 'anyone', | |
| 'role': 'writer', | |
| } | |
| drive_service.permissions().create(fileId=spreadsheetId, body=permission1, supportsAllDrives=True).execute() | |
| return spreadsheetId | |
| def update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath): | |
| ws = gc.open_by_key(spreadsheetId) | |
| worksheet = ws.worksheet(0) | |
| worksheet.title = 'Legend and data created' | |
| worksheet.clear() | |
| ws.create_developer_metadata('path', pdfpath) | |
| splittedpdfpath = re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,/<>?]', pdfpath) | |
| if splittedpdfpath[-2].startswith('2.2') or splittedpdfpath[-2].startswith('2.1'): | |
| worksheet.set_dataframe(start='A1', df=SimilarAreaDictionary) | |
| else: | |
| top_header_format = [ | |
| {'mergeCells': { # areas | |
| 'mergeType': 'MERGE_ROWS', | |
| 'range': { | |
| 'sheetId': '0', | |
| 'startRowIndex': 1, | |
| 'endRowIndex': 2, | |
| 'startColumnIndex': 3, | |
| 'endColumnIndex': 5 | |
| } | |
| }}, | |
| {'mergeCells': { # perimeters | |
| 'mergeType': 'MERGE_ROWS', | |
| 'range': { | |
| 'sheetId': '0', | |
| 'startRowIndex': 1, | |
| 'endRowIndex': 2, | |
| 'startColumnIndex': 5, | |
| 'endColumnIndex': 7 | |
| } | |
| }}, | |
| {'mergeCells': { # lengths | |
| 'mergeType': 'MERGE_ROWS', | |
| 'range': { | |
| 'sheetId': '0', | |
| 'startRowIndex': 1, | |
| 'endRowIndex': 2, | |
| 'startColumnIndex': 7, | |
| 'endColumnIndex': 9 | |
| } | |
| }}, | |
| {'mergeCells': { # legend and data created | |
| 'mergeType': 'MERGE_ROWS', | |
| 'range': { | |
| 'sheetId': '0', | |
| 'startRowIndex': 0, | |
| 'endRowIndex': 1, | |
| 'startColumnIndex': 0, | |
| 'endColumnIndex': 11 | |
| } | |
| }} | |
| ] | |
| spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': top_header_format}).execute() | |
| worksheet.cell((1, 1)).value = 'Legend and Data Created' | |
| worksheet.cell((2, 1)).value = 'Guess' | |
| worksheet.cell((2, 2)).value = 'Color' | |
| worksheet.cell((2, 3)).value = 'Count' | |
| worksheet.cell((2, 4)).value = 'Areas' | |
| worksheet.cell((2, 6)).value = 'Perimeters' | |
| worksheet.cell((2, 8)).value = 'Lengths' | |
| worksheet.cell((2, 10)).value = 'Texts' | |
| worksheet.cell((2, 11)).value = 'Comments' | |
| second_row_data = ['Nr', 'm2', 'Total', 'm', 'Total', 'm', 'Total'] | |
| worksheet.update_row(3, second_row_data, col_offset=2) | |
| worksheet.update_col(1, list(SimilarAreaDictionary['Guess']), row_offset=3) | |
| worksheet.update_col(3, list(SimilarAreaDictionary['Occurences']), row_offset=3) | |
| worksheet.update_col(4, list(SimilarAreaDictionary['Area']), row_offset=3) | |
| worksheet.update_col(5, list(SimilarAreaDictionary['Total Area']), row_offset=3) | |
| worksheet.update_col(6, list(SimilarAreaDictionary['Perimeter']), row_offset=3) | |
| worksheet.update_col(7, list(SimilarAreaDictionary['Total Perimeter']), row_offset=3) | |
| worksheet.update_col(8, list(SimilarAreaDictionary['Length']), row_offset=3) | |
| worksheet.update_col(9, list(SimilarAreaDictionary['Total Length']), row_offset=3) | |
| worksheet.update_col(10, list(SimilarAreaDictionary['Texts']), row_offset=3) | |
| worksheet.update_col(11, list(SimilarAreaDictionary['Comments']), row_offset=3) | |
| if splittedpdfpath[-2].startswith('1.0'): | |
| colorsUsed = [ | |
| [SimilarAreaDictionary['R'].iloc[i], SimilarAreaDictionary['G'].iloc[i], SimilarAreaDictionary['B'].iloc[i]] | |
| for i in range(len(SimilarAreaDictionary)) | |
| ] | |
| elif splittedpdfpath[-2].startswith('3.2'): | |
| colorsUsed = list(SimilarAreaDictionary['Color']) | |
| rowsLen = len(SimilarAreaDictionary.values.tolist()) | |
| lastcell = worksheet.cell((rowsLen + 2, 1)) | |
| lastcellNotation = str(lastcell.address.label) | |
| columnsLen = len(SimilarAreaDictionary.columns.values.tolist()) | |
| lastUsedCol = columnsLen + 1 | |
| worksheet.adjust_column_width(start=2, end=3) | |
| worksheet.adjust_column_width(start=10, end=10) | |
| worksheet.adjust_column_width(start=4, end=9, pixel_size=60) | |
| startrow = 3 | |
| endColindex = 11 | |
| endrow = 3 | |
| sheetId = '0' | |
| batch_requests = [] | |
| for i in range(len(colorsUsed)): | |
| r, g, b = colorsUsed[i] | |
| batch_requests.append({ | |
| "updateCells": { | |
| "range": { | |
| "sheetId": sheetId, | |
| "startRowIndex": i + startrow, | |
| "startColumnIndex": 1, | |
| }, | |
| "rows": [ | |
| { | |
| "values": [ | |
| { | |
| "userEnteredFormat": { | |
| "backgroundColor": { | |
| "red": r / 255, | |
| "green": g / 255, | |
| "blue": b / 255, | |
| "alpha": 0.4, | |
| } | |
| } | |
| } | |
| ] | |
| } | |
| ], | |
| "fields": "userEnteredFormat.backgroundColor", | |
| } | |
| }) | |
| batch_requests.append({ | |
| "updateBorders": { | |
| "range": { | |
| "sheetId": sheetId, | |
| "startRowIndex": 0, | |
| "endRowIndex": len(SimilarAreaDictionary) + endrow, | |
| "startColumnIndex": 0, | |
| "endColumnIndex": endColindex | |
| }, | |
| "top": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green": 0.0, | |
| "blue": 0.0 | |
| } | |
| }, | |
| "bottom": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green": 0.0, | |
| "blue": 0.0 | |
| } | |
| }, | |
| "left": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green": 0.0, | |
| "blue": 0.0 | |
| } | |
| }, | |
| "right": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green": 0.0, | |
| "blue": 0.0 | |
| } | |
| }, | |
| "innerHorizontal": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green": 0.0, | |
| "blue": 0.0 | |
| } | |
| }, | |
| "innerVertical": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green": 0.0, | |
| "blue": 0.0 | |
| } | |
| } | |
| } | |
| }) | |
| spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body={'requests': batch_requests}).execute() | |
| model_cell = worksheet.cell('A1') | |
| model_cell.set_text_format('bold', True) | |
| model_cell.set_horizontal_alignment(pygsheets.custom_types.HorizontalAlignment.CENTER) | |
| model_cell.color = (213 / 255, 219 / 255, 255 / 255) | |
| pygsheets.DataRange('A2', 'K2', worksheet=worksheet).apply_format(model_cell) | |
| def legendGoogleSheets(SimilarAreaDictionary, path, pdfpath, spreadsheetId=0): | |
| titles = gc.spreadsheet_titles() | |
| if path in titles: | |
| ws = gc.open(path) | |
| spreadsheetId = ws.id | |
| else: | |
| spreadsheetId = create_new_sheet(path) | |
| ws=gc.open_by_key(spreadsheetId) | |
| update_sheet(spreadsheetId, SimilarAreaDictionary, pdfpath) | |
| spreadsheet_url = f"https://docs.google.com/spreadsheets/d/{spreadsheetId}" | |
| drive_service.permissions().update(transferOwnership=True, fileId=spreadsheetId, permissionId='11OfoB4Z6wOVII8mYmbnCbbqTQs7rYA65') | |
| namepathArr = [path, spreadsheetId, ws.get_developer_metadata('path')[0].value] | |
| return gc, spreadsheet_service, spreadsheetId, spreadsheet_url, namepathArr | |
| ####################### | |
| def mapnametoLegend(McTName): | |
| sectionKey = McTName.pop() | |
| key=sectionKey[0] | |
| section=sectionKey[1] | |
| # spreadsheet_service,drive_service,gc=authorizeLegend() | |
| spreadsheet_key =str(key) # Please set the Spreadsheet ID. | |
| ws = gc.open_by_key(spreadsheet_key) | |
| # guessednamesfinal=getguessnames(gc,ws) | |
| sheetnames=[] | |
| unit='' | |
| # ws.add_worksheet("Summary") # Please set the new sheet name. | |
| for i in ws._sheet_list: | |
| print(i) | |
| sheetnames.append(i.title) | |
| print(i.index) | |
| if 'XML Export Summary' in sheetnames: | |
| worksheetS = ws.worksheet_by_title('XML Export Summary') | |
| else: | |
| ws.add_worksheet("XML Export Summary") # Please set the new sheet name. | |
| worksheetw = ws.worksheet(0) #legend | |
| worksheetS = ws.worksheet_by_title('XML Export Summary') | |
| summaryId= ws[1].id | |
| print('summaryyyID',summaryId) | |
| print('summaryyyID2',worksheetS.id) | |
| worksheetS.clear() | |
| countnames=0 | |
| row0=['MC_T Name','Qty','Unit'] | |
| worksheetS.update_row(1,row0) | |
| for i in range(len(McTName)): | |
| allgbnames='' | |
| item='' | |
| print(McTName[i][0]) | |
| # firstpart= re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,./<>?]', McTName[i][0]) | |
| print('kkk' ,McTName[i][2]) | |
| if McTName[i][2].startswith('Area'): | |
| if section.startswith('1.0') or section.startswith('3.2'): | |
| rowvalue=5# column 5 | |
| ar=0 | |
| unit='m2' | |
| if McTName[i][2].startswith('Perimeter'): | |
| if section.startswith('1.0') or section.startswith('3.2'): | |
| rowvalue=7# column 7 | |
| ar=0 | |
| unit='m' | |
| if McTName[i][2].startswith('Length'): | |
| if section.startswith('1.0') or section.startswith('3.2'): | |
| rowvalue=9# column 7 | |
| ar=0 | |
| unit='m' | |
| if McTName[i][2].startswith('Count'): | |
| if section.startswith('1.0') or section.startswith('3.2'): | |
| rowvalue=3# column 7 | |
| ar=0 | |
| unit='Nr' | |
| print('mcct',McTName[i][1]) | |
| if isinstance(McTName[i][1], list): | |
| guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) | |
| for m in McTName[i][1]: | |
| if m: | |
| if m.startswith('text1'): | |
| name=m.removeprefix('text1') | |
| allgbnames+= name +' +' | |
| indices = [o for o, x in enumerate(guessednames) if x == name] | |
| print(indices) | |
| for j in range(len(indices)): | |
| # print('kjjjj',roww[j]) | |
| ar+=float(worksheetw.cell((indices[j]+1 ,rowvalue)).value) | |
| else: | |
| item+=m + ' ,' | |
| print(item) | |
| n= McTName[i][0] + ' ( '+ allgbnames[:-2] +' , ' + item[:-1] + ' ) ' | |
| else: | |
| if McTName[i][1].startswith('text1'): | |
| name=McTName[i][1].removeprefix('text1') | |
| allgbnames+= name | |
| roww=worksheetw.find(name) | |
| print(roww) | |
| for j in range(len(roww)): | |
| print('kjjjj',roww[j]) | |
| ar+=float(worksheetw.cell((roww[j].row ,rowvalue)).value) | |
| n= McTName[i][0] + ' ( '+ allgbnames + ' ) ' | |
| rowi=[str(n),ar,unit] | |
| worksheetS.update_row(i+2,rowi) | |
| # worksheetS.adjust_column_width(start=1,end=4) | |
| worksheetS.adjust_column_width(start=1,end=1, pixel_size=350) | |
| worksheetS.adjust_column_width(start=2,end=2, pixel_size=100) | |
| worksheetS.adjust_column_width(start=3,end=3) | |
| xx=(worksheetS.cell( ( len(McTName) +1 ,3)) ).address.label | |
| model_cell1 =worksheetS.cell('A2') | |
| model_cell1.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.LEFT ) | |
| pygsheets.DataRange('A2', str(xx), worksheet=worksheetS).apply_format(model_cell1) | |
| model_cell =worksheetS.cell('A1') | |
| model_cell.set_text_format('bold', True) | |
| model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER ) | |
| pygsheets.DataRange('A1','C1', worksheet=worksheetS).apply_format(model_cell) | |
| body2={ | |
| "requests": [ | |
| { | |
| "updateBorders": { | |
| "range": { | |
| "sheetId": str(summaryId), | |
| "startRowIndex": 0, | |
| "endRowIndex": len(McTName) +1 , | |
| "startColumnIndex": 0, | |
| "endColumnIndex": 3 | |
| }, | |
| "top": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green":0.0, | |
| "blue":0.0 | |
| }, | |
| }, | |
| "bottom": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green":0.0, | |
| "blue":0.0 | |
| }, | |
| }, | |
| "left":{ | |
| "style": "SOLID", | |
| "width":2, | |
| "color": { | |
| "red": 0.0, | |
| "green":0.0, | |
| "blue":0.0 | |
| }, | |
| }, | |
| "right":{ | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green":0.0, | |
| "blue":0.0 | |
| }, | |
| }, | |
| "innerHorizontal":{ | |
| "style": "SOLID", | |
| "width":2, | |
| "color": { | |
| "red": 0.0, | |
| "green":0.0, | |
| "blue":0.0 | |
| }, | |
| }, | |
| "innerVertical": { | |
| "style": "SOLID", | |
| "width": 2, | |
| "color": { | |
| "red": 0.0, | |
| "green":0.0, | |
| "blue":0.0 | |
| }, | |
| }, | |
| } | |
| } | |
| ] | |
| } | |
| spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_key, body=body2).execute() | |
| return summaryId #,guessednamesfinal | |
| # print(x,xarea) | |
| def getguessnames(gc,ws): | |
| guessednamesfinal=[] | |
| worksheetw = ws.worksheet(0) | |
| guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) | |
| print(guessednames[2:]) | |
| for item in guessednames[2:]: | |
| if item not in guessednamesfinal: | |
| guessednamesfinal.append(item) | |
| print(guessednamesfinal) | |
| return guessednamesfinal | |
| ################################################################ | |
| def deletefromlegend(deletedrows,SimilarAreaDictionarycopy,section, areaPermArr=[]): | |
| items=[] | |
| print('dletefromlegend') | |
| idx=0 | |
| if section.startswith('1.0'): | |
| areaPermArr=ast.literal_eval(areaPermArr) | |
| myDict=eval(SimilarAreaDictionarycopy) | |
| SimilarAreaDictionarycopy=pd.DataFrame(myDict) | |
| # deletedrows=eval(deletedrows) | |
| strings=deletedrows['content'] | |
| areastodelete = [] | |
| perimstodelete=[] | |
| lengthstodelete=[] | |
| for item in strings: | |
| newitem=str(item).split('\n \n') | |
| input_str = " ".join(str(newitem).split()) | |
| # Search for the Area value | |
| matchA = re.search(r"Area=(\d+\.\d+)", input_str) | |
| matchL = re.search(r"Length=(\d+\.\d+)", input_str) | |
| matchP = re.search(r"Perimeter=(\d+\.\d+)", input_str) | |
| if matchA: | |
| areastodelete.append(float(matchA.group(1))) | |
| if matchP: | |
| perimstodelete.append(float(matchP.group(1))) | |
| if matchL: | |
| lengthstodelete.append(float(matchL.group(1))) | |
| print('Areas to delete:', areastodelete) | |
| print('Perimeters to delete:', perimstodelete) | |
| print('Lengths to delete:', lengthstodelete) | |
| for i in range(len(areastodelete)):#item in areastodelete: | |
| if section.startswith('1.0'): | |
| tol=0.3 | |
| elif section.startswith('3.2'): | |
| tol=1 | |
| areamin=round(areastodelete[i],1)- tol | |
| areamax=round(areastodelete[i],1)+ tol | |
| if section.startswith('1.0'): | |
| for p in range(len(areaPermArr)): | |
| if areastodelete[i] in areaPermArr[p]: | |
| width= areaPermArr[p][1] | |
| height= areaPermArr[p][2] | |
| break | |
| widthMin= width -10 | |
| widthMax= width +10 | |
| heightMin = height-10 | |
| heightMax=height+10 | |
| if len(areastodelete)>0: | |
| found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]] | |
| elif section.startswith('3.2'): | |
| found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )]] | |
| if len(found.index.values) >0: | |
| occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences'] | |
| if occ== 1: #drop row | |
| SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0]) | |
| else: #occ minus 1 , total area - areavalue , total perim - perimvalue | |
| print('occ>1') | |
| if section.startswith('1.0'): | |
| if len(areastodelete)>0: | |
| idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ] | |
| elif section.startswith('3.2'): | |
| idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )] | |
| if len(areastodelete)>0: | |
| comment = SimilarAreaDictionarycopy.loc[idx, 'Comments'] | |
| if pd.notna(comment.iloc[0]) and 'Area' in str(comment.iloc[0]): | |
| matches = re.findall(r'\b\d+\b', str(SimilarAreaDictionarycopy.loc[idx, 'Comments'])) | |
| area_occurrences = int(matches[1]) -1 | |
| perimeter_occurrences = int(matches[2]) | |
| print(area_occurrences, perimeter_occurrences) | |
| SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' | |
| if area_occurrences > perimeter_occurrences: | |
| SimilarAreaDictionarycopy.loc[idx,'Occurences'] = area_occurrences | |
| elif perimeter_occurrences> area_occurrences: | |
| SimilarAreaDictionarycopy.loc[idx,'Occurences'] = perimeter_occurrences | |
| elif int(area_occurrences)==int(perimeter_occurrences): | |
| SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1 | |
| if section.startswith('1.0'): | |
| SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i] | |
| else: | |
| print('not yet') | |
| area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1 | |
| perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] | |
| print(area_occurrences,perimeter_occurrences) | |
| SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' | |
| SimilarAreaDictionarycopy.loc[idx,'Total Area'] = SimilarAreaDictionarycopy.loc[idx,'Total Area'] - areastodelete[i] | |
| for i in range(len(perimstodelete)):#item in areastodelete: | |
| if section.startswith('1.0'): | |
| tol=0.3 | |
| elif section.startswith('3.2'): | |
| tol=10 | |
| if len(perimstodelete)>0: | |
| print(perimstodelete[i]) | |
| perimmin=round(perimstodelete[i],1)- 0.3 | |
| perimmax=round(perimstodelete[i],1)+ 0.3 | |
| print('perimmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm') | |
| if section.startswith('1.0'): | |
| for p in range(len(areaPermArr)): | |
| if areastodelete[i] in areaPermArr[p]: | |
| print('AAA',areaPermArr[p]) | |
| area= areaPermArr[p][0] | |
| width= areaPermArr[p][1] | |
| height= areaPermArr[p][2] | |
| break | |
| widthMin= width -10 | |
| widthMax= width +10 | |
| heightMin = height-10 | |
| heightMax=height+10 | |
| found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[( (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]] | |
| elif section.startswith('3.2'): | |
| found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[( (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) )]] | |
| if len(found.index.values) >0: | |
| occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences'] | |
| if occ== 1: #drop row | |
| print('occ=1') | |
| print(found) | |
| SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0]) | |
| else: #occ minus 1 , total area - areavalue , total perim - perimvalue | |
| print('occ>1') | |
| if section.startswith('1.0'): | |
| if len(perimstodelete)>0: | |
| idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ] | |
| elif section.startswith('3.2'): | |
| if len(perimstodelete)>0: | |
| perimmin=round(perimstodelete[i],1)- 1 | |
| perimmax=round(perimstodelete[i],1)+ 1 | |
| idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) )] | |
| if len(perimstodelete)>0: | |
| comment = SimilarAreaDictionarycopy.loc[idx, 'Comments'] | |
| if pd.notna(comment.iloc[0]) and 'Area' in str(comment.iloc[0]): | |
| matches = re.findall(r'\b\d+\b', str(SimilarAreaDictionarycopy.loc[idx, 'Comments'])) | |
| area_occurrences = int(matches[1]) | |
| perimeter_occurrences = int(matches[2])-1 | |
| print(area_occurrences, perimeter_occurrences) | |
| SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' | |
| if area_occurrences > perimeter_occurrences: | |
| SimilarAreaDictionarycopy.loc[idx,'Occurences'] = area_occurrences | |
| elif perimeter_occurrences> area_occurrences: | |
| SimilarAreaDictionarycopy.loc[idx,'Occurences'] = perimeter_occurrences | |
| elif int(area_occurrences)==int(perimeter_occurrences): | |
| SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1 | |
| if section.startswith('1.0'): | |
| SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i] | |
| else: | |
| print('not yet') | |
| area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] | |
| perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1 | |
| SimilarAreaDictionarycopy.loc[idx, 'Comments'] = f'Area occurrences: {area_occurrences}, Perimeter occurrences: {perimeter_occurrences}' | |
| area_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] -1 | |
| perimeter_occurrences = SimilarAreaDictionarycopy.loc[idx, 'Occurences'].iloc[0] | |
| SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] = SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] - perimstodelete[i] | |
| return SimilarAreaDictionarycopy | |
| ###################### | |
| ###################### | |
| # # -*- coding: utf-8 -*- | |
| # """Google Sheet to XML.ipynb | |
| # Automatically generated by Colaboratory. | |
| # Original file is located at | |
| # https://colab.research.google.com/drive/1T-b1N8Gq6wwbBurODzJIdhRQNeRbyBnz | |
| # """ | |
| # from googleapiclient.discovery import build | |
| # from google.oauth2 import service_account | |
| # import pygsheets | |
| # import ast | |
| # import re | |
| # import pandas as pd | |
| # def authorizeLegend(): | |
| # SCOPES = [ | |
| # 'https://www.googleapis.com/auth/spreadsheets', | |
| # 'https://www.googleapis.com/auth/drive', | |
| # 'https://www.googleapis.com/auth/drive.metadata' | |
| # ] | |
| # credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES) | |
| # spreadsheet_service = build('sheets', 'v4', credentials=credentials) | |
| # drive_service = build('drive', 'v3', credentials=credentials) | |
| # gc = pygsheets.authorize(custom_credentials=credentials, client_secret='credentials.json') | |
| # return spreadsheet_service,drive_service,gc | |
| # def legendGoogleSheets(SimilarAreaDictionary,path ,pdfpath, spreadsheetId=0): | |
| # spreadsheet_service,drive_service,gc=authorizeLegend() | |
| # ######## | |
| # legendTitle= path | |
| # titles=gc.spreadsheet_titles() | |
| # if legendTitle in titles: | |
| # print('found sheet ', legendTitle) | |
| # ws=gc.open(str(legendTitle)) | |
| # spreadsheetId=ws.id | |
| # else: | |
| # # ####### create new sheet | |
| # print('creating new sheeet') | |
| # spreadsheet_details = { | |
| # 'properties': { | |
| # 'title': path | |
| # } | |
| # } | |
| # sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, | |
| # fields='spreadsheetId').execute() | |
| # spreadsheetId = sheet.get('spreadsheetId') | |
| # permission1 = { | |
| # 'type': 'anyone', | |
| # 'role': 'writer', | |
| # # 'emailAddress': 'marthe.adr@gmail.com' | |
| # } | |
| # # permission2 = { | |
| # # 'type': 'user', | |
| # # 'role': 'writer', | |
| # # 'emailAddress': 'marthe.adr@gmail.com', | |
| # # 'pendingOwner': True | |
| # # } | |
| # drive_service.permissions().create(fileId=spreadsheetId, body=permission1, supportsAllDrives=True ).execute() | |
| # ws=gc.open_by_key(spreadsheetId) | |
| # sheetId = '0' # Please set sheet ID. | |
| # worksheet = ws.worksheet(0) | |
| # worksheet.title='Legend and data created' | |
| # worksheet.clear() | |
| # print('PDFPATHHH',pdfpath) | |
| # ws.create_developer_metadata('path',pdfpath) | |
| # splittedpdfpath=re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,/<>?]', pdfpath) | |
| # namepathArr=[legendTitle , spreadsheetId,ws.get_developer_metadata('path')[0].value] | |
| # if splittedpdfpath[-2].startswith('2.2') or splittedpdfpath[-2].startswith('2.1') : | |
| # worksheet.set_dataframe(start='A1',df=SimilarAreaDictionary) | |
| # print(SimilarAreaDictionary) | |
| # else: | |
| # top_header_format = [ | |
| # {'mergeCells': { #areas | |
| # 'mergeType': 'MERGE_ROWS', | |
| # 'range': { | |
| # 'sheetId': '0', | |
| # 'startRowIndex': 1, | |
| # 'endRowIndex': 2, | |
| # 'startColumnIndex': 3, | |
| # 'endColumnIndex':5 | |
| # } | |
| # }}, | |
| # {'mergeCells': { #perimeters | |
| # 'mergeType': 'MERGE_ROWS', | |
| # 'range': { | |
| # 'sheetId': '0', | |
| # 'startRowIndex': 1, | |
| # 'endRowIndex': 2, | |
| # 'startColumnIndex': 5, | |
| # 'endColumnIndex':7 | |
| # } | |
| # }}, | |
| # {'mergeCells': { #lengths | |
| # 'mergeType': 'MERGE_ROWS', | |
| # 'range': { | |
| # 'sheetId': '0', | |
| # 'startRowIndex': 1, | |
| # 'endRowIndex': 2, | |
| # 'startColumnIndex': 7, | |
| # 'endColumnIndex':9 | |
| # } | |
| # }}, | |
| # {'mergeCells': { # legend and data created | |
| # 'mergeType': 'MERGE_ROWS', | |
| # 'range': { | |
| # 'sheetId': '0', | |
| # 'startRowIndex': 0, | |
| # 'endRowIndex': 1, | |
| # 'startColumnIndex': 0, | |
| # 'endColumnIndex':10 | |
| # } | |
| # }} | |
| # ] | |
| # spreadsheet_service.spreadsheets().batchUpdate( spreadsheetId=spreadsheetId , body={'requests': top_header_format} ).execute() | |
| # worksheet.cell((1,1)).value='Legend and Data Created' | |
| # worksheet.cell((2,1)).value='Guess' | |
| # worksheet.cell((2,2)).value='Color' | |
| # worksheet.cell((2,3)).value='Count' | |
| # worksheet.cell((2,4)).value='Areas' | |
| # worksheet.cell((2,6)).value='Perimeters' | |
| # worksheet.cell((2,8)).value='Lengths' | |
| # worksheet.cell((2,10)).value='Texts' | |
| # second_row_data=['Nr','m2','Total','m','Total','m','Total'] | |
| # if splittedpdfpath[-2].startswith('1.0') or splittedpdfpath[-2].startswith('3.2'): | |
| # worksheet.update_row(3,second_row_data,col_offset=2) | |
| # worksheet.update_col(1,list(SimilarAreaDictionary['Guess']),row_offset=3) | |
| # worksheet.update_col(3,list(SimilarAreaDictionary['Occurences']),row_offset=3) | |
| # worksheet.update_col(4,list(SimilarAreaDictionary['Area']),row_offset=3) | |
| # worksheet.update_col(5,list(SimilarAreaDictionary['Total Area']),row_offset=3) | |
| # worksheet.update_col(6,list(SimilarAreaDictionary['Perimeter']),row_offset=3) | |
| # worksheet.update_col(7,list(SimilarAreaDictionary['Total Perimeter']),row_offset=3) | |
| # worksheet.update_col(8,list(SimilarAreaDictionary['Length']),row_offset=3) | |
| # worksheet.update_col(9,list(SimilarAreaDictionary['Total Length']),row_offset=3) | |
| # worksheet.update_col(10,list(SimilarAreaDictionary['Texts']),row_offset=3) | |
| # if splittedpdfpath[-2].startswith('1.0'): | |
| # colorsUsed=[] | |
| # for i in range(len(SimilarAreaDictionary)): | |
| # colorsUsed.append([SimilarAreaDictionary['R'].iloc[i] ,SimilarAreaDictionary['G'].iloc[i] , SimilarAreaDictionary['B'].iloc[i]] ) | |
| # elif splittedpdfpath[-2].startswith('3.2'): | |
| # colorsUsed=list(SimilarAreaDictionary['Color']) | |
| # #legend specs here | |
| # rowsLen=len(SimilarAreaDictionary.values.tolist()) #kam row -- last row = rowsLen +1 | |
| # lastcell=worksheet.cell((rowsLen+2,1)) #row,col | |
| # lastcellNotation=str(lastcell.address.label) | |
| # # worksheet.set_data_validation('A3',lastcellNotation, condition_type='ONE_OF_LIST', condition_values=['Ground Beam','Pile Cap'], showCustomUi=True) | |
| # #get lengths of df | |
| # columnsLen=len(SimilarAreaDictionary.columns.values.tolist()) #kam column -- last col = columnsLen+1 3shan base0 | |
| # lastUsedCol=columnsLen+1 | |
| # worksheet.adjust_column_width(start=2,end=3) | |
| # worksheet.adjust_column_width(start=10,end=10) | |
| # # if splittedpdfpath[-2].startswith('1.0'): | |
| # worksheet.adjust_column_width(start=4,end=9,pixel_size=60) | |
| # startrow = 3 | |
| # # elif splittedpdfpath[-2].startswith('3.2'): | |
| # # startrow=2 | |
| # sheetId = '0' # Please set sheet ID. | |
| # for i in range(len(colorsUsed)): | |
| # print(colorsUsed[i]) | |
| # r,g,b=colorsUsed[i] | |
| # body = { | |
| # "requests": [ | |
| # { | |
| # "updateCells": { | |
| # "range": { | |
| # "sheetId": sheetId, | |
| # "startRowIndex": i+startrow, | |
| # # "endRowIndex":4 , | |
| # "startColumnIndex":1, | |
| # # "endColumnIndex": 0 | |
| # }, | |
| # "rows": [ | |
| # { | |
| # "values": [ | |
| # { | |
| # "userEnteredFormat": { | |
| # "backgroundColor": { | |
| # "red": r/255, | |
| # "green": g/255, | |
| # "blue": b/255, | |
| # "alpha": 0.4, | |
| # } | |
| # } | |
| # } | |
| # ] | |
| # } | |
| # ], | |
| # "fields": "userEnteredFormat.backgroundColor", | |
| # } | |
| # } | |
| # ] | |
| # } | |
| # res = spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body).execute() | |
| # # if splittedpdfpath[-2].startswith('1.0'): | |
| # endColindex=10 | |
| # endrow=3 | |
| # body2={ | |
| # "requests": [ | |
| # { | |
| # "updateBorders": { | |
| # "range": { | |
| # "sheetId": sheetId, | |
| # "startRowIndex": 0, | |
| # "endRowIndex": len(SimilarAreaDictionary)+endrow, | |
| # "startColumnIndex": 0, | |
| # "endColumnIndex": endColindex | |
| # }, | |
| # "top": { | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "bottom": { | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "left":{ | |
| # "style": "SOLID", | |
| # "width":2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "right":{ | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "innerHorizontal":{ | |
| # "style": "SOLID", | |
| # "width":2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "innerVertical": { | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # } | |
| # } | |
| # ] | |
| # } | |
| # spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body2).execute() | |
| # model_cell =worksheet.cell('A1') | |
| # model_cell.set_text_format('bold', True) | |
| # model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER ) | |
| # model_cell.color = (213/255, 219/255 ,255/255) | |
| # pygsheets.DataRange('A2','J2', worksheet=worksheet).apply_format(model_cell) | |
| # spreadsheet_url = "https://docs.google.com/spreadsheets/d/%s" % spreadsheetId | |
| # print(spreadsheet_url) | |
| # drive_service.permissions().update(transferOwnership=True , fileId=spreadsheetId,permissionId='11OfoB4Z6wOVII8mYmbnCbbqTQs7rYA65') | |
| # return gc,spreadsheet_service,spreadsheetId ,spreadsheet_url , namepathArr | |
| # ####################### | |
| # def mapnametoLegend(McTName): | |
| # sectionKey = McTName.pop() | |
| # key=sectionKey[0] | |
| # section=sectionKey[1] | |
| # spreadsheet_service,drive_service,gc=authorizeLegend() | |
| # spreadsheet_key =str(key) # Please set the Spreadsheet ID. | |
| # ws = gc.open_by_key(spreadsheet_key) | |
| # # guessednamesfinal=getguessnames(gc,ws) | |
| # sheetnames=[] | |
| # unit='' | |
| # # ws.add_worksheet("Summary") # Please set the new sheet name. | |
| # for i in ws._sheet_list: | |
| # print(i) | |
| # sheetnames.append(i.title) | |
| # print(i.index) | |
| # if 'XML Export Summary' in sheetnames: | |
| # worksheetS = ws.worksheet_by_title('XML Export Summary') | |
| # else: | |
| # ws.add_worksheet("XML Export Summary") # Please set the new sheet name. | |
| # worksheetw = ws.worksheet(0) #legend | |
| # worksheetS = ws.worksheet_by_title('XML Export Summary') | |
| # summaryId= ws[1].id | |
| # print('summaryyyID',summaryId) | |
| # print('summaryyyID2',worksheetS.id) | |
| # worksheetS.clear() | |
| # countnames=0 | |
| # row0=['MC_T Name','Qty','Unit'] | |
| # worksheetS.update_row(1,row0) | |
| # for i in range(len(McTName)): | |
| # allgbnames='' | |
| # item='' | |
| # print(McTName[i][0]) | |
| # # firstpart= re.split(r'[`\-=~!@#$%^&*()_+\[\]{};\'\\:"|<,./<>?]', McTName[i][0]) | |
| # print('kkk' ,McTName[i][2]) | |
| # if McTName[i][2].startswith('Area'): | |
| # if section.startswith('1.0'): | |
| # rowvalue=5# column 5 | |
| # elif section.startswith('3.2'): | |
| # rowvalue=3 | |
| # ar=0 | |
| # unit='m2' | |
| # if McTName[i][2].startswith('Perimeter'): | |
| # if section.startswith('1.0'): | |
| # rowvalue=7# column 7 | |
| # elif section.startswith('3.2'): | |
| # rowvalue=3 | |
| # ar=0 | |
| # unit='m' | |
| # if McTName[i][2].startswith('Length'): | |
| # if section.startswith('1.0'): | |
| # rowvalue=9# column 7 | |
| # elif section.startswith('3.2'): | |
| # rowvalue=3 | |
| # ar=0 | |
| # unit='m' | |
| # if McTName[i][2].startswith('Count'): | |
| # if section.startswith('1.0'): | |
| # rowvalue=3# column 7 | |
| # elif section.startswith('3.2'): | |
| # rowvalue=3 | |
| # ar=0 | |
| # unit='Nr' | |
| # print('mcct',McTName[i][1]) | |
| # if isinstance(McTName[i][1], list): | |
| # guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) | |
| # for m in McTName[i][1]: | |
| # if m: | |
| # if m.startswith('text1'): | |
| # name=m.removeprefix('text1') | |
| # allgbnames+= name +' +' | |
| # indices = [o for o, x in enumerate(guessednames) if x == name] | |
| # print(indices) | |
| # for j in range(len(indices)): | |
| # # print('kjjjj',roww[j]) | |
| # ar+=float(worksheetw.cell((indices[j]+1 ,rowvalue)).value) | |
| # else: | |
| # item+=m + ' ,' | |
| # print(item) | |
| # n= McTName[i][0] + ' ( '+ allgbnames[:-2] +' , ' + item[:-1] + ' ) ' | |
| # else: | |
| # if McTName[i][1].startswith('text1'): | |
| # name=McTName[i][1].removeprefix('text1') | |
| # allgbnames+= name | |
| # roww=worksheetw.find(name) | |
| # print(roww) | |
| # for j in range(len(roww)): | |
| # print('kjjjj',roww[j]) | |
| # ar+=float(worksheetw.cell((roww[j].row ,rowvalue)).value) | |
| # n= McTName[i][0] + ' ( '+ allgbnames + ' ) ' | |
| # rowi=[str(n),ar,unit] | |
| # worksheetS.update_row(i+2,rowi) | |
| # # worksheetS.adjust_column_width(start=1,end=4) | |
| # worksheetS.adjust_column_width(start=1,end=1, pixel_size=350) | |
| # worksheetS.adjust_column_width(start=2,end=2, pixel_size=100) | |
| # worksheetS.adjust_column_width(start=3,end=3) | |
| # xx=(worksheetS.cell( ( len(McTName) +1 ,3)) ).address.label | |
| # model_cell1 =worksheetS.cell('A2') | |
| # model_cell1.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.LEFT ) | |
| # pygsheets.DataRange('A2', str(xx), worksheet=worksheetS).apply_format(model_cell1) | |
| # model_cell =worksheetS.cell('A1') | |
| # model_cell.set_text_format('bold', True) | |
| # model_cell.set_horizontal_alignment( pygsheets.custom_types.HorizontalAlignment.CENTER ) | |
| # pygsheets.DataRange('A1','C1', worksheet=worksheetS).apply_format(model_cell) | |
| # body2={ | |
| # "requests": [ | |
| # { | |
| # "updateBorders": { | |
| # "range": { | |
| # "sheetId": str(summaryId), | |
| # "startRowIndex": 0, | |
| # "endRowIndex": len(McTName) +1 , | |
| # "startColumnIndex": 0, | |
| # "endColumnIndex": 3 | |
| # }, | |
| # "top": { | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "bottom": { | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "left":{ | |
| # "style": "SOLID", | |
| # "width":2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "right":{ | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "innerHorizontal":{ | |
| # "style": "SOLID", | |
| # "width":2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # "innerVertical": { | |
| # "style": "SOLID", | |
| # "width": 2, | |
| # "color": { | |
| # "red": 0.0, | |
| # "green":0.0, | |
| # "blue":0.0 | |
| # }, | |
| # }, | |
| # } | |
| # } | |
| # ] | |
| # } | |
| # spreadsheet_service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_key, body=body2).execute() | |
| # return summaryId #,guessednamesfinal | |
| # # print(x,xarea) | |
| # def getguessnames(gc,ws): | |
| # guessednamesfinal=[] | |
| # worksheetw = ws.worksheet(0) | |
| # guessednames=worksheetw.get_col(1, returnas='matrix', include_tailing_empty=False) | |
| # print(guessednames[2:]) | |
| # for item in guessednames[2:]: | |
| # if item not in guessednamesfinal: | |
| # guessednamesfinal.append(item) | |
| # print(guessednamesfinal) | |
| # return guessednamesfinal | |
| # ################################################################ | |
| # def deletefromlegend(deletedrows,SimilarAreaDictionarycopy,section, areaPermArr=[]): | |
| # items=[] | |
| # idx=0 | |
| # if section.startswith('1.0'): | |
| # areaPermArr=ast.literal_eval(areaPermArr) | |
| # myDict=eval(SimilarAreaDictionarycopy) | |
| # SimilarAreaDictionarycopy=pd.DataFrame(myDict) | |
| # # deletedrows=eval(deletedrows) | |
| # strings=deletedrows['content'] | |
| # areastodelete = [] | |
| # perimstodelete=[] | |
| # lengthstodelete=[] | |
| # for item in strings: | |
| # items.append(str(item).split('\n \n')) | |
| # # print('itemsssssss',float(re.findall("\d+\.\d+", str(items[i][0]).split()[0])[0])) #take area and perim mn hna l sec 3.2 and +/- value margin | |
| # for i in range(len(items)): | |
| # print('ITEMSS',str(items[i]).split()) | |
| # items=ast.literal_eval(str(items[i])) | |
| # areastodelete.append(float(re.findall("\d+\.\d+", str(items[i][0]).split()[1])[0])) | |
| # perimstodelete.append(float(re.findall("\d+\.\d+", str(items[i][1]).split()[1])[0]) ) | |
| # lengthstodelete.append(float(re.findall("\d+\.\d+", str(items[i][2]).split()[1])[0]) ) | |
| # for i in range(len(areastodelete)):#item in areastodelete: | |
| # areamin=round(areastodelete[i],1)- 0.3 | |
| # areamax=round(areastodelete[i],1)+ 0.3 | |
| # perimmin=round(perimstodelete[i],1)- 0.3 | |
| # perimmax=round(perimstodelete[i],1)+ 0.3 | |
| # if section.startswith('1.0'): | |
| # for p in range(len(areaPermArr)): | |
| # if areastodelete[i] in areaPermArr[p]: | |
| # print('AAA',areaPermArr[p]) | |
| # area= areaPermArr[p][0] | |
| # width= areaPermArr[p][1] | |
| # height= areaPermArr[p][2] | |
| # # if section.startswith('1.0'): | |
| # widthMin= width -10 | |
| # widthMax= width +10 | |
| # heightMin = height-10 | |
| # heightMax=height+10 | |
| # found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) & (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ]] | |
| # elif section.startswith('3.2'): | |
| # areamin=round(areastodelete[i],1)- 0.1 | |
| # areamax= round(areastodelete[i],1) + 0.1 | |
| # found=SimilarAreaDictionarycopy.loc[SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) )]] | |
| # if len(found.index.values ) >0: | |
| # occ=SimilarAreaDictionarycopy.loc[found.index.values[0],'Occurences'] | |
| # if occ== 1: #drop row | |
| # print('occ=1') | |
| # SimilarAreaDictionarycopy= SimilarAreaDictionarycopy.drop(found.index.values[0]) | |
| # else: #occ minus 1 , total area - areavalue , total perim - perimvalue | |
| # print('occ>1') | |
| # if section.startswith('1.0'): | |
| # idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Rounded'] >=areamin) & (SimilarAreaDictionarycopy['Rounded']<=areamax) & (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) ) & ( ((SimilarAreaDictionarycopy['Width']>=widthMin) & (SimilarAreaDictionarycopy['Width']<=widthMax) & (SimilarAreaDictionarycopy['Height']>=heightMin) & (SimilarAreaDictionarycopy['Height']<=heightMax) ) | ((SimilarAreaDictionarycopy['Width']>=heightMin) & (SimilarAreaDictionarycopy['Width']<=heightMax) & (SimilarAreaDictionarycopy['Height']>=widthMin) & (SimilarAreaDictionarycopy['Height']<=widthMax) )) ] | |
| # elif section.startswith('3.2'): | |
| # perimmin=round(perimstodelete[i],1)- 50 | |
| # perimmax=round(perimstodelete[i],1)+ 50 | |
| # idx=SimilarAreaDictionarycopy.index[((SimilarAreaDictionarycopy['Area'] >=areamin) & (SimilarAreaDictionarycopy['Area']<=areamax) & (SimilarAreaDictionarycopy['Perimeter'] >=perimmin) & (SimilarAreaDictionarycopy['Perimeter']<=perimmax) )] | |
| # SimilarAreaDictionarycopy.loc[idx,'Total Area'] = SimilarAreaDictionarycopy.loc[idx,'Total Area'] - areastodelete[i] | |
| # SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] = SimilarAreaDictionarycopy.loc[idx,'Total Perimeter'] - perimstodelete[i] | |
| # SimilarAreaDictionarycopy.loc[idx,'Total Length'] = SimilarAreaDictionarycopy.loc[idx,'Total Length'] - lengthstodelete[i] | |
| # SimilarAreaDictionarycopy.loc[idx,'Occurences'] = int(SimilarAreaDictionarycopy.loc[idx,'Occurences']) - 1 | |
| # return SimilarAreaDictionarycopy | |
| # ############################################################# |