如何找出列表中不在Pandas DataFrame中的数据并生成NA行

原始DataFrame #

import pandas as pd

df = pd.DataFrame([
        ['server1', 'NA', 'NA', '2011-03-31'],
        ['server1', '2011-02-22', 'NA', 'NA'],
        ['server1', 'NA', '2011-06-22', 'NA'],
        ['server2', 'NA', 'NA', '2011-12-31'],
        ['server2', 'NA', '2011-02-21', 'NA'],
        ['server3', 'NA', 'NA', '2011-08-29'],
    ], columns=['hostname', 'patch_date1', 'patch_date2', 'patch_date3'])

print(df)

输出：

hostname patch_date1 patch_date2 patch_date3
0  server1          NA          NA  2011-03-31
1  server1  2011-02-22          NA          NA
2  server1          NA  2011-06-22          NA
3  server2          NA          NA  2011-12-31
4  server2          NA  2011-02-21          NA
5  server3          NA          NA  2011-08-29

当前尝试的代码 #

使用isin筛选列表中存在的主机名：

list_to_search = ['server1','server2','server3','server4','server5']
df = df[df.hostname.isin(list_to_search)]

print(df)

输出：

hostname patch_date1 patch_date2 patch_date3
0  server1          NA          NA  2011-03-31
1  server1  2011-02-22          NA          NA
2  server1          NA  2011-06-22          NA
3  server2          NA          NA  2011-12-31
4  server2          NA  2011-02-21          NA
5  server3          NA          NA  2011-08-29

需求 #

获取列表中不在DataFrame内的主机名对应的全NA行，期望结果：

hostname patch_date1 patch_date2 patch_date3
0  server4          NA          NA          NA
1  server5          NA          NA          NA

解决方案 #

步骤1：找出缺失的主机名 #

筛选出list_to_search中不存在于原DataFrame主机名列的元素：

existing_hosts = df['hostname'].unique()
missing_hosts = [host for host in list_to_search if host not in existing_hosts]

步骤2：创建全NA行的DataFrame #

用缺失的主机名构建新DataFrame，保持和原DataFrame相同的列结构，其余列填充NA：

# 直接指定列名，自动填充NaN后替换为'NA'
missing_df = pd.DataFrame({'hostname': missing_hosts}, columns=df.columns).fillna('NA')

print(missing_df)

输出：

hostname patch_date1 patch_date2 patch_date3
0  server4          NA          NA          NA
1  server5          NA          NA          NA

完整代码 #

import pandas as pd

df = pd.DataFrame([
        ['server1', 'NA', 'NA', '2011-03-31'],
        ['server1', '2011-02-22', 'NA', 'NA'],
        ['server1', 'NA', '2011-06-22', 'NA'],
        ['server2', 'NA', 'NA', '2011-12-31'],
        ['server2', 'NA', '2011-02-21', 'NA'],
        ['server3', 'NA', 'NA', '2011-08-29'],
    ], columns=['hostname', 'patch_date1', 'patch_date2', 'patch_date3'])

list_to_search = ['server1','server2','server3','server4','server5']

existing_hosts = df['hostname'].unique()
missing_hosts = [host for host in list_to_search if host not in existing_hosts]

missing_df = pd.DataFrame({'hostname': missing_hosts}, columns=df.columns).fillna('NA')
print(missing_df)

内容的提问来源于stack exchange，提问作者Bandhala Raja Selvam