<jupyter_start><jupyter_text>**A Duck's Dream**  **Breathers on COVID-19 - Wrangling**<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import re
from google.colab import drive
drive.mount('/content/drive')
df_real = pd.read_csv("/content/drive/My Drive/A Duck's Dream/distribuicao_respiradores.csv", sep=';')
df = df_real.copy()
df.head()
df.info()
df['VALOR'] = df['VALOR'].str.replace(',','.')
df['VALOR'] = pd.to_numeric(df['VALOR'], errors='coerce')
df.tail()
df.info()
df_date = df['DATA'].str.contains('[0-9]{2}.[0-9]{2}.[0-9]{4}')
df_date = df_date.loc[df_date == False]
df_date
sel = df.iloc[[300, 837, 1503]]
sel
#One way to do it
df.at[837,'DATA'] = '30/04/2020'
df.at[1503,'DATA'] = '16/10/2020'
df_date = df['DATA DE ENTREGA'].str.contains('[0-9]{2}.[0-9]{2}.[0-9]{4}')
df_date = df_date.loc[df_date == False]
df_date
df_date = pd.to_datetime(df['DATA DE ENTREGA'], errors='coerce')
error = df_date.loc[df_date.isnull()]
error
df.loc[[837]]
df.at[837,'DATA DE ENTREGA'] = '01/05/2020'
#Create a dataframe that excludes our broken data for calculating delay
df_delay = df.copy()
df_delay = df_delay.drop([300],axis=0)
df_delay['DATA DE ENTREGA'] = pd.to_datetime(df_delay['DATA DE ENTREGA'], format="%d/%m/%Y")
df_delay['DATA'] = pd.to_datetime(df_delay['DATA'], format="%d/%m/%Y")
df_delay['DELAY'] = df_delay['DATA DE ENTREGA'] - df_delay['DATA']
sorted = df_delay.sort_values(by='DELAY')
sorted.head()
#Exclude the shitty columns 829 and 828
df_delay = df_delay.drop([828,829], axis=0)
df_delay.head()
df_delay.describe()
mapper = {"KTK/VAYIRE":"KTK","LEISTUNG/VYAIRE":"LEISTUNG","Leistung":"LEISTUNG","MAGNAMED-REQUISIÇÃO":"MAGNAMED",
          "MAGNAMED/REQUISIÇÃO":"MAGNAMED","SUZANO/RESMED":"SUZANO","SUZANO/Resmed":"SUZANO","SUZANO/Zhongxun Medical":"SUZANO",
          "VYAIRE LTV 1200 USA":"VYAIRE", "VYAIRE LTV 2200 USA":"VYAIRE"}

df['FORNECEDOR'] = df['FORNECEDOR'].replace(mapper)
df.tail()<jupyter_output><empty_output><jupyter_text>**Exploratory Data Analysis**<jupyter_code>breath_by_state = df.groupby(['DESTINO'])['QUANTIDADE'].sum()
sns.set(style="whitegrid")
sns.set_palette("pastel")
plt.figure(figsize=(13,7))
ax = sns.barplot(breath_by_state.index, breath_by_state)
ax.set_xticklabels(labels=breath_by_state.index, rotation=90)
mean_by_breather = df['VALOR'].sum()/df['QUANTIDADE'].sum()
print('Your average breather was acquired by {} reais'.format(round(mean_by_breather)))
group_by_quant = df.groupby(['DESTINO'])['QUANTIDADE'].sum()
group_by_value = df.groupby(['DESTINO'])['VALOR'].sum()
mean_by_state = pd.DataFrame(group_by_quant)
mean_by_state['VALOR'] = group_by_value
mean_by_state['MEAN'] = mean_by_state['VALOR']/mean_by_state['QUANTIDADE']
plt.figure(figsize=(12,7))
ax = sns.barplot(x=mean_by_state.index, y=mean_by_state['MEAN'])
ax.set_xticklabels(labels=breath_by_state.index, rotation=90)
df_delay.info()
total_state = df_delay.groupby(['DESTINO'])['DESTINO'].count()
delay_by_state = df_delay.groupby(['DESTINO'])['DELAY'].sum()
df_delay_by_delivery = pd.DataFrame(total_state)
df_delay_by_delivery['DELAY'] = delay_by_state
df_delay_by_delivery['MEAN-DELAY'] = df_delay_by_delivery['DELAY']/df_delay_by_delivery['DESTINO']
df_delay_by_delivery['MEAN-DELAY'] = pd.to_numeric(df_delay_by_delivery['MEAN-DELAY'])
sns.set(style="whitegrid")
sns.set_palette('colorblind')
plt.figure(figsize=(12,7))
ax = sns.barplot(x=df_delay_by_delivery.index, y=df_delay_by_delivery['MEAN-DELAY'])
ax.set_xticklabels(labels=breath_by_state.index, rotation=90)
breath_by_supp = df.groupby(['FORNECEDOR','DESTINO'])['QUANTIDADE'].sum()
cost_by_supp_state = df.groupby(['FORNECEDOR','DESTINO'])['VALOR'].sum()
df_bbs = pd.DataFrame(breath_by_supp)
df_bbs.to_html('temp.html')
df_bcss = pd.DataFrame(breath_by_supp)
df_bcss['VALOR'] = cost_by_supp_state
df_bcss['MEAN'] = df_bcss['VALOR']/df_bcss['QUANTIDADE']
df_bcss.to_html('cost_by_supp_by_state.html')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1913983903420523, 'fraction_numerical': 0.026911468812877263, 'mean_word_length': 8.469047619047618, 'pattern_counts': {'":': 10, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 0, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '384991', 'license': 'MIT', 'n_tokens_mistral': 1751, 'n_tokens_neox': 1676, 'n_words': 218, 'path': 'Mechanical_Ventilator_COVID_19_Notebook_Part_2.ipynb', 'repo_name': 'marcogemaque/COVID-19-Breathers', 'size': '3976'}