Python Forum
SQL select join operation in python(Select.. join)
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL select join operation in python(Select.. join)
#1
I have data set called Account_universe
ACC_ID ZIP_CODE
001AAD 638400
002ADF 638402
003AAS 638902
004AGF 123480
005AQA 987650

Next data set Alt_Account_univ
ACC_ID ALT_ID SOURCE
001AAD 10000 confd
002ADF 10001 Non confd
003AAS 10002 Non confd
004AGF 10003 Non confd
005AQA 10004 confd


In sql I will do like

Select a.ACCT_ID,b.ALT_ID,a.ZIP_CODE
from Account_universe a
left join Alt_Account_univ b on b.acct_id = a.acct_id


In pandas I will do like

df_a = Account_universe
df_b= Alt_Account_univ
df1 =df_a.merge(df_b[['ACCT_ID','ALT_ID']],on='ACCT_ID',how='left')
What I have tried in python without(pandas)

set(df_a) & set(df_b)
Here how to join on specific columns and I have to write only a.ACCT_ID,b.ALT_ID,a.ZIP_CODE these columns in another list. I am aware this set will act like A intersect B any other possible ways to do this operation?.

Thanks in Advance!!
Reply
#2
If I understand you it would be something along the lines of
## using you pseudo-code
Select ACCT_ID, ZIP_CODE from a 
recs_a=cur_a.fetchall()
select ALT_ID from b 
recs_b=cur_b.fetchall()

combined_list=[]
for ctr, row in enumerate(recs_a):  ## assumes a and b are the same length
    combined_list.append([row[0], recs_b[ctr], row[1]]) 
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  os.path.join() 'NoneType' confusion gowb0w 11 1,418 Sep-22-2023, 11:13 PM
Last Post: deanhystad
  Formatting outputs created with .join command klairel 2 592 Aug-23-2023, 08:52 AM
Last Post: perfringo
  ''.join and start:stop:step notation for lists ringgeest11 2 2,378 Jun-24-2023, 06:09 AM
Last Post: ferdnyc
Photo How to select NULL and blank values from MySQL table into csv python300 9 2,328 Dec-27-2022, 09:43 PM
Last Post: deanhystad
  Will JoinableQueue.join() always notice the counter reaching 0? RobinVeer 1 730 Oct-07-2022, 09:28 AM
Last Post: Larz60+
Lightbulb IMAP select command Linh_lee 2 1,072 Aug-19-2022, 09:11 PM
Last Post: Linh_lee
  [SOLVED] [BeautifulSoup] Turn select() into comma-separated string? Winfried 0 1,086 Aug-19-2022, 08:07 PM
Last Post: Winfried
  select files such as text file RolanRoll 2 1,128 Jun-25-2022, 08:07 PM
Last Post: RolanRoll
  how to join by stack multiple types in numpy arrays caro 1 1,101 Jun-20-2022, 05:02 PM
Last Post: deanhystad
  select Eof extension files based on text list of filenames with if condition RolanRoll 1 1,474 Apr-04-2022, 09:29 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020