Python/Pandas：创建新的数据框，获取错误“不可对齐的布尔系列作为索引器提供”

Question

我想比较两个数据框，并根据另一个数据框中是否存在一个值来返回不同的结果集。

这里是我的示例代码：

pmdf = pd.DataFrame(
     { 
     'Journal' : ['US Drug standards.','Acta veterinariae.','Bulletin of big toe science.','The UK journal of dermatology.','Journal of Hypothetical Journals'], 
     'ISSN': ['0096-0225', '0567-8315','0007-4977','0007-0963','8675-309J'], 
     } 
     ) 

pmdf = pmdf[['Journal'] + pmdf.columns[:-1].tolist()] 

jcrdf = pd.DataFrame(
     { 
     'Full Journal Title': ['Drug standards.','Acta veterinaria.','Bulletin of marine science.','The British journal of dermatology.'], 
     'Abbreviated Title': ['DStan','Avet','Marsci','BritSkin'], 
     'Total Cites': ['223','444','324','166'], 
     'ISSN': ['0096-0225','0567-8315','0007-4977','0007-0963'], 
     'All_ISSNs': ['0096-0225,0096-0225','0567-8315,1820-7448,0567-8315','0007-4977,0007-4977','0007-0963,0007-0963,0366-077X,1365-2133']       
     }) 
jcrdf = jcrdf.set_index('Full Journal Title') 

pmdf_issn = pmdf['ISSN'].values.tolist() 

此行让我从数据框中jcrdf包含ISSN从数据帧PMDF

pmjcrmatch = jcrdf[jcrdf['All_ISSNs'].str.contains('|'.join(pmdf_issn))] 

我想下面的行创建一个新的数据框的行来自pmdf的值不在jcfdf中的值，所以我否定了先前的陈述并选择了第一个数据帧。

pmjcrnomatch = pmdf[~jcrdf['All_ISSNs'].str.contains('|'.join(pmdf_issn))] 

我得到一个错误：“为索引提供Unalignable布尔系列（布尔系列和索引对象的索引不匹配”

我不找了很多关于此特定错误，至少没有什么帮助我走向一个解决方案。

是“str.contains”不排序是和不是在第二数据框项目的最佳方式？

Answer 1

您尝试应用布尔一个数据帧的索引到另一个数据帧如果两个数据帧的长度相匹配，则可以使用y。在你的情况下，你应该使用isin。

# get all rows from jcrdf where `ALL_ISSNs` contains any of the `ISSN` in `pmdf`. 
pmjcrmatch = jcrdf[jcrdf.All_ISSNs.str.contains('|'.join(pmdf.ISSN))] 
# assign all remaining rows from `jcrdf` to a new dataframe. 
pmjcrnomatch = jcrdf[~jcrdf.ISSN.isin(pmjcrmatch.ISSN)] 

编辑 让我们尝试另一种方法：

首先我想创建一个查找对你一系列ISSN然后通过隔离场比赛创造的DIFF：

import pandas as pd 

pmdf = pd.DataFrame(
     { 
     'Journal' : ['US Drug standards.','Acta veterinariae.','Bulletin of big toe science.','The UK journal of dermatology.','Journal of Hypothetical Journals'], 
     'ISSN': ['0096-0225', '0567-8315','0007-4977','0007-0963','8675-309J'], 
     } 
     ) 

pmdf = pmdf[['Journal'] + pmdf.columns[:-1].tolist()] 

jcrdf = pd.DataFrame(
     { 
     'Full Journal Title': ['Drug standards.','Acta veterinaria.','Bulletin of marine science.','The British journal of dermatology.'], 
     'Abbreviated Title': ['DStan','Avet','Marsci','BritSkin'], 
     'Total Cites': ['223','444','324','166'], 
     'ISSN': ['0096-0225','0567-8315','0007-4977','0007-0963'], 
     'All_ISSNs': ['0096-0225,0096-0225','0567-8315,1820-7448,0567-8315','0007-4977,0007-4977','0007-0963,0007-0963,0366-077X,1365-2133'] 
     }) 
jcrdf = jcrdf.set_index('Full Journal Title') 

# create lookup from all issns to avoid expansice string matching 
jcrdf_lookup = pd.DataFrame(jcrdf['All_ISSNs'].str.split(',').tolist(), 
          index=jcrdf.ISSN).stack(level=0).reset_index(level=0) 

# compare extracted ISSNs from ALL_ISSNs with pmdf.ISSN 
matches = jcrdf_lookup[jcrdf_lookup[0].isin(pmdf.ISSN)] 
jcrdfmatch = jcrdf[jcrdf.ISSN.isin(matches.ISSN)] 
jcrdfnomatch = pmdf[~pmdf.ISSN.isin(matches[0])]