Dupes and Object Prefixes

import pandas as pd
import numpy as np
from datetime import date

from simple_salesforce import Salesforce

pd.options.display.max_rows = 999

sf = Salesforce(username='',
                password='',
                security_token='')

desc = sf.describe()
objects = []
obj_labels = [obj['label']  for obj in desc['sobjects']]
obj_names  = [obj['name']   for obj in desc['sobjects']]
obj_custom = [obj['custom'] for obj in desc['sobjects']]
for label, name, custom in zip(obj_labels, obj_names, obj_custom):
    objects.append((label, name, custom))
objects = pd.DataFrame(objects,
                       columns = ['label','name','custom'])
print(str(objects.shape[0]) + ' objects')
objects.head(10);

xref = (pd.read_excel('dupes-and-object-prefixes/object_xref.xlsx',
                      skiprows=1)
        .drop(columns=['Unnamed: 2']))
xref.head()

desc = sf.DuplicateRecordSet.describe()
fields = []
field_labels = [field['label'] for field in desc['fields']]
field_names =  [field['name']  for field in desc['fields']]
field_types =  [field['type']  for field in desc['fields']]
for label, name, dtype in zip(field_labels, field_names, field_types):
    fields.append((label, name, dtype))
fields = pd.DataFrame(fields,
                      columns = ['label','name','type'])
obj = 'DuplicateRecordSet'
field_list = ', '.join(list(fields['name']))
query = 'SELECT ' + field_list + '''
FROM ''' + obj
q = sf.query_all(query)
ds = (pd.DataFrame(q['records'])
      .drop(columns=['attributes']))
print(str(ds.shape) + ': records, fields')

ds.columns = 'ds_' + ds.columns
pd.DataFrame(ds.iloc[0].T)

desc = sf.DuplicateRecordItem.describe()
fields = []
field_labels = [field['label'] for field in desc['fields']]
field_names =  [field['name']  for field in desc['fields']]
field_types =  [field['type']  for field in desc['fields']]
for label, name, dtype in zip(field_labels, field_names, field_types):
    fields.append((label, name, dtype))
fields = pd.DataFrame(fields,
                      columns = ['label','name','type'])
obj = 'DuplicateRecordItem'
field_list = ', '.join(list(fields['name']))
query = 'SELECT ' + field_list + '''
FROM ''' + obj
q = sf.query_all(query)
di = (pd.DataFrame(q['records'])
      .drop(columns=['attributes']))
print(str(di.shape) + ': records, fields')

di.columns = 'di_' + di.columns
pd.DataFrame(di.iloc[0].T)

dups = pd.merge(ds[['ds_Id','ds_Name']], di,
                left_on='ds_Id',
                right_on='di_DuplicateRecordSetId',
                how='outer',
                indicator=True)
print(dups._merge.value_counts())
dups = dups.drop(columns=['_merge'])

dups['prefix'] = dups['di_RecordId'].str[:3]
print(dups['prefix'].value_counts())

dups = pd.merge(dups, xref,
                on='prefix',
                how='left')
(dups[['ds_Id','prefix','object']]
 .groupby(['prefix','object'])
 .count()
 .reset_index())