title: Is it possible to connect pivot table cells to data using pandas once the file has been saved?
body: <p>I have successfully written a script that takes two files, filters out for the correct data, and creates a pivot table of the data. However, I want the end file to have the same functionality as an Excel file PivotTable. For example, you can add extra columns/rows, right click to filter, or click on cells to bring up all matching rows from the data. Is it possible to do this using Pandas? Here is my code script, which isolates all the data and creates the visual look of the PivotTable, but not the actual functionality of one. Thanks!</p>
  
  <pre><code>import pandas as pd
  from datetime import date
  
  today = date.today()
  d1 = today.strftime('%m.%d.%Y')
  
  excel1 = 'lookupfile.xlsx'
  excel2 = 'mainfile.xlsx'
  new_file = 'newfile ' + d1 +'.xlsx'
  writer = pd.ExcelWriter(new_file, engine='xlsxwriter')
  
  
  cutoff_date = '2019-09-23'
  ref_num = 'REFERENCE NUMBER'
  gnc = 'DISPLAY CODE'
  obooked_date = 'ORIGINAL BOOKED DATE'
  rep_name = 'REPRESENTATIVE NAME'
  
  #Delete top 3 junk rows of Excel File and last two summary rows
  df1 = pd.read_excel(excel1, header=3)
  df1 = (df1[:-2])
  
  df2 = pd.read_excel(excel2)
  
  #finds all ref numbers that have the ESBK code
  has_gnc = df1[(df1[gnc] == 'ESBK022') | (df1[gnc] == 'ESBK023')
                | (df1[gnc] == 'ESBK024')]
  
  #does a vlookup
  lookup_table = df2.loc[~df2[ref_num].isin(has_gnc[ref_num])]
  
  #removes everything that's before the cutoff date
  lookup_table = lookup_table[pd.to_datetime(lookup_table[obooked_date]) &gt;= cutoff_date]
  
  #creates Pivot Table layout
  df3 = lookup_table.pivot_table(index=rep_name, columns=obooked_date, aggfunc ='count', values=ref_num, margins='True')
  df3 = df3.iloc[:-1]
  
  
  #saves File
  df3.to_excel(writer,sheet_name='PIVOT', index=True)
  lookup_table.to_excel(writer,sheet_name='DATA', index=False)
  
  writer.save()
  </code></pre>