String Count

[Kristenl2784]

Hello,

Down where my code says Stop2 (close to the bottom) I'm trying to count how many times a string occurs inside a file. It's almost working correctly but my only problem is, is that it counts a similar string for instance the string is A118, and there's another string very similar to that inside the file called A118X. I don't want it to count A118X I just want it to count A118. I want it to count A118X on it's own. I have 1000's of strings to go through, each need to be counted individually.

import openpyxl as xl 
import os
import pandas as pd
import xlsxwriter
import xlrd
from xlsxwriter.utility import xl_rowcol_to_cell


input_dir = 'C:\\work\\comparison\\NNM'
Summary = 'C:\\work\\comparison\\Summary.xlsx'
Dwell = 'C:\\work\\comparison\\Dwell.xlsx'
newFile = 'C:\\work\\comparison\\Comparison.xlsx'




files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]

i=0

wb3=xlsxwriter.Workbook(newFile)
ws3=wb3.add_worksheet('Comparison')


format = wb3.add_format()
format.set_align('center')
format.set_align('vcenter')
format.set_bold(True)
format.set_font_size(14)
format.set_bg_color('#C9C9C9')
format.set_border(style=1)


for row_num in range(1,30):
    ws3.write_formula(row_num - 1, 5,
                            '=ABS(C%d - E%d)' % (row_num, row_num))
	

for row_num in range(1,30):
    ws3.write_formula(row_num - 1, 10,
                            '=ABS(H%d - J%d)' % (row_num, row_num))	
	
for row_num in range(1,30):
    ws3.write_formula(row_num - 1, 15,
                            '=ABS(N%d - O%d)' % (row_num, row_num))		
	

ws3.write_row("A1:Q1", ['TNAME','Start Segment','Start Chainage(ft)','Start Segment','Start Chainage(ft)','Start Chainage Difference(ft)','End Segment','End Chainage(ft)','End Segment','End Chainage(ft)','End Chainage Difference(ft)','Stops','Stops','Distance','Distance','Distance Difference (ft)','Comments'], format)
ws3.set_column(1, 17, 35)
wb3.close()

wb3 = xl.load_workbook(newFile) 
ws3 = wb3.worksheets[0]

wb2 = xl.load_workbook(Summary) 
ws2 = wb2.worksheets[1]


for file in files: 
   input_file =  os.path.join(input_dir, file)
   wb1=xl.load_workbook(input_file)
   ws1=wb1.worksheets[0]
   

   Stop1 = pd.read_excel(file,usecols = "F",squeeze = True)
   counts1 = Stop1.value_counts()
   z1 = counts1[0]/2
   
   Stop2 = pd.read_excel(Dwell,usecols = "A",squeeze = True)
   Detail=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
   counts2 = Stop2.str.match(Detail).sum()

   
   
   ws3[f'A{i+2}']=ws1['A1'].value[28:]
   ws3[f'D{i+2}']=ws1['B4'].value
   ws3[f'E{i+2}']=ws1['D4'].value
   ws3[f'I{i+2}']=ws1['B'][-1].value
   ws3[f'J{i+2}']=ws1['D'][-1].value
   ws3[f'O{i+2}']=ws1['E'][-1].value 
   ws3[f'N{i+2}']=ws2[f'I{i+6}'].value   
   ws3[f'M{i+2}']=z1
   ws3[f'L{i+2}']=counts2
  
   i += 1
  
   wb3.save(newFile)

[millpond]

While I am still too new to Python to give much assistance for correcting your code, I am familiar enough with regex to know that 'Match' means exactly that, and will return anything that contains the pattern specified.

I would use a regex with the \b pattern boundary.

In Perl I would use:
$mypattern =~ /\bA100\b/g;

The pattern is the same but the the syntax for python is a bit different, better explained in:
https://docs.python.org/3/howto/regex.html