python merge返回ValueError:无法将DataFrame与类型为合并的实例合并

Question

我正在尝试读入两个文件，一个文本文件和一个csv文件，解析它们，然后写出一个包含两个工作表的excel文件。

我合并这两个文件会返回一个我不理解的ValueError。

这是我的代码：

jobs = pd.read_csv(r'test01.txt', delim_whitespace=True, skiprows=[0, 2])
cmdb = pd.read_csv(r'cmdb.csv', encoding='ansi')

# new data frame with split value columns 
new = jobs["Job_Name"].str.split("_", n = 2, expand = True) 

# making seperate App Code column from new data frame 
jobs["appCode"]= new[1] 

# Get unique app codes and sort them
apps = new[1].unique()
apps.sort()

merged = pd.merge(apps,cmdb, left_on="appCode", right_on="application", how='inner')

这是我收到的错误：

  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\reshape\merge.py", line 538, in __init__
    'type {left}'.format(left=type(left)))

ValueError: can not merge DataFrame with instance of type <class 'numpy.ndarray'>

我不清楚左边的值是如何变成一个“numpy.ndarray”类的，我也不清楚我能做些什么来纠正这个问题。

感谢这可能是非常基础的，但我是python的新手，似乎无法理解这一点。

我正在阅读的作业文本文件看起来像这样-

Job_Name                                                         Last Start           Last End             
________________________________________________________________ ____________________ ____________________ 
PRD_ABC_CAT_QUARTERLYJOBS                                        -----                -----                
PRD_UNC_DOG_QUARTERLYZZZZZZZZZZZREPORT                           -----                -----                
PRD_ZYX_DOG_ABCDEF_WHATEVER_BATCH                               09/15/2016 10:00:00  09/15/2016 10:00:00  
PRD_ZYX_DOG_ABCDEF_WHATEVER_TRANSMIT_TW                         09/13/2016 10:00:11  09/13/2016 10:00:12  
PRD_ZYX_DOG_ABCDEF_WHATEVER_ARCHIVE                             09/13/2016 10:00:13  09/13/2016 10:00:13  
PRD_ZYX_DD_ABCDEF_AFFEE_BATCH                                   12/07/2016 20:00:02  12/07/2016 20:00:02  
PRD_ZYX_DD_ABCDEF_AFFEE_BATCH2                                  -----                12/07/2016 20:06:56  

我正在rading的cmdb csv文件看起来像这样-

application,name,status
ABC,GOOFY,LIVE
DEF,PLUTO,UNKNOWN
RRR,DAISY,HOT
UNC,DONALD,DEAD
ZYX,ALVIN,SLEEPING

Answer 1

apps = new[1].unique()

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.unique.html

pandas返回的unique方法

If the input is an Index, the return is an Index If the input is a Categorical dtype, the return is a Categorical If the input is a Series/ndarray, the return will be an ndarray

所以apps不是一个数据帧。

示例用法：

jobs = pd.DataFrame(
{ 'Job_Name': [ 'PRD_ZYX_DOG_ABCDEF_WHATEVER_BATCH', 'PRD_ZYX_DOG_ABCDEF_WHATEVER_TRANSMIT_TW',
               'PRD_ZYX_DOG_ABCDEF_WHATEVER_ARCHIVE', 'PRD_ZYX_DD_ABCDEF_AFFEE_BATCH'],
  'Last Start': [ 1,2,3,4], 'Last End': [10,20,30,40]})

cmdb = pd.DataFrame({
        'application': ['ABC','DEF','RRR', 'UNC','ZYX'],
        'name': ['GOOFY','PLUTO','DAISY','DONALD','ALVIN'],
        'status': ['LIVE','UNKNOWN','HOT', 'DEAD', 'SLEEPING']})

# Create appCode
jobs["appCode"] = jobs["Job_Name"].str.split("_",  n = 2, expand=True)[1]

# If you want to merge the jobss and cmdb then you can use
merged = pd.merge(jobs,cmdb, left_on="appCode", right_on="application", how='inner')

# Of if you want to filter the contents of cmdb with only the 
# application values from appCode of jobs you can use
filtered = cmdb[cmdb['application'].isin(jobs['appCode'])]

如果

​

​

然后

​

​