Pandas：在ByteArray的列上合并

Question

有什么想法，我可以加入两个熊猫阵列在一个通常命名为tearray领域？源代码中的字段（Teradata）是一个实际的ByteArray，从Teradata的角度来看，这不能强制为字符或Teradata之外可用的内容）

Teradata导出可以很好地读入Panda的数组。但是我不能将两个表与一个通常命名的字段（DatabaseId）合并，而这个字段是bytearray。在

（以pd和itertools的形式进口熊猫）

当我尝试一个简单的合并：

merge1 = pd.merge(tvm, dbase, on="DatabaseId")

我得到的错误是：

^{pr2}$

我搜索StackOverflow并找到一个similar problem for joining on a cell containing a collection

dbase['DBID'] = dbase.DatabaseId.apply(lambda r: type(sorted(r.iteritems())))

但我得到了一个错误：

AttributeError: 'bytearray' object has no attribute 'iteritems'

更新

数据示例 通过熊猫收集的数据

dbase = pd.read_sql('select databaseid, databasename from ud812.dbase sample 10', conn)
conn is a connection to a teradata database

Teradata中的数据类型对于所有列都是Varchar，除了：

DatabaseID = bytearray (Byte(4))
TVMID = bytearray (Byte(4))

>>> dbase.dtypes
DatabaseId      object
DatabaseName    object
dtype: object
>>> dbase
         DatabaseId         DatabaseName
0  [2, 0, 243, 185]  PCDW_CRS_BBCONV3_TB
1  [2, 0, 168, 114]            PAMLIF_TB
2  [2, 0, 133, 153]        PADW_PRESN_TB
3   [2, 0, 29, 184]       CEDW_MOBILE_TB
4  [2, 0, 190, 183]  CEDW_MODEL_SCORE_TB
5    [2, 0, 71, 55]            PBBBAM_TB
6  [2, 0, 169, 183]          CEDW_OCC_TB
7  [2, 0, 201, 183]    CCDW_DGTL_DEAL_TB
8    [0, 0, 139, 8]           PRECDSS_TB
9  [2, 0, 142, 203]             CDBDW_TB
>>>
>>>
>>> tvm.dtypes
TVMId         object
DatabaseId    object
TVMName       object
TableKind     object
CreateText    object
dtype: object
>>> tvm
                      TVMId        DatabaseId                        TVMName  \
0    [230, 1, 41, 11, 0, 0]   [2, 0, 67, 183]               JCP_03538_112002
1   [214, 1, 60, 133, 0, 0]   [2, 0, 186, 52]        STL_AUTHNCTD_RULE_EXECN
2    [193, 2, 59, 48, 0, 0]  [2, 0, 225, 150]       uye177_Xsell_EM_OPCL_TB2
3    [0, 2, 235, 154, 0, 0]  [2, 0, 244, 181]  PL_CALCD_INVSTR_MTHLY_HIST_ST
4   [255, 1, 131, 76, 0, 0]   [2, 0, 110, 63]            IMH867_AVA0803_SNAP
5  [125, 1, 217, 138, 0, 0]  [2, 0, 237, 153]            FD_ACCT_STMT_ADR_ST
6   [224, 0, 80, 233, 0, 0]  [2, 0, 243, 127]             EXP_SRCH_RSLT_DESC
7    [208, 1, 72, 15, 0, 0]     [2, 0, 8, 57]      SGI_PAY_DENIED_SEP_112012
8    [246, 0, 27, 61, 0, 0]  [2, 0, 143, 130]                      CR_INDIVD
9  [186, 1, 242, 167, 0, 0]   [0, 0, 244, 18]                 wzu448_sb_apps

  TableKind                                         CreateText
0         T                                               None
1         V  CREATE VIEW  ... ... ... ... ... ... ... ... ...
2         T                                               None
3         V  CREATE VIEW  ... ... ... ... ... ... ... ... ...
4         T                                               None
5         V  CREATE VIEW  ... ... ... ... ... ... ... ... ...
6         V  CREATE VIEW  ... ... ... ... ... ... ... ... ...
7         V  CREATE VIEW  ... ... ... ... ... ... ... ... ...
8         V  CREATE VIEW  ... ... ... ... ... ... ... ... ...
9         T                                               None

Answer 1

将您的bytearray转换为它们不可变的表亲bytes。在

import pandas as pd

# Create your example `dbase`
DatabaseId_dbase = list(map(bytearray, [[2, 0, 243, 185], [2, 0, 168, 114],
    [2, 0, 133, 153], [2, 0, 29, 184], [2, 0, 190, 183], [2, 0, 71, 55],
    [2, 0, 169, 183], [2, 0, 201, 183], [0, 0, 139, 8], [2, 0, 142, 203]]))
DatabaseName = ['PCDW_CRS_BBCONV3_TB', 'PAMLIF_TB', 'PADW_PRESN_TB',
    'CEDW_MOBILE_TB', 'CEDW_MODEL_SCORE_TB', 'PBBBAM_TB', 'CEDW_OCC_TB',
    'CCDW_DGTL_DEAL_TB', 'PRECDSS_TB', 'CDBDW_TB']
dbase = pd.DataFrame({'DatabaseId': DatabaseId_dbase,
                      'DatabaseName': DatabaseName})

# Create your example `tvm`
DatabaseId_tvm = list(map(bytearray, [[2, 0, 67, 183], [2, 0, 186, 52],
    [2, 0, 225, 150], [2, 0, 244, 181], [2, 0, 110, 63], [2, 0, 237, 153],
    [2, 0, 243, 127], [2, 0, 243, 185], [2, 0, 143, 130], [0, 0, 244, 18]]))
TVMId = list(map(bytearray, [[230, 1, 41, 11, 0, 0], [214, 1, 60, 133, 0, 0],
    [193, 2, 59, 48, 0, 0], [0, 2, 235, 154, 0, 0], [255, 1, 131, 76, 0, 0],
    [125, 1, 217, 138, 0, 0], [224, 0, 80, 233, 0, 0], [208, 1, 72, 15, 0, 0],
    [246, 0, 27, 61, 0, 0], [186, 1, 242, 167, 0, 0]]))
TVMName = ['JCP_03538_112002', 'STL_AUTHNCTD_RULE_EXECN',
    'uye177_Xsell_EM_OPCL_TB2', 'PL_CALCD_INVSTR_MTHLY_HIST_ST',
    'IMH867_AVA0803_SNAP', 'FD_ACCT_STMT_ADR_ST', 'EXP_SRCH_RSLT_DESC',
    'SGI_PAY_DENIED_SEP_112012', 'CR_INDIVD', 'wzu448_sb_apps']
TableKind = ['T', 'V', 'T', 'V', 'T', 'V', 'V', 'V', 'V', 'T']
tvm = pd.DataFrame({'DatabaseId': DatabaseId_tvm, 'TVMId': TVMId,
                    'TVMName': TVMName, 'TableKind': TableKind})

# This line would fail with the following error
#     TypeError: type object argument after * must be a sequence, not map
# merge = pd.merge(tvm, dbase, on='DatabaseId')

# Apply the `bytes` constructor to the `bytearray` columns    
dbase['DatabaseId'] = dbase['DatabaseId'].apply(bytes)
tvm['DatabaseId'] = tvm['DatabaseId'].apply(bytes)
tvm['TVMId'] = tvm['TVMId'].apply(bytes)

# Now it works!
merge = pd.merge(tvm, dbase, on='DatabaseId')

结果merge是

^{pr2}$

（我必须更改tvm中某一行的DatabaseId字段，否则{}将为空。我也没有包括CreateText列，这对SO来说太尴尬了）