df.join（）的问题：valueError：您试图合并object和int64列

Question

这些问题都没有涉及这个问题：问题1和问题2，我在pandas文档中也找不到答案。

您好，我正在尝试查找此错误的潜在原因：

ValueError: You are trying to merge on object and int64 columns.

  account  apt  apt_p  balance       date  day    flag  month  reps     reqid  year
0  AA0420    0    0.0  -578.30 2019-03-01    1       1      3    10  82f2d761  2019
1  AA0420    0    0.1  -578.30 2019-03-02    2       1      3    10  82f2d761  2019
2  AA0420    0    0.1  -578.30 2019-03-03    3       1      3    10  82f2d761  2019
3  AA0421    0    0.1  -607.30 2019-03-04    4       1      3    10  82f2d761  2019
4  AA0421    0    0.1  -610.21 2019-03-05    5       1      3    10  82f2d761  2019

打印(the_big_df.info())输出：

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 36054 entries, 0 to 36053
Data columns (total 11 columns):
account        36054 non-null object
apt            36054 non-null int64
apt_p          36054 non-null float64
balance        36054 non-null float64
date           36054 non-null datetime64[ns]
day            36054 non-null int64
flag           36054 non-null int64
month          36054 non-null int64
reps           36054 non-null int32
reqid          36054 non-null object
year           36054 non-null int64
dtypes: datetime64[ns](1), float64(2), int32(1), int64(5), object(2)
memory usage: 3.2+ MB

下面是我传递给join()的数据文件；打印(df_to_join.head(5)):

      reqid     id
0  54580f39  13301
1  3ba905c0  77114
2  5f2d80da  13302
3  a1478e98  77115
4  9b09854b  78598

打印(df_to_join.info())输出：

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 14332 entries, 0 to 14331
Data columns (total 2 columns):
reqid    14332 non-null object
dni      14332 non-null object

the_max_df = the_big_df.join(df_to_join,on='reqid')

ValueError: You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat

Answer 1

这里的问题是对联接工作方式的误解：当您说the_big_df.join(df_to_join,on='reqid')时，它并不意味着像乍一看所假设的那样在the_big_df.reqid==df_to_join.reqid上联接，而是在the_big_df.reqid==df_to_join.index上联接。由于requid类型为object，而索引类型为int64，因此会出现错误。

请参见join的文档：

在索引上或键列上与其他数据表连接列。
...
on:str、str列表或类数组，可选
调用方中的列或索引级别名称，以连接其他中的索引，否则连接索引上的索引。

请看下面的示例：

df1 = pd.DataFrame({'id1': [1, 2], 'val1': [11,12]})
df2 = pd.DataFrame({'id2': [3, 4], 'val2': [21,22]})
print(df1)
#   id1  val1
#0    1    11
#1    2    12
print(df2)
#   id2  val2
#0    3    21
#1    4    22

# join on df1.id1 (int64) == df2.index (int64) 
print(df1.join(df2, on='id1'))
#   id1  val1  id2  val2
#0    1    11  4.0  22.0
#1    2    12  NaN   NaN

# now df3 same as df1 but id3 as object:
df3 = pd.DataFrame({'id3': ['1', '2'], 'val1': [11,12]})

# try to join on df3.id3 (object) == df2.index (int64) 
df3.join(df2, on='id3')
#ValueError: You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat

>>> df3.join(df2, on='id3')
  id3  val1  id2  val2
0   1    11  NaN   NaN
1   2    12  NaN   NaN