案例分析：美国各州人口数据分析
- 需求： - 导入文件，查看原始数据 - 将人口数据和各州简称数据进行合并 - 将合并的数据中重复的abbreviation列进行删除 - 查看存在缺失数据的列 - 找到有哪些state/region使得state的值为NaN，进行去重操作 - 为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN - 合并各州面积数据areas - 我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行 - 去除含有缺失数据的行 - 找出2010年的全民人口数据 - 计算各州的人口密度 - 排序，并找出人口密度最高的五个州 df.sort_values()
# 1.导入文件，查看原始数据import numpy as npfrom pandas import DataFrame,Seriesimport pandas as pdabb = pd.read_csv('./data/state-abbrevs.csv')pop = pd.read_csv('./data/state-population.csv')area = pd.read_csv('./data/state-areas.csv')# 查看的数据abb.head(1) state abbreviation0 Alabama ALpop.head(1) state/region ages year population0 AL under18 2012 1117489.0 # 2 将人口数据和各州简称数据进行合并abb_pop = pd.merge(abb,pop,left_on='abbreviation',right_on='state/region',how='outer')abb_pop.head(3) state abbreviation state/region ages year population0 Alabama AL AL under18 2012 1117489.01 Alabama AL AL total 2012 4817528.02 Alabama AL AL under18 2010 1130966.0
# 3 将合并的数据中重复的abbreviation列进行删除abb_pop.drop(labels='abbreviation',axis=1,inplace=True)
# 4 查看存在缺失数据的列abb_pop.isnull().any(axis=0)state Truestate/region Falseages Falseyear Falsepopulation Truedtype: bool
# 5 找到有哪些state/region使得state的值为NaN，进行去重操作# 找到哪些简称 的全称为空 (就是先找到state中的空值 ，通过state在找到state/region) # 把简称找到以后 进行去重# 找全称为空,用该数据找到简称，然后去重abb_pop.head(5) state state/region ages year population0 Alabama AL under18 2012 1117489.01 Alabama AL total 2012 4817528.02 Alabama AL under18 2010 1130966.03 Alabama AL total 2010 4785570.04 Alabama AL under18 2011 1125763.0# 5.1.找出state中的空值abb_pop['state'].isnull()# 5.2.将布尔值作为元数据的行索引：定位到所有state为空对应的行数据abb_pop.loc[abb_pop['state'].isnull()]# 5.3.将空对应的行数据中的简称这一列的数据取出进行去重操作abb_pop.loc[abb_pop['state'].isnull()]['state/region'].unique()# array([], dtype=object)
# 6 为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN# 6.1.找出USA对应state列中的空值# 返回的是bool值abb_pop['state/region'] == 'USA'# 6.2.取出USA对应的行数据abb_pop.loc[abb_pop['state/region'] == 'USA']indexs = abb_pop.loc[abb_pop['state/region'] == 'USA'].indexindexsInt64Index([2496, 2497, 2498, 2499, 2500, 2501, 2502, 2503, 2504, 2505, 2506, 2507, 2508, 2509, 2510, 2511, 2512, 2513, 2514, 2515, 2516, 2517, 2518, 2519, 2520, 2521, 2522, 2523, 2524, 2525, 2526, 2527, 2528, 2529, 2530, 2531, 2532, 2533, 2534, 2535, 2536, 2537, 2538, 2539, 2540, 2541, 2542, 2543], dtype='int64')# 6.3.将USA对应的空值覆盖成对应的值abb_pop.loc[indexs,'state'] = 'United States'# 6.4 找到PR所对应的行数据abb_pop['state/region'] == 'PR'abb_pop.loc[abb_pop['state/region'] == 'PR']indexs = abb_pop.loc[abb_pop['state/region'] == 'PR'].indexabb_pop.loc[indexs,'state'] = 'ppprrr'area.head() state area (sq. mi)0 Alabama 524231 Alaska 6564252 Arizona 1140063 Arkansas 531824 California 163707
# 7 合并各州面积数据areasabb_pop_area = pd.merge(abb_pop,area,how='outer')abb_pop_area.head() state state/region ages year population area (sq. mi)0 Alabama AL under18 2012.0 1117489.0 52423.01 Alabama AL total 2012.0 4817528.0 52423.02 Alabama AL under18 2010.0 1130966.0 52423.03 Alabama AL total 2010.0 4785570.0 52423.04 Alabama AL under18 2011.0 1125763.0 52423.0
# 8 我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行# 9 去除含有缺失数据的行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()].indexabb_pop_area.drop(labels=indexs,axis=0,inplace=True)
# 10 找出2010年的全民人口数据# query 做条件查询df_2010 = abb_pop_area.query('year == 2010 & ages == "total"')df_2010 # 11 计算各州的人口密度abb_pop_area['midu'] = abb_pop_area['population'] / abb_pop_area['area (sq. mi)']abb_pop_area.head(1) state state/region ages year population area (sq. mi) midu0 Alabama AL under18 2012.0 1117489.0 52423.0 21.316769
# 12 排序，并找出人口密度最高的五个州 df.sort_values()abb_pop_area.sort_values(by='midu',axis=0,ascending=False)