SQL query with list and merging the result to dataframe with column location change-CodePudding

I am new to python and learning as I code. I have a DataFrame (df1) which I read from excel and from df1, I am taking a column ("Product_ID") converting to list and passing the list to SQL query to get the results. Results are stored in another DataFrame (df2) then I am merging df1 and df2 on column "Product_ID" and writing to excel. But in excel I am seeing only one row. That could be because results from SQL is creating a DataFrame for each product. How could I write all rows to excel and also when I merge df2 with df1 how can I change location of column in df2.

Below is my code

file = path to excel
df1 = pd.read_excel(file)
prod_list = frm_df['Product_ID'].tolist() # list of product_ids

for x in prod_list:
    SQL = pd.read_sql_query('''SELECT Product_ID, Amount from table where Product_ID= '{x}' 
     '''.format(x = x), cnxn)
    df2 = pd.DataFrame(SQL)
    merge = pd.merge(df1, df2, on='Product_ID')
    writer = pd.ExcelWriter('output.xlsx')
    merge.to_excel(writer, 'data')
    writer.save()

df1 output is

      Name     Product_ID   IND INN     FAM INN
0      Allen     0072       1400        4200
1      Radio     0068       1500        2400    
2      COMP      0430       3500        7000

df2 output:

  Product_ID    AMOUNT
0   0072      1400.0
  Product_ID    AMOUNT
0   0068       2400.0
  Product_ID    AMOUNT
0   0430       3500.0

merge output:

      Name     Product_ID   IND INN     FAM INN  AMOUNT
0      Allen     0072       1400        4200      1400

       Name    Product_ID    IND INN     FAM INN  AMOUNT

 0     Radio     0068        1500        2400     2400  

        Name     Product_ID  IND INN     FAM INN  AMOUNT

  0     COMP         0430       3500        7000    3500

In excel I am seeing only 1 row which is related to Product_ID. I want my merge dataframe as below:

      Name     Product_ID   IND INN  AMOUNT   FAM INN
0      Allen     0072       1400      1400        4200
1      Radio     0068       1500      2400        2400  
2      COMP      0430       3500      3500        7000

df1.to_dict() output:

{'Unnamed: 0': {0: 0, 1: 1, 2: 2}, 'Group Name': {0: 'Allen, Inc.', 1: 
'American.', 2: 'COM'}, 'Product_ID': {0: '0072', 1: '0068', 2: '0430'}, 
 'IND INN': {0: 1400, 1: 1500, 2: 3500}, 'FAM INN': {0:4200, 1: 2400, 
  2:7000}

df2.to_dict() output:

{'Product_ID': {0: '0072'}, 'AMOUNT': {0: 1400.0}}
{'Product_ID': {0: '0068'}, 'AMOUNT': {0: 2400.0}}
{'Product_ID': {0: '0430'}, 'AMOUNT': {0: 3500.0}}

CodePudding user response：

You can build DataFrames from the dicts, (use stack str.get unstack to build df2). Then merge:

df1 = pd.DataFrame({'Unnamed: 0': {0: 0, 1: 1, 2: 2}, 
                    'Group Name': {0: 'Allen, Inc.', 1: 'American.', 2: 'COM'}, 
                    'Product_ID': {0: '0072', 1: '0068', 2: '0430'}, 
                    'IND INN': {0: 1400, 1: 1500, 2: 3500}, 
                    'FAM INN': {0:4200, 1: 2400, 2:7000}})

df2 = pd.DataFrame([{'Product_ID': {0: '0072'}, 'AMOUNT': {0: 1400.0}},
                    {'Product_ID': {0: '0068'}, 'AMOUNT': {0: 2400.0}},
                    {'Product_ID': {0: '0430'}, 'AMOUNT': {0: 3500.0}}])

df2 = df2.stack().str.get(0).unstack()
merged = df1.merge(df2, on='Product_ID').drop(columns='Unnamed: 0')

Output:

    Group Name Product_ID  IND INN  FAM INN  AMOUNT
0  Allen, Inc.       0072     1400     4200  1400.0
1    American.       0068     1500     2400  2400.0
2          COM       0430     3500     7000  3500.0