Question
You are provided an enrollment file: IFT394_TO_IA_CAD_20170904.xlsx Convert it to a .csv file. The table below provided the expected format of each field in the
You are provided an enrollment file: IFT394_TO_IA_CAD_20170904.xlsx
Convert it to a .csv file.
The table below provided the expected format of each field in the file:
| Field Name | Format | Purpose & Constraint |
| Effective Date | MM/DD/YYYY | Indicate the coverage effective date |
| Status | 5 character max (eg Drop, Add, ) | Action indicator. If Add, member is added to enrollment; if Change, info about an enrolled member is updated; if Drop, member is cancelled |
| EmployeeID | Identified employee | |
| ClientID | Up to 6 integer digits | Identified client |
| MemberFirstName | ||
| MemberMiddleName | ||
| MemberLastName | ||
| MemberSSN | ||
| DOB_Month | 2 digits. | Month of birth |
| DOB_Day | 2 digits | Day of birth |
| DOB_Year | 4 digits | Year of birth |
| Address1 | ||
| Address2 | Not required | |
| City | ||
| State | ||
| ZipCode | ||
| AreaCode | 3 digit | |
| HomePhone | 7 digits | |
| | Must include @ and . | |
| Deduction Method | Employer Payroll Deduction or Employer sponsored | |
| Customer-Defined | Not required | |
| Relationship | 1 char (P, S, C, D) | Primary, Spouse, Child, or Dependent |
| Primary | 1 char (Y, N) | Designate primary enrollment |
| FamilyID | Shared value across family enrollments. | |
| UniqueID | Uniquely identified each member | |
| Plan_Type | Individual or Family | Designate the plan type of the member |
| ProductID | PAP, PA | Not required |
You are to develop a Python script to pre-process the file so that:
The header names are written as in the table above, and in that order. For example, we must have Effective Date instead of EffectiveDate; DOB_Year instead of DOB Year; Plan_Type instead of Plan Type; or ProductID instead of PID.
The area code is exactly 3 digits and the home phone is exactly 7 digits. Analyze these two fields carefully to understand how you can design your script to capture issues such as +1 xxx xxxxxxx, or an area code with 3 digits, but a home phone with 10-digits still.
There cannot be a relationship D, S, or C that is Primary. If this occurs, your script must set the dependent as N (primary).
There cannot be a D, S, or C relationship without an identifying primary, or that is of plan type Individual. A primary and a dependent are linked by their FamilyID. If this occurs, your script report must include an alert to the user about it.
All zip codes must be 5 digits
Months of birth must be between 1 and 12; and day of birth must be between 1 and 31 depending on the month of birth. For example, you cannot have a month of birth a 2 (i.e. February) and a day of birth as 30.
Year of birth must be 4 digits.
SSN must be 9 digits; not other character such as - or .
Finally, your script must rearrange the records so that Primaries are processed before the dependents. In other words, primaries must come first.
Contents of Excel file the . needs to be removed after the g in the emails because Chegg flags g.mail as an inappropriate word. Lastly this needs to be a Python script thank you for taking the time to look at it!
| EffectiveDate | Status | EmployeeID | ClientID | MemberFirstName | MemberMiddleName | MemberLastName | MemberSSN | DOB Month | DOB_Day | DOB_Year | Address1 | Address2 | City | State | ZipCode | Area_Code | Home_Phone | DeductionMethod | CustomerDefined | Relationship | Primary | Family_ID | UniqueID | Plan_Type | PID | |
| 3/1/1999 | Add | 123123123 | 176456 | Taylor | Justin | Jeffries | 320-22-3145 | 4 | 23 | 1990 | 123 E Test Drive | Milwaukee | WI | 53005 | 262 | 1231230 | TaylorJustin@g.mail.com | Employer Payroll Deduction | P | Y | 998754 | 667 | Individual | PA | ||
| 1/1/2000 | Drop | 324543210 | 441234 | Chad | Wilson | Smith | 344-11-5413 | 5 | 22 | 1980 | 540 E Mainest St | San Diego | CA | 92001 | 000 | 6464310000 | ChadWilson@g.mail.com | Employer Payroll Deduction | P | Y | F777 | 665 | Family | PAP | ||
| 12/31/1999 | Change | 300778890 | 908765 | Alex | Arturo | Coleson | 432-33-3434 | 2 | 13 | 1971 | 5111 W Arboles Drive | Los Angeles | CA | 92221 | 949 | 9110911 | AlexArturo@y.ahoo.com | Employer Sponsored | D | N | F777 | 654 | Family | PA | ||
| 1/2/2003 | Drop | 543098432 | 784332 | Alexandra | Marie | Samson | 655-32-0999 | 1 | 6 | 1963 | 6800 Main St | Santa Barbara | CA | 92018 | 000 | +19494110114 | AlexandraMarie@aol.com | Employer Sponsored | D | Y | F778 | 332 | Family | PA | ||
| 11/11/2011 | Drop | 321543654 | 301001 | Kyle | Robert | Walker | 765-12-5656 | 3 | 5 | 1967 | 77123 W Faraway Drive | Newport Beach | CA | 92312 | 858 | 7896756 | KyleRobert@eathlink.net | Employer Payroll Deduction | P | Y | F778 | 333 | Family | PA | ||
| 11/11/2017 | Add | 1 | 111111 | Thomas | Edward | Brady | 123-45-6789 | 10 | 10 | 1977 | 1 Patriots Place | Foxboro | MA | 2035 | 619 | 6191234567 | tbrady@g.mail.com | Employer Payroll Deduction | P | Y | 1 | 1 | Individual | |||
| 11/11/2017 | Add | 2 | 222222 | Kennedy | S | Nou | 213-53-8976 | 12 | 15 | 1994 | 2007 Fruit Tree Ln. | Queen Crek | AZ | 85142 | 623 | 6232340458 | knou1@asu.edu | Employer Payroll Deduction | S | N | 665462 | 665462 | Individual | |||
| 1/1/2016 | Add | P123 | 123456 | Austin | Bradley | Mork | 123-45-6789 | 2 | 1 | 1994 | 505 W BASELINE RD | 2003 | Tempe | AZ | 85283 | 000 | +16024325110 | amork1@asu.edu | Employer Payroll Deduction | P | Y | 198721 | 198721 | Individual | PAP | |
| 2/1/2016 | Drop | P124 | 111111 | John | Wayne | Doe | 987-65-4321 | 7 | 24 | 1980 | 1234 FAKE LANE RD | Phoenix | AZ | 85254 | 602 | 4023214 | johnwaynedoe@g.mail.com | Employer Sponsored | P | Y | 987111 | 987111 | Individual | PA | ||
| 3/1/2016 | Add | P125 | 222222 | Alice | Ann | Girl | 456-78-9123 | 9 | 12 | 1990 | 6732 LAKESIDE RD | Tucson | AZ | 85641 | 000 | +14809807632 | aliceanngirl@g.mail.com | Employer Payroll Deduction | P | Y | 222321 | 222321 | Individual | PA | ||
| 4/3/2017 | Add | P126 | 333333 | Bob | Chris | Guy | 321-78-9654 | 1 | 30 | 1974 | 1095 MOUNTAIN RD | Tempe | AZ | 85283 | 602 | 7915402 | bobchrisguy@g.mail.com | Employer Payroll Deduction | P | Y | F123 | L111 | Family | PAP | ||
| 4/3/2017 | Change | P127 | 444444 | Amber | Blah | Guy | 693-58-2471 | 12 | 5 | 1978 | 1095 MOUNTAIN RD | Tempe | AZ | 85283 | 602 | 4715897 | amberblahguy@g.mail.com | Employer Sponsored | S | N | F123 | L112 | Family | PAP | ||
| 11/11/2017 | Change | 3 | 333333 | Luke | Skywalker | 978-65-4321 | 11 | 31 | 1951 | 456 Tatooine Place | Tatooine | AK | 2121 | 234 | 2349876540 | lskywalk@g.mail.com | Employer Payroll Deduction | C | Y | 3 | 3 | Family | ||||
| 11/11/2017 | Drop | 4 | 444444 | Peter | Parker | 798-56-2143 | 10 | 14 | 1980 | 221 Baker St | New York City | NY | 45678 | 978 | 9783216547 | pparker@g.mail.com | Employer sponsored | D | N | 4 | 4 | Family | ||||
| 11/11/2017 | Drop | 5 | 555555 | Bartholomey | Henry | Allen | 456-12-9783 | 12 | 12 | 1975 | 1255 Random St. | Central City | KA | 91023 | 455 | 4557863542 | ballen@g.mail.com | Employer sponsored | P | Y | 5 | 5 | Family |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Logic In Databases International Workshop Lid 96 San Miniato Italy July 1 2 1996 Proceedings Lncs 1154
Authors: Dino Pedreschi ,Carlo Zaniolo
1st Edition
3540618147, 978-3540618140
