Finding the largest difference in population among multiple counties?

Question

I'm learning pandas on python and can't seem to finish this problem. There are 6 population columns, POPESTIMATE2010 to POPESTIMATE 2016, and I need to find the county with the largest population change between these years. (e.g. If County Population in the 5 year period is 100, 120, 80, 105, 100, 130, then its largest change in the period would be |130-80| = 50.)

What I've done so far is manage to manipulate the data into an array and list, but I'm not sure which is better to solve this problem:

import numpy as np
def answer_seven():
    sumlev = census_df.SUMLEV.values == 50
    data = census_df[['POPESTIMATE2010', 'POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013','POPESTIMATE2014','POPESTIMATE2015', 'CTYNAME']].values[sumlev]
    s = pd.Series(data[:, 0], [data[:, 1], data[:, 2], data[:, 3], data[:, 4], data[:, 5], data[:, 6]], dtype=np.int64)
return data
answer_seven()

Output when I return the data:

array([[54660, 55253, 55175, ..., 55290, 55347, 'Autauga County'],
   [183193, 186659, 190396, ..., 199713, 203709, 'Baldwin County'],
   [27341, 27226, 27159, ..., 26815, 26489, 'Barbour County'],
   ..., 
   [21102, 20912, 20989, ..., 20903, 20822, 'Uinta County'],
   [8545, 8469, 8443, ..., 8316, 8328, 'Washakie County'],
   [7181, 7114, 7065, ..., 7185, 7234, 'Weston County']], dtype=object)

And I get a list when I return the s list:

55253   55175   55038   55290   55347   Autauga County         54660
186659  190396  195126  199713  203709  Baldwin County        183193
27226   27159   26973   26815   26489   Barbour County         27341
22733   22642   22512   22549   22583   Bibb County            22861
57711   57776   57734   57658   57673   Blount County          57373
10629   10606   10628   10829   10696   Bullock County         10887
20673   20408   20261   20276   20154   Butler County          20944
117768  117286  116575  115993  115620  Calhoun County        118437
33993   34075   34153   34052   34123   Chambers County        34098
26080   26023   26084   25995   25859   Cherokee County        25976
43739   43697   43795   43921   43943   Chilton County         43665
13593   13543   13378   13289   13170   Choctaw County         13841
25570   25144   25116   24847   24675   Clarke County          25767
13670   13456   13467   13538   13555   Clay County            13880
14971   14921   15028   15072   15018   Cleburne County        14973
50448   51173   50755   50831   51211   Coffee County          50177
54443   54472   54471   54480   54354   Colbert County         54514
13121   12996   12875   12662   12672   Conecuh County         13208
11348   11195   11059   10807   10724   Coosa County           11758
38060   37818   37830   37888   37835   Covington County       37796
13896   13951   13932   13948   13963   Crenshaw County        13853
80469   80374   80756   81221   82005   Cullman County         80473
50109   50324   49833   49501   49565   Dale County            50358
43178   42777   42021   41662   41131   Dallas County          43803
71387   70942   70869   71012   71130   DeKalb County          71142
80012   80432   80883   81022   81468   Elmore County          79465
38213   38034   37857   37784   37789   Escambia County        38309
104236  104235  103852  103452  103057  Etowah County         104442
17062   16960   16857   16842   16759   Fayette County         17231
31729   31648   31507   31592   31696   Franklin County        31734
                                                               ...  

I've looked multiple forum posts, but I can't find anything that really relates to this. I know that the best way to do this is to create a 'HIGHEST' column and a 'LOWEST' column, then find the county with the largest difference, but I don't know how to find the max/min of values within an array. Really appreciate the help!

Answer 1

I think this should solve your problem

temp = census_df[census_df['SUMLEV'] == 50].set_index('CTYNAME')
yrs = ['POPESTIMATE2010','POPESTIMATE2011','POPESTIMATE2012','POPESTIMATE2013', 'POPESTIMATE2014', 'POPESTIMATE2015']
res = temp.loc[:,yrs].max(axis=1) - temp.loc[:,yrs].min(axis=1)
res.idxmax()

Answer 2

Given the data you have mentioned (limited to just a few lines for demo purposes) let's first transform it into a proper DataFrame:

from io import StringIO

dataset = """\
55253   55175   55038   55290   55347   Autauga County         54660
186659  190396  195126  199713  203709  Baldwin County        183193
27226   27159   26973   26815   26489   Barbour County         27341
22733   22642   22512   22549   22583   Bibb County            22861
57711   57776   57734   57658   57673   Blount County          57373
"""

df = pd.DataFrame.from_csv(StringIO(dataset), sep='\s{2,}', header=None).reset_index()
df.columns = ['y1', 'y2', 'y3', 'y4', 'y5', 'name', 'y6']
df = df.set_index('name')
df.head()

                y1      y2      y3      y4      y5      y6
name                        
Autauga County  55253   55175   55038   55290   55347   54660
Baldwin County  186659  190396  195126  199713  203709  183193
Barbour County  27226   27159   26973   26815   26489   27341
Bibb County     22733   22642   22512   22549   22583   22861
Blount County   57711   57776   57734   57658   57673   57373

Then you can use numpy's min and max methods to calculate the minimum and maximum values within the dataset. Afterwards you can create a new DataFrame consisting of the largest diffs. No need for any loops within python which are slow compared to the optimised methods in pandas or numpy.

df2 = DataFrame((np.max(df.values, axis=1) - np.min(df.values, axis=1)), index=df.index, columns=['largest_diff'])
df2.head()

                largest_diff
name    
Autauga County  687
Baldwin County  20516
Barbour County  852
Bibb County     349
Blount County   403