<jupyter_start><jupyter_text>[View in Colaboratory](https://colab.research.google.com/github/mrdeds/deep-learning-coursera/blob/master/frecuencia_de_n_grams.ipynb)<jupyter_code>!pip install psycopg2
!pip install stop_words
import psycopg2
import pandas as pd
import json
from nltk import everygrams, FreqDist
from nltk.tokenize import RegexpTokenizer
import numpy as np
from google.colab import files

uploaded = files.upload()

for fn in uploaded.keys():
  print('User uploaded file "{name}" with length {length} bytes'.format(
      name=fn, length=len(uploaded[fn])))
creds = uploaded.get('creds_data.json')
dic_creds = json.loads(creds.decode())
host_check=fn

host_wh=dic_creds.get('host_wh')
port_wh=dic_creds.get('port_wh')
user_wh=dic_creds.get('user_wh')
password_wh=dic_creds.get('password_wh')
database_wh=dic_creds.get('database_wh')

host_l=dic_creds.get('host_l')
port_l=dic_creds.get('port_l')
user_l=dic_creds.get('user_l')
password_l=dic_creds.get('password_l')
database_l=dic_creds.get('database_l')
def sql_to_dataframe(query, base='wh'):
  if base=='wh':
    conn = psycopg2.connect(host=host_wh, port=port_wh, user=user_wh, password=password_wh, database=database_wh,)
  elif base=='llamadas':
    conn = psycopg2.connect(host=host_l, port=port_l, user=user_l, password=password_l, database=database_l,)
  try:
    cursor = conn.cursor()
    cursor.execute(query)
    DataSQL = cursor.fetchall() # careful, the results could be huge
    conn.commit()
  finally:
    conn.close()
  
  colnames = [desc[0] for desc in cursor.description]
  DF = pd.DataFrame(DataSQL) # Ponemos datos en DataFrame
  DF.columns = colnames # Asignamos nombres a las columnas
  
  return DF
query = "SELECT rh.id AS \"ID RH\",\
    rh.name AS \"Nombre del RH\",\
    rh.numeroextension__c AS \"Número de extensión\",\
    RIGHT(lead.phone,8) AS \"phone\",\
    lead.id AS \"ID Lead\",\
    lead.rating as \"Calificación\"\
    FROM rh_all_recursoshumanos__c AS rh\
    JOIN salesforce_lead AS lead ON rh.ID=lead.vendedorasignado__c\
    WHERE rh.name not in ('Agente de Preventa','ARD Virtual') and phone not in ('','None')\
    GROUP BY rh.name, rh.id, lead.phone, rh.numeroextension__c, lead.id, lead.rating\
    ORDER BY rh.id ASC,\
    rh.numeroextension__c ASC"

salesforce_DF = sql_to_dataframe(query)
salesforce_DF.head(10)
len(salesforce_DF)
type(salesforce_DF)
query = "SELECT rh.id AS \"ID RH\",\
    rh.name AS \"Nombre del RH\",\
    rh.numeroextension__c AS \"Número de extensión\",\
    RIGHT(lead.mobilephone,8) AS \"phone\",\
    lead.id AS \"ID Lead\",\
    lead.rating as \"Calificación\"\
    FROM rh_all_recursoshumanos__c AS rh\
    JOIN salesforce_lead AS lead ON rh.ID=lead.vendedorasignado__c\
    WHERE rh.name not in ('Agente de Preventa','ARD Virtual')\
    and phone not in ('','None')\
    GROUP BY rh.name, rh.id, lead.mobilephone, rh.numeroextension__c, lead.id, lead.rating\
    ORDER BY rh.id ASC,\
    rh.numeroextension__c ASC"

salesforce_mobile_DF = sql_to_dataframe(query)
len(salesforce_mobile_DF)
query_llamadas = "SELECT id as \"id de llamada\",\
texto,\
llama AS extension,\
RIGHT(recibe,8) as \"phone\" \
FROM llamadas \
where texto != '' \
and llama::float BETWEEN 1470  and 1900\
GROUP BY id, texto, llama, recibe \
ORDER BY id asc, texto, llama, recibe"

llamadas_DF = sql_to_dataframe(query_llamadas, base="llamadas")
llamadas_DF.head(10)
len(llamadas_DF.index)
DF_fijo = pd.merge(salesforce_DF,llamadas_DF, on='phone')
DF_mobile = pd.merge(salesforce_mobile_DF, llamadas_DF, on='phone')
DF = pd.concat([DF_mobile, DF_fijo])
len(DF)
Cerrados = DF.loc[DF['calificación'] == 'Cerrado']
Cerrados_fijo = DF_fijo.loc[DF_fijo['calificación'] == 'Cerrado']
Cerrados_mobile = DF_mobile.loc[DF_mobile['calificación'] == 'Cerrado']
ratio = len(Cerrados)/len(DF)
ratio_fijo = len(Cerrados_fijo)/len(DF_fijo)
ratio_mobile = len(Cerrados_mobile)/len(DF_mobile)

print('ratio de cerrados a no cerrados total: {}'.format(ratio))
print('ratio de cerrados a no cerrados fijo: {}'.format(ratio_fijo))
print('ratio de cerrados a no cerrados mobile: {}'.format(ratio_mobile))
print('leads totales:{}'.format(len(DF_mobile)))
tokenizer = RegexpTokenizer('[A-zÀ-ÿ]\w+')
textos = DF["texto"]
DF["tokens"] = DF["texto"].apply(tokenizer.tokenize)
from stop_words import get_stop_words

stop_words = get_stop_words('spanish')
X_prev = DF['tokens'].values
lista_frase_limpia = []
lista_texto_limpio = []
for lista in X_prev:
    frase_limpia = [palabra for palabra in lista if palabra not in stop_words]
    texto_limpio =  ' '.join(frase_limpia)
    lista_frase_limpia.append(frase_limpia)

    lista_texto_limpio.append(texto_limpio)

DF['clean tokens'] = lista_frase_limpia 
DF['clean text'] = lista_texto_limpio

lista_ngrams = []

for textos in DF['clean tokens']:
  ngrams = list(everygrams(textos, 4, 6))
  lista_ngrams.append(ngrams)
DF['ngrams'] = lista_ngrams
DF=DF.drop(DF[DF.ngrams.map(len) <= 2].index)
n_grams = DF[(DF.calificación == 'Cerrado') & (DF.ngrams != '[]') & (~DF["clean text"].str.contains('mensaje después tono')) ]["ngrams"]
todos_ngrams = [ngram for ngrams in n_grams for ngram in ngrams]
ngrams_nocomunes = [str(i) for i in todos_ngrams if "\'buenas\', \'tardes\'" not in (str(i).lower()) and "\'buenos\', \'días\'" not in (str(i).lower()) and "\'bueno\', \'bueno\', \'bueno\'" not in (str(i).lower())
                   and "\'tecla\', \'gato\'" not in (str(i).lower()) and "\'cómo\', \'encuentras\'" not in (str(i).lower()) and "'calidad\'" not in (str(i).lower()) and "\'cómo\', \'encuentras\'" not in (str(i).lower())
                   and "\'cavazos\'" not in (str(i).lower()) and "\'hola\', \'qué\', \'tal\'" not in (str(i).lower()) and "\'encuentran\', \'ocupados\'" not in (str(i).lower())] 
from collections import Counter

counter = Counter(ngrams_nocomunes)

kk = [list(counter.keys()),list(counter.values())]

freq_ngrams_nocomunes = pd.DataFrame(np.array(kk).T, columns=['N-Gram','Count']).sort_values(by=['Count'] , ascending=False)
res = freq_ngrams_nocomunes[freq_ngrams_nocomunes["Count"].astype(int)>20]
res["Count"] = res["Count"].astype(int)
res.sort_values(by=["Count"], ascending=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15920478422498788, 'fraction_numerical': 0.0040407305640859864, 'mean_word_length': 4.990319457889642, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '629727', 'license': 'MIT', 'n_tokens_mistral': 2455, 'n_tokens_neox': 2368, 'n_words': 506, 'path': 'frecuencia_de_n_grams.ipynb', 'repo_name': 'mrdeds/deep-learning-coursera', 'size': '6187'}