def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item["Birth Year"],
                                                  item["Birth Month"],
                                                  item["Birth Day"])

    dob_city = item["Birth City"]
    dob_state = item["Birth State"]
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ""
    name = "%s: %s %s %s" % (
        item["cl_id"],
        item["First Name"],
        item["Last Name"],
        str(date_dob),
    )
    fjc_check = Person.objects.filter(fjc_id=item["jid"])
    if len(fjc_check) > 0:
        print("Warning: %s exists" % name)
        return

    pres_check = Person.objects.filter(
        name_first=item["First Name"],
        name_last=item["Last Name"],
        date_dob=date_dob,
    )

    if not testing:
        print("Now processing: %s" % name)
    if len(pres_check) > 0:
        print("%s is a president." % name)
        person = pres_check[0]
        person.fjc_id = item["jid"]

    else:
        date_dod, date_granularity_dod = process_date(item["Death Year"],
                                                      item["Death Month"],
                                                      item["Death Day"])

        dod_city = item["Death City"]
        dod_state = item["Death State"]
        # if foreign-dead, leave blank for now.
        if len(dod_state) > 2:
            dod_state = ""

        if not pd.isnull(item["Middle Name"]):
            if len(item["Middle Name"]) == 1:
                item["Middle Name"] += "."

        if not pd.isnull(item["Gender"]):
            gender = get_gender(item["Gender"])

        # Instantiate Judge object.
        person = Person(
            name_first=item["First Name"],
            name_middle=item["Middle Name"],
            name_last=item["Last Name"],
            name_suffix=get_suffix(item["Suffix"]),
            gender=gender,
            fjc_id=item["jid"],
            cl_id=item["cl_id"],
            date_dob=date_dob,
            date_granularity_dob=date_granularity_dob,
            dob_city=dob_city,
            dob_state=dob_state,
            date_dod=date_dod,
            date_granularity_dod=date_granularity_dod,
            dod_city=dod_city,
            dod_state=dod_state,
        )

    if not testing:
        person.save()

    listraces = get_races(item["Race or Ethnicity"])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    add_positions_from_row(item, person, testing)

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        school_str = " (%s)" % schoolnum
        schoolname = item["School" + school_str]

        if pd.isnull(schoolname):
            continue

        if pd.isnull(item["Degree" + school_str]):
            degs = [""]
        else:
            degs = [x.strip() for x in item["Degree" + school_str].split(";")]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item["Degree Year" + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(
                    person=person,
                    school=school,
                    degree_detail=degtype,
                    degree_level=deg_level,
                    degree_year=degyear,
                )
                if not testing:
                    degree.save()

    # Non-judicial positions
    titles, locations, startyears, endyears = transform_employ(
        item["Professional Career"])
    # There is no field in the FJC data with variables commented out below
    # (i.e. "Bankruptcy and Magistrate service").  This is commented out
    # for reference as pulling out bankruptcy and magistrate service does
    # need to be implemented (yet).

    # titles2, locations2, startyears2, endyears2 = transform_bankruptcy(item['Bankruptcy and Magistrate service'])
    # titles = titles + titles2
    # locations = locations + locations2
    # startyears = startyears + startyears2
    # endyears = endyears + endyears2

    for i in range(len(titles)):
        job_title = titles[i]
        if (pd.isnull(job_title) or job_title == ""
                or job_title.startswith("Nominated")):
            continue
        location = locations[i]
        start_year = startyears[i]
        end_year = endyears[i]

        job_title = job_title.strip()

        if pd.isnull(start_year) or start_year == "":
            date_start = None
            date_start_granularity = ""
        else:
            try:
                start_year = int(start_year)
            except:
                continue
            date_start = date(start_year, 1, 1)
            date_start_granularity = GRANULARITY_YEAR
        if not pd.isnull(end_year) and end_year.isdigit():
            end_year = int(end_year)
            date_end = date(end_year, 1, 1)
            date_end_granularity = GRANULARITY_YEAR
        else:
            date_end = None
            date_end_granularity = ""

        if not pd.isnull(location):
            location = location.strip()
            if "," in location:
                city, state = [x.strip() for x in location.split(",")]
                org = ""
                if state in STATES_NORMALIZED.values():
                    pass
                elif state.lower() in STATES_NORMALIZED.keys():
                    state = STATES_NORMALIZED[state.lower()]
                else:
                    city, state = "", ""
                    org = location
            else:
                city, state = "", ""
                org = location
            # test for schools and courts
        else:
            city, state, org = "", "", ""

        position = Position(
            person=person,
            job_title=job_title,
            date_start=date_start,
            date_granularity_start=date_start_granularity,
            date_termination=date_end,
            date_granularity_termination=date_end_granularity,
            location_city=city,
            location_state=state,
            organization_name=org,
        )

        if not testing:
            try:
                position.save()
            except Exception:
                continue
def make_mag_bk_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge
    object.  Returns a Judge object.  To be used for importing bankruptcy
    and magistrate judges.  Example data below:

    CL_ID: fjc-bk-12
    COURT: AR,E
    JUDGE_NAME: JONES, PHYLLIS M.
    POSITION: Bankruptcy
    NAME_LAST: Jones
    NAME_FIRST: Phyllis
    NAME_MIDDLE: M.
    NAME_SUFFIX:
    GENDER: Female
    START_DATE: 2015-01-07
    START_DATE_GRANULARITY: %Y-%m-%d
    SOURCE: https://www.uscourts.gov/judicial-milestones/phyllis-m-jones
    """

    name = "{CL_ID}: {NAME_FIRST} {NAME_LAST}".format(**item)
    print("Now processing: %s" % name)

    if Person.objects.filter(cl_id=item["CL_ID"]).exists():
        raise ValidationError(
            "CL_ID already exists for the record being imported: %s" % name)

    if Person.objects.filter(
            name_first=item["NAME_FIRST"],
            name_middle=item["NAME_MIDDLE"],
            name_last=item["NAME_LAST"],
    ).exists():
        raise ValidationError(
            "Name already exists for record being imported %s" % name)

    if not pd.isnull(item["NAME_MIDDLE"]):
        if len(item["NAME_MIDDLE"]) == 1:
            item["NAME_MIDDLE"] += "."

    if not pd.isnull(item["GENDER"]):
        gender = get_gender(item["GENDER"])

    if not item["NAME_SUFFIX"] == "":
        suffix = get_suffix(item["NAME_SUFFIX"])
    else:
        suffix = ""

    # Instantiate Judge object.
    person = Person(
        name_first=item["NAME_FIRST"],
        name_middle=item["NAME_MIDDLE"],
        name_last=item["NAME_LAST"],
        name_suffix=suffix,
        gender=gender,
        cl_id=item["CL_ID"],
    )

    if not testing:
        person.save()

    # Add position.
    if re.search("Bankruptcy", item["POSITION"]):
        position_type = Position.JUDGE
        if item["COURT"]:
            court = FJC_BANKRUPTCY_COURTS[item["COURT"]]
    else:
        position_type = Position.MAGISTRATE
        if item["COURT"]:
            court = FJC_DISTRICT_COURTS[item["COURT"]]

    date_start = process_date_string(item["START_DATE"])

    position = Position(
        person=person,
        position_type=position_type,
        court_id=court,
        date_start=date_start,
        date_granularity_start=item["START_DATE_GRANULARITY"],
    )

    if not testing:
        position.save()

    sources = Source(person=person,
                     url=item["SOURCE"],
                     date_accessed=str(date.today()))

    if not testing:
        sources.save()
示例#3
0
def make_state_judge(item, testing=False):
    """Takes the state judge data <item> and associates it with a Judge object.

    Saves the judge to the DB.
    """

    if pd.isnull(item["startyear"]):
        return

    date_dob, date_granularity_dob = process_date(item["birthyear"],
                                                  item["birthmonth"],
                                                  item["birthday"])
    date_dod, date_granularity_dod = process_date(item["deathyear"],
                                                  item["deathmonth"],
                                                  item["deathday"])

    if item["firstname"] == "":
        return
    if not pd.isnull(item["midname"]):
        if len(item["midname"]) == 1:
            item["midname"] = f"{item['midname']}."

    had_alias_result = False
    check = Person.objects.filter(
        name_first=item["firstname"],
        name_last=item["lastname"],
        date_dob=date_dob,
    )
    name = (
        f"{item['cl_id']}: {item['firstname']} {item['lastname']} {date_dob}")
    if check.count() > 0:
        print(f"Warning: {name} exists.")
        person = check[0]
        if person.is_alias:
            # Grab the correct person and set our alias variable to True
            person = person.is_alias_of
    else:
        print(f"Now processing: {name}")

        person = Person(
            gender=item["gender"],
            date_dob=date_dob,
            date_granularity_dob=date_granularity_dob,
            date_dod=date_dod,
            date_granularity_dod=date_granularity_dod,
        )
        if not had_alias_result:
            # Only set the name and ID values on non-alias results, otherwise
            # you overwrite the good name with the alias name.
            person.cl_id = item["cl_id"]
            person.name_first = item["firstname"]
            person.name_middle = item["midname"]
            person.name_last = item["lastname"]
            person.name_suffix = get_suffix(item["suffname"])

        if not testing:
            person.save()

        if not pd.isnull(item["nickname"]):
            person_alias = Person(
                cl_id=f"{item['cl_id']}-alias-1",
                name_first=item["nickname"],
                name_middle=item["midname"],
                name_last=item["lastname"],
                name_suffix=get_suffix(item["suffname"]),
                is_alias_of=person,
            )
            if not testing:
                person_alias.save()

    if "colr" in item["cl_id"]:
        courtid = get_state_court_object(f"{item['court']} of {item['state']}")
    else:
        courtid = get_state_court_object(item["court"])

    if courtid is None:
        print(item)
        raise Exception

    # assign start date
    date_start, date_granularity_start = process_date(item["startyear"],
                                                      item["startmonth"],
                                                      item["startday"])

    if item["endyear"] > 2016:
        item["endyear"] = None
    date_termination, date_granularity_termination = process_date(
        item["endyear"], item["endmonth"], item["endday"])

    judgeship = Position(
        person=person,
        court_id=courtid,
        position_type="jud",
        date_start=date_start,
        date_granularity_start=date_granularity_start,
        date_termination=date_termination,
        date_granularity_termination=date_granularity_termination,
        # how_selected = get_select(courtid,item['startyear']),
        termination_reason=item["howended"],
    )

    if not testing:
        judgeship.save()

    if not pd.isnull(item["college"]):
        if ";" in item["college"]:
            colls = [x.strip() for x in item["college"].split(";")]
        else:
            colls = [item["college"].strip()]
        for coll in colls:
            school = get_school(coll)
            if school is not None:
                college = Education(person=person,
                                    school=school,
                                    degree_level="ba")
                if not testing:
                    college.save()

    if not pd.isnull(item["lawschool"]):
        if ";" in item["lawschool"]:
            lschools = [x.strip() for x in item["lawschool"].split(";")]
        else:
            lschools = [item["lawschool"].strip()]

        for L in lschools:
            lschool = get_school(L)
            if lschool is not None:
                lawschool = Education(person=person,
                                      school=lschool,
                                      degree_level="jd")
                if not testing:
                    lawschool.save()

    # iterate through job variables and add to career if applicable
    for jobvar in [
            "prevjudge",
            "prevprivate",
            "prevpolitician",
            "prevprof",
            "postjudge",
            "postprivate",
            "postpolitician",
            "postprof",
    ]:
        if pd.isnull(item[jobvar]) or item[jobvar] == 0:
            continue
        position_type = None
        if "judge" in jobvar:
            position_type = "jud"
        elif "private" in jobvar:
            position_type = "prac"
        elif "politician" in jobvar:
            position_type = "legis"
        elif "prof" in jobvar:
            position_type = "prof"

        job_start = None
        job_end = None
        if "prev" in jobvar:
            job_start = date_start.year - 1
            job_end = date_start.year - 1
        if "post" in jobvar:
            if date_termination is None:
                continue
            job_start = date_termination.year + 1
            job_end = date_termination.year + 1

        job = Position(
            person=person,
            position_type=position_type,
            date_start=date(job_start, 1, 1),
            date_granularity_start="%Y",
            date_termination=date(job_end, 1, 1),
            date_granularity_termination="%Y",
        )
        if not testing:
            job.save()

    if not pd.isnull(item["politics"]):
        politics = PoliticalAffiliation(
            person=person, political_party=item["politics"].lower())
        if not testing:
            politics.save()

    if not pd.isnull(item["links"]):
        links = item["links"]
        if ";" in links:
            urls = [x.strip() for x in links.split(";")]
        else:
            urls = [links]
        for url in urls:
            source = Source(person=person, notes=item["notes"], url=url)

            if not testing:
                source.save()
def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item['Birth year'],
                                                  item['Birth month'],
                                                  item['Birth day'])

    dob_city = item['Place of Birth (City)']
    dob_state = item['Place of Birth (State)']
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ''
    name = "%s: %s %s %s" % (item['cl_id'], item['firstname'],
                             item['lastname'], str(date_dob))
    fjc_check = Person.objects.filter(
        fjc_id=item['Judge Identification Number'])
    if len(fjc_check) > 0:
        print('Warning: %s exists' % name)
        return

    pres_check = Person.objects.filter(name_first=item['firstname'],
                                       name_last=item['lastname'],
                                       date_dob=date_dob)

    if not testing:
        print("Now processing: %s" % name)
    if len(pres_check) > 0:
        print('%s is a president.' % name)
        person = pres_check[0]
        person.fjc_id = item['Judge Identification Number']

    else:
        date_dod, date_granularity_dod = process_date(item['Death year'],
                                                      item['Death month'],
                                                      item['Death day'])

        dod_city = item['Place of Death (City)']
        dod_state = item['Place of Death (State)']
        # if foreign-dead, leave blank for now.
        if len(dod_state) > 2:
            dod_state = ''

        if not pd.isnull(item['midname']):
            if len(item['midname']) == 1:
                item['midname'] += '.'

        # instantiate Judge object
        person = Person(name_first=item['firstname'],
                        name_middle=item['midname'],
                        name_last=item['lastname'],
                        name_suffix=get_suffix(item['suffname']),
                        gender=item['gender'],
                        fjc_id=item['Judge Identification Number'],
                        cl_id=item['cl_id'],
                        date_dob=date_dob,
                        date_granularity_dob=date_granularity_dob,
                        dob_city=dob_city,
                        dob_state=dob_state,
                        date_dod=date_dod,
                        date_granularity_dod=date_granularity_dod,
                        dod_city=dod_city,
                        dod_state=dod_state)

    if not testing:
        person.save()

    listraces = get_races(item['race'])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    add_positions_from_row(item, person, testing)

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        if schoolnum > 1:
            school_str = ' (%s)' % schoolnum
        else:
            school_str = ''

        schoolname = item['Name of School' + school_str]
        if pd.isnull(schoolname):
            continue

        if pd.isnull(item['Degree' + school_str]):
            degs = ['']
        else:
            degs = [x.strip() for x in item['Degree' + school_str].split(';')]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item['Degree year' + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(person=person,
                                   school=school,
                                   degree_detail=degtype,
                                   degree_level=deg_level,
                                   degree_year=degyear)
                if not testing:
                    degree.save()

    # Non-judicial positions
    titles, locations, startyears, endyears = transform_employ(
        item['Employment text field'])
    titles2, locations2, startyears2, endyears2 = transform_bankruptcy(
        item['Bankruptcy and Magistrate service'])
    titles = titles + titles2
    locations = locations + locations2
    startyears = startyears + startyears2
    endyears = endyears + endyears2

    for i in range(len(titles)):
        job_title = titles[i]
        if pd.isnull(job_title) or job_title == '' or job_title.startswith(
                'Nominated'):
            continue
        location = locations[i]
        start_year = startyears[i]
        end_year = endyears[i]

        job_title = job_title.strip()
        if pd.isnull(start_year) or start_year == '':
            #print
            #print(name)
            #print(job_title,location,start_year,end_year)
            #print('No start date.')
            continue
        else:
            try:
                start_year = int(start_year)
            except:
                continue
            date_start = date(start_year, 1, 1)
            date_start_granularity = GRANULARITY_YEAR
        if not pd.isnull(end_year) and end_year.isdigit():
            end_year = int(end_year)
            date_end = date(end_year, 1, 1)
            date_end_granularity = GRANULARITY_YEAR
        else:
            date_end = None
            date_end_granularity = ''

        if not pd.isnull(location):
            location = location.strip()
            if ',' in location:
                city, state = [x.strip() for x in location.split(',')]
                org = ''
                if state in STATES_NORMALIZED.values():
                    pass
                elif state.lower() in STATES_NORMALIZED.keys():
                    state = STATES_NORMALIZED[state.lower()]
                else:
                    city, state = '', ''
                    org = location
            else:
                city, state = '', ''
                org = location
            # test for schools and courts
        else:
            city, state, org = '', '', ''

        position = Position(person=person,
                            job_title=job_title,
                            date_start=date_start,
                            date_granularity_start=date_start_granularity,
                            date_termination=date_end,
                            date_granularity_termination=date_end_granularity,
                            location_city=city,
                            location_state=state,
                            organization_name=org)
        if not testing:
            try:
                position.save()
            except Exception, e:
                continue
def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item['Birth year'],
                                                  item['Birth month'],
                                                  item['Birth day'])

    dob_city = item['Place of Birth (City)']
    dob_state = item['Place of Birth (State)']
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ''
    name = "%s: %s %s %s" % (item['cl_id'], item['firstname'], item['lastname'],
                             str(date_dob))
    fjc_check = Person.objects.filter(fjc_id=item['Judge Identification Number'])
    if len(fjc_check) > 0:
        print ('Warning: %s exists' % name)
        return
   
    pres_check = Person.objects.filter(name_first=item['firstname'],
                                  name_last=item['lastname'], date_dob=date_dob)

    if not testing:
        print ("Now processing: %s" % name)    
        #pass
    if len(pres_check) > 0:
        print ('%s is a president.' % name)
        person = pres_check[0]
        person.fjc_id = item['Judge Identification Number']
        
    else:
        date_dod, date_granularity_dod = process_date(item['Death year'],
                                                      item['Death month'],
                                                      item['Death day'])
    
        dod_city = item['Place of Death (City)']
        dod_state = item['Place of Death (State)']
        # if foreign-dead, leave blank for now.
        if len(dod_state) > 2:
            dod_state = ''
    
        if not pd.isnull(item['midname']):
            if len(item['midname']) == 1:
                item['midname'] += '.'
    
        # instantiate Judge object
        person = Person(
                name_first=item['firstname'],
                name_middle=item['midname'],
                name_last=item['lastname'],
                name_suffix=get_suffix(item['suffname']),
                gender=item['gender'],
                fjc_id=item['Judge Identification Number'],
                cl_id=item['cl_id'],
    
                date_dob=date_dob,
                date_granularity_dob=date_granularity_dob,
                dob_city=dob_city,
                dob_state=dob_state,
                date_dod=date_dod,
                date_granularity_dod=date_granularity_dod,
                dod_city=dod_city,
                dod_state=dod_state
        )

    if not testing:
        person.save()

    listraces = get_races(item['race'])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    prev_politics = None
    # add position items (up to 6 of them)
    for posnum in range(1, 7):
        if posnum > 1:
            pos_str = ' (%s)' % posnum
        else:
            pos_str = ''

        if pd.isnull(item['Court Name' + pos_str]):
            continue
        courtid = get_court_object(item['Court Name' + pos_str])
        if courtid is None:
            raise

        date_nominated = process_date_string(
                item['Nomination Date Senate Executive Journal' + pos_str])
        date_recess_appointment = process_date_string(
                item['Recess Appointment date' + pos_str])
        date_referred_to_judicial_committee = process_date_string(
                item['Referral date (referral to Judicial Committee)' + pos_str])
        date_judicial_committee_action = process_date_string(
                item['Committee action date' + pos_str])
        date_hearing = process_date_string(item['Hearings' + pos_str])
        date_confirmation = process_date_string(
                item['Senate Vote Date (Confirmation Date)' + pos_str])

        # assign start date
        date_start = process_date_string(item['Commission Date' + pos_str])                
        if pd.isnull(date_start) and not pd.isnull(date_recess_appointment):
            date_start = date_recess_appointment
        if pd.isnull(date_start):
            # if still no start date, skip
            continue
        date_termination = process_date_string(
                item['Date of Termination' + pos_str])
        date_retirement = process_date_string(
                item['Retirement from Active Service' + pos_str])

        if date_termination is None:
            date_granularity_termination = ''
        else:
            date_granularity_termination = GRANULARITY_DAY
            
        # check duplicate position
        dupe_search = Position.objects.filter(
                                            person=person,                                            
                                            position_type='jud',
                                            date_start=date_start,
                                            date_termination=date_termination)
        if len(dupe_search) > 0:
            print('Duplicate position:',dupe_search)
            continue

        # assign appointing president
        if not pd.isnull(item['Renominating President name' + pos_str]):
            appointstr = item['Renominating President name' + pos_str]
        else:
            appointstr = item['President name' + pos_str]
        appointer = None
        if appointstr not in ['Assignment', 'Reassignment']:
            names = appointstr.split()

            if len(names) == 3:
                first, mid, last = names
            else:
                first, last = names[0], names[-1]
                mid = ''
            appoint_search = Position.objects.filter(
                person__name_first__iexact=first,
                person__name_last__iexact=last)
            if len(appoint_search) > 1:
                appoint_search = Position.objects.filter(
                    person__name_first__iexact=first,
                    person__name_last__iexact=last,
                    person__name_middle__iexact=mid,
                    position_type='pres',
                    )
            if len(appoint_search) > 1:
                appoint_search = Position.objects.filter(
                    person__name_first__iexact=first,
                    person__name_last__iexact=last,
                    person__name_middle__iexact=mid,
                    position_type='pres',
                    date_start__lte=date_nominated,
                    date_termination__gte=date_nominated
                    )
            if len(appoint_search) == 0:
                print(names, appoint_search)
            if len(appoint_search) > 1:
                print(names, appoint_search)
            if len(appoint_search) == 1:
                appointer = appoint_search[0]

        # senate votes data
        votes = item['Senate vote Ayes/Nays' + pos_str]
        if not pd.isnull(votes):
            votes_yes, votes_no = votes.split('/')
        else:
            votes_yes = None
            votes_no = None
        if item['Senate voice vote' + pos_str] == "Yes":
            voice_vote = True
        else:
            voice_vote = False

        termdict = {'Abolition of Court': 'abolished',
                    'Death': 'ded',
                    'Reassignment': 'other_pos',
                    'Appointment to Another Judicial Position': 'other_pos',
                    'Impeachment & Conviction': 'bad_judge',
                    'Recess Appointment-Not Confirmed': 'recess_not_confirmed',
                    'Resignation': 'resign',
                    'Retirement': 'retire_vol'
                    }
        term_reason = item['Termination specific reason' + pos_str]
        if pd.isnull(term_reason):
            term_reason = ''
        else:
            term_reason = termdict[term_reason]

        position = Position(
                person=person,
                court_id=courtid,
                position_type='jud',

                date_nominated=date_nominated,
                date_recess_appointment=date_recess_appointment,
                date_referred_to_judicial_committee=date_referred_to_judicial_committee,
                date_judicial_committee_action=date_judicial_committee_action,
                date_hearing=date_hearing,
                date_confirmation=date_confirmation,
                date_start=date_start,
                date_granularity_start=GRANULARITY_DAY,
                date_termination=date_termination,
                date_granularity_termination=date_granularity_termination,
                date_retirement=date_retirement,

                appointer=appointer,

                voice_vote=voice_vote,
                votes_yes=votes_yes,
                votes_no=votes_no,
                vote_type='s',
                how_selected='a_pres',
                termination_reason=term_reason
        )

        if not testing:
            position.save()

        # set party
        p = item['Party Affiliation of President' + pos_str]
        if not pd.isnull(p) and p not in ['Assignment', 'Reassignment']:
            party = get_party(item['Party Affiliation of President' + pos_str])
            if prev_politics is None:
                if pd.isnull(date_nominated):
                    politicsgran = ''
                else:
                    politicsgran = GRANULARITY_DAY
                politics = PoliticalAffiliation(
                        person=person,
                        political_party=party,
                        date_start=date_nominated,
                        date_granularity_start=politicsgran,
                        source='a',
                )
                if not testing:
                    politics.save()
                prev_politics = party
            elif party != prev_politics:
                # account for changing political affiliation
                politics.date_end = date_nominated
                politics.date_granularity_end = GRANULARITY_DAY
                if not testing:
                    politics.save()
                politics = PoliticalAffiliation(
                    person=person,
                    political_party=party,
                    date_start=date_nominated,
                    date_granularity_start=GRANULARITY_DAY,
                    source='a'
                )
                if not testing:
                    politics.save()
        rating = get_aba(item['ABA Rating' + pos_str])
        if rating is not None:
            nom_year = date_nominated.year
            aba = ABARating(
                    person=person,
                    rating=rating,
                    year_rated=nom_year
            )
            if not testing:
                aba.save()

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        if schoolnum > 1:
            school_str = ' (%s)' % schoolnum
        else:
            school_str = ''

        schoolname = item['Name of School' + school_str]
        if pd.isnull(schoolname):
            continue

        if pd.isnull(item['Degree' + school_str]):
            degs = ['']
        else:
            degs = [x.strip() for x in item['Degree' + school_str].split(';')]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item['Degree year' + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(
                        person=person,
                        school=school,
                        degree_detail=degtype,
                        degree_level=deg_level,
                        degree_year=degyear
                )
                if not testing:
                    degree.save()

    # Non-judicial positions
    titles, locations, startyears, endyears = transform_employ(item['Employment text field'])
    titles2, locations2, startyears2, endyears2 = transform_bankruptcy(item['Bankruptcy and Magistrate service'])
    titles = titles + titles2
    locations = locations + locations2
    startyears = startyears + startyears2
    endyears = endyears + endyears2
   
    for i in range(len(titles)):
        job_title = titles[i]
        if pd.isnull(job_title) or job_title=='' or job_title.startswith('Nominated'):
            continue
        location = locations[i]
        start_year = startyears[i]
        end_year = endyears[i]
  
        job_title = job_title.strip()
        if pd.isnull(start_year) or start_year == '':
            #print
            #print(name)            
            #print(job_title,location,start_year,end_year)
            #print('No start date.')
            continue
        else:
            try:
                start_year = int(start_year)
            except:
                continue
            date_start = date(start_year,1,1)
            date_start_granularity = GRANULARITY_YEAR
        if not pd.isnull(end_year) and end_year.isdigit():
            end_year = int(end_year)            
            date_end = date(end_year,1,1)
            date_end_granularity = GRANULARITY_YEAR
        else:
            date_end = None
            date_end_granularity = ''

        if not pd.isnull(location):   
            location = location.strip()              
            if ',' in location:            
                city, state = [x.strip() for x in location.split(',')]
                org = ''
                if state in STATES_NORMALIZED.values():
                    pass
                elif state.lower() in STATES_NORMALIZED.keys():
                    state = STATES_NORMALIZED[state.lower()]
                else:                        
                    city, state = '',''
                    org = location
            else:
                city, state = '',''
                org = location                    
             # test for schools and courts
        else:
            city,state,org = '','',''
                
        position = Position(
                person=person,                
                job_title=job_title,
                
                date_start=date_start,
                date_granularity_start=date_start_granularity,
                date_termination=date_end,
                date_granularity_termination=date_end_granularity,
                
                location_city = city,
                location_state = state,
                organization_name = org
        )
        if not testing:
            try:
                position.save()            
            except Exception, e:
                continue
def make_president(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    m, d, y = [int(x) for x in item["Born"].split("/")]
    date_dob = date(y, m, d)
    dob_city = item["birth city"].strip()
    dob_state = item["birth state"].strip()

    date_dod, dod_city, dod_state = None, "", ""
    if not pd.isnull(item["Died"]):
        m, d, y = [int(x) for x in item["Died"].split("/")]
        date_dod = date(y, m, d)
        dod_city = item["death city"].strip()
        dod_state = item["death state"].strip()
        death_gran = GRANULARITY_DAY
    else:
        death_gran = ""

    if not pd.isnull(item["midname"]):
        if len(item["midname"]) == 1:
            item["midname"] += "."

    # instantiate Judge object
    person = Person(
        name_first=item["firstname"],
        name_middle=item["midname"],
        name_last=item["lastname"],
        gender="m",
        cl_id=item["cl_id"],
        date_dob=date_dob,
        date_granularity_dob=GRANULARITY_DAY,
        dob_city=dob_city,
        dob_state=dob_state,
        date_dod=date_dod,
        date_granularity_dod=death_gran,
        dod_city=dod_city,
        dod_state=dod_state,
        religion=item["Religion"],
    )

    if not testing:
        person.save()

    race = Race.objects.get(race="w")
    if not testing:
        person.race.add(race)

    if item["lastname"] == "Obama":
        race = Race.objects.get(race="b")
        if not testing:
            person.race.add(race)

    party = item["party"].lower()
    politics = PoliticalAffiliation(person=person,
                                    political_party=party,
                                    source="b")
    if not testing:
        politics.save()

    position = Position(
        person=person,
        position_type="pres",
        date_start=date(item["term_start"], 1, 1),
        date_granularity_start=GRANULARITY_YEAR,
        date_termination=date(item["term_end"], 1, 1),
        date_granularity_termination=GRANULARITY_YEAR,
        location_city="Washington",
        location_state="DC",
    )

    if not testing:
        position.save()

    if not pd.isnull(item["start2"]):
        position = Position(
            person=person,
            position_type="pres",
            date_start=date(int(item["start2"]), 1, 1),
            date_granularity_start=GRANULARITY_YEAR,
            date_termination=date(int(item["end2"]), 1, 1),
            date_granularity_termination=GRANULARITY_YEAR,
            location_city="Washington",
            location_state="DC",
        )
        if not testing:
            position.save()
def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item['Birth year'],
                                                  item['Birth month'],
                                                  item['Birth day'])

    dob_city = item['Place of Birth (City)']
    dob_state = item['Place of Birth (State)']
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ''

    check = Person.objects.filter(fjc_id=item['Judge Identification Number'])
    if len(check) > 0:
        print('Warning: ' + item['firstname'] + ' ' + item['lastname'] + ' ' +
              str(date_dob) + ' exists.')
        return

    date_dod, date_granularity_dod = process_date(item['Death year'],
                                                  item['Death month'],
                                                  item['Death day'])

    dod_city = item['Place of Death (City)']
    dod_state = item['Place of Death (State)']
    # if foreign-dead, leave blank for now.
    if len(dod_state) > 2:
        dod_state = ''

    # instantiate Judge object
    person = Person(name_first=item['firstname'],
                    name_middle=item['midname'],
                    name_last=item['lastname'],
                    name_suffix=get_suffix(item['suffname']),
                    gender=item['gender'],
                    fjc_id=item['Judge Identification Number'],
                    cl_id=item['cl_id'],
                    date_dob=date_dob,
                    date_granularity_dob=date_granularity_dob,
                    dob_city=dob_city,
                    dob_state=dob_state,
                    date_dod=date_dod,
                    date_granularity_dod=date_granularity_dod,
                    dod_city=dod_city,
                    dod_state=dod_state)

    if not testing:
        person.save()

    listraces = get_races(item['race'])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    # add position items (up to 6 of them)
    for posnum in range(1, 7):
        if posnum > 1:
            pos_str = ' (%s)' % posnum
        else:
            pos_str = ''

        if pd.isnull(item['Court Name' + pos_str]):
            continue
        courtid = get_court_object(item['Court Name' + pos_str])
        if courtid is None:
            raise

        date_nominated = process_date_string(
            item['Nomination Date Senate Executive Journal'])
        date_recess_appointment = process_date_string(
            item['Recess Appointment date'])
        date_referred_to_judicial_committee = process_date_string(
            item['Referral date (referral to Judicial Committee)'])
        date_judicial_committee_action = process_date_string(
            item['Committee action date'])
        date_hearing = process_date_string(item['Hearings'])
        date_confirmation = process_date_string(
            item['Senate Vote Date (Confirmation Date)'])

        # assign start date
        date_start = process_date_string(item['Commission Date' + pos_str])
        if pd.isnull(date_start) and not pd.isnull(date_recess_appointment):
            date_start = date_recess_appointment
        if pd.isnull(date_start):
            # if still no start date, skip
            continue
        date_termination = process_date_string(item['Date of Termination' +
                                                    pos_str])
        date_retirement = process_date_string(
            item['Retirement from Active Service' + pos_str])

        if date_termination is None:
            date_granularity_termination = ''
        else:
            date_granularity_termination = '%Y-%m-%d'

        votes_yes = None
        votes_no = None

        termdict = {
            'Abolition of Court': 'abolished',
            'Death': 'ded',
            'Reassignment': 'other_pos',
            'Appointment to Another Judicial Position': 'other_pos',
            'Impeachment & Conviction': 'bad_judge',
            'Recess Appointment-Not Confirmed': 'recess_not_confirmed',
            'Resignation': 'resign',
            'Retirement': 'retire_vol'
        }
        term_reason = item['Termination specific reason' + pos_str]
        if pd.isnull(term_reason):
            term_reason = ''
        else:
            term_reason = termdict[term_reason]

        position = Position(
            person=person,
            court_id=courtid,
            position_type='jud',
            date_nominated=date_nominated,
            date_recess_appointment=date_recess_appointment,
            date_referred_to_judicial_committee=
            date_referred_to_judicial_committee,
            date_judicial_committee_action=date_judicial_committee_action,
            date_hearing=date_hearing,
            date_confirmation=date_confirmation,
            date_start=date_start,
            date_granularity_start='%Y-%m-%d',
            date_termination=date_termination,
            date_granularity_termination=date_granularity_termination,
            date_retirement=date_retirement,
            votes_yes=votes_yes,
            votes_no=votes_no,
            how_selected='a_pres',
            termination_reason=term_reason)

        if not testing:
            position.save()

        # set party
        p = item['Party Affiliation of President' + pos_str]
        if not pd.isnull(p) and p not in ['Assignment', 'Reassignment']:
            party = get_party(item['Party Affiliation of President' + pos_str])
            politics = PoliticalAffiliation(person=person,
                                            political_party=party,
                                            source='a')
            if not testing:
                politics.save()

        rating = get_aba(item['ABA Rating' + pos_str])
        if rating is not None:
            aba = ABARating(person=person, rating=rating)
            if not testing:
                aba.save()

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        if schoolnum > 1:
            school_str = ' (%s)' % schoolnum
        else:
            school_str = ''

        schoolname = item['Name of School' + school_str]
        if pd.isnull(schoolname):
            continue

        if pd.isnull(item['Degree' + school_str]):
            degs = ['']
        else:
            degs = [x.strip() for x in item['Degree' + school_str].split(';')]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item['Degree year' + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(person=person,
                                   school=school,
                                   degree=degtype,
                                   degree_level=deg_level,
                                   degree_year=degyear)
                if not testing:
                    degree.save()

    if not pd.isnull(item['Employment text field']):
        notes = item['Employment text field']
        source = Source(person=person, notes=notes)
        if not testing:
            source.save()

    if not pd.isnull(item['Bankruptcy and Magistrate service']):
        notes = item['Bankruptcy and Magistrate service']
        source = Source(person=person, notes=notes)
        if not testing:
            source.save()
def make_state_judge(item, testing=False):
    """Takes the state judge data <item> and associates it with a Judge object.

    Saves the judge to the DB.
    """

    if pd.isnull(item['startyear']):
        return

    date_dob, date_granularity_dob = process_date(item['birthyear'],
                                                  item['birthmonth'],
                                                  item['birthday'])
    date_dod, date_granularity_dod = process_date(item['deathyear'],
                                                  item['deathmonth'],
                                                  item['deathday'])

    if item['firstname'] == '':
        return
    if not pd.isnull(item['midname']):
        if len(item['midname']) == 1:
            item['midname'] = item['midname'] + '.'

    had_alias_result = False
    check = Person.objects.filter(name_first=item['firstname'],
                                  name_last=item['lastname'],
                                  date_dob=date_dob)
    name = "%s: %s %s %s" % (item['cl_id'], item['firstname'],
                             item['lastname'], date_dob)
    if check.count() > 0:
        print('Warning: %s exists.' % name)
        person = check[0]
        if person.is_alias:
            # Grab the correct person and set our alias variable to True
            person = person.is_alias_of
    else:
        print("Now processing: %s" % name)

        person = Person(gender=item['gender'],
                        date_dob=date_dob,
                        date_granularity_dob=date_granularity_dob,
                        date_dod=date_dod,
                        date_granularity_dod=date_granularity_dod)
        if not had_alias_result:
            # Only set the name and ID values on non-alias results, otherwise
            # you overwrite the good name with the alias name.
            person.cl_id = item['cl_id']
            person.name_first = item['firstname']
            person.name_middle = item['midname']
            person.name_last = item['lastname']
            person.name_suffix = get_suffix(item['suffname'])

        if not testing:
            person.save()

        if not pd.isnull(item['nickname']):
            person_alias = Person(cl_id=item['cl_id'] + "-alias-1",
                                  name_first=item['nickname'],
                                  name_middle=item['midname'],
                                  name_last=item['lastname'],
                                  name_suffix=get_suffix(item['suffname']),
                                  is_alias_of=person)
            if not testing:
                person_alias.save()

    if 'colr' in item['cl_id']:
        courtid = get_court_object(item['court'] + ' of ' + item['state'])
    else:
        courtid = get_court_object(item['court'])

    if courtid is None:
        print(item)
        raise

    # assign start date
    date_start, date_granularity_start = process_date(item['startyear'],
                                                      item['startmonth'],
                                                      item['startday'])

    if item['endyear'] > 2016:
        item['endyear'] = None
    date_termination, date_granularity_termination = process_date(
        item['endyear'], item['endmonth'], item['endday'])

    judgeship = Position(
        person=person,
        court_id=courtid,
        position_type='jud',
        date_start=date_start,
        date_granularity_start=date_granularity_start,
        date_termination=date_termination,
        date_granularity_termination=date_granularity_termination,
        # how_selected = get_select(courtid,item['startyear']),
        termination_reason=item['howended'])

    if not testing:
        judgeship.save()

    if not pd.isnull(item['college']):
        if ';' in item['college']:
            colls = [x.strip() for x in item['college'].split(';')]
        else:
            colls = [item['college'].strip()]
        for coll in colls:
            school = get_school(coll)
            if school is not None:
                college = Education(
                    person=person,
                    school=school,
                    degree_level='ba',
                )
                if not testing:
                    college.save()

    if not pd.isnull(item['lawschool']):
        if ';' in item['lawschool']:
            lschools = [x.strip() for x in item['lawschool'].split(';')]
        else:
            lschools = [item['lawschool'].strip()]

        for L in lschools:
            lschool = get_school(L)
            if lschool is not None:
                lawschool = Education(
                    person=person,
                    school=lschool,
                    degree_level='jd',
                )
                if not testing:
                    lawschool.save()

    # iterate through job variables and add to career if applicable
    for jobvar in [
            'prevjudge', 'prevprivate', 'prevpolitician', 'prevprof',
            'postjudge', 'postprivate', 'postpolitician', 'postprof'
    ]:
        if pd.isnull(item[jobvar]) or item[jobvar] == 0:
            continue
        position_type = None
        if 'judge' in jobvar:
            position_type = 'jud'
        elif 'private' in jobvar:
            position_type = 'prac'
        elif 'politician' in jobvar:
            position_type = 'legis'
        elif 'prof' in jobvar:
            position_type = 'prof'

        job_start = None
        job_end = None
        if 'prev' in jobvar:
            job_start = date_start.year - 1
            job_end = date_start.year - 1
        if 'post' in jobvar:
            if date_termination is None:
                continue
            job_start = date_termination.year + 1
            job_end = date_termination.year + 1

        job = Position(person=person,
                       position_type=position_type,
                       date_start=date(job_start, 1, 1),
                       date_granularity_start='%Y',
                       date_termination=date(job_end, 1, 1),
                       date_granularity_termination='%Y')
        if not testing:
            job.save()

    if not pd.isnull(item['politics']):
        politics = PoliticalAffiliation(
            person=person, political_party=item['politics'].lower())
        if not testing:
            politics.save()

    if not pd.isnull(item['links']):
        links = item['links']
        if ';' in links:
            urls = [x.strip() for x in links.split(';')]
        else:
            urls = [links]
        for url in urls:
            source = Source(person=person, notes=item['notes'], url=url)

            if not testing:
                source.save()
def make_state_judge(item, testing=False):
    """Takes the state judge data <item> and associates it with a Judge object.

    Saves the judge to the DB.
    """

    if pd.isnull(item['startyear']):
        return

    date_dob, date_granularity_dob = process_date(item['birthyear'],
                                                  item['birthmonth'],
                                                  item['birthday'])
    date_dod, date_granularity_dod = process_date(item['deathyear'],
                                                  item['deathmonth'],
                                                  item['deathday'])

    if item['firstname'] == '':
        return

    check = Person.objects.filter(name_first=item['firstname'],
                                  name_last=item['lastname'],
                                  date_dob=date_dob)
    if len(check) > 0:
        print('Warning: ' + item['firstname'] + ' ' + item['lastname'] + ' ' +
              str(date_dob) + ' exists.')
        person = check[0]
    else:

        person = Person(cl_id=item['cl_id'],
                        name_first=item['firstname'],
                        name_middle=item['midname'],
                        name_last=item['lastname'],
                        name_suffix=get_suffix(item['suffname']),
                        gender=item['gender'],
                        date_dob=date_dob,
                        date_granularity_dob=date_granularity_dob,
                        date_dod=date_dod,
                        date_granularity_dod=date_granularity_dod)

        if not testing:
            person.save()

    courtid = get_court_object(item['court'] + ' of ' + item['state'])

    if courtid is None:
        raise

    # assign start date
    date_start, date_granularity_start = process_date(item['startyear'],
                                                      item['startmonth'],
                                                      item['startday'])

    if not pd.isnull(item['endyear']) and item['endyear'] < 2016:
        item['endyear'] = None
    date_termination, date_granularity_termination = process_date(
        item['endyear'], item['endmonth'], item['endday'])

    judgeship = Position(
        person=person,
        court_id=courtid,
        position_type='jud',
        date_start=date_start,
        date_granularity_start=date_granularity_start,
        date_termination=date_termination,
        date_granularity_termination=date_granularity_termination,
        # how_selected = get_select(courtid,item['startyear']),
        termination_reason=item['howended'])

    if not testing:
        judgeship.save()

    if not pd.isnull(item['college']):
        if ';' in item['college']:
            colls = [x.strip() for x in item['college'].split(';')]
        else:
            colls = [item['college'].strip()]
        for coll in colls:
            school = get_school(coll)
            if school is not None:
                college = Education(
                    person=person,
                    school=school,
                    degree_level='ba',
                )
                if not testing:
                    college.save()

    if not pd.isnull(item['lawschool']):
        if ';' in item['lawschool']:
            lschools = [x.strip() for x in item['lawschool'].split(';')]
        else:
            lschools = [item['lawschool'].strip()]

        for L in lschools:
            lschool = get_school(L)
            if lschool is not None:
                lawschool = Education(
                    person=person,
                    school=lschool,
                    degree_level='jd',
                )
                if not testing:
                    lawschool.save()

    # iterate through job variables and add to career if applicable
    for jobvar in [
            'prevjudge', 'prevprivate', 'prevpolitician', 'prevprof',
            'postjudge', 'postprivate', 'postpolitician', 'postprof'
    ]:
        if pd.isnull(item[jobvar]) or item[jobvar] == 0:
            continue
        position_type = None
        if 'judge' in jobvar:
            position_type = 'jud'
        elif 'private' in jobvar:
            position_type = 'prac'
        elif 'politician' in jobvar:
            position_type = 'legis'
        elif 'prof' in jobvar:
            position_type = 'prof'

        job_start = None
        job_end = None
        if 'prev' in jobvar:
            job_start = date_start.year - 1
            job_end = date_start.year - 1
        if 'post' in jobvar:
            if date_termination is None:
                continue
            job_start = date_termination.year + 1
            job_end = date_termination.year + 1

        job = Position(person=person,
                       position_type=position_type,
                       date_start=date(job_start, 1, 1),
                       date_granularity_start='%Y',
                       date_termination=date(job_end, 1, 1),
                       date_granularity_termination='%Y')
        if not testing:
            job.save()

    if not pd.isnull(item['politics']):
        politics = PoliticalAffiliation(person=person,
                                        political_party=item['politics'])
        if not testing:
            politics.save()

    if not pd.isnull(item['links']):
        links = item['links']
        if ';' in links:
            urls = [x.strip() for x in links.split(';')]
        else:
            urls = [links]
        for url in urls:
            source = Source(person=person, notes=item['notes'], url=url)

            if not testing:
                source.save()
def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item['Birth year'],
                                                  item['Birth month'],
                                                  item['Birth day'])

    dob_city = item['Place of Birth (City)']
    dob_state = item['Place of Birth (State)']
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ''

    check = Person.objects.filter(fjc_id=item['Judge Identification Number'])
    if len(check) > 0:
        print(
        'Warning: ' + item['firstname'] + ' ' + item['lastname'] + ' ' + str(
            date_dob) + ' exists.')
        return

    date_dod, date_granularity_dod = process_date(item['Death year'],
                                                  item['Death month'],
                                                  item['Death day'])

    dod_city = item['Place of Death (City)']
    dod_state = item['Place of Death (State)']
    # if foreign-dead, leave blank for now.
    if len(dod_state) > 2:
        dod_state = ''

    # instantiate Judge object
    person = Person(
            name_first=item['firstname'],
            name_middle=item['midname'],
            name_last=item['lastname'],
            name_suffix=get_suffix(item['suffname']),
            gender=item['gender'],
            fjc_id=item['Judge Identification Number'],
            cl_id=item['cl_id'],

            date_dob=date_dob,
            date_granularity_dob=date_granularity_dob,
            dob_city=dob_city,
            dob_state=dob_state,
            date_dod=date_dod,
            date_granularity_dod=date_granularity_dod,
            dod_city=dod_city,
            dod_state=dod_state
    )

    if not testing:
        person.save()

    listraces = get_races(item['race'])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    # add position items (up to 6 of them)
    for posnum in range(1, 7):
        if posnum > 1:
            pos_str = ' (%s)' % posnum
        else:
            pos_str = ''

        if pd.isnull(item['Court Name' + pos_str]):
            continue
        courtid = get_court_object(item['Court Name' + pos_str])
        if courtid is None:
            raise

        date_nominated = process_date_string(
                item['Nomination Date Senate Executive Journal'])
        date_recess_appointment = process_date_string(
                item['Recess Appointment date'])
        date_referred_to_judicial_committee = process_date_string(
                item['Referral date (referral to Judicial Committee)'])
        date_judicial_committee_action = process_date_string(
                item['Committee action date'])
        date_hearing = process_date_string(item['Hearings'])
        date_confirmation = process_date_string(
                item['Senate Vote Date (Confirmation Date)'])

        # assign start date
        date_start = process_date_string(item['Commission Date' + pos_str])
        if pd.isnull(date_start) and not pd.isnull(date_recess_appointment):
            date_start = date_recess_appointment
        if pd.isnull(date_start):
            # if still no start date, skip
            continue
        date_termination = process_date_string(
                item['Date of Termination' + pos_str])
        date_retirement = process_date_string(
                item['Retirement from Active Service' + pos_str])

        if date_termination is None:
            date_granularity_termination = ''
        else:
            date_granularity_termination = '%Y-%m-%d'

        votes_yes = None
        votes_no = None

        termdict = {'Abolition of Court': 'abolished',
                    'Death': 'ded',
                    'Reassignment': 'other_pos',
                    'Appointment to Another Judicial Position': 'other_pos',
                    'Impeachment & Conviction': 'bad_judge',
                    'Recess Appointment-Not Confirmed': 'recess_not_confirmed',
                    'Resignation': 'resign',
                    'Retirement': 'retire_vol'
                    }
        term_reason = item['Termination specific reason' + pos_str]
        if pd.isnull(term_reason):
            term_reason = ''
        else:
            term_reason = termdict[term_reason]

        position = Position(
                person=person,
                court_id=courtid,
                position_type='jud',

                date_nominated=date_nominated,
                date_recess_appointment=date_recess_appointment,
                date_referred_to_judicial_committee=date_referred_to_judicial_committee,
                date_judicial_committee_action=date_judicial_committee_action,
                date_hearing=date_hearing,
                date_confirmation=date_confirmation,
                date_start=date_start,
                date_granularity_start='%Y-%m-%d',
                date_termination=date_termination,
                date_granularity_termination=date_granularity_termination,
                date_retirement=date_retirement,

                votes_yes=votes_yes,
                votes_no=votes_no,
                how_selected='a_pres',
                termination_reason=term_reason
        )

        if not testing:
            position.save()

        # set party
        p = item['Party Affiliation of President' + pos_str]
        if not pd.isnull(p) and p not in ['Assignment', 'Reassignment']:
            party = get_party(item['Party Affiliation of President' + pos_str])
            politics = PoliticalAffiliation(
                    person=person,
                    political_party=party,
                    source='a'
            )
            if not testing:
                politics.save()

        rating = get_aba(item['ABA Rating' + pos_str])
        if rating is not None:
            aba = ABARating(
                    person=person,
                    rating=rating
            )
            if not testing:
                aba.save()

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        if schoolnum > 1:
            school_str = ' (%s)' % schoolnum
        else:
            school_str = ''

        schoolname = item['Name of School' + school_str]
        if pd.isnull(schoolname):
            continue

        if pd.isnull(item['Degree' + school_str]):
            degs = ['']
        else:
            degs = [x.strip() for x in item['Degree' + school_str].split(';')]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item['Degree year' + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(
                        person=person,
                        school=school,
                        degree=degtype,
                        degree_level=deg_level,
                        degree_year=degyear
                )
                if not testing:
                    degree.save()

    if not pd.isnull(item['Employment text field']):
        notes = item['Employment text field']
        source = Source(
                person=person,
                notes=notes
        )
        if not testing:
            source.save()

    if not pd.isnull(item['Bankruptcy and Magistrate service']):
        notes = item['Bankruptcy and Magistrate service']
        source = Source(
                person=person,
                notes=notes
        )
        if not testing:
            source.save()
def make_state_judge(item, testing=False):
    """Takes the state judge data <item> and associates it with a Judge object.

    Saves the judge to the DB.
    """

    if pd.isnull(item['startyear']):
        return

    date_dob, date_granularity_dob = process_date(item['birthyear'],
                                                  item['birthmonth'],
                                                  item['birthday'])
    date_dod, date_granularity_dod = process_date(item['deathyear'],
                                                  item['deathmonth'],
                                                  item['deathday'])

    if item['firstname'] == '':
        return
    if not pd.isnull(item['midname']):
        if len(item['midname']) == 1:
            item['midname'] = item['midname'] + '.'

    had_alias_result = False
    check = Person.objects.filter(name_first=item['firstname'],
                                  name_last=item['lastname'], date_dob=date_dob)
    name = "%s: %s %s %s" % (item['cl_id'], item['firstname'], item['lastname'],
                             date_dob)
    if check.count() > 0:
        print('Warning: %s exists.' % name)
        person = check[0]
        if person.is_alias:
            # Grab the correct person and set our alias variable to True
            person = person.is_alias_of
    else:
        print("Now processing: %s" % name)

        person = Person(
                gender=item['gender'],
                date_dob=date_dob,
                date_granularity_dob=date_granularity_dob,
                date_dod=date_dod,
                date_granularity_dod=date_granularity_dod
        )
        if not had_alias_result:
            # Only set the name and ID values on non-alias results, otherwise
            # you overwrite the good name with the alias name.
            person.cl_id = item['cl_id']
            person.name_first = item['firstname']
            person.name_middle = item['midname']
            person.name_last = item['lastname']
            person.name_suffix = get_suffix(item['suffname'])

        if not testing:
            person.save()

        if not pd.isnull(item['nickname']):
            person_alias = Person(
                    cl_id=item['cl_id'] + "-alias-1",
                    name_first=item['nickname'],
                    name_middle=item['midname'],
                    name_last=item['lastname'],
                    name_suffix=get_suffix(item['suffname']),
                    is_alias_of=person
                    )
            if not testing:
                person_alias.save()

    if 'colr' in item['cl_id']:
        courtid = get_court_object(item['court'] + ' of ' + item['state'])
    else:
        courtid = get_court_object(item['court'])

    if courtid is None:
        print(item)
        raise

    # assign start date
    date_start, date_granularity_start = process_date(item['startyear'],
                                                      item['startmonth'],
                                                      item['startday'])

    if item['endyear'] > 2016:
        item['endyear'] = None
    date_termination, date_granularity_termination = process_date(
            item['endyear'],
            item['endmonth'],
            item['endday'])

    judgeship = Position(
            person=person,
            court_id=courtid,
            position_type='jud',
            date_start=date_start,
            date_granularity_start=date_granularity_start,
            date_termination=date_termination,
            date_granularity_termination=date_granularity_termination,
            # how_selected = get_select(courtid,item['startyear']),
            termination_reason=item['howended']
    )

    if not testing:
        judgeship.save()

    if not pd.isnull(item['college']):
        if ';' in item['college']:
            colls = [x.strip() for x in item['college'].split(';')]
        else:
            colls = [item['college'].strip()]
        for coll in colls:
            school = get_school(coll)
            if school is not None:
                college = Education(
                        person=person,
                        school=school,
                        degree_level='ba',
                )
                if not testing:
                    college.save()

    if not pd.isnull(item['lawschool']):
        if ';' in item['lawschool']:
            lschools = [x.strip() for x in item['lawschool'].split(';')]
        else:
            lschools = [item['lawschool'].strip()]

        for L in lschools:
            lschool = get_school(L)
            if lschool is not None:
                lawschool = Education(
                        person=person,
                        school=lschool,
                        degree_level='jd',
                )
                if not testing:
                    lawschool.save()

    # iterate through job variables and add to career if applicable
    for jobvar in ['prevjudge', 'prevprivate', 'prevpolitician', 'prevprof',
                   'postjudge', 'postprivate', 'postpolitician', 'postprof']:
        if pd.isnull(item[jobvar]) or item[jobvar] == 0:
            continue
        position_type = None
        if 'judge' in jobvar:
            position_type = 'jud'
        elif 'private' in jobvar:
            position_type = 'prac'
        elif 'politician' in jobvar:
            position_type = 'legis'
        elif 'prof' in jobvar:
            position_type = 'prof'

        job_start = None
        job_end = None
        if 'prev' in jobvar:
            job_start = date_start.year - 1
            job_end = date_start.year - 1
        if 'post' in jobvar:
            if date_termination is None:
                continue
            job_start = date_termination.year + 1
            job_end = date_termination.year + 1

        job = Position(
                person=person,
                position_type=position_type,
                date_start=date(job_start, 1, 1),
                date_granularity_start='%Y',
                date_termination=date(job_end, 1, 1),
                date_granularity_termination='%Y'
        )
        if not testing:
            job.save()

    if not pd.isnull(item['politics']):
        politics = PoliticalAffiliation(
                person=person,
                political_party=item['politics'].lower()
        )
        if not testing:
            politics.save()

    if not pd.isnull(item['links']):
        links = item['links']
        if ';' in links:
            urls = [x.strip() for x in links.split(';')]
        else:
            urls = [links]
        for url in urls:
            source = Source(
                    person=person,
                    notes=item['notes'],
                    url=url
            )

            if not testing:
                source.save()
def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item['Birth year'],
                                                  item['Birth month'],
                                                  item['Birth day'])

    dob_city = item['Place of Birth (City)']
    dob_state = item['Place of Birth (State)']
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ''
    name = "%s: %s %s %s" % (item['cl_id'], item['firstname'], item['lastname'],
                             str(date_dob))
    fjc_check = Person.objects.filter(fjc_id=item['Judge Identification Number'])
    if len(fjc_check) > 0:
        print ('Warning: %s exists' % name)
        return

    pres_check = Person.objects.filter(name_first=item['firstname'],
                                  name_last=item['lastname'], date_dob=date_dob)

    if not testing:
        print ("Now processing: %s" % name)
    if len(pres_check) > 0:
        print ('%s is a president.' % name)
        person = pres_check[0]
        person.fjc_id = item['Judge Identification Number']

    else:
        date_dod, date_granularity_dod = process_date(item['Death year'],
                                                      item['Death month'],
                                                      item['Death day'])

        dod_city = item['Place of Death (City)']
        dod_state = item['Place of Death (State)']
        # if foreign-dead, leave blank for now.
        if len(dod_state) > 2:
            dod_state = ''

        if not pd.isnull(item['midname']):
            if len(item['midname']) == 1:
                item['midname'] += '.'

        # instantiate Judge object
        person = Person(
                name_first=item['firstname'],
                name_middle=item['midname'],
                name_last=item['lastname'],
                name_suffix=get_suffix(item['suffname']),
                gender=item['gender'],
                fjc_id=item['Judge Identification Number'],
                cl_id=item['cl_id'],

                date_dob=date_dob,
                date_granularity_dob=date_granularity_dob,
                dob_city=dob_city,
                dob_state=dob_state,
                date_dod=date_dod,
                date_granularity_dod=date_granularity_dod,
                dod_city=dod_city,
                dod_state=dod_state
        )

    if not testing:
        person.save()

    listraces = get_races(item['race'])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    add_positions_from_row(item, person, testing)

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        if schoolnum > 1:
            school_str = ' (%s)' % schoolnum
        else:
            school_str = ''

        schoolname = item['Name of School' + school_str]
        if pd.isnull(schoolname):
            continue

        if pd.isnull(item['Degree' + school_str]):
            degs = ['']
        else:
            degs = [x.strip() for x in item['Degree' + school_str].split(';')]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item['Degree year' + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(
                        person=person,
                        school=school,
                        degree_detail=degtype,
                        degree_level=deg_level,
                        degree_year=degyear
                )
                if not testing:
                    degree.save()

    # Non-judicial positions
    titles, locations, startyears, endyears = transform_employ(item['Employment text field'])
    titles2, locations2, startyears2, endyears2 = transform_bankruptcy(item['Bankruptcy and Magistrate service'])
    titles = titles + titles2
    locations = locations + locations2
    startyears = startyears + startyears2
    endyears = endyears + endyears2

    for i in range(len(titles)):
        job_title = titles[i]
        if pd.isnull(job_title) or job_title=='' or job_title.startswith('Nominated'):
            continue
        location = locations[i]
        start_year = startyears[i]
        end_year = endyears[i]

        job_title = job_title.strip()
        if pd.isnull(start_year) or start_year == '':
            #print
            #print(name)
            #print(job_title,location,start_year,end_year)
            #print('No start date.')
            continue
        else:
            try:
                start_year = int(start_year)
            except:
                continue
            date_start = date(start_year,1,1)
            date_start_granularity = GRANULARITY_YEAR
        if not pd.isnull(end_year) and end_year.isdigit():
            end_year = int(end_year)
            date_end = date(end_year,1,1)
            date_end_granularity = GRANULARITY_YEAR
        else:
            date_end = None
            date_end_granularity = ''

        if not pd.isnull(location):
            location = location.strip()
            if ',' in location:
                city, state = [x.strip() for x in location.split(',')]
                org = ''
                if state in STATES_NORMALIZED.values():
                    pass
                elif state.lower() in STATES_NORMALIZED.keys():
                    state = STATES_NORMALIZED[state.lower()]
                else:
                    city, state = '', ''
                    org = location
            else:
                city, state = '', ''
                org = location
             # test for schools and courts
        else:
            city, state, org = '', '', ''

        position = Position(
                person=person,
                job_title=job_title,

                date_start=date_start,
                date_granularity_start=date_start_granularity,
                date_termination=date_end,
                date_granularity_termination=date_end_granularity,

                location_city=city,
                location_state=state,
                organization_name=org
        )
        if not testing:
            try:
                position.save()
            except Exception:
                continue
def make_president(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    m, d, y = [int(x) for x in item['Born'].split('/')]
    date_dob = date(y, m, d)
    dob_city = item['birth city'].strip()
    dob_state = item['birth state'].strip()

    date_dod, dod_city, dod_state = None, '', ''
    if not pd.isnull(item['Died']):
        m, d, y = [int(x) for x in item['Died'].split('/')]
        date_dod = date(y, m, d)
        dod_city = item['death city'].strip()
        dod_state = item['death state'].strip()
        death_gran = GRANULARITY_DAY
    else:
        death_gran = ''

    if not pd.isnull(item['midname']):
        if len(item['midname']) == 1:
            item['midname'] += '.'

    # instantiate Judge object
    person = Person(
        name_first=item['firstname'],
        name_middle=item['midname'],
        name_last=item['lastname'],
        gender='m',
        cl_id=item['cl_id'],

        date_dob=date_dob,
        date_granularity_dob=GRANULARITY_DAY,
        dob_city=dob_city,
        dob_state=dob_state,
        date_dod=date_dod,
        date_granularity_dod=death_gran,
        dod_city=dod_city,
        dod_state=dod_state,
        religion=item['Religion']
    )

    if not testing:
        person.save()

    race = Race.objects.get(race='w')
    if not testing:
        person.race.add(race)

    if item['lastname'] == 'Obama':
        race = Race.objects.get(race='b')
        if not testing:
            person.race.add(race)

    party = item['party'].lower()
    politics = PoliticalAffiliation(
        person=person,
        political_party=party,
        source='b'
    )
    if not testing:
        politics.save()

    position = Position(
        person=person,
        position_type='pres',
        date_start=date(item['term_start'], 1, 1),
        date_granularity_start=GRANULARITY_YEAR,
        date_termination=date(item['term_end'], 1, 1),
        date_granularity_termination=GRANULARITY_YEAR,
        location_city='Washington',
        location_state='DC'
    )

    if not testing:
        position.save()

    if not pd.isnull(item['start2']):
        position = Position(
            person=person,
            position_type='pres',
            date_start=date(int(item['start2']), 1, 1),
            date_granularity_start=GRANULARITY_YEAR,
            date_termination=date(int(item['end2']), 1, 1),
            date_granularity_termination=GRANULARITY_YEAR,
            location_city='Washington',
            location_state='DC'
        )
        if not testing:
            position.save()
def make_president(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    m, d, y = [int(x) for x in item['Born'].split('/')]
    date_dob = date(y, m, d)
    dob_city = item['birth city'].strip()
    dob_state = item['birth state'].strip()

    date_dod, dod_city, dod_state = None, '', ''
    if not pd.isnull(item['Died']):
        m, d, y = [int(x) for x in item['Died'].split('/')]
        date_dod = date(y, m, d)
        dod_city = item['death city'].strip()
        dod_state = item['death state'].strip()
        death_gran = GRANULARITY_DAY
    else:
        death_gran = ''

    if not pd.isnull(item['midname']):
        if len(item['midname']) == 1:
            item['midname'] += '.'

    # instantiate Judge object
    person = Person(name_first=item['firstname'],
                    name_middle=item['midname'],
                    name_last=item['lastname'],
                    gender='m',
                    cl_id=item['cl_id'],
                    date_dob=date_dob,
                    date_granularity_dob=GRANULARITY_DAY,
                    dob_city=dob_city,
                    dob_state=dob_state,
                    date_dod=date_dod,
                    date_granularity_dod=death_gran,
                    dod_city=dod_city,
                    dod_state=dod_state,
                    religion=item['Religion'])

    if not testing:
        person.save()

    race = Race.objects.get(race='w')
    if not testing:
        person.race.add(race)

    if item['lastname'] == 'Obama':
        race = Race.objects.get(race='b')
        if not testing:
            person.race.add(race)

    party = item['party'].lower()
    politics = PoliticalAffiliation(person=person,
                                    political_party=party,
                                    source='b')
    if not testing:
        politics.save()

    position = Position(person=person,
                        position_type='pres',
                        date_start=date(item['term_start'], 1, 1),
                        date_granularity_start=GRANULARITY_YEAR,
                        date_termination=date(item['term_end'], 1, 1),
                        date_granularity_termination=GRANULARITY_YEAR,
                        location_city='Washington',
                        location_state='DC')

    if not testing:
        position.save()

    if not pd.isnull(item['start2']):
        position = Position(person=person,
                            position_type='pres',
                            date_start=date(int(item['start2']), 1, 1),
                            date_granularity_start=GRANULARITY_YEAR,
                            date_termination=date(int(item['end2']), 1, 1),
                            date_granularity_termination=GRANULARITY_YEAR,
                            location_city='Washington',
                            location_state='DC')
        if not testing:
            position.save()
def make_federal_judge(item, testing=False):
    """Takes the federal judge data <item> and associates it with a Judge object.
    Returns a Judge object.
    """

    date_dob, date_granularity_dob = process_date(item['Birth year'],
                                                  item['Birth month'],
                                                  item['Birth day'])

    dob_city = item['Place of Birth (City)']
    dob_state = item['Place of Birth (State)']
    # if foreign-born, leave blank for now.
    if len(dob_state) > 2:
        dob_state = ''
    name = "%s: %s %s %s" % (item['cl_id'], item['firstname'],
                             item['lastname'], str(date_dob))
    fjc_check = Person.objects.filter(
        fjc_id=item['Judge Identification Number'])
    if len(fjc_check) > 0:
        print('Warning: %s exists' % name)
        return

    pres_check = Person.objects.filter(name_first=item['firstname'],
                                       name_last=item['lastname'],
                                       date_dob=date_dob)

    if not testing:
        print("Now processing: %s" % name)
        #pass
    if len(pres_check) > 0:
        print('%s is a president.' % name)
        person = pres_check[0]
        person.fjc_id = item['Judge Identification Number']

    else:
        date_dod, date_granularity_dod = process_date(item['Death year'],
                                                      item['Death month'],
                                                      item['Death day'])

        dod_city = item['Place of Death (City)']
        dod_state = item['Place of Death (State)']
        # if foreign-dead, leave blank for now.
        if len(dod_state) > 2:
            dod_state = ''

        if not pd.isnull(item['midname']):
            if len(item['midname']) == 1:
                item['midname'] += '.'

        # instantiate Judge object
        person = Person(name_first=item['firstname'],
                        name_middle=item['midname'],
                        name_last=item['lastname'],
                        name_suffix=get_suffix(item['suffname']),
                        gender=item['gender'],
                        fjc_id=item['Judge Identification Number'],
                        cl_id=item['cl_id'],
                        date_dob=date_dob,
                        date_granularity_dob=date_granularity_dob,
                        dob_city=dob_city,
                        dob_state=dob_state,
                        date_dod=date_dod,
                        date_granularity_dod=date_granularity_dod,
                        dod_city=dod_city,
                        dod_state=dod_state)

    if not testing:
        person.save()

    listraces = get_races(item['race'])
    races = [Race.objects.get(race=r) for r in listraces]
    for r in races:
        if not testing:
            person.race.add(r)

    prev_politics = None
    # add position items (up to 6 of them)
    for posnum in range(1, 7):
        if posnum > 1:
            pos_str = ' (%s)' % posnum
        else:
            pos_str = ''

        if pd.isnull(item['Court Name' + pos_str]):
            continue
        courtid = get_court_object(item['Court Name' + pos_str])
        if courtid is None:
            raise

        date_nominated = process_date_string(
            item['Nomination Date Senate Executive Journal' + pos_str])
        date_recess_appointment = process_date_string(
            item['Recess Appointment date' + pos_str])
        date_referred_to_judicial_committee = process_date_string(
            item['Referral date (referral to Judicial Committee)' + pos_str])
        date_judicial_committee_action = process_date_string(
            item['Committee action date' + pos_str])
        date_hearing = process_date_string(item['Hearings' + pos_str])
        date_confirmation = process_date_string(
            item['Senate Vote Date (Confirmation Date)' + pos_str])

        # assign start date
        date_start = process_date_string(item['Commission Date' + pos_str])
        if pd.isnull(date_start) and not pd.isnull(date_recess_appointment):
            date_start = date_recess_appointment
        if pd.isnull(date_start):
            # if still no start date, skip
            continue
        date_termination = process_date_string(item['Date of Termination' +
                                                    pos_str])
        date_retirement = process_date_string(
            item['Retirement from Active Service' + pos_str])

        if date_termination is None:
            date_granularity_termination = ''
        else:
            date_granularity_termination = GRANULARITY_DAY

        # check duplicate position
        dupe_search = Position.objects.filter(
            person=person,
            position_type='jud',
            date_start=date_start,
            date_termination=date_termination)
        if len(dupe_search) > 0:
            print('Duplicate position:', dupe_search)
            continue

        # assign appointing president
        if not pd.isnull(item['Renominating President name' + pos_str]):
            appointstr = item['Renominating President name' + pos_str]
        else:
            appointstr = item['President name' + pos_str]
        appointer = None
        if appointstr not in ['Assignment', 'Reassignment']:
            names = appointstr.split()

            if len(names) == 3:
                first, mid, last = names
            else:
                first, last = names[0], names[-1]
                mid = ''
            appoint_search = Position.objects.filter(
                person__name_first__iexact=first,
                person__name_last__iexact=last)
            if len(appoint_search) > 1:
                appoint_search = Position.objects.filter(
                    person__name_first__iexact=first,
                    person__name_last__iexact=last,
                    person__name_middle__iexact=mid,
                    position_type='pres',
                )
            if len(appoint_search) > 1:
                appoint_search = Position.objects.filter(
                    person__name_first__iexact=first,
                    person__name_last__iexact=last,
                    person__name_middle__iexact=mid,
                    position_type='pres',
                    date_start__lte=date_nominated,
                    date_termination__gte=date_nominated)
            if len(appoint_search) == 0:
                print(names, appoint_search)
            if len(appoint_search) > 1:
                print(names, appoint_search)
            if len(appoint_search) == 1:
                appointer = appoint_search[0]

        # senate votes data
        votes = item['Senate vote Ayes/Nays' + pos_str]
        if not pd.isnull(votes):
            votes_yes, votes_no = votes.split('/')
        else:
            votes_yes = None
            votes_no = None
        if item['Senate voice vote' + pos_str] == "Yes":
            voice_vote = True
        else:
            voice_vote = False

        termdict = {
            'Abolition of Court': 'abolished',
            'Death': 'ded',
            'Reassignment': 'other_pos',
            'Appointment to Another Judicial Position': 'other_pos',
            'Impeachment & Conviction': 'bad_judge',
            'Recess Appointment-Not Confirmed': 'recess_not_confirmed',
            'Resignation': 'resign',
            'Retirement': 'retire_vol'
        }
        term_reason = item['Termination specific reason' + pos_str]
        if pd.isnull(term_reason):
            term_reason = ''
        else:
            term_reason = termdict[term_reason]

        position = Position(
            person=person,
            court_id=courtid,
            position_type='jud',
            date_nominated=date_nominated,
            date_recess_appointment=date_recess_appointment,
            date_referred_to_judicial_committee=
            date_referred_to_judicial_committee,
            date_judicial_committee_action=date_judicial_committee_action,
            date_hearing=date_hearing,
            date_confirmation=date_confirmation,
            date_start=date_start,
            date_granularity_start=GRANULARITY_DAY,
            date_termination=date_termination,
            date_granularity_termination=date_granularity_termination,
            date_retirement=date_retirement,
            appointer=appointer,
            voice_vote=voice_vote,
            votes_yes=votes_yes,
            votes_no=votes_no,
            vote_type='s',
            how_selected='a_pres',
            termination_reason=term_reason)

        if not testing:
            position.save()

        # set party
        p = item['Party Affiliation of President' + pos_str]
        if not pd.isnull(p) and p not in ['Assignment', 'Reassignment']:
            party = get_party(item['Party Affiliation of President' + pos_str])
            if prev_politics is None:
                if pd.isnull(date_nominated):
                    politicsgran = ''
                else:
                    politicsgran = GRANULARITY_DAY
                politics = PoliticalAffiliation(
                    person=person,
                    political_party=party,
                    date_start=date_nominated,
                    date_granularity_start=politicsgran,
                    source='a',
                )
                if not testing:
                    politics.save()
                prev_politics = party
            elif party != prev_politics:
                # account for changing political affiliation
                politics.date_end = date_nominated
                politics.date_granularity_end = GRANULARITY_DAY
                if not testing:
                    politics.save()
                politics = PoliticalAffiliation(
                    person=person,
                    political_party=party,
                    date_start=date_nominated,
                    date_granularity_start=GRANULARITY_DAY,
                    source='a')
                if not testing:
                    politics.save()
        rating = get_aba(item['ABA Rating' + pos_str])
        if rating is not None:
            nom_year = date_nominated.year
            aba = ABARating(person=person, rating=rating, year_rated=nom_year)
            if not testing:
                aba.save()

    # add education items (up to 5 of them)
    for schoolnum in range(1, 6):
        if schoolnum > 1:
            school_str = ' (%s)' % schoolnum
        else:
            school_str = ''

        schoolname = item['Name of School' + school_str]
        if pd.isnull(schoolname):
            continue

        if pd.isnull(item['Degree' + school_str]):
            degs = ['']
        else:
            degs = [x.strip() for x in item['Degree' + school_str].split(';')]
        for degtype in degs:
            deg_level = get_degree_level(degtype)
            degyear = item['Degree year' + school_str]
            try:
                int(degyear)
            except:
                degyear = None
            school = get_school(schoolname)
            if school is not None:
                degree = Education(person=person,
                                   school=school,
                                   degree_detail=degtype,
                                   degree_level=deg_level,
                                   degree_year=degyear)
                if not testing:
                    degree.save()

    # Non-judicial positions
    titles, locations, startyears, endyears = transform_employ(
        item['Employment text field'])
    titles2, locations2, startyears2, endyears2 = transform_bankruptcy(
        item['Bankruptcy and Magistrate service'])
    titles = titles + titles2
    locations = locations + locations2
    startyears = startyears + startyears2
    endyears = endyears + endyears2

    for i in range(len(titles)):
        job_title = titles[i]
        if pd.isnull(job_title) or job_title == '' or job_title.startswith(
                'Nominated'):
            continue
        location = locations[i]
        start_year = startyears[i]
        end_year = endyears[i]

        job_title = job_title.strip()
        if pd.isnull(start_year) or start_year == '':
            #print
            #print(name)
            #print(job_title,location,start_year,end_year)
            #print('No start date.')
            continue
        else:
            try:
                start_year = int(start_year)
            except:
                continue
            date_start = date(start_year, 1, 1)
            date_start_granularity = GRANULARITY_YEAR
        if not pd.isnull(end_year) and end_year.isdigit():
            end_year = int(end_year)
            date_end = date(end_year, 1, 1)
            date_end_granularity = GRANULARITY_YEAR
        else:
            date_end = None
            date_end_granularity = ''

        if not pd.isnull(location):
            location = location.strip()
            if ',' in location:
                city, state = [x.strip() for x in location.split(',')]
                org = ''
                if state in STATES_NORMALIZED.values():
                    pass
                elif state.lower() in STATES_NORMALIZED.keys():
                    state = STATES_NORMALIZED[state.lower()]
                else:
                    city, state = '', ''
                    org = location
            else:
                city, state = '', ''
                org = location
            # test for schools and courts
        else:
            city, state, org = '', '', ''

        position = Position(person=person,
                            job_title=job_title,
                            date_start=date_start,
                            date_granularity_start=date_start_granularity,
                            date_termination=date_end,
                            date_granularity_termination=date_end_granularity,
                            location_city=city,
                            location_state=state,
                            organization_name=org)
        if not testing:
            try:
                position.save()
            except Exception, e:
                continue