In [147]:
import pandas as pd
df = pd.read_csv('ADDRESSES_20191214.csv')
df1 = df[df.index % 2 != 0]
df2 = df[df.index % 2 == 0]
In [170]:
names = df2['Addresses'].values
no_ands = [name.replace(' and ', ' & ') for name in names]
no_ands[:5]
Out[170]:
['Mr. Ahmad Hussain, Abdul & Family445 Richmond Rd',
 'Mr. & Mrs. Nicholas Ackerman1413 Bedford Avenue, No. 2',
 'Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge618 S. Citrus Avenue',
 'Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur Dean, Alice3331 Jemez Drive',
 'Mr. & Mrs. Kenneth Van Amringe4593 Viro Road']
In [171]:
addies = df1['Addresses'].values
proper_lc = [addy.replace('La Canada', 'La Cañada') for addy in addies]
proper_lc[:5]
Out[171]:
['La Cañada, California 91011',
 'Brooklyn, New York 11216',
 'Fullerton, California 92833',
 'San Diego, California 92117',
 'La Cañada, California 91011']
In [172]:
new_df = pd.DataFrame({'first': no_ands, 'second': proper_lc})
new_df[:5]
Out[172]:
first second
0 Mr. Ahmad Hussain, Abdul & Family445 Richmond Rd La Cañada, California 91011
1 Mr. & Mrs. Nicholas Ackerman1413 Bedford Avenu... Brooklyn, New York 11216
2 Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge618... Fullerton, California 92833
3 Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur ... San Diego, California 92117
4 Mr. & Mrs. Kenneth Van Amringe4593 Viro Road La Cañada, California 91011
In [174]:
import re
def split_on_num(string):
    reformatted = list(filter(None, re.split(r'(\d+)', string)))
    name = reformatted[0]
    street_addy = ' '.join(reformatted[1:])
    return name, street_addy

new_df['reformatted'] = new_df.apply(lambda x: split_on_num(x['first']), axis=1)
In [175]:
new_df[:5]
Out[175]:
first second reformatted
0 Mr. Ahmad Hussain, Abdul & Family445 Richmond Rd La Cañada, California 91011 (Mr. Ahmad Hussain, Abdul & Family, 445 Richm...
1 Mr. & Mrs. Nicholas Ackerman1413 Bedford Avenu... Brooklyn, New York 11216 (Mr. & Mrs. Nicholas Ackerman, 1413 Bedford A...
2 Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge618... Fullerton, California 92833 (Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge, ...
3 Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur ... San Diego, California 92117 (Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur...
4 Mr. & Mrs. Kenneth Van Amringe4593 Viro Road La Cañada, California 91011 (Mr. & Mrs. Kenneth Van Amringe, 4593 Viro Road)
In [176]:
new_df['Name on Envelope'] = new_df.apply(lambda x: x['reformatted'][0], axis=1)
new_df['Address 1'] = new_df.apply(lambda x: x['reformatted'][1], axis=1)
new_df['Address 2'] = ''

new_df
Out[176]:
first second reformatted Name on Envelope Address 1 Address 2
0 Mr. Ahmad Hussain, Abdul & Family445 Richmond Rd La Cañada, California 91011 (Mr. Ahmad Hussain, Abdul & Family, 445 Richm... Mr. Ahmad Hussain, Abdul & Family 445 Richmond Rd
1 Mr. & Mrs. Nicholas Ackerman1413 Bedford Avenu... Brooklyn, New York 11216 (Mr. & Mrs. Nicholas Ackerman, 1413 Bedford A... Mr. & Mrs. Nicholas Ackerman 1413 Bedford Avenue, No. 2
2 Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge618... Fullerton, California 92833 (Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge, ... Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge 618 S. Citrus Avenue
3 Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur ... San Diego, California 92117 (Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur... Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur ... 3331 Jemez Drive
4 Mr. & Mrs. Kenneth Van Amringe4593 Viro Road La Cañada, California 91011 (Mr. & Mrs. Kenneth Van Amringe, 4593 Viro Road) Mr. & Mrs. Kenneth Van Amringe 4593 Viro Road
... ... ... ... ... ... ...
123 Mr. & Mrs. Jamen Wurm5156 Oakwood Avenue La Cañada, California 91011 (Mr. & Mrs. Jamen Wurm, 5156 Oakwood Avenue) Mr. & Mrs. Jamen Wurm 5156 Oakwood Avenue
124 Chris Yamashita1231 S Hill Street # 279 Los Angeles, California 90015 (Chris Yamashita, 1231 S Hill Street # 279) Chris Yamashita 1231 S Hill Street # 279
125 Lauren, Reed & the cat that "Isn't Yours"1415 ... South Pasadena, California 91030 (Lauren, Reed & the cat that "Isn't Yours", 14... Lauren, Reed & the cat that "Isn't Yours" 1415 Fair Oaks Ave
126 Mr. Chris Johnson & Ms Liene Zemjāne1016 N Cur... West Hollywood, California 90046 (Mr. Chris Johnson & Ms Liene Zemjāne, 1016 N... Mr. Chris Johnson & Ms Liene Zemjāne 1016 N Curson Ave, Apt 7
127 Mr. & Mrs. George Zimmerman3445 Members Club D... Southport, North Carolina 28461 (Mr. & Mrs. George Zimmerman, 3445 Members Cl... Mr. & Mrs. George Zimmerman 3445 Members Club Drive

128 rows × 6 columns

In [177]:
new_df['City'] = new_df.apply(lambda x: x['second'].split(',')[0], axis=1)
new_df['State'] = new_df.apply(lambda x: ' '.join(x['second'].split(',')[1].split()[:-1]), axis=1)
new_df['Zip'] = new_df.apply(lambda x: x['second'].split(',')[1].split()[-1], axis=1)
new_df
Out[177]:
first second reformatted Name on Envelope Address 1 Address 2 City State Zip
0 Mr. Ahmad Hussain, Abdul & Family445 Richmond Rd La Cañada, California 91011 (Mr. Ahmad Hussain, Abdul & Family, 445 Richm... Mr. Ahmad Hussain, Abdul & Family 445 Richmond Rd La Cañada California 91011
1 Mr. & Mrs. Nicholas Ackerman1413 Bedford Avenu... Brooklyn, New York 11216 (Mr. & Mrs. Nicholas Ackerman, 1413 Bedford A... Mr. & Mrs. Nicholas Ackerman 1413 Bedford Avenue, No. 2 Brooklyn New York 11216
2 Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge618... Fullerton, California 92833 (Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge, ... Mr. & Mrs. Sean Aldridge, Mr. Clay Aldridge 618 S. Citrus Avenue Fullerton California 92833
3 Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur ... San Diego, California 92117 (Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur... Mr. & Mrs. Billy Kash, Ms Rosemary, Mr Arthur ... 3331 Jemez Drive San Diego California 92117
4 Mr. & Mrs. Kenneth Van Amringe4593 Viro Road La Cañada, California 91011 (Mr. & Mrs. Kenneth Van Amringe, 4593 Viro Road) Mr. & Mrs. Kenneth Van Amringe 4593 Viro Road La Cañada California 91011
... ... ... ... ... ... ... ... ... ...
123 Mr. & Mrs. Jamen Wurm5156 Oakwood Avenue La Cañada, California 91011 (Mr. & Mrs. Jamen Wurm, 5156 Oakwood Avenue) Mr. & Mrs. Jamen Wurm 5156 Oakwood Avenue La Cañada California 91011
124 Chris Yamashita1231 S Hill Street # 279 Los Angeles, California 90015 (Chris Yamashita, 1231 S Hill Street # 279) Chris Yamashita 1231 S Hill Street # 279 Los Angeles California 90015
125 Lauren, Reed & the cat that "Isn't Yours"1415 ... South Pasadena, California 91030 (Lauren, Reed & the cat that "Isn't Yours", 14... Lauren, Reed & the cat that "Isn't Yours" 1415 Fair Oaks Ave South Pasadena California 91030
126 Mr. Chris Johnson & Ms Liene Zemjāne1016 N Cur... West Hollywood, California 90046 (Mr. Chris Johnson & Ms Liene Zemjāne, 1016 N... Mr. Chris Johnson & Ms Liene Zemjāne 1016 N Curson Ave, Apt 7 West Hollywood California 90046
127 Mr. & Mrs. George Zimmerman3445 Members Club D... Southport, North Carolina 28461 (Mr. & Mrs. George Zimmerman, 3445 Members Cl... Mr. & Mrs. George Zimmerman 3445 Members Club Drive Southport North Carolina 28461

128 rows × 9 columns

In [ ]:
In [ ]: