爬虫 人口调查

• 需求：
  • 导入文件，查看原始数据
  • 将人口数据和各州简称数据进行合并
  • 将合并的数据中重复的abbreviation列进行删除
  • 查看存在缺失数据的列
  • 找到有哪些state/region使得state的值为NaN，进行去重操作
  • 为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN
  • 合并各州面积数据areas
  • 我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行
  • 去除含有缺失数据的行
  • 找出2010年的全民人口数据
  • 计算各州的人口密度
  • 排序，并找出人口密度最高的五个州 df.sort_values()

In [1]:

 

 

 

 

 

import numpy as np

from pandas import DataFrame,Series

import pandas as pd

 

 

In [2]:

 

 

 

 

 

abb = pd.read_csv('./data/state-abbrevs.csv')

pop = pd.read_csv('./data/state-population.csv')

area = pd.read_csv('./data/state-areas.csv')

 

 

In [3]:

 

 

 

 

 

abb.head(1)

 

 

Out[3]:

	state	abbreviation
0	Alabama	AL

In [5]:

 

 

 

 

 

pop.head(1)

 

 

Out[5]:

	state/region	ages	year	population
0	AL	under18	2012	1117489.0

In [6]:

 

 

 

 

 

area.head(1)

 

 

Out[6]:

	state	area (sq. mi)
0	Alabama	52423

In [8]:

 

 

 

 

 

#将人口数据和各州简称数据进行合并

abb_pop=pd.merge(abb,pop,how='outer',left_on='abbreviation',right_on='state/region')

​

abb_pop.head(2)

 

 

Out[8]:

	state	abbreviation	state/region	ages	year	population
0	Alabama	AL	AL	under18	2012	1117489.0
1	Alabama	AL	AL	total	2012	4817528.0

In [11]:

 

 

 

 

 

#将合并的数据中重复的abbreviation列进行删除

abb_pop.drop(labels='abbreviation',axis=1,inplace=True)

abb_pop.head(2)

 

 

Out[11]:

	state	state/region	ages	year	population
0	Alabama	AL	under18	2012	1117489.0
1	Alabama	AL	total	2012	4817528.0

In [13]:

 

 

 

 

 

#查看存在缺失数据的列

abb_pop.isnull().any(axis=0)

 

 

Out[13]:

state            True
state/region    False
ages            False
year            False
population       True
dtype: bool

In [14]:

 

 

 

 

 

#找到有哪些state/region使得state的值为NaN，进行去重操作

abb_pop.head(5)

 

 

Out[14]:

	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

In [15]:

 

 

 

 

 

#1.找出state中的空值

abb_pop['state'].isnull()

#2.将布尔值作为元数据的行索引：定位到所有state为空对应的行数据

abb_pop.loc[abb_pop['state'].isnull()]

#3.将空对应的行数据中的简称这一列的数据取出进行去重操作

abb_pop.loc[abb_pop['state'].isnull()]['state/region'].unique()

 

 

Out[15]:

array(['PR', 'USA'], dtype=object)

In [17]:

 

 

 

 

 

#为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN

​

#1.找出USA对应state列中的空值

abb_pop['state/region'] == 'USA'

#2.取出USA对应的行数据

abb_pop.loc[abb_pop['state/region'] == 'USA']

indexs = abb_pop.loc[abb_pop['state/region'] == 'USA'].index

#3.将USA对应的空值覆盖成对应的值

abb_pop.loc[indexs,'state'] = 'United States'

​

 

 

In [18]:

 

 

 

 

 

abb_pop['state/region'] == 'PR'

abb_pop.loc[abb_pop['state/region'] == 'PR']

indexs = abb_pop.loc[abb_pop['state/region'] == 'PR'].index

abb_pop.loc[indexs,'state'] = 'PPRR'

 

 

In [20]:

 

 

 

 

 

area.head(1)

 

 

Out[20]:

	state	area (sq. mi)
0	Alabama	52423

In [23]:

 

 

 

 

 

# 合并各州面积数据areas

abb_pop_area=pd.merge(abb_pop,area,how='outer')

abb_pop_area.head(2)

 

 

Out[23]:

	state	state/region	ages	year	population	area (sq. mi)
0	Alabama	AL	under18	2012.0	1117489.0	52423.0
1	Alabama	AL	total	2012.0	4817528.0	52423.0

In [34]:

 

 

 

 

 

# 我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行

abb_pop_area['area (sq. mi)'].isnull()

​

abb_pop_area[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)

abb_pop_area.head(3)

 

 

Out[34]:

	state	state/region	ages	year	population	area (sq. mi)
0	Alabama	AL	under18	2012.0	1117489.0	52423.0
1	Alabama	AL	total	2012.0	4817528.0	52423.0
2	Alabama	AL	under18	2010.0	1130966.0	52423.0

In [38]:

 

 

 

 

 

# 找出2010年的全民人口数据

​

df_2010=abb_pop_area.query('year==2010 & ages=="total"')

df_2010

​

 

. . .

In [39]:

 

 

 

 

 

# 计算各州的人口密度

​

abb_pop_area['midu']=abb_pop_area['population']/abb_pop_area['area (sq. mi)']

​

abb_pop_area.head(2)

 

 

Out[39]:

	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

In [41]:

 

 

 

 

 

# 排序，并找出人口密度最高的五个州 df.sort_values()

​

abb_pop_area.sort_values(by='midu',axis=0,ascending=False).head(5)

​

 

 

Out[41]:

	state	state/region	ages	year	population	area (sq. mi)	midu
391	District of Columbia	DC	total	2013.0	646449.0	68.0	9506.602941
385	District of Columbia	DC	total	2012.0	633427.0	68.0	9315.102941
387	District of Columbia	DC	total	2011.0	619624.0	68.0	9112.117647
431	District of Columbia	DC	total	1990.0	605321.0	68.0	8901.779412
389	District of Columbia	DC	total	2010.0	605125.0	68.0	8898.897059

In [ ]:

 

 

 

 

 

​