pandas数据分析给力教程【完整版】（五）

Myth丶恋晨 2022-09-15 15:57 197阅读 0赞

# pandas的拼接操作 #

[上一篇：pandas数据分析给力教程【完整版】（四）][pandas]  
[下一篇：pandas数据分析给力教程【完整版】（六）][pandas 1]

pandas的拼接分为两种：

*  级联：pd.concat, pd.append
 *  合并：pd.merge

import numpy as np
    import pandas as pd
    from pandas import Series, DataFrame
    import matplotlib.pyplot as plt

## 0. 回顾numpy的级联 ##

============================================

练习12：

1.  生成2个3\*3的矩阵，对其分别进行两个维度上的级联

============================================

n1 = np.random.randint(0,100, size=(3,3))
    n2 = np.random.randint(0,100, size=(3,3))
    display(n1,n2)

array([[99, 49, 30],
           [36, 48, 70],
           [18, 90, 77]])
    
    
    
    array([[82, 12,  2],
           [40, 46, 99],
           [55,  5, 77]])

np.concatenate((n1,n2), axis=0)

array([[99, 49, 30],
           [36, 48, 70],
           [18, 90, 77],
           [82, 12,  2],
           [40, 46, 99],
           [55,  5, 77]])

np.concatenate((n1,n2), axis=1)

array([[99, 49, 30, 82, 12,  2],
           [36, 48, 70, 40, 46, 99],
           [18, 90, 77, 55,  5, 77]])

np.hstack((n1,n2))

array([[99, 49, 30, 82, 12,  2],
           [36, 48, 70, 40, 46, 99],
           [18, 90, 77, 55,  5, 77]])

np.vstack((n1,n2))

array([[99, 49, 30],
           [36, 48, 70],
           [18, 90, 77],
           [82, 12,  2],
           [40, 46, 99],
           [55,  5, 77]])

为方便讲解，我们首先定义一个生成DataFrame的函数：

def make_df(index, cols):
        df = DataFrame({ col: [col + str(i) for i in index] for col in cols})
        df.index = index
        return df

make_df([1,2,3,4], list('ABCD'))

## 1. 使用pd.concat()级联 ##

pandas使用pd.concat函数，与np.concatenate函数类似，只是多了一些参数：

objs
    axis=0
    join='outer'
    join_axes=None
    ignore_index=False
    keys = [value1,value2...]

pd.concat  concatenate()

pd.concat()

### 1) 简单级联 ###

df1 = make_df([1,2,3,4], list('ABCD'))
    df2 = make_df([1,2,3,4], list('ABCD'))

和np.concatenate一样，优先增加行数（默认axis=0）

# axis=0, 默认增加行数
    pd.concat((df1,df2))

pd.concat((df1,df2), axis=1, ignore_index=True)

可以通过设置axis来改变级联方向

级连会把该方向上索引相同的元素放在一行（一列），index/columns在级联时可以重复

也可以选择忽略ignore\_index，重新索引

pd.concat((df1,df2), ignore_index=True)

或者使用多层索引 keys

concat(\[x,y\],keys=\[‘x’,‘y’\])

pd.concat((df1,df2), keys=['df1', 'df2'])

============================================

练习13：

1.  想一想级联的应用场景？
2.  使用昨天的知识，建立一个期中考试张三、李四的成绩表ddd
3.  假设新增考试学科"计算机"，如何实现？
4.  新增王老五同学的成绩，如何实现？

============================================

data = np.random.randint(0,150, size=(4,3))
    index = ['张三', '李四', '王五', '赵六']
    columns = ['语文', '数学', '英语']
    df = DataFrame(index=index, data=data, columns=columns)
    df

df['计算机'] = np.xx

data = np.random.randint(0,150, size=(4,1))
    index = ['张三', '李四', '王五', '赵六']
    columns = ['计算机']
    computer = DataFrame(index=index, data=data, columns=columns)
    computer

df = pd.concat((df, computer), axis=1)

data = np.random.randint(0,150, size=(1,4))
    index = ['老王']
    columns = ['语文', '数学', '英语', '计算机']
    wang = DataFrame(index=index, data=data, columns=columns)
    wang

pd.concat((df, wang))

### 2) 不匹配级联 ###

不匹配指的是级联的维度的索引不一致。例如纵向级联时列索引不一致，横向级联时行索引不一致

df1

df2 = make_df([2,3,4,5], list('BCDE'))
    df2

# 不匹配级联,匹配索引进行级联,不匹配的索引,补NaN
    pd.concat((df1, df2), sort=True)

pd.concat((df1, df2), sort=True, axis=1)

有3种连接方式：

*  外连接：补NaN（默认模式）

pd.concat((df1, df2), sort=True, axis=1, join='inner') # 内连接

*  内连接：只连接匹配的项
 *  连接指定轴 join\_axes

pd.concat((df1, df2), sort=True, axis=1, join_axes=[df1.index])

d:\1903\.venv\lib\site-packages\ipykernel_launcher.py:1: FutureWarning: The join_axes-keyword is deprecated. Use .reindex or .reindex_like on the result to achieve the same functionality.
      """Entry point for launching an IPython kernel.

pd.concat((df1, df2), sort=True, axis=1, join_axes=[df2.index])

pd.concat((df1, df2), sort=True, axis=0, join_axes=[df2.columns])

============================================

练习14：

假设【期末】考试ddd2的成绩没有张三的，只有李四、王老五、赵小六的，使用多种方法级联df

============================================

data = np.random.randint(0,150, size=(4,3))
    index = ['张三', '李四', '王老五', '赵小六']
    columns = ['语文', '数学', '英语']
    df = DataFrame(index=index, data=data, columns=columns)
    df

data = np.random.randint(0,150, size=(3,3))
    index = ['李四', '王老五', '赵小六']
    columns = ['语文', '数学', '英语']
    ddd = DataFrame(index=index, data=data, columns=columns)
    ddd

# 增加行数
    pd.concat((df, ddd), keys=['期中', '期末'])

pd.concat((df, ddd), keys=['期中', '期末'], axis=1, sort=True)

pd.concat((df, ddd), keys=['期中', '期末'], axis=1, sort=True, join_axes=[ddd.index])

### 3) 使用append()函数添加 ###

由于在后面级联的使用非常普遍，因此有一个函数append专门用于在后面添加

注意: concat是pd的方法, append是DataFrame对象的方法.

df.append(ddd, verify_integrity=False)

============================================

练习15：

新建一个只有张三李四王老五的期末考试成绩单ddd3，使用append()与期中考试成绩表ddd级联

============================================

归纳总结:
        pandas的级联: pd.concat((df1, df2, df3, ...), ignore_index<忽略索引>, keys<说明索引>, join<内外连接,inner,outer>, join_axes<指定左右连接,>)
        dataframe.append(other, ignore_index, verify_integrity, sort)

## 2. 使用pd.merge()合并 ##

merge与concat的区别在于，merge需要依据某一共同的列来进行合并

使用pd.merge()合并时，会自动根据两者相同column名称的那一列，作为key来进行合并。

注意每一列元素的顺序不要求一致

### 1) 一对一合并 ###

df1 = DataFrame({ 'name':['张三','李四','Chales'],'id':[1,2,3],'age':[22,21,25]})
    
    df2 = DataFrame({ 'sex':['男','男','女'],'id':[2,3,4],'group':['sale','search','service']})
    display(df1, df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>id</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>1</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>李四</td> 
   <td>2</td> 
   <td>21</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Chales</td> 
   <td>3</td> 
   <td>25</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>sex</th> 
   <th>id</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>男</td> 
   <td>2</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>男</td> 
   <td>3</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>女</td> 
   <td>4</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

pd.merge(df1, df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>id</th> 
   <th>age</th> 
   <th>sex</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>李四</td> 
   <td>2</td> 
   <td>21</td> 
   <td>男</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Chales</td> 
   <td>3</td> 
   <td>25</td> 
   <td>男</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

df1.merge(df2)

### 2) 多对一合并 ###

df1 = DataFrame({ 'name':['张三','李四','Chales'],'id':[1,2,2],'age':[22,21,25]})
    
    df2 = DataFrame({ 'sex':['男','男','女'],'id':[2,3,4],'group':['sale','search','service']})
    display(df1,df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>id</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>1</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>李四</td> 
   <td>2</td> 
   <td>21</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Chales</td> 
   <td>2</td> 
   <td>25</td> 
  </tr> 
 </tbody> 
</table>

pd.merge(df1,df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>id</th> 
   <th>age</th> 
   <th>sex</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>李四</td> 
   <td>2</td> 
   <td>21</td> 
   <td>男</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Chales</td> 
   <td>2</td> 
   <td>25</td> 
   <td>男</td> 
   <td>sale</td> 
  </tr> 
 </tbody> 
</table>

### 3) 多对多合并 ###

df1 = DataFrame({ 'name':['张三','李四','张三'],'salary':[10000,12000,20000],'age':[22,21,25]})
    
    df2 = DataFrame({ 'sex':['男','男','女'],'name':['张三','张三','凡凡'],'group':['sale','search','service']})
    display(df1,df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>李四</td> 
   <td>12000</td> 
   <td>21</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>sex</th> 
   <th>name</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>男</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>男</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>女</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

pd.merge(df1,df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age</th> 
   <th>sex</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>男</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>男</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>男</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>男</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

### 4) key的规范化 ###

*  使用on=显式指定哪一列为key,当有多个key相同时使用

df1 = DataFrame({ 'name':['张三','李四','张三'],'salary':[10000,12000,20000],'age':[22,21,25]})
    
    df2 = DataFrame({ 'age':[21,18,29],'name':['张三','张三','凡凡'],'group':['sale','search','service']})
    display(df1,df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>age</th> 
   <th>name</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

# 当有多个列数据相同的时候, 需要使用on来指定具体用哪一列合并
    pd.merge(df1, df2, on='name')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age_x</th> 
   <th>age_y</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>21</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>18</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>21</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>18</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

*  使用left\_on和right\_on指定左右两边的列作为key，当左右两边的key都不相等时使用

df1 = DataFrame({ 'name':['张三','李四','张三'],'salary':[10000,12000,20000],'age':[22,21,25]})
    
    df2 = DataFrame({ '年龄':[21,18,29],'名字':['张三','张三','凡凡'],'group':['sale','search','service']})
    display(df1,df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>年龄</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

pd.merge(df1, df2, left_on='name', right_on='名字')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age</th> 
   <th>年龄</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

当左边的列和右边的index相同的时候,使用right\_index=True

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>年龄</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>22</th> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>21</th> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>25</th> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

pd.merge(df1, df2, left_on='age', right_index=True)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age</th> 
   <th>年龄</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>李四</td> 
   <td>12000</td> 
   <td>21</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

pd.merge(df2, df1, left_index=True, right_on='age')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>年龄</th> 
   <th>名字</th> 
   <th>group</th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
   <td>李四</td> 
   <td>12000</td> 
   <td>21</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
  </tr> 
 </tbody> 
</table>

============================================

练习16：

1.  假设有两份成绩单，除了ddd是张三李四王老五之外，还有ddd4是张三和赵小六的成绩单，如何合并？
2.  如果ddd4中张三的名字被打错了，成为了张十三，怎么办？
3.  自行练习多对一，多对多的情况
4.  自学left\_index,right\_index

============================================

### 5) 内合并与外合并 ###

*  内合并：只保留两者都有的key（默认模式）

df1 = DataFrame({ 'name':['张三','李四','张三'],'salary':[10000,12000,20000],'age':[22,21,25]})
    
    df2 = DataFrame({ 'age':[21,18,29],'名字':['张三','张三','凡凡'],'group':['sale','search','service']})
    display(df1, df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>age</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

# 默认是内合并
    pd.merge(df1, df2, left_on='name', right_on='名字')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age_x</th> 
   <th>age_y</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

*  外合并 how=‘outer’：补NaN

pd.merge(df1, df2, left_on='name', right_on='名字', how='outer')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age_x</th> 
   <th>age_y</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000.0</td> 
   <td>22.0</td> 
   <td>21.0</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>10000.0</td> 
   <td>22.0</td> 
   <td>18.0</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>20000.0</td> 
   <td>25.0</td> 
   <td>21.0</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000.0</td> 
   <td>25.0</td> 
   <td>18.0</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>李四</td> 
   <td>12000.0</td> 
   <td>21.0</td> 
   <td>NaN</td> 
   <td>NaN</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>5</th> 
   <td>NaN</td> 
   <td>NaN</td> 
   <td>NaN</td> 
   <td>29.0</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

*  左合并、右合并：how=‘left’，how=‘right’，

pd.merge(df1, df2, left_on='name', right_on='名字', how='left')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age_x</th> 
   <th>age_y</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>21.0</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>10000</td> 
   <td>22</td> 
   <td>18.0</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>李四</td> 
   <td>12000</td> 
   <td>21</td> 
   <td>NaN</td> 
   <td>NaN</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>21.0</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>张三</td> 
   <td>20000</td> 
   <td>25</td> 
   <td>18.0</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

# 右合并
    pd.merge(df1, df2, left_on='name', right_on='名字', how='right')

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>salary</th> 
   <th>age_x</th> 
   <th>age_y</th> 
   <th>名字</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>10000.0</td> 
   <td>22.0</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>20000.0</td> 
   <td>25.0</td> 
   <td>21</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>10000.0</td> 
   <td>22.0</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>20000.0</td> 
   <td>25.0</td> 
   <td>18</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>NaN</td> 
   <td>NaN</td> 
   <td>NaN</td> 
   <td>29</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

============================================

练习17：

1.  考虑应用情景，使用多种方式合并ddd与ddd4

============================================

### 6) 列冲突的解决 ###

当列冲突时，即有多个列名称相同时，需要使用on=来指定哪一个列作为key，配合suffixes指定冲突列名

#期中
    df1 = DataFrame({ 'name':['张三','李四','张三'],'degree':[120,118,149],'age':[22,21,25]})
    
    #期末考试
    df2 = DataFrame({ 'degree':[99,97,129],'name':['张三','张三','凡凡'],'group':['sale','search','service']})
    display(df1, df2)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>degree</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>120</td> 
   <td>22</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>李四</td> 
   <td>118</td> 
   <td>21</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>149</td> 
   <td>25</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>degree</th> 
   <th>name</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>99</td> 
   <td>张三</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>97</td> 
   <td>张三</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>129</td> 
   <td>凡凡</td> 
   <td>service</td> 
  </tr> 
 </tbody> 
</table>

# suffix 后缀, prefix 前缀
    pd.merge(df1, df2, on='name', suffixes=['_df1', '_df2'])

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>degree_df1</th> 
   <th>age</th> 
   <th>degree_df2</th> 
   <th>group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>张三</td> 
   <td>120</td> 
   <td>22</td> 
   <td>99</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>张三</td> 
   <td>120</td> 
   <td>22</td> 
   <td>97</td> 
   <td>search</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>张三</td> 
   <td>149</td> 
   <td>25</td> 
   <td>99</td> 
   <td>sale</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>张三</td> 
   <td>149</td> 
   <td>25</td> 
   <td>97</td> 
   <td>search</td> 
  </tr> 
 </tbody> 
</table>

可以使用suffixes=自己指定后缀

============================================

练习18：

假设有两个同学都叫李四，ddd5、ddd6都是张三和李四的成绩表，如何合并？

============================================

pd.merge(how<指定合并方式,内外左右>, 
             on<指定要合并的列>, 
             left_on<单独指定左边表要合并的列.>, 
             right_on<指定右边边要合并的列>, 
             left_index/right_index<指定使用index来进行合并>, 
             suffixes<指定相同列名的后缀>)

假设有两份成绩单，除了ddd是张三李四王老五之外，还有ddd4是张三和赵小六的成绩单，如何合并？
    如果ddd4中张三的名字被打错了，成为了张十三，怎么办？

# 用名字作为 列名
    index = ['语文', '数学', '英语']
    columns = ['张三', '李四', '王老五']
    data = np.random.randint(0,150, size=(3,3))
    ddd = DataFrame(data=data, index=index, columns=columns)
    ddd

index = ['语文', '数学', '英语']
    columns = ['张三', '赵小六']
    data = np.random.randint(0,150, size=(3,2))
    ddd4 = DataFrame(data=data, index=index, columns=columns)
    ddd4

ddd4['张三'] = ddd['张三']
    ddd4

# 使用右边表的索引.
    result = pd.merge(ddd, ddd4, on='张三', left_index=True)
    result

ddd4.rename({ '张三': '张十三'}, axis=1, inplace=True)

ddd4

result = pd.merge(ddd, ddd4, left_on='张三', right_on='张十三', left_index=True)
    result.drop(columns='张十三', inplace=True)
    result

## 作业 ##

## 3. 案例分析：美国各州人口数据分析 ##

作业知识补充

# unique() 去重函数
    s = Series(['Tom','Lucy','Tom','dancer','Lucy'])

s.unique()

array(['Tom', 'Lucy', 'dancer'], dtype=object)

n = DataFrame({ 'name':['Tom','Lucy','Tom','dancer','Lucy'],'age':[12,13,12,11,15]})
    n

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>name</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>Tom</td> 
   <td>12</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Lucy</td> 
   <td>13</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Tom</td> 
   <td>12</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>dancer</td> 
   <td>11</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>Lucy</td> 
   <td>15</td> 
  </tr> 
 </tbody> 
</table>

# 查询 名字是lucy,并且年龄大于13岁的行
    n.query('name=="Lucy" & age>13')

首先导入文件，并查看数据样本

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

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>abbreviation</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>Alabama</td> 
   <td>AL</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Alaska</td> 
   <td>AK</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Arizona</td> 
   <td>AZ</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>Arkansas</td> 
   <td>AR</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>California</td> 
   <td>CA</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>1117489.0</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>AL</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>4817528.0</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>1130966.0</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>AL</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>4785570.0</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>1125763.0</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>area (sq. mi)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>Alabama</td> 
   <td>52423</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Alaska</td> 
   <td>656425</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Arizona</td> 
   <td>114006</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>Arkansas</td> 
   <td>53182</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>California</td> 
   <td>163707</td> 
  </tr> 
 </tbody> 
</table>

合并pop与abbrevs两个DataFrame，分别依据state/region列和abbreviation列来合并。

为了保留所有信息，使用外合并。

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

去除abbreviation的那一列（axis=1）

abb_pop.drop(columns='abbreviation', inplace=True)

查看存在缺失数据的列。

使用.isnull().any()，只有某一列存在一个缺失数据，就会显示True。

abb_pop.isnull().any()

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

查看缺失数据

abb_pop

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>1117489.0</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>4817528.0</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>1130966.0</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>4785570.0</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>1125763.0</td> 
  </tr> 
  <tr> 
   <th>5</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>4801627.0</td> 
  </tr> 
  <tr> 
   <th>6</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>4757938.0</td> 
  </tr> 
  <tr> 
   <th>7</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>1134192.0</td> 
  </tr> 
  <tr> 
   <th>8</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>1111481.0</td> 
  </tr> 
  <tr> 
   <th>9</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>4833722.0</td> 
  </tr> 
  <tr> 
   <th>10</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>4672840.0</td> 
  </tr> 
  <tr> 
   <th>11</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>1132296.0</td> 
  </tr> 
  <tr> 
   <th>12</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>4718206.0</td> 
  </tr> 
  <tr> 
   <th>13</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>1134927.0</td> 
  </tr> 
  <tr> 
   <th>14</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>4569805.0</td> 
  </tr> 
  <tr> 
   <th>15</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>1117229.0</td> 
  </tr> 
  <tr> 
   <th>16</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>4628981.0</td> 
  </tr> 
  <tr> 
   <th>17</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>1126798.0</td> 
  </tr> 
  <tr> 
   <th>18</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>4530729.0</td> 
  </tr> 
  <tr> 
   <th>19</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>1113662.0</td> 
  </tr> 
  <tr> 
   <th>20</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>4503491.0</td> 
  </tr> 
  <tr> 
   <th>21</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>1113083.0</td> 
  </tr> 
  <tr> 
   <th>22</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>4467634.0</td> 
  </tr> 
  <tr> 
   <th>23</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>1120409.0</td> 
  </tr> 
  <tr> 
   <th>24</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>4480089.0</td> 
  </tr> 
  <tr> 
   <th>25</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>1116590.0</td> 
  </tr> 
  <tr> 
   <th>26</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>1121287.0</td> 
  </tr> 
  <tr> 
   <th>27</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>4430141.0</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>4452173.0</td> 
  </tr> 
  <tr> 
   <th>29</th> 
   <td>Alabama</td> 
   <td>AL</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>1122273.0</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>2514</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>71946051.0</td> 
  </tr> 
  <tr> 
   <th>2515</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>282162411.0</td> 
  </tr> 
  <tr> 
   <th>2516</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>72376189.0</td> 
  </tr> 
  <tr> 
   <th>2517</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>279040181.0</td> 
  </tr> 
  <tr> 
   <th>2518</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>284968955.0</td> 
  </tr> 
  <tr> 
   <th>2519</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>72671175.0</td> 
  </tr> 
  <tr> 
   <th>2520</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>287625193.0</td> 
  </tr> 
  <tr> 
   <th>2521</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>72936457.0</td> 
  </tr> 
  <tr> 
   <th>2522</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>290107933.0</td> 
  </tr> 
  <tr> 
   <th>2523</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>73100758.0</td> 
  </tr> 
  <tr> 
   <th>2524</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>292805298.0</td> 
  </tr> 
  <tr> 
   <th>2525</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>73297735.0</td> 
  </tr> 
  <tr> 
   <th>2526</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>295516599.0</td> 
  </tr> 
  <tr> 
   <th>2527</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>73523669.0</td> 
  </tr> 
  <tr> 
   <th>2528</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>298379912.0</td> 
  </tr> 
  <tr> 
   <th>2529</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>73757714.0</td> 
  </tr> 
  <tr> 
   <th>2530</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>301231207.0</td> 
  </tr> 
  <tr> 
   <th>2531</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>74019405.0</td> 
  </tr> 
  <tr> 
   <th>2532</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>304093966.0</td> 
  </tr> 
  <tr> 
   <th>2533</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>74104602.0</td> 
  </tr> 
  <tr> 
   <th>2534</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>73585872.0</td> 
  </tr> 
  <tr> 
   <th>2535</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>316128839.0</td> 
  </tr> 
  <tr> 
   <th>2536</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>306771529.0</td> 
  </tr> 
  <tr> 
   <th>2537</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>74134167.0</td> 
  </tr> 
  <tr> 
   <th>2538</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>74119556.0</td> 
  </tr> 
  <tr> 
   <th>2539</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>309326295.0</td> 
  </tr> 
  <tr> 
   <th>2540</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>73902222.0</td> 
  </tr> 
  <tr> 
   <th>2541</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>311582564.0</td> 
  </tr> 
  <tr> 
   <th>2542</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>73708179.0</td> 
  </tr> 
  <tr> 
   <th>2543</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>313873685.0</td> 
  </tr> 
 </tbody> 
</table>

2544 rows × 5 columns

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

0       False
    1       False
    2       False
    3       False
    4       False
            ...  
    2539     True
    2540     True
    2541     True
    2542     True
    2543     True
    Length: 2544, dtype: bool

abb_pop[abb_pop.isnull().any(axis=1)]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2448</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2449</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2450</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2451</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2452</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2453</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2454</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2455</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2456</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2457</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2458</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2459</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2460</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2461</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2462</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2463</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2464</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2465</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2466</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2467</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2468</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>3810605.0</td> 
  </tr> 
  <tr> 
   <th>2469</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>1089063.0</td> 
  </tr> 
  <tr> 
   <th>2470</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>3818774.0</td> 
  </tr> 
  <tr> 
   <th>2471</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>1077566.0</td> 
  </tr> 
  <tr> 
   <th>2472</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>3823701.0</td> 
  </tr> 
  <tr> 
   <th>2473</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>1065051.0</td> 
  </tr> 
  <tr> 
   <th>2474</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>3826878.0</td> 
  </tr> 
  <tr> 
   <th>2475</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>1035919.0</td> 
  </tr> 
  <tr> 
   <th>2476</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>3826095.0</td> 
  </tr> 
  <tr> 
   <th>2477</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>1050615.0</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>2514</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>71946051.0</td> 
  </tr> 
  <tr> 
   <th>2515</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>282162411.0</td> 
  </tr> 
  <tr> 
   <th>2516</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>72376189.0</td> 
  </tr> 
  <tr> 
   <th>2517</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>279040181.0</td> 
  </tr> 
  <tr> 
   <th>2518</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>284968955.0</td> 
  </tr> 
  <tr> 
   <th>2519</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>72671175.0</td> 
  </tr> 
  <tr> 
   <th>2520</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>287625193.0</td> 
  </tr> 
  <tr> 
   <th>2521</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>72936457.0</td> 
  </tr> 
  <tr> 
   <th>2522</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>290107933.0</td> 
  </tr> 
  <tr> 
   <th>2523</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>73100758.0</td> 
  </tr> 
  <tr> 
   <th>2524</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>292805298.0</td> 
  </tr> 
  <tr> 
   <th>2525</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>73297735.0</td> 
  </tr> 
  <tr> 
   <th>2526</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>295516599.0</td> 
  </tr> 
  <tr> 
   <th>2527</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>73523669.0</td> 
  </tr> 
  <tr> 
   <th>2528</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>298379912.0</td> 
  </tr> 
  <tr> 
   <th>2529</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>73757714.0</td> 
  </tr> 
  <tr> 
   <th>2530</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>301231207.0</td> 
  </tr> 
  <tr> 
   <th>2531</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>74019405.0</td> 
  </tr> 
  <tr> 
   <th>2532</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>304093966.0</td> 
  </tr> 
  <tr> 
   <th>2533</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>74104602.0</td> 
  </tr> 
  <tr> 
   <th>2534</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>73585872.0</td> 
  </tr> 
  <tr> 
   <th>2535</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>316128839.0</td> 
  </tr> 
  <tr> 
   <th>2536</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>306771529.0</td> 
  </tr> 
  <tr> 
   <th>2537</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>74134167.0</td> 
  </tr> 
  <tr> 
   <th>2538</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>74119556.0</td> 
  </tr> 
  <tr> 
   <th>2539</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>309326295.0</td> 
  </tr> 
  <tr> 
   <th>2540</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>73902222.0</td> 
  </tr> 
  <tr> 
   <th>2541</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>311582564.0</td> 
  </tr> 
  <tr> 
   <th>2542</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>73708179.0</td> 
  </tr> 
  <tr> 
   <th>2543</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>313873685.0</td> 
  </tr> 
 </tbody> 
</table>

96 rows × 5 columns

根据数据是否缺失情况显示数据，如果缺失为True，那么显示

找到有哪些state/region使得state的值为NaN，使用unique()查看非重复值

abb_pop[abb_pop.isnull().any(axis=1)]['state/region'].unique()

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

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

记住这样清除缺失数据NaN的方法！

cond = abb_pop[abb_pop.isnull().any(axis=1)]['state/region'] == 'PR'

abb_pop[abb_pop.isnull().any(axis=1)]['state'][cond]= 'Puerto Rico'

d:\1903\.venv\lib\site-packages\pandas\core\generic.py:9116: SettingWithCopyWarning: 
    A value is trying to be set on a copy of a slice from a DataFrame
    
    See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      self._update_inplace(new_data)
    d:\1903\.venv\lib\site-packages\IPython\core\interactiveshell.py:3326: SettingWithCopyWarning: 
    A value is trying to be set on a copy of a slice from a DataFrame
    
    See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      exec(code_obj, self.user_global_ns, self.user_ns)

abb_pop[abb_pop.isnull().any(axis=1)][cond]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2448</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2449</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2450</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2451</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2452</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2453</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2454</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2455</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2456</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2457</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2458</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2459</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2460</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2461</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2462</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2463</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2464</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2465</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2466</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2467</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2468</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>3810605.0</td> 
  </tr> 
  <tr> 
   <th>2469</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>1089063.0</td> 
  </tr> 
  <tr> 
   <th>2470</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>3818774.0</td> 
  </tr> 
  <tr> 
   <th>2471</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>1077566.0</td> 
  </tr> 
  <tr> 
   <th>2472</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>3823701.0</td> 
  </tr> 
  <tr> 
   <th>2473</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>1065051.0</td> 
  </tr> 
  <tr> 
   <th>2474</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>3826878.0</td> 
  </tr> 
  <tr> 
   <th>2475</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>1035919.0</td> 
  </tr> 
  <tr> 
   <th>2476</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>3826095.0</td> 
  </tr> 
  <tr> 
   <th>2477</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>1050615.0</td> 
  </tr> 
  <tr> 
   <th>2478</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>3821362.0</td> 
  </tr> 
  <tr> 
   <th>2479</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>1019447.0</td> 
  </tr> 
  <tr> 
   <th>2480</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>3805214.0</td> 
  </tr> 
  <tr> 
   <th>2481</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>998543.0</td> 
  </tr> 
  <tr> 
   <th>2482</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>3782995.0</td> 
  </tr> 
  <tr> 
   <th>2483</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>973613.0</td> 
  </tr> 
  <tr> 
   <th>2484</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>3760866.0</td> 
  </tr> 
  <tr> 
   <th>2485</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>945705.0</td> 
  </tr> 
  <tr> 
   <th>2486</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>814068.0</td> 
  </tr> 
  <tr> 
   <th>2487</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>3615086.0</td> 
  </tr> 
  <tr> 
   <th>2488</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>3740410.0</td> 
  </tr> 
  <tr> 
   <th>2489</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>920794.0</td> 
  </tr> 
  <tr> 
   <th>2490</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>3721208.0</td> 
  </tr> 
  <tr> 
   <th>2491</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>896945.0</td> 
  </tr> 
  <tr> 
   <th>2492</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>869327.0</td> 
  </tr> 
  <tr> 
   <th>2493</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>3686580.0</td> 
  </tr> 
  <tr> 
   <th>2494</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>841740.0</td> 
  </tr> 
  <tr> 
   <th>2495</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>3651545.0</td> 
  </tr> 
 </tbody> 
</table>

abb_pop[abb_pop.isnull().any(axis=1)].loc[cond, 'state'] = 'Puerto Rico'

d:\1903\.venv\lib\site-packages\pandas\core\indexing.py:480: SettingWithCopyWarning: 
    A value is trying to be set on a copy of a slice from a DataFrame.
    Try using .loc[row_indexer,col_indexer] = value instead
    
    See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      self.obj[item] = s

abb_pop[abb_pop.isnull().any(axis=1)][cond]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2448</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2449</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2450</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2451</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2452</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2453</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2454</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2455</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2456</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2457</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2458</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2459</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2460</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2461</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2462</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2463</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2464</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2465</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2466</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2467</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2468</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>3810605.0</td> 
  </tr> 
  <tr> 
   <th>2469</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>1089063.0</td> 
  </tr> 
  <tr> 
   <th>2470</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>3818774.0</td> 
  </tr> 
  <tr> 
   <th>2471</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>1077566.0</td> 
  </tr> 
  <tr> 
   <th>2472</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>3823701.0</td> 
  </tr> 
  <tr> 
   <th>2473</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>1065051.0</td> 
  </tr> 
  <tr> 
   <th>2474</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>3826878.0</td> 
  </tr> 
  <tr> 
   <th>2475</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>1035919.0</td> 
  </tr> 
  <tr> 
   <th>2476</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>3826095.0</td> 
  </tr> 
  <tr> 
   <th>2477</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>1050615.0</td> 
  </tr> 
  <tr> 
   <th>2478</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>3821362.0</td> 
  </tr> 
  <tr> 
   <th>2479</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>1019447.0</td> 
  </tr> 
  <tr> 
   <th>2480</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>3805214.0</td> 
  </tr> 
  <tr> 
   <th>2481</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>998543.0</td> 
  </tr> 
  <tr> 
   <th>2482</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>3782995.0</td> 
  </tr> 
  <tr> 
   <th>2483</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>973613.0</td> 
  </tr> 
  <tr> 
   <th>2484</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>3760866.0</td> 
  </tr> 
  <tr> 
   <th>2485</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>945705.0</td> 
  </tr> 
  <tr> 
   <th>2486</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>814068.0</td> 
  </tr> 
  <tr> 
   <th>2487</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>3615086.0</td> 
  </tr> 
  <tr> 
   <th>2488</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>3740410.0</td> 
  </tr> 
  <tr> 
   <th>2489</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>920794.0</td> 
  </tr> 
  <tr> 
   <th>2490</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>3721208.0</td> 
  </tr> 
  <tr> 
   <th>2491</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>896945.0</td> 
  </tr> 
  <tr> 
   <th>2492</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>869327.0</td> 
  </tr> 
  <tr> 
   <th>2493</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>3686580.0</td> 
  </tr> 
  <tr> 
   <th>2494</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>841740.0</td> 
  </tr> 
  <tr> 
   <th>2495</th> 
   <td>NaN</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>3651545.0</td> 
  </tr> 
 </tbody> 
</table>

temp = abb_pop[abb_pop.isnull().any(axis=1)].copy()

temp.loc[cond, 'state'] = 'Puerto Rico'

temp

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2448</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2449</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2450</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2451</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2452</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2453</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2454</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2455</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2456</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2457</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2458</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2459</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2460</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2461</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2462</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2463</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2464</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2465</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2466</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2467</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2468</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>3810605.0</td> 
  </tr> 
  <tr> 
   <th>2469</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>1089063.0</td> 
  </tr> 
  <tr> 
   <th>2470</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>3818774.0</td> 
  </tr> 
  <tr> 
   <th>2471</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>1077566.0</td> 
  </tr> 
  <tr> 
   <th>2472</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>3823701.0</td> 
  </tr> 
  <tr> 
   <th>2473</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>1065051.0</td> 
  </tr> 
  <tr> 
   <th>2474</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>3826878.0</td> 
  </tr> 
  <tr> 
   <th>2475</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>1035919.0</td> 
  </tr> 
  <tr> 
   <th>2476</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>3826095.0</td> 
  </tr> 
  <tr> 
   <th>2477</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>1050615.0</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>2514</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>71946051.0</td> 
  </tr> 
  <tr> 
   <th>2515</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>282162411.0</td> 
  </tr> 
  <tr> 
   <th>2516</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>72376189.0</td> 
  </tr> 
  <tr> 
   <th>2517</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>279040181.0</td> 
  </tr> 
  <tr> 
   <th>2518</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>284968955.0</td> 
  </tr> 
  <tr> 
   <th>2519</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>72671175.0</td> 
  </tr> 
  <tr> 
   <th>2520</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>287625193.0</td> 
  </tr> 
  <tr> 
   <th>2521</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>72936457.0</td> 
  </tr> 
  <tr> 
   <th>2522</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>290107933.0</td> 
  </tr> 
  <tr> 
   <th>2523</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>73100758.0</td> 
  </tr> 
  <tr> 
   <th>2524</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>292805298.0</td> 
  </tr> 
  <tr> 
   <th>2525</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>73297735.0</td> 
  </tr> 
  <tr> 
   <th>2526</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>295516599.0</td> 
  </tr> 
  <tr> 
   <th>2527</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>73523669.0</td> 
  </tr> 
  <tr> 
   <th>2528</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>298379912.0</td> 
  </tr> 
  <tr> 
   <th>2529</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>73757714.0</td> 
  </tr> 
  <tr> 
   <th>2530</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>301231207.0</td> 
  </tr> 
  <tr> 
   <th>2531</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>74019405.0</td> 
  </tr> 
  <tr> 
   <th>2532</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>304093966.0</td> 
  </tr> 
  <tr> 
   <th>2533</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>74104602.0</td> 
  </tr> 
  <tr> 
   <th>2534</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>73585872.0</td> 
  </tr> 
  <tr> 
   <th>2535</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>316128839.0</td> 
  </tr> 
  <tr> 
   <th>2536</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>306771529.0</td> 
  </tr> 
  <tr> 
   <th>2537</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>74134167.0</td> 
  </tr> 
  <tr> 
   <th>2538</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>74119556.0</td> 
  </tr> 
  <tr> 
   <th>2539</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>309326295.0</td> 
  </tr> 
  <tr> 
   <th>2540</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>73902222.0</td> 
  </tr> 
  <tr> 
   <th>2541</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>311582564.0</td> 
  </tr> 
  <tr> 
   <th>2542</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>73708179.0</td> 
  </tr> 
  <tr> 
   <th>2543</th> 
   <td>NaN</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>313873685.0</td> 
  </tr> 
 </tbody> 
</table>

96 rows × 5 columns

cond = abb_pop[abb_pop.isnull().any(axis=1)]['state/region'] == 'USA'

temp.loc[cond, 'state'] = 'United State of American'

temp

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2448</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2449</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2450</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2451</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2452</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2453</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2454</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2455</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2456</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2457</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2458</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2459</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1995</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2460</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2461</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1996</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2462</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2463</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1998</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2464</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2465</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1997</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2466</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2467</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2468</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>3810605.0</td> 
  </tr> 
  <tr> 
   <th>2469</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>1089063.0</td> 
  </tr> 
  <tr> 
   <th>2470</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>3818774.0</td> 
  </tr> 
  <tr> 
   <th>2471</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>1077566.0</td> 
  </tr> 
  <tr> 
   <th>2472</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>3823701.0</td> 
  </tr> 
  <tr> 
   <th>2473</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>1065051.0</td> 
  </tr> 
  <tr> 
   <th>2474</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>3826878.0</td> 
  </tr> 
  <tr> 
   <th>2475</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>1035919.0</td> 
  </tr> 
  <tr> 
   <th>2476</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>3826095.0</td> 
  </tr> 
  <tr> 
   <th>2477</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>1050615.0</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>2514</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>71946051.0</td> 
  </tr> 
  <tr> 
   <th>2515</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>282162411.0</td> 
  </tr> 
  <tr> 
   <th>2516</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>72376189.0</td> 
  </tr> 
  <tr> 
   <th>2517</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>279040181.0</td> 
  </tr> 
  <tr> 
   <th>2518</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>284968955.0</td> 
  </tr> 
  <tr> 
   <th>2519</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>72671175.0</td> 
  </tr> 
  <tr> 
   <th>2520</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>287625193.0</td> 
  </tr> 
  <tr> 
   <th>2521</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>72936457.0</td> 
  </tr> 
  <tr> 
   <th>2522</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>290107933.0</td> 
  </tr> 
  <tr> 
   <th>2523</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>73100758.0</td> 
  </tr> 
  <tr> 
   <th>2524</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>292805298.0</td> 
  </tr> 
  <tr> 
   <th>2525</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>73297735.0</td> 
  </tr> 
  <tr> 
   <th>2526</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>295516599.0</td> 
  </tr> 
  <tr> 
   <th>2527</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>73523669.0</td> 
  </tr> 
  <tr> 
   <th>2528</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>298379912.0</td> 
  </tr> 
  <tr> 
   <th>2529</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>73757714.0</td> 
  </tr> 
  <tr> 
   <th>2530</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>301231207.0</td> 
  </tr> 
  <tr> 
   <th>2531</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>74019405.0</td> 
  </tr> 
  <tr> 
   <th>2532</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>304093966.0</td> 
  </tr> 
  <tr> 
   <th>2533</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>74104602.0</td> 
  </tr> 
  <tr> 
   <th>2534</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>73585872.0</td> 
  </tr> 
  <tr> 
   <th>2535</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>316128839.0</td> 
  </tr> 
  <tr> 
   <th>2536</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>306771529.0</td> 
  </tr> 
  <tr> 
   <th>2537</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>74134167.0</td> 
  </tr> 
  <tr> 
   <th>2538</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>74119556.0</td> 
  </tr> 
  <tr> 
   <th>2539</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>309326295.0</td> 
  </tr> 
  <tr> 
   <th>2540</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>73902222.0</td> 
  </tr> 
  <tr> 
   <th>2541</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>311582564.0</td> 
  </tr> 
  <tr> 
   <th>2542</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>73708179.0</td> 
  </tr> 
  <tr> 
   <th>2543</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>313873685.0</td> 
  </tr> 
 </tbody> 
</table>

96 rows × 5 columns

abb_pop[abb_pop.isnull().any(axis=1)] = temp

abb_pop[abb_pop.isnull().any(axis=1)]

合并各州面积数据areas，使用左合并。

思考一下为什么使用外合并？

abb_pop.head()

areas.head()

abb_pop_areas = abb_pop.merge(areas, how='outer')
    abb_pop_areas.head()

继续寻找存在缺失数据的列

abb_pop_areas.isnull().any()

state            False
    state/region     False
    ages             False
    year             False
    population        True
    area (sq. mi)     True
    dtype: bool

我们会发现area(sq.mi)这一列有缺失数据，为了找出是哪一行，我们需要找出是哪个state没有数据

abb_pop_areas[abb_pop_areas.isnull().any(axis=1)]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state</th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
   <th>area (sq. mi)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2448</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2449</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2450</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2451</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2452</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2453</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2454</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2455</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2456</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2457</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2458</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1995</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2459</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1995</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2460</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1996</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2461</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1996</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2462</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1998</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2463</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1998</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2464</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1997</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2465</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1997</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2466</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2467</th> 
   <td>Puerto Rico</td> 
   <td>PR</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>NaN</td> 
   <td>3515.0</td> 
  </tr> 
  <tr> 
   <th>2496</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1990</td> 
   <td>64218512.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2497</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1990</td> 
   <td>249622814.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2498</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1991</td> 
   <td>252980942.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2499</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1991</td> 
   <td>65313018.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2500</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1992</td> 
   <td>66509177.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2501</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1992</td> 
   <td>256514231.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2502</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1993</td> 
   <td>259918595.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2503</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1993</td> 
   <td>67594938.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2504</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1994</td> 
   <td>68640936.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2505</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1994</td> 
   <td>263125826.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>2514</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>1999</td> 
   <td>71946051.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2515</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2000</td> 
   <td>282162411.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2516</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2000</td> 
   <td>72376189.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2517</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>1999</td> 
   <td>279040181.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2518</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2001</td> 
   <td>284968955.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2519</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2001</td> 
   <td>72671175.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2520</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2002</td> 
   <td>287625193.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2521</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2002</td> 
   <td>72936457.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2522</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2003</td> 
   <td>290107933.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2523</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2003</td> 
   <td>73100758.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2524</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2004</td> 
   <td>292805298.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2525</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2004</td> 
   <td>73297735.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2526</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2005</td> 
   <td>295516599.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2527</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2005</td> 
   <td>73523669.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2528</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2006</td> 
   <td>298379912.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2529</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2006</td> 
   <td>73757714.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2530</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2007</td> 
   <td>301231207.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2531</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2007</td> 
   <td>74019405.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2532</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2008</td> 
   <td>304093966.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2533</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2008</td> 
   <td>74104602.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2534</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2013</td> 
   <td>73585872.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2535</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2013</td> 
   <td>316128839.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2536</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2009</td> 
   <td>306771529.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2537</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2009</td> 
   <td>74134167.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2538</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2010</td> 
   <td>74119556.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2539</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>309326295.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2540</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2011</td> 
   <td>73902222.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2541</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2011</td> 
   <td>311582564.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2542</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>under18</td> 
   <td>2012</td> 
   <td>73708179.0</td> 
   <td>NaN</td> 
  </tr> 
  <tr> 
   <th>2543</th> 
   <td>United State of American</td> 
   <td>USA</td> 
   <td>total</td> 
   <td>2012</td> 
   <td>313873685.0</td> 
   <td>NaN</td> 
  </tr> 
 </tbody> 
</table>

68 rows × 6 columns

去除含有缺失数据的行

abb_pop_areas.dropna(axis=0, how='any', inplace=True)

查看数据是否缺失

abb_pop_areas.isnull().any()

state            False
    state/region     False
    ages             False
    year             False
    population       False
    area (sq. mi)    False
    dtype: bool

abb_pop_areas.head()

abb_pop_areas.dtypes

state             object
    state/region      object
    ages              object
    year               int64
    population       float64
    area (sq. mi)    float64
    dtype: object

找出2010年的全民人口数据,df.query(查询语句)

result = abb_pop_areas.query('ages=="total" & year==2010')

对查询结果进行处理，以state列作为新的行索引:set\_index

result.set_index(keys=['state'], inplace=True)
    result.head()

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>state/region</th> 
   <th>ages</th> 
   <th>year</th> 
   <th>population</th> 
   <th>area (sq. mi)</th> 
  </tr> 
  <tr> 
   <th>state</th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>Alabama</th> 
   <td>AL</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>4785570.0</td> 
   <td>52423.0</td> 
  </tr> 
  <tr> 
   <th>Alaska</th> 
   <td>AK</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>713868.0</td> 
   <td>656425.0</td> 
  </tr> 
  <tr> 
   <th>Arizona</th> 
   <td>AZ</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>6408790.0</td> 
   <td>114006.0</td> 
  </tr> 
  <tr> 
   <th>Arkansas</th> 
   <td>AR</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>2922280.0</td> 
   <td>53182.0</td> 
  </tr> 
  <tr> 
   <th>California</th> 
   <td>CA</td> 
   <td>total</td> 
   <td>2010</td> 
   <td>37333601.0</td> 
   <td>163707.0</td> 
  </tr> 
 </tbody> 
</table>

计算人口密度。注意是Series/Series，其结果还是一个Series。

density = result['population'] / result['area (sq. mi)']
    density.head()

state
    Alabama        91.287603
    Alaska          1.087509
    Arizona        56.214497
    Arkansas       54.948667
    California    228.051342
    dtype: float64

排序，并找出人口密度最高的五个州sort\_values()

density.sort_values(inplace=True)

density.tail()

state
    Connecticut              645.600649
    Rhode Island             681.339159
    New Jersey              1009.253268
    Puerto Rico             1058.665149
    District of Columbia    8898.897059
    dtype: float64

找出人口密度最低的五个州

density.head()

state
    Alaska           1.087509
    Wyoming          5.768079
    Montana          6.736171
    North Dakota     9.537565
    South Dakota    10.583512
    dtype: float64

要点总结：

*  统一用loc()索引
 *  善于使用.isnull().any()找到存在NaN的列
 *  善于使用.unique()确定该列中哪些key是我们需要的
 *  一般使用外合并、左合并，目的只有一个：宁愿该列是NaN也不要丢弃其他列的信息

## 回顾：Series/DataFrame运算与ndarray运算的区别 ##

*  Series与DataFrame没有广播，如果对应index没有值，则记为NaN；或者使用add的fill\_value来补缺失值
 *  ndarray有广播，通过重复已有值来计算

[pandas]: https://blog.csdn.net/zly717216/article/details/120751592
[pandas 1]: https://blog.csdn.net/zly717216/article/details/120750923