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

Dash Python
1

Hello,
I’m deploying my app to stage and I’m getting this error in the dataframe,but on my test version everything is working fine.
Does anyone have any ideas?

Traceback (most recent call last):
File “C:\Users\Administrator\Documents\dashboard\jifeng_reporting\apps\app2.py”, line 154, in show_users
dataframe = dataframe.merge(scrap_codes, how=“left”, on=“DefectCode”)
File “C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\frame.py”, line 8192, in merge
return merge(
File “C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\reshape\merge.py”, line 74, in merge
op = _MergeOperation(
File “C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\reshape\merge.py”, line 672, in init
self._maybe_coerce_merge_keys()
File “C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\reshape\merge.py”, line 1193, in _maybe_coerce_merge_keys
raise ValueError(msg)
ValueError: You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concat

this is my app.callback.

@app.callback(
    [Output("report_check_criteria", "children"),
    Output("excel_data2", "data"),
    Output('alert2', 'children')],
        [Input("submit_button2", "n_clicks")],
        state=[State("start_date_picker", "date"),
        State("start_time_picker", "value"),
        State("end_date_picker", "date"),
        State("end_time_picker", "value"),
        State("database", "value"),
        State("result_selection", "value"),
        State("language", "value")]
)
def show_users(n_clicks, start_date_picker, start_time_picker, end_date_picker, end_time_picker, database, result_selection, language):
    if n_clicks:
        start_time = datetime.datetime.strptime(start_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(start_time_picker[0:2]))
        end_time = datetime.datetime.strptime(end_date_picker, "%Y-%m-%d") + datetime.timedelta(hours=int(end_time_picker[0:2]))
        parameters = [start_time, end_time]

        query_string = mssql_queries.build_db_query_string_app2_new(result_selection)
        records = mssql_conn.execute_query(query_string, parameters, str(database))
        print("result_selection")
        #query_string = mysql_queries.build_db_query_string_app2(str(database))
        #records = connect.execute_query(query_string, parameters, str(database))
        if records:
            dataframe = pd.DataFrame.from_records(records)
            dataframe.columns = ["ID", "Timestamp", "Type", "Result", "DefectCode"]
            
            mask = dataframe.applymap(type) != bool
            d = {True: "1", False: "0"}
            dataframe = dataframe.where(mask, dataframe.replace(d))

            # Scrap Codes
            query_string = mssql_queries.build_db_query_string_defect_descriptions(language)
            records = mssql_conn.execute_without_params(query_string, str(database))
            scrap_codes = pd.DataFrame.from_records(records)
            scrap_codes.columns = ["DefectCode", "DefectDescription"]
            dataframe = dataframe.merge(scrap_codes, how="left", on="DefectCode")
            print("----")
            print(dataframe)

            return dbc.Table.from_dataframe(dataframe), dataframe.to_json(date_format='iso', orient='split'), None
        else:
            return None, None, dbc.Alert("No entries within the specified timeframe.", color="warning", duration=5000)
    else:
        return dbc.Table(None), None, None
2

Hi, sorry for the late reply just got the notification. It seems like your scrap_codes “DefectCode” doesn’t have the correct type, i.e. it’s not an int64. you could try to apply that type to the whole column but if there are any wrong values it won’t work.

scrap_codes[“DefectCode”]=scrap_codes[“DefectCode”].apply(numpy.int64)

or np.64 instead of numpy.int64 depending on how/if you imported numpy

3

Hi,

So I’m thinking of making it in two options.

  1. scrap_codes[“DefectCode”].astype(str).astype(int)
  2. scrap_codes[“DefectCode”]=scrap_codes[“DefectCode”].apply(numpy.int64)

But whatever I paste in the code the whole app just turns off,is there any order that I need to put this in #scrap codes?

This fails on the live version so I don’t know how to check what went wrong.

This is what I get from the print(df.dtypes)

result_selection

ID int64
Timestamp datetime64[ns]
Type int64
Result object
DefectCode int64
DefectDescription object
dtype: object

1 Like
4

This is what I get now;

Traceback (most recent call last):
File “C:\Users\Administrator\Documents\dashboard\jifeng_reporting\apps\app2.py”, line 155, in show_users
scrap_codes[“DefectCode”] = scrap_codes[“DefectCode”].apply(numpy.int64)
File “C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\frame.py”, line 3024, in getitem
indexer = self.columns.get_loc(key)
File “C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Lib\site-packages\pandas\core\indexes\range.py”, line 354, in get_loc
raise KeyError(key)
KeyError: ‘DefectCode’

5

when you run apply it should be after you name the columns so insert it here

scrap_codes = pd.DataFrame.from_records(records)
scrap_codes.columns = ["DefectCode", "DefectDescription"]

scrap_codes[“DefectCode”]=scrap_codes[“DefectCode”].apply(numpy.int64)  

dataframe = dataframe.merge(scrap_codes, how="left", on="DefectCode")
1 Like
6

Oh wow,

so it was just the spacing that affected my code to apply?
it’s working super.Thank you!