python - Pandas merge return empty dataframe

Question

I have two dataframes

current_bin.info()    
<class 'pandas.core.frame.DataFrame'>
Int64Index: 16 entries, 0 to 15
Data columns (total 3 columns):
id               16 non-null object
fpd              16 non-null float64
avgSpeedBinID    16 non-null object
dtypes: float64(1), object(2)

the current_bin data frame looks like:

current_bin
    id          fpd         avgSpeedBinID
0   1.1.4.1     2.818623    1
1   1.1.4.10    0.266681    10
2   1.1.4.11    0.250017    11
3   1.1.4.12    0.234749    12
4   1.1.4.13    0.222515    13
5   1.1.4.14    0.216150    14
6   1.1.4.15    0.218368    15
7   1.1.4.16    0.227663    16
8   1.1.4.2     1.475454    2
9   1.1.4.3     0.805842    3
10  1.1.4.4     0.581797    4
11  1.1.4.5     0.450314    5
12  1.1.4.6     0.379107    6
13  1.1.4.7     0.335155    7
14  1.1.4.8     0.305992    8
15  1.1.4.9     0.284210    9

and

avg.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 16 entries, 0 to 15
Data columns (total 4 columns):
avgSpeedBinID      16 non-null int64
avgBinSpeed        16 non-null float64
avgSpeedBinDesc    16 non-null object
temp               16 non-null int64
dtypes: float64(1), int64(2), object(1)

which looks like:

    avgSpeedBinID   avgBinSpeed avgSpeedBinDesc             temp
0   1               3           speed < 2.5mph              0
1   2               5           2.5mph <= speed < 7.5mph    0
2   3               10          7.5mph <= speed < 12.5mph   0
3   4               15          12.5mph <= speed < 17.5mph  0
4   5               20          17.5mph <= speed <22.5mph   0
5   6               25          22.5mph <= speed < 27.5mph  0
6   7               30          27.5mph <= speed < 32.5mph  0
7   8               35          32.5mph <= speed < 37.5mph  0
8   9               40          37.5mph <= speed < 42.5mph  0
9   10              45          42.5mph <= speed < 47.5mph  0
10  11              50          47.5mph <= speed < 52.5mph  0
11  12              55          52.5mph <= speed < 57.5mph  0
12  13              60          57.5mph <= speed < 62.5mph  0
13  14              65          62.5mph <= speed < 67.5mph  0
14  15              70          67.5mph <= speed < 72.5mph  0
15  16              75          72.5mph <= speed            0

both dataframes have a value 1 to 16 on the avgSpeedBinID field, however, when i try to merge the data frames together

avg.merge(current_bin, on='avgSpeedBinID')

I'm getting a null dataframe

avgSpeedBinID   avgBinSpeed avgSpeedBinDesc temp    id  fpd

Why is this happening and how can i correct the problem?

Answer 1

The avgSpeedBinID in the current bin dataframe is type str and in avg is int. Just cast the str one into an int and the merge will work.

current_bin['avgSpeedBinID'] = current_bin['avgSpeedBinID'].astype(int)

avg.merge(current_bin, on='avgSpeedBinID')


    avgSpeedBinID   avgBinSpeed avgSpeedBinDesc             temp    id   fpd
0   1               3            speed < 2.5mph             0   1.1.4.1  2.818623
1   2               5            2.5mph <= speed < 7.5mph   0   1.1.4.2  1.475454
2   3               10           7.5mph <= speed < 12.5mph  0   1.1.4.3  0.805842
3   4               15           12.5mph <= speed < 17.5mph 0   1.1.4.4  0.581797
4   5               20           17.5mph <= speed <22.5mph  0   1.1.4.5  0.450314
5   6               25           22.5mph <= speed < 27.5mph 0   1.1.4.6  0.379107
6   7               30           27.5mph <= speed < 32.5mph 0   1.1.4.7  0.335155
7   8               35           32.5mph <= speed < 37.5mph 0   1.1.4.8  0.305992  
8   9               40           37.5mph <= speed < 42.5mph 0   1.1.4.9  0.284210
9   10              45           42.5mph <= speed < 47.5mph 0   1.1.4.10 0.266681
10  11              50           47.5mph <= speed < 52.5mph 0   1.1.4.11 0.250017
11  12              55           52.5mph <= speed < 57.5mph 0   1.1.4.12 0.234749
12  13              60           57.5mph <= speed < 62.5mph 0   1.1.4.13 0.222515
13  14              65           62.5mph <= speed < 67.5mph 0   1.1.4.14 0.216150
14  15              70           67.5mph <= speed < 72.5mph 0   1.1.4.15 0.218368
15  16              75           72.5mph <= speed           0   1.1.4.16 0.22763