# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Exploratory Data Analysis

import pandas as pd
import numpy as np

# there are three main files:
# opportunities
# quotes 
# leads
#
# leads is for testing purposes
#

##############################
# load opportunities
opportunities = pd.read_csv('opportunities.csv')

opportunities.shape

opportunities.columns

opportunities.head(10)

opportunities.Stage.value_counts()

# 1686 with no address
opportunities[opportunities['Mailing Address Line 1'].isnull()].shape

opportunities[opportunities['Billing Address Line 1'].isnull() == False].head()



opportunities[opportunities['Mailing Address Line 1'].isnull()
              ].Stage.value_counts()

opportunities[opportunities['Mailing Address Line 1'].isnull() ==
              False].Stage.value_counts()

#####################################
# load quotes
quotes = pd.read_csv('quotes.csv')

quotes.shape

quotes.head()

quotes.Stage.value_counts()

quotes[quotes['Bill To Street'].isnull() == False]





quotes.columns

#########################################
# How many Opportunity records are in quote?
opportunities.merge(quotes, on='Opportunity ID', how='inner').shape

# look at the matching records, see if they make sense
opportunities.merge(quotes, on='Opportunity ID')
# looks ok, but a surprising number have

quotes[quotes['Opportunity ID'] == '0061a00000G3jSG']

# +
# what about the ones without addresses
# -

opp_no_address = opportunities[opportunities['Billing Address Line 1'].isnull(
)]

opp_no_address.shape

opp_no_address.merge(quotes, on='Opportunity ID').shape

# total missing addresses:
print(2499 - 1322 + 230)

################################
# load leads
leads = pd.read_csv('leads.csv')

leads.head()

leads.shape

leads[leads['Street'].isnull()]

leads['Lead Status'].value_counts().head(8)

leads['Lead Source'].value_counts()

leads.columns

quotes.columns

# +
########################
# merge quotes and opportunities
################################
# -

# concatenate quote address
quotes['Address'] = quotes['Bill To Street']+' '+quotes['Bill To City']+' ' + \
    quotes['Bill To Country']+' '+quotes['Bill To State/Province'] + \
    ' '+quotes['Bill To Zip/Postal Code']

# +
# quotes.head()
# -

opportunities.columns



# concatenate opportunities address
# opportunities['']+' '+
# not using this because we don't want to create incomplete addresses
#opportunities['Billing Address Line 1']+' '+opportunities['Billing Address Line 2']+' '+opportunities['Billing Address Line 3']
opportunities['Address'] = np.nan

# +
# opportunities.head()

# +
# change both data frames to have the same column names
# new columns = id, name, close_date, stage, address
# -

new_cols = ['id', 'name', 'close_date', 'stage', 'address']

opp = opportunities[['Opportunity ID',
                     'Opportunity Name', 'Close Date', 'Stage', 'Address']]

opp.columns = new_cols

# +
# opp.head()
# -

quo = quotes[['Opportunity ID', 'Opportunity Name',
              'Close Date', 'Stage', 'Address']]

quo.columns = new_cols


# +
# quo.head()
# -

# from opp, remove records that are in quo
def in_quo(id):
    if id in quo.id.values:
        return True
    else:
        return False


opp.id.apply(in_quo).value_counts()

opp2 = opp[opp.id.apply(in_quo) == False]

# +
# opp2.head()
# -

# append the two dataframes
df = quo.append(opp2, ignore_index=True)

df.shape

# +
# check for duplicates
# -

df.id.value_counts().head(10)

df[df.id == '0061a00000KN2II']

df.shape

df.drop_duplicates().shape

df.drop_duplicates(inplace=True)

df.shape

# +
# about df.id.value_counts().head(100)   #about 90 records w/ value count > 1
# -

df.drop_duplicates(subset=['id'], keep='last', inplace=True)

df.shape

print('total:', df.shape[0])
print('with full address:', df[df['address'].isnull() == False].shape[0])
print('missing/incomplete addresses:',
      df[df['address'].isnull() == True].shape[0])

leads[leads['Lead ID'] == '0061a00000GFPd7']

StarcoderdataJupyterScriptsDedupFiltered