Aula 07a -- Preparo e Limpeza de Dados -- PUC MDT Ciência de Dados 2021 / Dan S. Reznik

Data Analytics Consultant, Instructor. Areas: Data Analytics, Geometry, Simulation, Visualization, Computer Vision, Robotics...

Workspace

Published

PUC MDT Data Science 2021

Edited

Jun 14, 2021

6 forks

viewof dt_clients = aq.fromCSV(await FileAttachment('dt_clients.csv').text())

.view({height:240,limit:200})

dt_clients.numRows()

viewof count_sexo = dt_clients

.groupby('Sexo')

.count()

.orderby(aq.desc('count'))

.view({height:240})

viewof ref_sexo = aq.table({Sexo_Lower:['m','f','homem','masculino','masc.','mulher','feminino'],

Sexo_Ref: ['M','F','M','M','M','F','F']})

.view({height:240})

count_sexo

.derive({Sexo_Lower:d=>op.lower(d.Sexo)})

.join_left(ref_sexo)

.select(aq.not('Sexo_Lower'))

.view()

viewof dt_clients_sex_clean = dt_clients

.derive({Sexo_Lower:d=>op.lower(d.Sexo)})

.join_left(ref_sexo)

.select(aq.not('Sexo_Lower'))

.rename({Sexo_Ref:'Sexo_Clean'})

.relocate('Sexo_Clean',{after:'Sexo'})

.view({height:240,limit:100})

dt_clients_sex_clean

.groupby('Sexo_Clean')

.count()

.view({height:240})

viewof dt_estados_count = dt_clients

.groupby('estado')

.count()

.orderby(aq.desc('count'))

.view({height:400})

viewof ref_estados = aq.fromCSV(await FileAttachment('ref_estados.csv').text())

.view({height:240})

ref_estados.numRows()

dt_clients

.antijoin(ref_estados)

.view({height:240})

viewof dt_clients_clean_estado= dt_clients

.semijoin(ref_estados)

.view({height:240})

dt_clients_clean_estado

.groupby('estado')

.count()

.view({height:240})

alphanum_mask('Anderson')

alphanum_mask('Joselino1344')

alphanum_mask('Bem-vindo a 2a bienal do livro, Paraty 2021')

viewof dt_clients_sobrenome_mask = dt_clients

.derive({Sobrenome_Mask:aq.escape(d => alphanum_mask(d.Sobrenome))},{after:'Nome'})

.view({height:240})

viewof count_sobrenomes = dt_clients_sobrenome_mask

.groupby('Sobrenome_Mask')

.count()

.orderby(aq.desc('count'))

.view()

containsNonAaSpc=function(str) {

return /[^Aa ]/.test(str);

}

containsNonAaSpc("Aaaaa *")

dt_clients_sobrenome_mask

.filter(aq.escape(d=>containsNonAaSpc(d.Sobrenome_Mask)))

.view({height:240})

viewof dt_clients_sobrenome_mask_clean = dt_clients_sobrenome_mask

.filter(aq.escape(d=>!containsNonAaSpc(d.Sobrenome_Mask)))

.view({height:240})

dt_clients_sobrenome_mask_clean

.groupby('Sobrenome_Mask')

.count()

.orderby(aq.desc('count'))

.view()

dt_clients

.derive({nascimento:aq.escape(d => alphanum_mask(d.nascimento))}) //.toISOString().slice(0,10)))})

.groupby('nascimento')

.count()

.orderby(aq.desc('count'))

.view()

dt_clients

.derive({cell:aq.escape(d => alphanum_mask(d.cell))})

.groupby('cell')

.count()

.orderby(aq.desc('count'))

.view()

cpf1='111-222-333-00'

cpf.validar(cpf1)

cpf1.length

cpf2='222.302.853-51'

cpf.validar(cpf2)

remove_non_digits(cpf1)

remove_non_digits(cpf1).length

dt_clients

.derive({cpf_valid:aq.escape(d=>cpf.validar(d.cpf))})

.groupby('cpf_valid')

.count()

.view()

viewof ref_cidades = aq.fromCSV(await FileAttachment('ref_cidades.csv').text())

.view({height:240})

reformata_data=function(str) {

return str.replace(/(\d{4})-(\d{2})-(\d{2})/,'$3-$2-$1');

}

reformata_data('1981-03-22')

remove_non_digits = function(str) {

return str.replace(/\D/g,'');

}

insere_tracinho=function(nine_digs) {

return `${nine_digs.slice(0,5)}-${nine_digs.slice(-4)}`;

}

alphanum_mask = function(s) {

return (s===null?s:remove_accents(s).replace(/\d/g,'9')

.replace(/[a-z]/g,'a')

.replace(/[A-Z]/g,'A'))

}

remove_accents = function(strAccents) {

var strAccents = strAccents.split('');

var strAccentsOut = new Array();

var strAccentsLen = strAccents.length;

var accents = "ÀÁÂÃÄÅàáâãäåÒÓÔÕÕÖØòóôõöøÈÉÊËèéêëðÇçÐÌÍÎÏìíîïÙÚÛÜùúûüÑñŠšŸÿýŽž";

var accentsOut = "AAAAAAaaaaaaOOOOOOOooooooEEEEeeeeeCcDIIIIiiiiUUUUuuuuNnSsYyyZz";

for (var y = 0; y < strAccentsLen; y++) {

if (accents.indexOf(strAccents[y]) != -1) {

strAccentsOut[y] = accentsOut.substr(accents.indexOf(strAccents[y]), 1);

} else

strAccentsOut[y] = strAccents[y];

}

strAccentsOut = strAccentsOut.join('');

return strAccentsOut;

}

'Rio' + ' de ' + 'Janeiro'

"São ".concat("Paulo")

`O quadrado de 7 é ${7*7}`

str_fla.length

str_fla.replace("flamengo", "vasco")

str_flu.replace("flamengo", "vasco")

str_fla.replace(/flamengo|fluminense/, "vasco")

str_flu.replace(/flamengo|fluminense/, "vasco")

str_flu2.replace(/flamengo|fluminense/, "vasco")

str_flu2.replace(/flamengo|fluminense/i, "vasco")

str_flu2.toLowerCase().replace(/flamengo|fluminense/, "vasco")

[str_fla,str_flu,str_ame].map(s=>s.replace(/flamengo|fluminense|américa/, "vasco"))

[str_fla,str_flu,str_ame,str_bot].map(s=>s.replace(/flamengo|fluminense|américa/, "vasco"))

str_fla

str_fla.replace(/do .*$/,"do vasco")

str_fla.replace(/^eu.*do/,"quem é")

str_fla.replace(/^.*do/,"quem é")

str_fla.replace(/^.*?do/,"quem é")

[str_fla,str_flu,str_ame,str_bot].map(s=>s.replace(/do .*$/i,"do vasco"))

phone1='+55(21)98544-3322'

remove_non_digits(phone1)

[phone1.length, remove_non_digits(phone1).length]

ddd1='21'

cell1='987653211'

ddd1+cell1

'('+ddd1+')'+cell1

cell1.slice(0,5)

cell1.slice(-4) // poderia ser cell1.slice(5,9)

cell1.slice(0,5)+'-'+cell1.slice(-4)

'('+ddd1+')'+cell1.slice(0,5)+'-'+cell1.slice(-4)

`(${ddd1})${cell1.slice(0,5)}-${cell1.slice(-4)}`

`(${ddd1})${insere_tracinho(cell1)}`

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.

Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.

Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.

Learn more