NinoCode/RandomCodeRequests/Narco_reqs.txt

--- NEW ---
"""In this project, Rawlings data check,old format I was asked to take two spreadsheets and using last name and the last 4 dig of ssn,
cross ref and give them a list of what matched"""

>>> matrix = {}
>>> contents = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\schedule a - list of pe claims to be paid Q3.csv").readlines()
# First I made a matrix with {fullname,full_ssn}, this came from the schedule a list
>>> for line in contents:
...     line = line.split("|")
...     name = line[0]
...     matrix[name] = line[1]

# Then I made a matrix of {lastName,[fullname,fullname,fullname]}, so that I could first search by last name and then per
# name test the ssn
>>> lastNameMatrix = {}
>>> for line in contents:
...     line = line.split("|")
...     name = line[0]
...     lastName = name.split(",")[0]
...     lastName = lastName.upper()
...     if lastName in lastNameMatrix.keys():
...             lastNameMatrix[lastName].append(name)
...     else:
...             lastNameMatrix[lastName] = [name,]

# Finally, going through the other file, I tested firs the last name and then, if that was found, checked the ssn per
# last name found.
contents = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NARCO Trust Claims Master File 11042014.csv").readlines()
>>> outputFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\Matched.txt",'w')
>>> errFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NotFound.txt",'w')
>>> for line in contents:
...     newLine = line.split("|")
...     lastName = newLine[4]
...     lastName = lastName.strip()
...     ssn = newLine[5]
...     if len(ssn) > 8:
...             ssnPart = ssn[-4:]
...             if lastName.upper() in lastNameMatrix.keys():
...                     for ln in lastNameMatrix[lastName.upper()]:
...                             if ssnPart == matrix[ln][-4:]:
...                                     outputFile.write("%s | %s"%(matrix[ln],line))
...                             else:
...                                     errFile.write(line)
...             else:
...                     errFile.write(line)
...                     
>>> outputFile.close()
>>> errFile.close()


--- END ---
--- ALTERNATE ---
""" new format"""
>>> matrix = {}
>>> contents = open(r"C:\Users\eborges\Box Sync\Client\Narco\New_Sched_A.csv").readlines()
# First I made a matrix with {fullname[full_ssn,full_ssn]}, this came from the schedule a list
>>> for line in contents:
...     line = line.split("|")
...     lastName = line[1]
...     firstName = line[2]
...     if line[3]:
...             midName = line[3]
...     else:
...             midName = None
...     if midName:
...             name = "%s, %s %s"% (lastName, firstName, midName)
...     else:
...             name = "%s, %s"% (lastName, firstName)
...     if name in matrix.keys():
...             matrix[name].append(line[4])
...     else:
...             matrix[name] = [line[4],]

# Then I made a matrix of {lastName,[fullname,fullname,fullname]}, so that I could first search by last name and then per
# name test the ssn
>>> lastNameMatrix = {}
# do this again each time...
>>> contents = open(r"C:\Users\eborges\Box Sync\Client\Narco\New_Sched_A.csv").readlines()
>>> for line in contents:
...     line = line.split("|")
...     lastName = line[1]
...     lastName = lastName.strip()
...     lastName = lastName.upper()
...     if lastName in lastNameMatrix.keys():
...             lastNameMatrix[lastName].append(name)
...     else:
...             lastNameMatrix[lastName] = [name,]
... 

# Finally, going through the other file, I tested first the last name and then, if that was found, checked the ssn per
# last name found and per ssn found
contents = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NARCO Trust Claims Master File 11042014.csv").readlines()
>>> outputFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\Matched_2.txt",'w')
>>> errFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NotFound_2.txt",'w')
>>> for line in contents:
...     newLine = line.split("|")
...     lastName = newLine[4]
...     lastName = lastName.strip()
...     ssn = newLine[5]
...     if len(ssn) > 8:
...             ssnPart = ssn[-4:]
...             if lastName.upper() in lastNameMatrix.keys():
...                     for ln in lastNameMatrix[lastName.upper()]:
...                             for fl_SSN in matrix[ln]:
...                                     if ssnPart == fl_SSN[-4:]:
...                                             outputFile.write("%s | %s"%(fl_SSN,line))
...                                     else:
...                                             errFile.write(line)
...             else:
...                     errFile.write(line)
>>> outputFile.close()
>>> errFile.close()
--- END ---

-----------------------------------------
""" In this project, I was asked to compare teh ssn column to two ssn columns in a separate sheet.
 I also had to normalize the ssn to look for hyphens"""
 
>>> contents = open(r"W:\Manny\Client\Narco\Acclaim New File for OST10242013\Acclaim New File for OST10242013.txt").readlines()
>>> masterList = []
>>> for line in contents:
...     line = line.replace("\n","")
...     line = line.split("\t")
...     ssn1 = line[10]
...     ssn2 = line[11]
...     ssn1 = ssn1.replace("-","")
...     ssn2 = ssn2.replace("-","")
...     if ssn1 == ssn2:
...             masterList.append(ssn1)
...     else:
...             masterList.append(ssn1)
...             masterList.append(ssn2)
...             
>>> len(masterList)
231683


contents = open(r"W:\Manny\Client\Narco\PEC_Internal_Report for 1125 CRMC claims - UAT 11.5.2013.txt").readlines()

checkList = []

outputFile = open(r"W:\Manny\Client\Narco\PEC_Internal_Report for 1125 CRMC claims - UAT 11.5.2013_Output.txt",'w')

>>> for line in contents:
...     line = line.replace("\n","")
...     ssn = line.split("\t")[3]
...     if ssn in masterList:
...             outputFile.write(line + "\tYES\n")
...     else:
...             outputFile.write(line + "\tNO\n")
...             
>>> outputFile.close()


""" In this one, she wanted me to cross ref an access table with an excel.  (Excel converted to tab delm but access
I left in mdb, since exporting was super messy."""

import os,sys, win32com.client
>>> daoDB = daoEngine.OpenDatabase(r"W:\Manny\Client\Narco\Summation.mdb")
>>> daoRSObj = daoDB.OpenRecordset("SELECT SUM_SSN FROM Summation")
>>> daoRSObj.MoveLast()
>>> fullCount = daoRSObj.RecordCount
>>> daoRSObj.MoveFirst()
>>> newList = []
>>> for i in range(fullCount):
...     newList.append(daoRSObj.Fields('SUM_SSN').Value)
...     daoRSObj.MoveNext()
...     
>>> daoRSObj.Close()
>>> len(newList)
138407
>>> newList.sort()
>>> fullList = []
fullList = []
>>> for i in newList:
...     if i:
...             if i == "0":
...                     pass
...             else:
...                     fullList.append(str(i))
...                     
>>> len(fullList)
82527
>>> fullList.append('363107041')
>>> fullList.append('464528121')
>>> fullList.sort()
>>> fullList[400]
'035207139'

>>> contents = open(r"W:\Manny\Client\Narco\20131105_Request\PEC_Internal_Report for 1125 CRMC claims - UAT 11.5.2013.txt").readlines()
>>> masterMatrix = {}
>>> for i in contents:
...     parts = i.split("\t")
...     masterMatrix[parts[3]] = i
...     
>>> len(masterMatrix.keys())

headder = contents[0]
>>> outputFile = open(r"W:\Manny\Client\Narco\20131105_Request\PEC_Summation_Crossref_output.txt",'w')
>>> outputFile.write(headder)
>>> for ssn in fullList:
...     if ssn in masterMatrix.keys():
...             outputFile.write(masterMatrix[ssn])
... 
>>> outputFile.close()
Revision:	559
Committed:	Tue Nov 25 15:54:29 2014 UTC (11 years, 4 months ago) by nino.borges
Content type:	text/plain
File size:	7088 byte(s)
Log Message:	Updates
#	User	Rev	Content
1	nino.borges	559	--- NEW ---
2			"""In this project, Rawlings data check,old format I was asked to take two spreadsheets and using last name and the last 4 dig of ssn,
3			cross ref and give them a list of what matched"""
4
5			>>> matrix = {}
6			>>> contents = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\schedule a - list of pe claims to be paid Q3.csv").readlines()
7			# First I made a matrix with {fullname,full_ssn}, this came from the schedule a list
8			>>> for line in contents:
9			... line = line.split("\|")
10			... name = line[0]
11			... matrix[name] = line[1]
12
13			# Then I made a matrix of {lastName,[fullname,fullname,fullname]}, so that I could first search by last name and then per
14			# name test the ssn
15			>>> lastNameMatrix = {}
16			>>> for line in contents:
17			... line = line.split("\|")
18			... name = line[0]
19			... lastName = name.split(",")[0]
20			... lastName = lastName.upper()
21			... if lastName in lastNameMatrix.keys():
22			... lastNameMatrix[lastName].append(name)
23			... else:
24			... lastNameMatrix[lastName] = [name,]
25
26			# Finally, going through the other file, I tested firs the last name and then, if that was found, checked the ssn per
27			# last name found.
28			contents = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NARCO Trust Claims Master File 11042014.csv").readlines()
29			>>> outputFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\Matched.txt",'w')
30			>>> errFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NotFound.txt",'w')
31			>>> for line in contents:
32			... newLine = line.split("\|")
33			... lastName = newLine[4]
34			... lastName = lastName.strip()
35			... ssn = newLine[5]
36			... if len(ssn) > 8:
37			... ssnPart = ssn[-4:]
38			... if lastName.upper() in lastNameMatrix.keys():
39			... for ln in lastNameMatrix[lastName.upper()]:
40			... if ssnPart == matrix[ln][-4:]:
41			... outputFile.write("%s \| %s"%(matrix[ln],line))
42			... else:
43			... errFile.write(line)
44			... else:
45			... errFile.write(line)
46			...
47			>>> outputFile.close()
48			>>> errFile.close()
49
50
51			--- END ---
52			--- ALTERNATE ---
53			""" new format"""
54			>>> matrix = {}
55			>>> contents = open(r"C:\Users\eborges\Box Sync\Client\Narco\New_Sched_A.csv").readlines()
56			# First I made a matrix with {fullname[full_ssn,full_ssn]}, this came from the schedule a list
57			>>> for line in contents:
58			... line = line.split("\|")
59			... lastName = line[1]
60			... firstName = line[2]
61			... if line[3]:
62			... midName = line[3]
63			... else:
64			... midName = None
65			... if midName:
66			... name = "%s, %s %s"% (lastName, firstName, midName)
67			... else:
68			... name = "%s, %s"% (lastName, firstName)
69			... if name in matrix.keys():
70			... matrix[name].append(line[4])
71			... else:
72			... matrix[name] = [line[4],]
73
74			# Then I made a matrix of {lastName,[fullname,fullname,fullname]}, so that I could first search by last name and then per
75			# name test the ssn
76			>>> lastNameMatrix = {}
77			# do this again each time...
78			>>> contents = open(r"C:\Users\eborges\Box Sync\Client\Narco\New_Sched_A.csv").readlines()
79			>>> for line in contents:
80			... line = line.split("\|")
81			... lastName = line[1]
82			... lastName = lastName.strip()
83			... lastName = lastName.upper()
84			... if lastName in lastNameMatrix.keys():
85			... lastNameMatrix[lastName].append(name)
86			... else:
87			... lastNameMatrix[lastName] = [name,]
88			...
89
90			# Finally, going through the other file, I tested first the last name and then, if that was found, checked the ssn per
91			# last name found and per ssn found
92			contents = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NARCO Trust Claims Master File 11042014.csv").readlines()
93			>>> outputFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\Matched_2.txt",'w')
94			>>> errFile = open(r"\\BSTDD967DTW1\Users\eborges\Box Sync\Client\Narco\NotFound_2.txt",'w')
95			>>> for line in contents:
96			... newLine = line.split("\|")
97			... lastName = newLine[4]
98			... lastName = lastName.strip()
99			... ssn = newLine[5]
100			... if len(ssn) > 8:
101			... ssnPart = ssn[-4:]
102			... if lastName.upper() in lastNameMatrix.keys():
103			... for ln in lastNameMatrix[lastName.upper()]:
104			... for fl_SSN in matrix[ln]:
105			... if ssnPart == fl_SSN[-4:]:
106			... outputFile.write("%s \| %s"%(fl_SSN,line))
107			... else:
108			... errFile.write(line)
109			... else:
110			... errFile.write(line)
111			>>> outputFile.close()
112			>>> errFile.close()
113			--- END ---
114
115			-----------------------------------------
116	nino.borges	480	""" In this project, I was asked to compare teh ssn column to two ssn columns in a separate sheet.
117			I also had to normalize the ssn to look for hyphens"""
118
119			>>> contents = open(r"W:\Manny\Client\Narco\Acclaim New File for OST10242013\Acclaim New File for OST10242013.txt").readlines()
120			>>> masterList = []
121			>>> for line in contents:
122			... line = line.replace("\n","")
123			... line = line.split("\t")
124			... ssn1 = line[10]
125			... ssn2 = line[11]
126			... ssn1 = ssn1.replace("-","")
127			... ssn2 = ssn2.replace("-","")
128			... if ssn1 == ssn2:
129			... masterList.append(ssn1)
130			... else:
131			... masterList.append(ssn1)
132			... masterList.append(ssn2)
133			...
134			>>> len(masterList)
135			231683
136
137
138			contents = open(r"W:\Manny\Client\Narco\PEC_Internal_Report for 1125 CRMC claims - UAT 11.5.2013.txt").readlines()
139
140			checkList = []
141
142			outputFile = open(r"W:\Manny\Client\Narco\PEC_Internal_Report for 1125 CRMC claims - UAT 11.5.2013_Output.txt",'w')
143
144			>>> for line in contents:
145			... line = line.replace("\n","")
146			... ssn = line.split("\t")[3]
147			... if ssn in masterList:
148			... outputFile.write(line + "\tYES\n")
149			... else:
150			... outputFile.write(line + "\tNO\n")
151			...
152	nino.borges	494	>>> outputFile.close()
153
154
155			""" In this one, she wanted me to cross ref an access table with an excel. (Excel converted to tab delm but access
156			I left in mdb, since exporting was super messy."""
157
158			import os,sys, win32com.client
159			>>> daoDB = daoEngine.OpenDatabase(r"W:\Manny\Client\Narco\Summation.mdb")
160			>>> daoRSObj = daoDB.OpenRecordset("SELECT SUM_SSN FROM Summation")
161			>>> daoRSObj.MoveLast()
162			>>> fullCount = daoRSObj.RecordCount
163			>>> daoRSObj.MoveFirst()
164			>>> newList = []
165			>>> for i in range(fullCount):
166			... newList.append(daoRSObj.Fields('SUM_SSN').Value)
167			... daoRSObj.MoveNext()
168			...
169			>>> daoRSObj.Close()
170			>>> len(newList)
171			138407
172			>>> newList.sort()
173			>>> fullList = []
174			fullList = []
175			>>> for i in newList:
176			... if i:
177			... if i == "0":
178			... pass
179			... else:
180			... fullList.append(str(i))
181			...
182			>>> len(fullList)
183			82527
184			>>> fullList.append('363107041')
185			>>> fullList.append('464528121')
186			>>> fullList.sort()
187			>>> fullList[400]
188			'035207139'
189
190			>>> contents = open(r"W:\Manny\Client\Narco\20131105_Request\PEC_Internal_Report for 1125 CRMC claims - UAT 11.5.2013.txt").readlines()
191			>>> masterMatrix = {}
192			>>> for i in contents:
193			... parts = i.split("\t")
194			... masterMatrix[parts[3]] = i
195			...
196			>>> len(masterMatrix.keys())
197
198			headder = contents[0]
199			>>> outputFile = open(r"W:\Manny\Client\Narco\20131105_Request\PEC_Summation_Crossref_output.txt",'w')
200			>>> outputFile.write(headder)
201			>>> for ssn in fullList:
202			... if ssn in masterMatrix.keys():
203			... outputFile.write(masterMatrix[ssn])
204			...
205			>>> outputFile.close()