- 需求：
- 导入文件，查看原始数据
- 将人口数据和各州简称数据进行合并
- 将合并的数据中重复的abbreviation列进行删除
- 查看存在缺失数据的列
- 找到有哪些state/region使得state的值为NaN，进行去重操作
- 为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN
- 合并各州面积数据areas
- 我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行
- 去除含有缺失数据的行
- 找出2010年的全民人口数据
- 计算各州的人口密度
- 排序，并找出人口密度最高的州
import numpy as np
import pandas as pd
from pandas import DataFrame
#导入文件，查看原始数据
abb = pd.read_csv('./data/state-abbrevs.csv') #state(州的全称)abbreviation（州的简称）
area = pd.read_csv('./data/state-areas.csv') #state州的全称，area (sq. mi)州的面积
pop = pd.read_csv('./data/state-population.csv')#state/region简称，ages年龄，year时间，population人口数量
#将人口数据和各州简称数据进行合并
abb_pop = pd.merge(abb,pop,left_on='abbreviation',right_on='state/region',how='outer')
abb_pop.head()
|
state |
abbreviation |
state/region |
ages |
year |
population |
| 0 |
Alabama |
AL |
AL |
under18 |
2012 |
1117489.0 |
| 1 |
Alabama |
AL |
AL |
total |
2012 |
4817528.0 |
| 2 |
Alabama |
AL |
AL |
under18 |
2010 |
1130966.0 |
| 3 |
Alabama |
AL |
AL |
total |
2010 |
4785570.0 |
| 4 |
Alabama |
AL |
AL |
under18 |
2011 |
1125763.0 |
#将合并的数据中重复的abbreviation列进行删除
abb_pop.drop(labels='abbreviation',axis=1,inplace=True)
abb_pop.head()
|
state |
state/region |
ages |
year |
population |
| 0 |
Alabama |
AL |
under18 |
2012 |
1117489.0 |
| 1 |
Alabama |
AL |
total |
2012 |
4817528.0 |
| 2 |
Alabama |
AL |
under18 |
2010 |
1130966.0 |
| 3 |
Alabama |
AL |
total |
2010 |
4785570.0 |
| 4 |
Alabama |
AL |
under18 |
2011 |
1125763.0 |
#查看存在缺失数据的列
#方式1：isnull，notll，any，all
abb_pop.isnull().any(axis=0)
#state,population这两列中是存在空值
state True
state/region False
ages False
year False
population True
dtype: bool
#方式2：
abb_pop.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 2544 entries, 0 to 2543
Data columns (total 5 columns):
state 2448 non-null object
state/region 2544 non-null object
ages 2544 non-null object
year 2544 non-null int64
population 2524 non-null float64
dtypes: float64(1), int64(1), object(3)
memory usage: 119.2+ KB
#找到有哪些state/region使得state的值为NaN，进行去重操作(将state中的空值对应的简称找到，且对简称进行去重)
abb_pop.head()
|
state |
state/region |
ages |
year |
population |
| 0 |
Alabama |
AL |
under18 |
2012 |
1117489.0 |
| 1 |
Alabama |
AL |
total |
2012 |
4817528.0 |
| 2 |
Alabama |
AL |
under18 |
2010 |
1130966.0 |
| 3 |
Alabama |
AL |
total |
2010 |
4785570.0 |
| 4 |
Alabama |
AL |
under18 |
2011 |
1125763.0 |
#思路：可以将state这一列中的空值对应的行数据取出，从该行数据中就可以取出简称的值
#1.将state中的空值定位到
abb_pop['state'].isnull()
#2.将上述的布尔值作为源数据的行索引
abb_pop.loc[abb_pop['state'].isnull()]#将state中空对应的行数据取出
#3.将简称取出
abb_pop.loc[abb_pop['state'].isnull()]['state/region']
#4.对简称去重
abb_pop.loc[abb_pop['state'].isnull()]['state/region'].unique()
#结论：只有PR和USA对应的全称数据为空值
array([], dtype=object)
#为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN
#思考：填充该需求中的空值可不可以使用fillna？
# - 不可以。fillna可以使用空的紧邻值做填充。fillna(value='xxx')使用指定的值填充空值
# 使用给元素赋值的方式进行填充！
#1.先给USA的全称对应的空值进行批量赋值
#1.1将USA对应的行数据找出（行数据中就存在state的空值）
abb_pop['state/region'] == 'USA'
abb_pop.loc[abb_pop['state/region'] == 'USA']#将usa对应的行数据取出
|
state |
state/region |
ages |
year |
population |
| 2496 |
NaN |
USA |
under18 |
1990 |
64218512.0 |
| 2497 |
NaN |
USA |
total |
1990 |
249622814.0 |
| 2498 |
NaN |
USA |
total |
1991 |
252980942.0 |
| 2499 |
NaN |
USA |
under18 |
1991 |
65313018.0 |
| 2500 |
NaN |
USA |
under18 |
1992 |
66509177.0 |
| 2501 |
NaN |
USA |
total |
1992 |
256514231.0 |
| 2502 |
NaN |
USA |
total |
1993 |
259918595.0 |
| 2503 |
NaN |
USA |
under18 |
1993 |
67594938.0 |
| 2504 |
NaN |
USA |
under18 |
1994 |
68640936.0 |
| 2505 |
NaN |
USA |
total |
1994 |
263125826.0 |
| 2506 |
NaN |
USA |
under18 |
1995 |
69473140.0 |
| 2507 |
NaN |
USA |
under18 |
1996 |
70233512.0 |
| 2508 |
NaN |
USA |
total |
1995 |
266278403.0 |
| 2509 |
NaN |
USA |
total |
1996 |
269394291.0 |
| 2510 |
NaN |
USA |
total |
1997 |
272646932.0 |
| 2511 |
NaN |
USA |
under18 |
1997 |
70920738.0 |
| 2512 |
NaN |
USA |
under18 |
1998 |
71431406.0 |
| 2513 |
NaN |
USA |
total |
1998 |
275854116.0 |
| 2514 |
NaN |
USA |
under18 |
1999 |
71946051.0 |
| 2515 |
NaN |
USA |
total |
2000 |
282162411.0 |
| 2516 |
NaN |
USA |
under18 |
2000 |
72376189.0 |
| 2517 |
NaN |
USA |
total |
1999 |
279040181.0 |
| 2518 |
NaN |
USA |
total |
2001 |
284968955.0 |
| 2519 |
NaN |
USA |
under18 |
2001 |
72671175.0 |
| 2520 |
NaN |
USA |
total |
2002 |
287625193.0 |
| 2521 |
NaN |
USA |
under18 |
2002 |
72936457.0 |
| 2522 |
NaN |
USA |
total |
2003 |
290107933.0 |
| 2523 |
NaN |
USA |
under18 |
2003 |
73100758.0 |
| 2524 |
NaN |
USA |
total |
2004 |
292805298.0 |
| 2525 |
NaN |
USA |
under18 |
2004 |
73297735.0 |
| 2526 |
NaN |
USA |
total |
2005 |
295516599.0 |
| 2527 |
NaN |
USA |
under18 |
2005 |
73523669.0 |
| 2528 |
NaN |
USA |
total |
2006 |
298379912.0 |
| 2529 |
NaN |
USA |
under18 |
2006 |
73757714.0 |
| 2530 |
NaN |
USA |
total |
2007 |
301231207.0 |
| 2531 |
NaN |
USA |
under18 |
2007 |
74019405.0 |
| 2532 |
NaN |
USA |
total |
2008 |
304093966.0 |
| 2533 |
NaN |
USA |
under18 |
2008 |
74104602.0 |
| 2534 |
NaN |
USA |
under18 |
2013 |
73585872.0 |
| 2535 |
NaN |
USA |
total |
2013 |
316128839.0 |
| 2536 |
NaN |
USA |
total |
2009 |
306771529.0 |
| 2537 |
NaN |
USA |
under18 |
2009 |
74134167.0 |
| 2538 |
NaN |
USA |
under18 |
2010 |
74119556.0 |
| 2539 |
NaN |
USA |
total |
2010 |
309326295.0 |
| 2540 |
NaN |
USA |
under18 |
2011 |
73902222.0 |
| 2541 |
NaN |
USA |
total |
2011 |
311582564.0 |
| 2542 |
NaN |
USA |
under18 |
2012 |
73708179.0 |
| 2543 |
NaN |
USA |
total |
2012 |
313873685.0 |
#1.2将USA对应的全称空对应的行索引取出
indexs = abb_pop.loc[abb_pop['state/region'] == 'USA'].index
abb_pop.iloc[indexs]
abb_pop.loc[indexs,'state'] = 'United States'
#2.可以将PR的全称进行赋值
abb_pop['state/region'] == 'PR'
abb_pop.loc[abb_pop['state/region'] == 'PR'] #PR对应的行数据
indexs = abb_pop.loc[abb_pop['state/region'] == 'PR'].index
abb_pop.loc[indexs,'state'] = 'PPPRRR'
#合并各州面积数据areas
abb_pop_area = pd.merge(abb_pop,area,how='outer')
#我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行
abb_pop_area['area (sq. mi)'].isnull()
abb_pop_area.loc[abb_pop_area['area (sq. mi)'].isnull()] #空对应的行数据
indexs = abb_pop_area.loc[abb_pop_area['area (sq. mi)'].isnull()].index
#去除含有缺失数据的行
abb_pop_area.drop(labels=indexs,axis=0,inplace=True)
#找出2010年的全民人口数据(基于df做条件查询)
abb_pop_area.query('ages == "total" & year == 2010')
|
state |
state/region |
ages |
year |
population |
area (sq. mi) |
| 3 |
Alabama |
AL |
total |
2010.0 |
4785570.0 |
52423.0 |
| 91 |
Alaska |
AK |
total |
2010.0 |
713868.0 |
656425.0 |
| 101 |
Arizona |
AZ |
total |
2010.0 |
6408790.0 |
114006.0 |
| 189 |
Arkansas |
AR |
total |
2010.0 |
2922280.0 |
53182.0 |
| 197 |
California |
CA |
total |
2010.0 |
37333601.0 |
163707.0 |
| 283 |
Colorado |
CO |
total |
2010.0 |
5048196.0 |
104100.0 |
| 293 |
Connecticut |
CT |
total |
2010.0 |
3579210.0 |
5544.0 |
| 379 |
Delaware |
DE |
total |
2010.0 |
899711.0 |
1954.0 |
| 389 |
District of Columbia |
DC |
total |
2010.0 |
605125.0 |
68.0 |
| 475 |
Florida |
FL |
total |
2010.0 |
18846054.0 |
65758.0 |
| 485 |
Georgia |
GA |
total |
2010.0 |
9713248.0 |
59441.0 |
| 570 |
Hawaii |
HI |
total |
2010.0 |
1363731.0 |
10932.0 |
| 581 |
Idaho |
ID |
total |
2010.0 |
1570718.0 |
83574.0 |
| 666 |
Illinois |
IL |
total |
2010.0 |
12839695.0 |
57918.0 |
| 677 |
Indiana |
IN |
total |
2010.0 |
6489965.0 |
36420.0 |
| 762 |
Iowa |
IA |
total |
2010.0 |
3050314.0 |
56276.0 |
| 773 |
Kansas |
KS |
total |
2010.0 |
2858910.0 |
82282.0 |
| 858 |
Kentucky |
KY |
total |
2010.0 |
4347698.0 |
40411.0 |
| 869 |
Louisiana |
LA |
total |
2010.0 |
4545392.0 |
51843.0 |
| 954 |
Maine |
ME |
total |
2010.0 |
1327366.0 |
35387.0 |
| 965 |
Montana |
MT |
total |
2010.0 |
990527.0 |
147046.0 |
| 1050 |
Nebraska |
NE |
total |
2010.0 |
1829838.0 |
77358.0 |
| 1061 |
Nevada |
NV |
total |
2010.0 |
2703230.0 |
110567.0 |
| 1146 |
New Hampshire |
NH |
total |
2010.0 |
1316614.0 |
9351.0 |
| 1157 |
New Jersey |
NJ |
total |
2010.0 |
8802707.0 |
8722.0 |
| 1242 |
New Mexico |
NM |
total |
2010.0 |
2064982.0 |
121593.0 |
| 1253 |
New York |
NY |
total |
2010.0 |
19398228.0 |
54475.0 |
| 1338 |
North Carolina |
NC |
total |
2010.0 |
9559533.0 |
53821.0 |
| 1349 |
North Dakota |
ND |
total |
2010.0 |
674344.0 |
70704.0 |
| 1434 |
Ohio |
OH |
total |
2010.0 |
11545435.0 |
44828.0 |
| 1445 |
Oklahoma |
OK |
total |
2010.0 |
3759263.0 |
69903.0 |
| 1530 |
Oregon |
OR |
total |
2010.0 |
3837208.0 |
98386.0 |
| 1541 |
Maryland |
MD |
total |
2010.0 |
5787193.0 |
12407.0 |
| 1626 |
Massachusetts |
MA |
total |
2010.0 |
6563263.0 |
10555.0 |
| 1637 |
Michigan |
MI |
total |
2010.0 |
9876149.0 |
96810.0 |
| 1722 |
Minnesota |
MN |
total |
2010.0 |
5310337.0 |
86943.0 |
| 1733 |
Mississippi |
MS |
total |
2010.0 |
2970047.0 |
48434.0 |
| 1818 |
Missouri |
MO |
total |
2010.0 |
5996063.0 |
69709.0 |
| 1829 |
Pennsylvania |
PA |
total |
2010.0 |
12710472.0 |
46058.0 |
| 1914 |
Rhode Island |
RI |
total |
2010.0 |
1052669.0 |
1545.0 |
| 1925 |
South Carolina |
SC |
total |
2010.0 |
4636361.0 |
32007.0 |
| 2010 |
South Dakota |
SD |
total |
2010.0 |
816211.0 |
77121.0 |
| 2021 |
Tennessee |
TN |
total |
2010.0 |
6356683.0 |
42146.0 |
| 2106 |
Texas |
TX |
total |
2010.0 |
25245178.0 |
268601.0 |
| 2117 |
Utah |
UT |
total |
2010.0 |
2774424.0 |
84904.0 |
| 2202 |
Vermont |
VT |
total |
2010.0 |
625793.0 |
9615.0 |
| 2213 |
Virginia |
VA |
total |
2010.0 |
8024417.0 |
42769.0 |
| 2298 |
Washington |
WA |
total |
2010.0 |
6742256.0 |
71303.0 |
| 2309 |
West Virginia |
WV |
total |
2010.0 |
1854146.0 |
24231.0 |
| 2394 |
Wisconsin |
WI |
total |
2010.0 |
5689060.0 |
65503.0 |
| 2405 |
Wyoming |
WY |
total |
2010.0 |
564222.0 |
97818.0 |
#计算各州的人口密度(人口除以面积)
abb_pop_area['midu'] = abb_pop_area['population'] / abb_pop_area['area (sq. mi)']
abb_pop_area
|
state |
state/region |
ages |
year |
population |
area (sq. mi) |
midu |
| 0 |
Alabama |
AL |
under18 |
2012.0 |
1117489.0 |
52423.0 |
21.316769 |
| 1 |
Alabama |
AL |
total |
2012.0 |
4817528.0 |
52423.0 |
91.897221 |
| 2 |
Alabama |
AL |
under18 |
2010.0 |
1130966.0 |
52423.0 |
21.573851 |
| 3 |
Alabama |
AL |
total |
2010.0 |
4785570.0 |
52423.0 |
91.287603 |
| 4 |
Alabama |
AL |
under18 |
2011.0 |
1125763.0 |
52423.0 |
21.474601 |
| 5 |
Alabama |
AL |
total |
2011.0 |
4801627.0 |
52423.0 |
91.593900 |
| 6 |
Alabama |
AL |
total |
2009.0 |
4757938.0 |
52423.0 |
90.760506 |
| 7 |
Alabama |
AL |
under18 |
2009.0 |
1134192.0 |
52423.0 |
21.635389 |
| 8 |
Alabama |
AL |
under18 |
2013.0 |
1111481.0 |
52423.0 |
21.202163 |
| 9 |
Alabama |
AL |
total |
2013.0 |
4833722.0 |
52423.0 |
92.206131 |
| 10 |
Alabama |
AL |
total |
2007.0 |
4672840.0 |
52423.0 |
89.137211 |
| 11 |
Alabama |
AL |
under18 |
2007.0 |
1132296.0 |
52423.0 |
21.599222 |
| 12 |
Alabama |
AL |
total |
2008.0 |
4718206.0 |
52423.0 |
90.002594 |
| 13 |
Alabama |
AL |
under18 |
2008.0 |
1134927.0 |
52423.0 |
21.649410 |
| 14 |
Alabama |
AL |
total |
2005.0 |
4569805.0 |
52423.0 |
87.171757 |
| 15 |
Alabama |
AL |
under18 |
2005.0 |
1117229.0 |
52423.0 |
21.311810 |
| 16 |
Alabama |
AL |
total |
2006.0 |
4628981.0 |
52423.0 |
88.300574 |
| 17 |
Alabama |
AL |
under18 |
2006.0 |
1126798.0 |
52423.0 |
21.494344 |
| 18 |
Alabama |
AL |
total |
2004.0 |
4530729.0 |
52423.0 |
86.426359 |
| 19 |
Alabama |
AL |
under18 |
2004.0 |
1113662.0 |
52423.0 |
21.243767 |
| 20 |
Alabama |
AL |
total |
2003.0 |
4503491.0 |
52423.0 |
85.906778 |
| 21 |
Alabama |
AL |
under18 |
2003.0 |
1113083.0 |
52423.0 |
21.232722 |
| 22 |
Alabama |
AL |
total |
2001.0 |
4467634.0 |
52423.0 |
85.222784 |
| 23 |
Alabama |
AL |
under18 |
2001.0 |
1120409.0 |
52423.0 |
21.372470 |
| 24 |
Alabama |
AL |
total |
2002.0 |
4480089.0 |
52423.0 |
85.460370 |
| 25 |
Alabama |
AL |
under18 |
2002.0 |
1116590.0 |
52423.0 |
21.299620 |
| 26 |
Alabama |
AL |
under18 |
1999.0 |
1121287.0 |
52423.0 |
21.389218 |
| 27 |
Alabama |
AL |
total |
1999.0 |
4430141.0 |
52423.0 |
84.507583 |
| 28 |
Alabama |
AL |
total |
2000.0 |
4452173.0 |
52423.0 |
84.927856 |
| 29 |
Alabama |
AL |
under18 |
2000.0 |
1122273.0 |
52423.0 |
21.408027 |
| ... |
... |
... |
... |
... |
... |
... |
... |
| 2419 |
Wyoming |
WY |
under18 |
2003.0 |
124182.0 |
97818.0 |
1.269521 |
| 2420 |
Wyoming |
WY |
total |
2004.0 |
509106.0 |
97818.0 |
5.204625 |
| 2421 |
Wyoming |
WY |
under18 |
2004.0 |
123974.0 |
97818.0 |
1.267395 |
| 2422 |
Wyoming |
WY |
total |
2002.0 |
500017.0 |
97818.0 |
5.111707 |
| 2423 |
Wyoming |
WY |
under18 |
2002.0 |
125495.0 |
97818.0 |
1.282944 |
| 2424 |
Wyoming |
WY |
total |
2001.0 |
494657.0 |
97818.0 |
5.056912 |
| 2425 |
Wyoming |
WY |
under18 |
2001.0 |
126212.0 |
97818.0 |
1.290274 |
| 2426 |
Wyoming |
WY |
total |
2000.0 |
494300.0 |
97818.0 |
5.053262 |
| 2427 |
Wyoming |
WY |
under18 |
2000.0 |
128774.0 |
97818.0 |
1.316465 |
| 2428 |
Wyoming |
WY |
total |
1999.0 |
491780.0 |
97818.0 |
5.027500 |
| 2429 |
Wyoming |
WY |
under18 |
1999.0 |
130793.0 |
97818.0 |
1.337106 |
| 2430 |
Wyoming |
WY |
total |
1997.0 |
489452.0 |
97818.0 |
5.003701 |
| 2431 |
Wyoming |
WY |
under18 |
1997.0 |
134328.0 |
97818.0 |
1.373244 |
| 2432 |
Wyoming |
WY |
under18 |
1998.0 |
132602.0 |
97818.0 |
1.355599 |
| 2433 |
Wyoming |
WY |
total |
1998.0 |
490787.0 |
97818.0 |
5.017349 |
| 2434 |
Wyoming |
WY |
under18 |
1996.0 |
135698.0 |
97818.0 |
1.387250 |
| 2435 |
Wyoming |
WY |
total |
1996.0 |
488167.0 |
97818.0 |
4.990564 |
| 2436 |
Wyoming |
WY |
total |
1995.0 |
485160.0 |
97818.0 |
4.959823 |
| 2437 |
Wyoming |
WY |
under18 |
1995.0 |
136785.0 |
97818.0 |
1.398362 |
| 2438 |
Wyoming |
WY |
under18 |
1994.0 |
137733.0 |
97818.0 |
1.408054 |
| 2439 |
Wyoming |
WY |
total |
1994.0 |
480283.0 |
97818.0 |
4.909965 |
| 2440 |
Wyoming |
WY |
under18 |
1992.0 |
137308.0 |
97818.0 |
1.403709 |
| 2441 |
Wyoming |
WY |
total |
1992.0 |
466251.0 |
97818.0 |
4.766515 |
| 2442 |
Wyoming |
WY |
total |
1993.0 |
473081.0 |
97818.0 |
4.836339 |
| 2443 |
Wyoming |
WY |
under18 |
1993.0 |
137458.0 |
97818.0 |
1.405242 |
| 2444 |
Wyoming |
WY |
total |
1991.0 |
459260.0 |
97818.0 |
4.695046 |
| 2445 |
Wyoming |
WY |
under18 |
1991.0 |
136720.0 |
97818.0 |
1.397698 |
| 2446 |
Wyoming |
WY |
under18 |
1990.0 |
136078.0 |
97818.0 |
1.391135 |
| 2447 |
Wyoming |
WY |
total |
1990.0 |
453690.0 |
97818.0 |
4.638103 |
| 2544 |
Puerto Rico |
NaN |
NaN |
NaN |
NaN |
3515.0 |
NaN |
2449 rows × 7 columns
#排序，并找出人口密度最高的州
abb_pop_area.sort_values(by='midu',axis=0,ascending=False).iloc[0]['state']
'District of Columbia'