Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to convert the below query to pandas but I've got stuck.

SQL
SELECT
    *
  FROM
    temp_rejects
  WHERE
    reject_type = 'REWORK'
),
rejects AS (
  SELECT
    *
  FROM
    temp_rejects
  WHERE
    reject_type = 'SCRAP'
)
SELECT
  rj.*
FROM
  reworks rw
  LEFT JOIN rejects rj ON rw.barcode = rj.barcode
AND
  rw.created_date < rj.created_date


What I have tried:

I have tried that:

Python
rewB4rej = data_manager.data['rejects_data']
rew = rewB4rej.query('reject_type == "REWORK"').reset_index()
rej = rewB4rej.query('reject_type == "SCRAP"').reset_index()
rewB4rej = rew.merge(rej, on=['barcode', (rew.created_date >
rej.created_date)], how='left')


Also was trying using .query() but had errors like "
Can only compare identically-labeled DataFrame objects
"
Posted
Updated 22-Nov-22 19:36pm

1 solution

Believe this would be because the column labels match, but the index labels do not.

For your case, drop the indexes and try:
Python
rewB4rej = data_manager.data['rejects_data']
rew = rewB4rej.query('reject_type == "REWORK"').reset_index(drop=True)
rej = rewB4rej.query('reject_type == "SCRAP"').reset_index(drop=True)
rewB4rej = rew.merge(rej, on=['barcode', (rew.created_date >
rej.created_date)], how='left')
 
Share this answer
 
Comments
Member 15758970 23-Nov-22 7:23am    
Hi, thanks for your reply however it still gives me an error. Exactly this "ValueError: Can only compare identically-labeled Series objects".
Sandeep Mewara 23-Nov-22 10:56am    
This error would arise when your DataFrames have different indexes and/or different shapes.

Try:

rewB4rej = rew.merge(rej, left_on=['barcode', (rew.created_date > rej.created_date)], right_on=['barcode', (rew.created_date > rej.created_date)], how='left')

# try with this to see too
rewB4rej = rew.merge(rej, left_on='barcode', right_on='barcode', how='left')

Refer: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.merge.html

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900