title: Iterating over Dataframes to generate new Dataframes in Python with Power BI
body: <p>Using python in power BI to process data prior to entering the visuals in report.  </p>
  
  <p>Example of what I'm doing:</p>
  
  <p>Purchase order database comes from oracle.
  Supply database comes from multiple loc (SQL/csv).</p>
  
  <p>Purchase order X requires 20 units of a / 30 units of b  (e.g)</p>
  
  <p>I have made it work with the below code - but I have yet to run it across a full sized dataset - all the threads i've accessed basically say the way I'm doing it is not ideal - I was curious if there is a simpler more memory efficient way of accomplishing this.</p>
  
  <p>I/o sources are pandas dataframes in PBI - it also supports numpy and a couple others as far as I'm aware.</p>
  
  <p>Code below shows how i'm doing it presently - it works very quickly on sample size of 500 or so rows - want to make sure I can scale it up though.</p>
  
  <p>The below example is using 2 dataframes: (df1 is the demand table and df2 is the supply table) to allocate the supply material over the orders) df3 is the temp array for storing the calculation.</p>
  
  <p>To further clarify - main concern is the iterrows() and that concern is purely based on what I've read (mostly on this site)</p>
  
  <p>Edit:  Thank you so much for taking time to read and help me!
  <a href="https://stackoverflow.com/users/10239974/eva-vw">https://stackoverflow.com/users/10239974/eva-vw</a></p>
  
  <p>Not sure how to add the output but here is what the data looks like (in pictures)<a href="https://i.stack.imgur.com/SYTGX.png" rel="nofollow noreferrer">df1</a></p>
  
  <p><a href="https://i.stack.imgur.com/eV5J2.png" rel="nofollow noreferrer">df2</a></p>
  
  <p>I didn't think I could merge them since the alloy tag in df1 is a requirement value and the alloy tag in df2 is an inventory value - that would be awesome if I could - I will give your code a try and let you know!  Thank you again.</p>
  
  <p>Oh one last detail - the "Counter" column I made was just to keep the index at where the requirement is - when I did the same code without it my df3 that I eventually combine to make my final df4 was only as many rows long as orders I allocated for - I wanted it to stay aligned with df1 so I could put them together at the end - when I added a value in there for each iteration it kept the proper location.  Df3 ends up like Counter:, Allocated  and then that gets added to DF1 so that Lbs(Required) and Allocated are together with the orders.</p>
  
  <p>Edit:  Final output after switching to merge of df1: <a href="https://i.stack.imgur.com/5k1Gh.png" rel="nofollow noreferrer">df1 with merge</a>
  <a href="https://stackoverflow.com/users/10239974/eva-vw">https://stackoverflow.com/users/10239974/eva-vw</a></p>
  
  <p>Edit:  Final output with original code iterrows(): df4: <a href="https://i.stack.imgur.com/B4Jqu.png" rel="nofollow noreferrer">df4</a>
  <a href="https://stackoverflow.com/users/10239974/eva-vw">https://stackoverflow.com/users/10239974/eva-vw</a></p>
  
  <p>Edit: Final output with adjusted function: <a href="https://i.stack.imgur.com/41awn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/41awn.png" alt="df1adjusted"></a>
  <a href="https://stackoverflow.com/users/10239974/eva-vw">https://stackoverflow.com/users/10239974/eva-vw</a></p>
  
  <p>To explain more - there was 53000 lbs of this component in inventory - so it stops allocating once it exhausts the inventory with the iterrows - but i think I can figure this portion out now that I understand your logic.</p>
  
  <p>Also btw - your code executed instantly - where as the iterrows loads for about a second - so from scaling perspective I'm very excited about this!</p>
  
  <pre><code>import pandas as pd
  df1 = pd.DataFrame.from_dict({'Name': ['a','b','c'], 'Alloy': ['R80','R80','R80'], 'Lbs': [6400,6400,6400]})
  df2 = pd.DataFrame.from_dict({'ITEM': ['R80'], 'Inv': [50000]})
  df3 = pd.DataFrame()
  df3["Allocated"] = ""
  df3["Counter"] = ""
  for i, row in df2.iterrows():
      x = df2.at[i, 'ITEM']
      y = df2.at[i, 'Inv']
      g = 0
      for j, row in df1.iterrows():
          g+=1
          df3.at[j, 'Counter'] = g
          if df1.at[j, 'Alloy'] == x:
              z = df1.at[j, 'Lbs']
              if y &gt;= z:
                  df3.at[j, 'Allocated'] = z
                  y = y - z
              else:
                  break
  
  df4 = pd.concat([df1,df3],axis=1)
  </code></pre>