Missing column value when merge tables in python

Question

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('linktoAPI')
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

Eric M · Accepted Answer · 2022-06-23 11:55:36Z

1

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'})

answered Jun 23, 2022 at 11:55

Eric M

1711 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phạm Tấn Thành Over a year ago

thank you, the thought of inappropriate datatype did not cross my mind. Have a nice day

Collectives™ on Stack Overflow

Missing column value when merge tables in python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related