Adjust Pandas Series Using np.where (Avoid ValueError: The truth value of a Series is ambiguous.)

Question

I have the following pandas series that I'm trying to adjust in an if/else manner depending on if the value in the series equals 'Unspecified'. I have been reviewing as much material on this common error in pandas but it doesn't seem to be helping. Can someone please help me have my pandas Series adjust the last state names to uppercase unless the value equals 'Unspecified'. This was my best guess test_series.where('Unspecified', test_series.str[:-2] + test_series.str[-2:].str.upper()) Thanks!!

test_series = pd.Series(['Asheville, nc', 'Cowpens, nc', 'Hendersonville, nc', 'Tryon, nc',
       'Fletcher, nc', 'Franklin, nc', 'Unspecified', 'Burnsville, nc',
       'Flat rock, nc', 'Fairview, nc', 'Greeneville, tn',
       'Weaverville, nc', 'Mills river, nc', 'Lake junaluska, nc',
       'Bristol, tn', 'Calhoun, ga', 'Canton, nc', 'Whittier, nc',
       'Bostic, nc', 'Horse shoe, nc', 'Reynolds, nc', 'Marion, nc',
       'Waynesville, nc', 'Candler, nc', 'Brevard, nc', 'Highlands, nc',
       'Knoxville, tn', 'Newport, tn', 'Greenville, sc',
       'Rutherfordton, nc', 'Hickory, nc', 'Asheboro, nc', 'Swannanoa, nc',
       'Spartanburg, sc', 'Jonesville, nc', 'Gaffney, sc', 'Randleman, nc',
       'Clyde, nc', 'Tryon,nc', 'Maryville, tn', 'Rutledge, tn',
       'Morganton, nc', 'Lake lure, nc', 'Sylva, nc', 'Mars hill, nc',
       'Dawsonville, ga', 'Arden, nc', 'Chadbourn, nc', 'Taylors, sc',
       'Oakley, nc', 'Charlotte, nc', 'Black mountain, nc',
       'Leicester, nc', 'East flat rock, nc', 'Morristown, tn',
       'Talbott, tn', 'Harmony, nc', 'Sevierville, tn', 'Newland, nc',
       'Kodak, tn', 'Marshall, nc', 'Edneyville, nc', 'Morristown, nc',
       'Forest city, nc', 'Greensboro, nc', 'Spruce pine, nc',
       'Shelby, nc', 'Barnardsville, nc', 'Tazewell, tn', 'Alexander, nc',
       'Bakersville, nc', 'Mountain home, nc', 'Clarkesville, ga',
       'Chesnee, sc', 'Pineville, nc', 'Elizabethton, tn', 'Oteen, nc',
       'Liberty, sc', 'Simpsonville, sc', 'Boone, nc', 'Clayton, ga',
       'Old fort, nc', 'Bat cave, nc', 'Johnson city, tn',
       'Bryson city, nc', 'Fayetteville, nc', 'Charleston, sc',
       'Grayson, ga', 'Murphy, nc', 'Inman, sc', 'Douglas, ga',
       'Columbus, nc', 'Glenville, nc', 'Easley, sc', 'Durham, nc',
       'Mill spring, nc', 'Clinton, tn', 'Piedmont, sc', 'Hot springs, nc',
       'Waxhaw, nc', 'La follette, tn', 'Cashiers, nc', 'Etowah, nc',
       'Nebo, nc', 'Yadkinville, nc', 'Toccoa, ga', 'Monroe, nc',
       'Boiling springs, sc', 'Cornelia, nc', 'Sparta, nc', 'Cherokee, nc',
       'Harriman, tn', 'Limestone, tn', 'Kingsport, tn', 'Laurel hill, nc',
       'Andrews, nc', 'Boiling spring, sc', 'Moncks corner, sc',
       'Cullowhee, nc', 'Clover, sc', 'Waynesvile, nc',
       'Maggie valley, nc', 'Hiawasssee, ga', 'Pigeon forge, tn',
       'Unicoi, tn', 'Gray, tn', 'Rosman, nc', 'Saluda, nc', 'Benson, nc',
       'Anderson, sc', 'Penrose, nc', 'Lake toxaway, nc',
       'Cedar mountain, nc', 'Chattanooga, tn', 'Turtletown, tn',
       'Almond, nc', 'Greenwood, sc', 'Lansing, nc', 'Wartburg, tn',
       'Cherryville, nc', 'Hildebran, nc', 'Raleigh, nc',
       'Pisgah forest, nc', 'Mooresboro, nc', 'Zebulon, nc',
       'Hiawassee, ga', 'Albemarle, nc', 'Burlington, nc', 'Salisbury, nc',
       'Livingston, tn', 'Twin brooks, nc', 'Ellenboro, nc', 'Lenoir, nc',
       'Milledgeville, ga', 'Overton, tn', 'Greer, sc', 'Thomasville, nc',
       'Jonesborough, tn', 'Blairsville, ga', 'Winston-salem, nc',
       'Atlanta, ga', 'Polk, nc', 'Dandridge, tn', 'Mooresville, nc'])

`test_series.where(test_series == 'Unspecified',...` – DJK Mar 08 '18 at 17:46 — DJK, Mar 08 '18 at 17:46

Scott Boston · Accepted Answer · 2018-03-08T18:16:21.480

I think you want:

test_series.where(test_series == 'Unspecified', 
                  test_series.str[:-2] + test_series.str[-2:].str.upper())

Output head(10):

0         Asheville, NC
1           Cowpens, NC
2    Hendersonville, NC
3             Tryon, NC
4          Fletcher, NC
5          Franklin, NC
6           Unspecified
7        Burnsville, NC
8         Flat rock, NC
9          Fairview, NC
dtype: object

Once again, list comprehension out performs .str accessor:

%timeit pd.Series([i if i == 'Unspecified' else i[:-2] + i[-2:].upper() for i in test_series])
1000 loops, best of 3: 342 µs per loop

vs

%%timeit 
test_series.where(test_series == 'Unspecified', 
                      test_series.str[:-2] + test_series.str[-2:].str.upper())

100 loops, best of 3: 2.84 ms per loop

Adjust Pandas Series Using np.where (Avoid ValueError: The truth value of a Series is ambiguous.)

1 Answers1