# ---
# 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
# ---

# ## Bibliotecas

import numpy as np
import pandas as pd
import seaborn as sns
import missingno as msno
import matplotlib.pyplot as plt
import datetime as dt

# ## 1º Df - Customers

df = pd.read_csv('customers_case.csv')

df.sample(10)

df.info()

# ## Categorizando de forma correta as variáveis:

df.columns = df.columns.str.replace(' ', '_')

df.columns

# ### Datas

df.statusUpdatedAt  = pd.to_datetime(df.statusUpdatedAt)
df.activation_date = pd.to_datetime(df.activation_date)
df.end_portability_date = pd.to_datetime(df.end_portability_date)

# ### Nums

# +
# Linhas sem valor estão erradas;

df.loc[df.age == '#VALOR!'] = np.nan
# -

df.info()

# ## Valores Nulos

msno.matrix(df)

df.statusPrevious.value_counts()

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df['statusPrevious'].value_counts().head(10).index ,y = df['statusPrevious'].value_counts().head(10)).set_title('Picture of clients status');
plt.xticks(rotation=90);

# **Estão em churn: cancelRequested | paymentFailedSubscription | paused | canceled**

# ### Qual é meu dataframe que possui os casos de Churn ? 

df.loc[df['statusPrevious'].isin(['cancelRequested','paymentFailedSubscription','paused','canceled'])]

df['clients_duration']=np.nan

df['clients_duration'].loc[df['statusPrevious'].isin(['cancelRequested','paymentFailedSubscription','paused','canceled'])] = (df.statusUpdatedAt.dt.year - df.activation_date.dt.year) * 12 + (df.statusUpdatedAt.dt.month - df.activation_date.dt.month)

df.sample(5)

# ## Df com base de clientes que cancelaram ou em processo de.... Criando Df_cc

df_cc=df.loc[df['statusPrevious'].isin(['cancelRequested','paymentFailedSubscription','paused','canceled'])].copy()

df_cc.sample(10)

# ### Analisando Distribuições dos Clientes que cancelaram:

df_cc.columns

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df_cc['gender'].value_counts().head(10).index ,y = df_cc['gender'].value_counts().head(10)).set_title('Gênero em relação a cancelamentos');
plt.xticks(rotation=90);

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df_cc['status'].value_counts().head(10).index ,y = df_cc['status'].value_counts().head(10)).set_title('Picture of clients status');
plt.xticks(rotation=90);

fig, ax = plt.subplots(figsize=(10,10))
sns.barplot(x=df_cc['statusUpdatedAt'].dt.month.value_counts(normalize=True).index ,y = df_cc['statusUpdatedAt'].dt.month.value_counts(normalize=True)*100).set_title('% de Churn x Meses do Ano');
plt.xticks(rotation=90);

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df_cc['state'].value_counts().head(10).index ,y = df_cc['state'].value_counts().head(10)).set_title('Picture of clients status');
plt.xticks(rotation=90);

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df_cc['recurrence'].value_counts().head(10).index ,y = df_cc['recurrence'].value_counts().head(10)).set_title('Picture of clients status');
plt.xticks(rotation=90);

df_cc['recurrence'].value_counts(normalize=True)*100

fig = plt.subplots( figsize=(10, 10))
sns.histplot(df_cc['clients_duration'],binwidth=1).set_title('Idade como cliente quando resolve cancelar');

df.clients_duration.value_counts(normalize=True)*100

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df_cc['current_package'].value_counts().head(10).index ,y = df_cc['current_package'].value_counts().head(10)).set_title('Picture of clients status');
plt.xticks(rotation=90);

df_cc['current_package'].value_counts(normalize=True)*100

df_cc.sample(10)

# ### Coluna com main_line - categorical

df_cc['end_portability_date'][1322]

df_cc['main_line']=0

df_cc['main_line'].loc[df_cc['end_portability_date'].isnull()] = 'No'

df_cc['main_line'].loc[df_cc['end_portability_date'].notnull()] = 'Yes'

df_cc.to_csv('customers_churn.csv',index=False)

# ## Linha principal

fig = plt.subplots( figsize=(10, 10))
sns.barplot(x=df_cc['main_line'].value_counts().head(10).index ,y = df_cc['main_line'].value_counts().head(10)).set_title('Picture of clients status');
plt.xticks(rotation=90);

# ## 2º Df orders.

df2 = pd.read_csv('orders_case.csv')

df2.sample(10)

df2.info()

# ### Datas

df2.created_at  = pd.to_datetime(df2.created_at)

# ## Valores Nulos

msno.matrix(df2)

# ## Informações relevantes

dft = df.merge(df2,how='left', left_on='uid', right_on='customer_id')

dft.info()

dft.sample(5)

dft.uid.value_counts()

# Diferentes clientes 5129!



dft_g = dft.groupby("uid")

dft_g.shape()

StarcoderdataJupyterScriptsDedupFiltered