title: Remove outliers from Pandas pivot_table rows
body: <p>I am currently working on a problem that entails looking at a number of purchased parts and determining if we are successful in our endeavors to reduce our cost.</p>
  
  <p>I am hit by a few issues though. Since our purchaser can choose to enter an order in any given number of Unit Of Measures (UOM), but does not always remember to put in a conversion factor, we sometimes run into an issue like the one illustrated in the below dataframe</p>
  
  <pre><code>df = pd.DataFrame(
      [
      ['AABBCCDD','2014/2015','Q2',31737.60],
      ['AABBCCDD','2014/2015','Q2',31737.60],
      ['AABBCCDD','2014/2015','Q2',31737.60],
      ['AABBCCDD','2014/2015','Q3',89060.84],
      ['AABBCCDD','2015/2016','Q3',71586.00],
      ['AABBCCDD','2016/2017','Q3',89060.82],
      ['AABBCCDD','2017/2018','Q3',98564.40],
      ['AABBCCDD','2017/2018','Q3',110691.24],
      ['AABBCCDD','2017/2018','Q4',93390.00],
      ['AABBCCDD','2018/2019','Q2',90420.00],
      ['AABBCCDD','2018/2019','Q3',13.08],
      ['AABBCCDD','2018/2019','Q3',13.08]
      ], 
      columns=['PART_NO','FiscalYear','FiscalQuarter','Price'])
  </code></pre>
  
  <p>As you can tell, the last two purchases carry a significantly lower unit cost. This is due to our previously purchasing one piece of full sheet goods, whereas now the purchaser chooses to enter the order in square inches of material</p>
  
  <p>Now.. the correct action is to go to the purchaser and have him/her fix the issue. I would like to get an overview of the issues beforehand</p>
  
  <p>I have tried pivoting the data</p>
  
  <pre><code>df_tab = pd.pivot_table(df, values='Price', index=['PART_NO'], columns=['FiscalYear','FiscalQuarter'], aggfunc=np.mean)
  </code></pre>
  
  <p>Resulting in the following:</p>
  
  <p><a href="https://i.stack.imgur.com/BbteE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BbteE.png" alt="enter image description here"></a></p>
  
  <p>Naturally I have thousands of parts that would go in this dataframe, having one row pr part number. It will probably go by date also not quarter, so the above is for simplification purposes.</p>
  
  <p>How would I go about the following 2 scenarios</p>
  
  <ol>
  <li>Replacing outlier values with null value creating a column at the
  end </li>
  <li>specifying that outlier values were present in a given row of
  data.</li>
  </ol>
  
  <p>-------------  EDIT  --------------</p>
  
  <p>I used a mix of the suggestions below and a few other pieces of inspiration, and arrived at the following solution</p>
  
  <pre><code># Imports
  import pyodbc
  import urllib
  from sql import SQL
  import pandas as pd
  from sqlalchemy import create_engine
  
  # Set variables
  upperQuantile = 0.8
  lowerQuantile = 0.2
  
  # Connect to server / database
  params = urllib.parse.quote_plus("Driver={SQL Server Native Client 11.0};Server=LT02670;Database=staging;Trusted_Connection=yes;")
  engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
  
  # Create dataframe containing raw data
  df = pd.read_sql(SQL(), engine)
  
  # define upper and lower quartile ranges for outlier detection
  def q1(x):
      return x.quantile(lowerQuantile)
  
  def q2(x):
      return x.quantile(upperQuantile)
  
  # define function for sorting out outliers
  f = {'PO_UNIT_PRICE_CURRENT_CURRENCY': ['median', 'std', q1,q2]}
  
  # group data and add function to data (adds columns median, std, q1 and q2)
  dfgrp = df.groupby(['PART_NO']).agg(f).reset_index()
  
  # Isolate part numbers in dataframe
  dfgrpPart = pd.DataFrame(dfgrp['PART_NO'])
  
  # Isolate value columns in dataframe
  dfgrpStat = dfgrp['PO_UNIT_PRICE_CURRENT_CURRENCY']
  
  # Join categorical data with values (this is done in order to eliminate multiindex caused py groupby function)
  dfgrp = dfgrpPart.join(dfgrpStat)
  
  # Add new columns to raw data extract
  df = df.join(dfgrp.set_index('PART_NO'), on='PART_NO').reset_index()
  
  # Remove outliers and 0-values
  idx = df[df['PO_UNIT_PRICE_CURRENT_CURRENCY'] &lt; df['q1']].index
  df.drop(idx, inplace=True)
  idx = df[df['PO_UNIT_PRICE_CURRENT_CURRENCY'] &gt; df['q2']].index
  df.drop(idx, inplace=True)
  idx = df[df['PO_UNIT_PRICE_CURRENT_CURRENCY'] &lt;= 0].index
  df.drop(idx, inplace=True)
  
  # Split dataframe into fiscal year chunks, and build lists of part numbers
  df_14_15 = df[df['FiscalYear'].str.match('2014/2015')]['PART_NO'].to_list()
  # df_15_16 = df[df['FiscalYear'].str.match('2015/2016')]['PART_NO'].to_list()
  df_16_17 = df[df['FiscalYear'].str.match('2016/2017')]['PART_NO'].to_list()
  # df_17_18 = df[df['FiscalYear'].str.match('2017/2018')]['PART_NO'].to_list()
  df_18_19 = df[df['FiscalYear'].str.match('2018/2019')]['PART_NO'].to_list()
  df_19_20 = df[df['FiscalYear'].str.match('2019/2020')]['PART_NO'].to_list()
  
  # create one list of unique part numbers from multiple years, i have chosen only some years, as we rarely order the same parts six years running
  partsList = list(set(df_14_15) &amp; set(df_16_17) &amp; set(df_18_19))
  
  # Use list of part numbers to filter out raw data into output dataframe
  dfAllYears = df[df['PART_NO'].isin(partsList)]
  
  # write data to excel file for further analysis, this will overwrite existing file so be careful
  dfAllYears.to_excel("output.xlsx", index=False, sheet_name='Data')
  </code></pre>
  
  <p>This enabled me to conduct my analysis and move on.</p>
  
  <p>I am not completely happy with the code though, and believe that I might be doing some things unnecessarily complex and not utilizing pandas to its fullest  </p>