title: Matching data from 2 pandas dataframes
body: <p><strong>Scenario:</strong> Following up from some previous questions, I now have a code that yields 2 dataframes (bid/ask).</p>
  
  <p><strong>What I am trying to do:</strong> These two dataframes have dates as column headers, identifiers as row index and number values for everything else. I want to match the dates, identifiers, bids and asks and output to txt in a format such as:</p>
  
  <pre><code>date 1    identifier 1    bid    ask
  date 1    identifier 2    bid    ask
  date 1    identifier 3    bid    ask
  date 2    identifier 1    bid    ask
  date 2    identifier 2    bid    ask
  date 2    identifier 4    bid    ask
  date 3    identifier 2    bid    ask
  date 2    identifier 3    bid    ask
  </code></pre>
  
  <p>and so on.</p>
  
  <p><strong>Obs1:</strong> Not all dates have values for all identifiers (some are NaN).</p>
  
  <p><strong>Obs2:</strong> I already tried (in previous versions of my code) to have lists of dataframes or have just two dataframes (bid and ask), but I keep incurring in multiple issues (ex: I get unknown value format or timeouts), so I decided to divide my source files in two sets.</p>
  
  <p><strong>Problem:</strong> I am not being able to properly match the data (like a vlookup)</p>
  
  <p><strong>Question1:</strong> What am I doing wrong? Is there a better way?</p>
  
  <p><strong>Obs3:</strong> I am currently trying to use the multiindex to create a 3d dataframe with the higher level being the (bid/ask). I also tried using series.map function of the pandas, to do a sort of "vlookup" between the dataframes, but that is not proving very efficient.</p>
  
  <p><strong>Current code:</strong></p>
  
  <pre><code>import pandas as pd
  import numpy as np
  import matplotlib.pyplot as plt
  import glob, os
  import datetime as dt
  from datetime import datetime
  import matplotlib as mpl
  from openpyxl import load_workbook
  import sys
  
  
  directory = os.path.join("C:\\","Users\\DGMS\\Desktop\\final 2\\run 1 test")        
  dfbid = pd.DataFrame()
  dfask = pd.DataFrame()
  
  b = pd.DataFrame()
  a = pd.DataFrame()
  
  for root,dirs,files in os.walk(directory):
  
      for file in files:
  
          f = os.path.join(root, file)
  
          wb = load_workbook(f)
  
          print (f)
          for sheet in wb.worksheets:
  
              if sheet.title == "Bid":
  
                  a = pd.read_excel(f, "Bid")
  
                  for i in range(1,len(a.columns)):
                      a.columns.values[i] = pd.to_datetime(a.columns.values[i])
  
                  dfbid = pd.concat([dfbid, a])
                  print ('bid done')
  
              elif sheet.title == "Ask":
  
                  b = pd.read_excel(f, "Ask")
  
                  for i in range(1,len(b.columns)):
                      b.columns.values[i] = pd.to_datetime(b.columns.values[i])
  
                  dfask = pd.concat([dfask, b])
                  print ('ask done')
  
  
  parts = {'Bid': dfbid, 'Ask': dfask}
  
  finalresult = pd.concat(parts)
  </code></pre>
  
  <p>This is a snippet of one of my first dataframe (ask):</p>
  
  <pre><code>                01/01/2010   02/01/2010   03/01/2010
  AT0000383864      160.614       161.1436    161.1532
  AT0000385745      109.3122      109.3144    109.3068
  AT0000386115      117.7972      118.0388    118.051
  AT0000A001X2      119.7004      120.0058    120.031
  AT0000A04967      152.8196      153.7868    153.792
  </code></pre>
  
  <p>This is a snippet of one of my second dataframe (bid):</p>
  
  <pre><code>                01/01/2010   02/01/2010   03/01/2010
  AT0000383864     161.038      161.5676    161.5772
  AT0000385745     109.4322     109.4344    109.4268
  AT0000386115     118.0202     118.2618    118.274
  AT0000A001X2     119.8284     120.1338    120.159
  </code></pre>
  
  <p>What I am trying to get (output to a txt):</p>
  
  <pre><code>AT0000383864    01/01/2010    161.038     160.614
  AT0000383864    02/01/2010    161.5676    161.1436
  AT0000383864    03/01/2010    161.5772    161.1532
  AT0000385745    01/01/2010    109.4322    109.3122
  </code></pre>
  
  <p>And so on. Remembering that some identifiers have data for the same dates in both dataframes (bid/ask), some have just for one of them.</p>