Missing column value when merge tables in python-CodePudding

I'm trying to merge 3 tables, namely history, history_value and hocvien to retrieve the columns, namely ['ngaybaoluu', 'ngayhoclai','lydo','hv_fullname','action', 'object', 'hv_coso']. The problem is that my python result come out with no value for the column ['ngaybaoluu']. I have tried the same inner join in SQL and get the desired result. Why is this the case? Please help me.

Python code (product result with no value for column ngaybaoluu)

# import necessary packages
  from urllib.request import urlopen
  import pandas as pd
  import json
  import requests

#Get a response and conver to json
req = requests.get('https://office.ieltsvietop.vn/api/get_data/history')
req_json = req.json()
# History_value table 
df = pd.DataFrame(json.loads(r['history_value']) for r in req_json)
history_value = df[['ketoan_id', 'lop_id', 'lydo','hv_id', 'ngayhoclai', 'ngaybaoluu', 'huyhopdong', 'chinhanh' ]]

# History table
response = urlopen('https://office.ieltsvietop.vn/api/get_data/history')
history = pd.read_json(response)
# Hocvien table
response = urlopen('https://office.ieltsvietop.vn/api/get_data/hocvien')
data_json = json.loads(response.read())
hocvien = pd.DataFrame(data_json)
display(hocvien.columns)
# Merge history_value to history using (ketoan_id)
history_value_history = history_value.merge(history, on ='ketoan_id', how ='inner',suffixes =('_left', '_right'))
display(history_value_history.columns)
# Merge history_value_history to hocvien using (hv_id)
history_value_history_hocvien = history_value_history.merge(hocvien, left_on = 'hv_id_right', right_on ='hv_id', how = 'inner')

display(history_value_history_hocvien[['ngaybaoluu', 'ngayhoclai','lydo','hv_fullname','action', 'object', 'hv_coso']])

print('Success')

SQL code (produce desired results)

SELECT hv_fullname,ngaybaoluu, ngayhoclai, ketoan_id, hv_coso, action, object, lop_id
FROM history_value
join history using (ketoan_id)
join hocvien on hocvien.hv_id = history.hv_id

CodePudding user response：

I'm not getting any matching data to merge on for this line:

history_value_history = history_value.merge(
    history,
    on='ketoan_id',
    how='inner',
    suffixes=('_left', '_right')
)

I wrote some code to get the 'ketoan_id' in history_value that has data, and check if any are in the history df. It was empty for me. Since I can see that there are matching data, I checked the data types and found that some were strings, not numbers. If you look at the contents of ids_with_vals you will notice that they are strings. Once I converted all of the dataframes, it worked. The types can really catch you sometimes.

# Create mask to find 'ngaybaoluu' rows that are False
mask = history_value['ngaybaoluu'].isna()
# Use an anti-mask to get the ids that you are tyring to merge on.
ids_with_vals = history_value.loc[~mask]['ketoan_id'].to_list()
# Create a mask of the df you are merging to see if there is any data.
mask = history['ketoan_id'].isin(ids_with_vals)
# I get an empty df.
history.loc[mask]

history_value = history_value.astype({'ketoan_id': 'float'})
history = history.astype({'hv_id': 'float'})
hocvien = hocvien.astype({'hv_id': 'float'})