Integre R, Tabela y Excel

Conteúdo

Este artigo foi publicado como parte do Data Science Blogathon.

Introdução

Neste artigo, Vou mostrar como podemos executar uma análise de regressão e otimizar o preço do aluguel em R, em seguida, cole o valor no excel, que mais tarde se conectará ao Tableau novamente para realizar cálculos adicionais com outros campos no conjunto de dados original.

Este tipo de integração perfeita entre 3 Diferentes ferramentas de análise podem ajudar os analistas de dados a realizar investigações estatísticas em R, em seguida, migre os resultados para o Tableau e visualize-os de uma forma compreensível para leitores empresariais.

Introdução ao conjunto de dados usado e requisitos de negócios

O conjunto de dados para este exemplo é retirado do projeto Capstone dentro “Excel a MySQL: Técnicas analíticas para empresas”. Esta é uma tabela que contém informações sobre os imóveis para locação de uma empresa, com informações sobre a taxa de ocupação de aluguel de curto prazo e o preço médio de aluguel por noite. Também temos dados sobre o preço no percentil 10 e percentil 90 de propriedades semelhantes na mesma região.

Nosso requisito comercial é encontrar um preço otimizado para cada propriedade, para que a receita possa ser maximizada. Uma vez que a renda é uma função da taxa de ocupação * preço do aluguel por noite * 365 (presumindo que a propriedade pode ser alugada durante todo o ano), precisamos expressar a taxa de ocupação em função do preço do aluguel por noite, que pode ser feito por regressão linear simples

A próxima tarefa é executar a função R optim, assim como usamos o Solver no Excel, para cada propriedade ou cada linha no conjunto de dados.

Com o preço otimizado e a taxa de ocupação esperada, podemos calcular o lucro bruto total da empresa e fazer muitas outras análises.

Conecte R com Tableau

26593captura-5554563

Primeiro, devemos conectar o Tableau com R.

Antes de conectar R com Tableau, certifique-se de que seu console R já tenha instalado o Rserve.

library("Rserve")

Rserve()

Agora, R deve imprimir ‘Iniciando Rserve …’. Se você ver este resultado, então R está se comunicando com o Tableau para estabelecer uma conexão.

2) Abra o Tableau e clique em Ajuda> Configuração e desempenho> Gerenciar conexões de serviço externas.
3) En el cuadro de diálogo que se abre, elija ‘localhostpara Servidor y escriba ‘6311para Puerto.
4) Mais tarde, haga clic en Probar conexión

Agora, debería aparecer un cuadro de diálogo que diga: ‘Conectado con éxito al servicio R serve’. Significa que está listo para usar R con Tableau

Cree un campo calculado que ejecute código R en Tableau

Cree un campo calculado y pegue el siguiente código:

SCRIPT_REAL(
"df <- quadro de dados(.arg1,.arg2,.arg3,.arg4,.arg5)

modelo <-lm(data = df,.arg1 ~ .arg2)

Create revenue function. 
revenue <- função(dados,Através dos) {
par_vs_10th <- par-data$.arg3
normalized_price <-0.1+0.8*par_vs_10th/data$.arg5
fcst_occupancy <-Coeficiente(modelo)['(Interceptar)']+Coeficiente(modelo)['.arg2']*normalized_price
fcst_st_revenue <-fcst_occupancy*365*par
fcst_st_revenue
}

Run optim for each row in df. Find the value of "Através dos"-rent price-that can optimize revenue function
para (eu em 1:agora(df)) 
{df[eu,'optimized_price'] <-ótimo(122,revenue,data = df[eu,],method='L-BFGS-B', control=list(fnscale=-1),lower=df[eu,'.arg3']) }

#retornar o preço otimizado como saída para o campo calculado
df $ optimized_price",

soma([Taxa de ocupação]),
média([sample_price_percentile]),
média([Preço Percentil10]),
média([Preço do percentil 90]),
média([percentile_90th_vs_10th]),
atr([Ws Property Id]))

O código R deve ser escrito em uma função como SCRIPT_REAL, que retorna valores numéricos. Existem outras funções R semelhantes no Tableau, como SCRIPT_BOOL e SCRIPT_INT, com base nos valores que você deseja recuperar.

Antes de correr, devemos criar uma mesa: df <-quadro de dados (.arg1, .arg2, ...)

.arg1, .arg2… são os campos de fonte de dados no Tableau. São as palavras em negrito do código. .arg1 é a taxa de ocupação, .arg2 es el sample_price_percentile.

O R_code será colocado entre colchetes (”“). A última linha de código: df $ optim_price irá determinar o valor de retorno para este cálculo.

Para uma explicação detalhada sobre como executar a regressão linear e ótima em R, veja o link abaixo:

https://nguyenkhoi6394.github.io/Excel-to-MySQL-Capstone-Project/Using_Optim_for_a_dataframe_in_R.html

Este cálculo é um cálculo de tabela. Certifique-se de que é calculado junto com o ID da propriedade.

89327pic1-3291782

Creemos una vista para ver esta medir.

46025pic2-7680083

Agora otimizamos o preço de cada propriedade.

Porém, agora ocorre um problema. Esta medida é um cálculo de tabela e só podemos ter um único valor por propriedade quando olhamos para ela em uma tabela. Não podemos incorporá-lo em outro cálculo.

Por exemplo, Quero normalizar o preço otimizado para um valor percentual usando a seguinte fórmula:

0,1 + 0,8 * (preço otimizado - preço do 10º percentil) / (90º percentil vs. 10º)

Tableau irá gerar um erro, dizendo que não podemos misturar uma medida agregada com um valor não agregado. Isso é realmente inconveniente e inflexível, pois podemos querer tirar proveito de um cálculo codificado por R para muito mais medições.

81229pic3-1992674

Para mitigar este problema, Eu vim com uma solução alternativa: escrever valores otimizados de R em arquivo csv ou excel, luego una este nuevo conjunto de datos con la Fonte de dados original en Tableau para la creación de otras visualizaciones o medidas.

Integração final

Vamos criar outro campo de cálculo no Tableau, chamado Script. Desta vez, não retornaremos um valor numérico, mas vamos escrever as saídas em um arquivo CSV externo. No meu exemplo, Escrevo em CSV para simplificar, mas você também pode gravar no arquivo xlsx se preferir.

SCRIPT_REAL(
"df <- quadro de dados(.arg1,.arg2,.arg3,.arg4,.arg5)
 modelo <-lm(data = df,.arg1 ~ .arg2)

revenue <- função(dados,Através dos){
 par_vs_10th <- par-data $ .arg3
 normalized_price <-0.1+0.8*par_vs_10th / data $ .arg5
 fcst_occupancy <-Coeficiente(modelo)['(Interceptar)']+Coeficiente(modelo)['.arg2']*normalized_price
 fcst_st_revenue <-fcst_occupancy * 365 * par
 fcst_st_revenue
 }

 para (eu em 1:agora(df)) {df[eu,'optimized_price'] <-ótimo(122,revenue,data = df[eu,],method='L-BFGS-B', control=list(fnscale=-1),lower=df[eu,'.arg3']) }
df $ normalized_optimized_price<-0.1+0.8*(df $ optimized_price-df $ .arg3)/(df $ .arg5)

#Crie um novo dataframe, substituindo .arg2(sample_percentile_price) com o preço normalizado otimizado
novo <-quadro de dados(.arg2 = df $ normalized_optimized_price)

#Preveja a taxa de ocupação com base no preço otimizado e adicione como uma nova coluna ao df
df['Previsão de ocupação']= Predict.lm(modelo, newdata = new)

#Adicionar ID de propriedade ao df
df['Ws Property Id']= .arg6

#Grava df em um arquivo csv
 write.table(df,'D:/Documentos / Business Analytics / 4. Visualização / Business Capstone / Blogathon / new.csv ',sep = ',',row.names = FALSE,citação = FALSE,col.names = TRUE)
 ",
soma([Taxa de ocupação]),
média([sample_price_percentile]),
média([Preço do percentil 10]),
média([Preço do percentil 90]),
média([percentile_90th_vs_10th]),
atr([Ws Property Id]))

A próxima etapa é criar uma nova planilha, Chamada Folha 2, por exemplo. A seguir, arraste o ID da propriedade e a medida do script para Detalhe no cartão de marca.

Você deve ver uma mensagem como a seguinte:

59814pic4-2654978

Apenas ignore essa mensagem de erro. Abra a pasta que você especificou no cálculo do script e você verá que um novo arquivo CSV acabou de ser criado.

20200pic5-2674267
87604pic6-7481776

Nossa próxima tarefa é mais simples, basta conectar a pasta de trabalho do Tableau com este arquivo csv e mesclá-lo com a fonte de dados original, de acordo com a chave estrangeira: ID de propriedade WS.

71228pic7-8411117

Agora, no Painel De dados, um novo conjunto de dados está disponível para uso.

80811pic8-6387792

Uma vez que temos o preço otimizado e a taxa de ocupação esperada como campos normais, podemos usá-los para cálculos adicionais sem problemas relacionados com o nível adicionado como acima.

Suponha que eu queira criar uma medida chamada Receita Bruta = Preço Otimizado * Taxa de ocupação * 365. O cálculo agora é válido.

74361pic9-9876650

No futuro, en caso de que haya cambios en los datos de Treinamento (sinal de preço por noite), ou se você adicionar mais funções ao modelo linear. Basta abrir a planilha 2 novamente para reativar o processo e recuperar novos resultados.

Notas finais

A capacidade de escrever código R em um cálculo torna o Tableau mais flexível do que seu rival, Power BI, em termos de conexão com plataformas externas de análise de dados. Ao combinar o Tableau, Excel e R, podemos usar o poder de muitas ferramentas simultaneamente para nossas práticas analíticas.

Tenha outras ideias e casos de uso relacionados ao uso de Python e R no Tableau? Não hesite em comentar este artigo..

Assine a nossa newsletter

Nós não enviaremos SPAM para você. Nós odiamos isso tanto quanto você.