title: Creating 2 data frames from 2 different excel sheets.I want to pick Col1 from excel1 & Col2 from excel2.Store these cols in a New excel sheet
body: <p><a href="https://i.stack.imgur.com/ndJG5.png" rel="nofollow noreferrer">Expected Result</a><a href="https://i.stack.imgur.com/538VX.png" rel="nofollow noreferrer">Actual Result</a><a href="https://i.stack.imgur.com/TIuBM.png" rel="nofollow noreferrer">3</a>Firstly Both the columns are different with different data.
  Gathering these   columns data from 2 different sheets and adding those collected columns in a new excel sheet. While writing to an excel sheet i can see BLANK cells.Below is the sample o/p i got.
  <a href="https://i.stack.imgur.com/knWa7.png" rel="nofollow noreferrer">Table</a>
  Please Find the attached snippet for reference (Actual &amp; Expected Results)</p>
  
  <pre><code>import pandas as pd
  
  data1 = pd.read_csv(r'File1.dat', sep='|')
  df1 = data1.loc[:, ['Sheet1_Col1']].drop_duplicates()
  
  
  data2 = pd.read_excel(r'File2.xlsx')
  df2= data2[data2['Sheet2_Col2'] == 'Number'].loc[:,'SOURCE_CODE'].drop_duplicates()
  
  df_all_rows = pd.concat([df1,df2], axis=1)
  
  after_concat = df_all_rows.to_excel('New_Sheet.xlsx',  sheet_name='Collected_columns', index=False)
  </code></pre>
  
  <p>Expected Result is columns collected from 2 different excel sheets should be able to add these collected data in a new excel sheet</p>