Connect Tableau to An Excel File

tableau connect excel file geographic map

I have just enrolled in a Data Science course on Udemy  and I learned good stuff.

Now that you downloaded the dataset in Excel file format, we’ll use Tableau to analyze this.

We’ll connect to the dataset using the « Excel » option.

Now that you downloaded the dataset which is in Excel format, we will use Tableau to analyze this.

We will connect the the dataset using the « Excel » option.

tableau connect excel file geographic map

Select the dataset in Excel file you downloaded and click on the « Open » button.

tableau connect excel file geographic map

And as you can see, there is only one tab.

tableau connect excel file geographic map

There is only one tab because in the Excel file there is only one tab. If in the Excel file there were several tabs, they would all have been listed here.

tableau connect excel file geographic map

It’s necessary to check that all data is « OK ». Scroll the lines and columns to see that. Everything is good, there are 10 000 lines as in the Excel file.

tableau connect excel file geographic map

Excellent, we connected our Excel source file to Tableau.

Now, click on the « Sheet1 » tab to access the Worksheet.

tableau connect excel file geographic map

tableau connect excel file geographic map

We’ll have a little fun.

For example, let’s look at what we have with « Geography »

tableau connect excel file geographic map

« Geography » is the dimension that gives us the country, so we’ll make a map to see where the clients from the bank come from.

Move « Geography » on this area.

tableau connect excel file geographic map

tableau connect excel file geographic map

Ah, it’s odd, nothing happens ?!? Why ? Look, when you look at « Geography », it’s not recognized by Tableau as a geographic dimension. Here,, you can see that Tableau recognized « Geography » as a dimension of type text with the label « ABC »

tableau connect excel file geographic map

Don’t worry, we can fix it quickly. Click on the arrow of « Geography ».

tableau connect excel file geographic map

Selects « Geography Roles » and « Country Region » so that the « Geography » dimension become geography’s type.

tableau connect excel file geographic map

Now you remove « Geography » made a table with a click-and-drag.

tableau connect excel file geographic map

tableau connect excel file geographic map

Look, we have a globe next to « Geography ». This means that Tableau recognize that « Geography » is a geographic dimension.

tableau connect excel file geographic map

Since « Geography » is a dimension of geography type, there are 2 new measures that have appeared : Latitude (generated) and Longitude (generated).

tableau connect excel file geographic map

Put « Geography » in this space with a click and drag.

tableau connect excel file geographic map

Look, this time there is a map.

tableau connect excel file geographic map

You have the possibility of zooming with these buttons.

tableau connect excel file geographic map

The map is fine but we’ll remove the blue dots and modify the map so that it’s easier to read.

We’ll color the countries and display the clients number that has in each country.

We know that in the dataset each line corresponds to a client. What we can do is use the « number Of Record », it means the total of number of observations. In this way, we can visualize the number of lines attended to each country and the number of lines attended to each country is the number of client per country.

Then, take the « number Of Record » and move it to « Colors ».

tableau connect excel file geographic map

Boom ! Each country has a color.

tableau connect excel file geographic map

Look at the color contrasts. France has a darker color which indicates that it is the country with the most clients. Germany and Spain have almost the same colors which indicates that they have almost the same clients number.

But we want to know the clients number per country without have the cursor on the country.

To do this we’ll add a label. Take « number Of Record » and moves it to « Label ».

tableau connect excel file geographic map

tableau connect excel file geographic map

We’ll increase the text’s size and put in bold. Click on « Label », click on « Font » and select « 12 » and bold.

tableau connect excel file geographic map

It’s cool, we can see the clients number per country. You have the possibility to zoom on a region. Click on « Zoom area » and drag and drag to select the region on the map.

tableau connect excel file geographic map

tableau connect excel file geographic map

Now we can see that the majority of clients are in France, this represents almost half of the total clients number of the dataset. Germany and Spain have almost the same number of clients.

Share this article if you think it can help someone you know. Thank you.

-Steph

Please follow, like and share:

Dataset For Data Mining

dataset data mining

I have just enrolled in a Data Science course on Udemy  and I learned good stuff.

To have the dataset to do Data Mining, you need to go to the superdatascience website . In « Part.1 Visualization », you see the section « How to use Tableau for Data Mining ». Click on « Churn Modeling » to download the file.

dataset data mining

Once you have downloaded the file, move the file to the directory you created for the course. In this directory, create a new directory (unless you already do it) named « 2.Chunk investigation ».

dataset data mining

dataset data mining

Open this fiel with Excel or with other spreadsheet software.

dataset data mining

Know that we use this dataset for the visualization part but we will also use this dataset for the modeling part.

Let’s analyze the data of this dataset.

This dataset is quite large because it contains 10 000 lines and a few columns. This is the list of a bank’s client. The client information is :

  • Customer id (login)

  • Surname (last name)

  • Credit score ( is the measure that indicates the client’s ability to borrow)

  • Geography (client’s country)

  • Gender (male or female)

  • Age

  • Tenure -(the number of years the client is in the bank)

  • Balance (balance of the client’s bank account)

  • NumOfProduct (number of product that the client has in the bank – credit card, contract, account)

  • HasCrCard (does the client have a credit card ?)

  • IsActiveMember (did the client use his/her credit card during the last month ?)

  • EstimatedSalary (the bank’s estimate of the client’s annual salary)

  • Exited (did the client leave the bank ?)

Now, I will explain the context related to this dataset. This bank has branches in several countries like Germany, Spain and France. This bank noticed that lately there were many clients who left the bank. The bank has a report called « churn rate » which is the customers rate who leave the bank and for a few months the « churn rate » is really higher than usual. It’s for this reason that the bank needs a data scientist (you) to find the problem and propose solutions.

This dataset is a small sample of clients bank. These are 10 000 randomly selected client.

The column « Exited » is a column that didn’t exist before. This column has created when the bank realized that there was an abnormal number of client who were leaving the bank.

dataset data mining

Then the bank observed these clients for 6 months to see which client left the bank.

dataset data mining

In the « Exited » column, the number « 1 » means that the client left the bank and the number « 0 » means that the client stayed in the bank.

To analyze this dataset, you’ll need to do A/B Tests. For exemple, a classic A/B Test is to see if women are more likely to left the bank than men. That’s means, see the number of men who left the bank, see the number of women who left the bank and then normalize by the total number of clients. It’s important to normalize the number of clients because there are not the same proportions of women as men. Next, based on the last column « Exited », you’ll find out if it’s the men or women who are likely to left the bank.

Once you have relevant results, you can show your report to the bank. And with this report you should be able to propose solutions to the bank. For example, if the report says that women leave the bank in bulk, it’s because there is a problem and it’s necessary to see whether the bank is offering women something right. Or it’s possible that another bank offers a much more attractive offer for women or something else.

You will learn how to investigate in the dataset and find answer through client information with A/B tests.

Share this article if you think it can help someone you know. Thank you.

-Steph

Please follow, like and share:

Explanation Of The Challenge And Data

challenge

I have just enrolled in a Data Science course on Udemy  and I learned good stuff.

This is the 1st challenge. Use your web browser to go to superdatascience website  to download a data set. In the « Part 1 Visualization » section, click on « Offices Supplies » to download the file « OfficeSupplies.csv ».

data csv file

To organize myself, I created a folder « Visualization » with 3 subfolders. Each subfolder corresponds to the section of « Visualization ». I put the data set « OfficeSupplies.csv » in the 1st subfolder.

data csv file

« OfficeSuplies.csv » is a CSV file. A CSV file is a text file that represents an table but the elements separated by comma.

You can open this CSV file with NotePad++  for PC or with Sublime Text  for Mac.

On the 1st line (column title), we see that we have 6 columns. Each column separated by a comma and the file contains 44 lines.

data csv file

You can also open a CSV file with Excel, OpenOffice  or LibreOffice  to have a table, which is easier to read.

To open a CSV file with Excel, here are the instructions :

  • Open a blank workbook

  • Go to Data tab

  • Click button « From Text » in the general external data section

  • Select your CSV file

  • Follow the Text Import Wizard (in step 2, select the delimiter of your text).

data csv file

This data set contains data from a store that sells office equipment :

  • Order date – date of sale

  • Region – The store is in 3 region (East, Central and West)

  • Rep – salesperson’s first name

  • Item – product’s name

  • Units – product’s quantity

  • Unit Price – price per unit

Each line shows how many sales there were for a product.

The challenge is to help the manager to know who made the most sales per region in the period of this data set. The period of this data set is from July 2014 to June 2015.

The person who made the most sales in each region has a bonus and there are 3 bonus so 1 bonus per region.

It’s will this challenge that we’ll use Tableau Public.

Share this article if you think it can help someone you know. Thank you.

-Steph

Please follow, like and share:

Back To The Source Of Human Strength (Part 2)

source river

I read a Nerd Fitness article  and I learned good stuff.

If you didn’t read part 1, click here 

Hypertrophy

When I started to train, I thought I can increase the amount of muscle fibers with strength training program. The truth is we have a specific number of muscle fibers. We can only increase the size of our muscles fibers, this is hypertrophy.

I discovered that there were several hypertrophies. Usually when people talk about hypertrophy, it’s sarcoplasmic hypertophy.

  • Sarcoplasmic hypertrophy

    This hypertrophy allows you to increase the amount of sarcoplasmic fluid in your muscles. This can increase the size of one of your muscles by 30 %.

  • Myofibril hypertrophy

    This hypertrophy allows you to strengthen your myofibril, the part of the muscle that contracts. This increase the strength of your muscle fibers, build strong muscles and super dense.

  • Transient hypertrophy

    This hypertrophy allows you to increase temporary the size of one of your muscles during and immediately after an exercice due to an accumulation of fluid (I still don’t know if it’s the sarcoplasmic fluid or glycogen) in the intercellular space. This is what gives the effect « the pump ».

To summarize to buid a muscle super dense and strong, it’s myofibril hypertrophy. To have bigger muscle, it’s sarcoplasimic hypertrophy. Transient hypertrophy is temporary and appears in the other 2 hypertrophies during the training.

During a strength training, here is what happens :

  • During an exercice you’ll break muscle tissue. Your body will repair this muscle and it will become stronger. Whenever you break muscle tissue, your body will do this process.
  • When you increase the number of repetitions, you increase the storage of glycogen in your muscle. This is where you muscle gets bigger.

For the training program

Strength training isn’t just lift weights. It’s more interesting and more complex than that.

Here are details :

chart hypertophy starting strength rep 1rm

A little help for this Excel’s table

  • Repetition (Rep) => An exercice is composed of a starting position and an arrival position. A repetition is when you make the movement from the starting position to the arrival position and you return to the starting position. All that make a repetition. For exemple when you squat, the starting position is standing. The arrival position is when your thighs are parallel to the floor and you return to the starting position, standing.

  • X Rep Max => This is the number of repetitions with the heaviest weight you are able to control. By exemple, 1 Rep Max (1RM), is that the weight is so heavy that you can do only one repetition. 10RM, is that you can control the weight successfully to do 10 repetitions. In the 11st, it’s the failure.

With this Excel table, you can have an idea to how you can do to gain stength (myofribril hypertophy) : A low number of repetition and heavy weight. To increase the size of your muscles (sarcoplasmic hypertrophy) : A high number of repetition and light weight.

ATTENTION : This Excel table give us an idea of how to do to reach our goal. It’s not something specific. When you do a high number of repetition to increase the size of your muscle, you gain also a little strength. Our body is smarter than us.

It’s for this reason I never understand girls or women who don’t want to « get bulk », so they do 3 sets of 16 repetitions or 5 sets of 1000 repetitions with a dumbbell of 2kg (4.4lbs).

It’s hard for a girl to bulk, no matter the weight or the number of repetitions, if they want to increase the size of muscles, that’s exactly what they have to do because it would make sarcoplasmic hypertrophy.

Recovery

recovery sleep

I’m sure you’ve already heard that muscles are built in the kitchen, not in the gym.

It’s because when you’re in the gym, you break your muscle fibers. When you’re outside of the gym, your body heals and your muscles become bigger and stronger. So it’s important to take a recovery’s day in your strength training program.

The general rule is to wait 40 hours before to work the same muscle group again.

Remember that your muscles works in team, which means that when you work your chest, you also work shoulders muscles and arms muscles.

Everyone at his own pace to recover, it depends on diet, sleep’s quality, age workout and other items like stretching or massage.

The basis of strength training is 4 days of training per week and one recovery’s day.

Sore muscles or DOMS

DOMS is the acronym for Delayed Onset Muscle Soreness. Sore means that your muscles haven’t recovered to 100 % (hence the « delayed onset »). This can last 1-2 days after your workout and this is normal. The process for your body to rebuild your muscles takes several days after a workout.

It’s normal that you have sore muscle for several days after your first training or after a long break. With each exercice, your muscle gets used to a movement and adapts to the stress caused by this movement so that you have less sore muscles. Stretching helps you to decrease sore muscles.

I think you motivated now, to start a strength training. Don’t worry, there is a lot of stength training program great for beginners on internet. The book « Starting Strength » is excellent for beginners, the Excel’s table of this article comes from this book.

 

 

I also put free strength training program at your disposal. Click on the links below.

Share this article if you think it can help someone you know. Thank you.

-Steph

Please follow, like and share:

Why Track Your Training’s Progress ?

 sport tracker app

I remember the first time in the gym, I didn’t know what to do. I was going on machines at random just to train the muscles that I wanted. Then I told myself that it’s better to follow a program to really evolve my muscles because I had seen a picture of people who didn’t train legs on Internet . So I bought two magazines Flex and Muscle & Fitness. I was really happy, inside there were several programs but what I liked most is that in one magazine, there was a training program section from 2 to 7 days for all muscle groups.

 

There my training completely changed, it’s harder, I have new sensations, I feel working muscles I didn’t know, everything is new. I knew I had to pay attention to nutrition but I wasn’t paying attention. My goal was to gain muscle, gain muscle, gain muscle.

muscle and fitness cover flex cover

After 3-4 months, I couldn’t find my body had changed especially, but my exercises were easy and I was sweating less. The workouts started a little bored me. Suddenly I see a guy who did an exercise with heavy weights (I forget the exercise), the guys go and I do the exercise right after him without change the weight. And then my friends, it was the drama, I can’t lifted, I tried, I tried and I failed.

This is where I wonder how he lift heavy weight ? He was more muscular than me which meant he was training for a long time. So I asked him after his set how he did because I wanted to lift as heavy as him. He takes me a notebook and a pen from his pocket, he shows me and I see that he notes everything he does for several months. He said: “You write everything and all two months you change the repetition numbers or weights and you’ll see you‘re going forward. If you dont do that you’re not a real athlete“. Being a “real athlete“, I don’t know why but it had upset me, he was treating me like a little jerk who was spending his time in the gym to show off.

The next day I was in the gym with an A4 paper and a pen, I had prepared everything, that’s what I had written on the sheet (I check examples on Google):

Date

Exercise name

Number of sets

Weight x repetitions

journal workout

And since that day, my training has a better level and I guarantee you that your workout is better quality and it’s motivating you to see your progress because your friends or your entourage around you will not congratulate you for that, they will become jealous, I warned you, it hurts but everyone live his life and you’re the one to take care of your.

And there are people you don’t even know who are going to create problems, I’m not particularly muscular, but do you know a celebrity who doesn’t criticize ? Well that’s another topic maybe I’ll talk to you about this another time.

Having a journal or a training log is really convenient because you forget nothing, everything is noted, you know what you did and you know what you’re gonna do for the next session. Planning your progression, visualize what you gonna lift in two months (increase reps or weight), feel the muscle during training to know if it’s the time or not to increase, avoid injury is very important. Change one element of an exercise like hand position or change the exercise to train the same muscle but in a different way. There are a lot of thing really cool to do and it’s great.

But one day, you know, I lost some pages. I loose my mind because I was doing an Excel file to make a graph of my evolution. Yes, in fact I’m a software writer and analyze data I like it. So I decided to use my performance’s data to have some graphs and it became a little obsessive.

digital workout journal

digital workout journal

And I got my new smartphone Sony Xperia Z3 compact, I choose it, it was in February 2015 for his images in 4K and quality sound for music. I make my special playlist for training, I took pictures of me to see my evolution, everything is cool. And one day I forget my notebook. I make a note on my sony, it’s take a long time and I said “Damn, fuck, but there is not an app for this ?!? “.

I back go home, I wrote data in my Excel file and I’m looking for an workout app. A scandal, scandal, millions of app for running but nothing for the gym. I seek, I seek and I found only 2 : Jefit and Bodyspace (Bodybuilding.com). I don’t understand why there are so few good app. There Touchfit: GSP that looks good but it’s bodyweight exercises.

Frankly when I see the number of people in the gym who always train same muscles, I think they will be distorted. Once I saw a guy in the street there was a huge back but no chest, I’m telling you, in his tight t-shirt, it was disgusting to see, oh my God. I don’t’ speak about toilet brush guys. That annoys me, I can’t anymore, shut up and train your legs.

I know that not everyone has the same goal but when you train you want to have a symmetric body, right? A well balanced body no matter your muscle mass, then no kidding? Stop stops immediately.

asymetric body

Make yourself a notebook, Excel, Google sheet file, an app and track your progress and you will see, you will be better than yesterday.

-Steph

P.S. All this made me want to make a platform on internet or an app to track performance and track bodyweight, circumference and body fat%. With a social networks that members could initiate challenges, it’ll be fun.

 

Please follow, like and share:

Pourquoi suivre ses progrès à l’entraînement ?

 sport tracker app

Je me souviens les première fois que j’allais à la salle, je savais pas quoi faire. J’allais sur des machines au hasard au feeling, suivant les muscles que j’avais envie de travailler. Puis, je me suis dit que je devais suivre une programme pour vraiment faire évoluer mes muscles parce que j’avais vu une photo de personnes qui ne travaillaient pas les jambes sur internet. Alors j’ai acheté 2 magasines Flex et Muscle&Fitness. J’étais vraiment content, dedans il y avait plusieurs programmes mais ce qui me plaisait le plus c’est que dans 1 des magasin, il y a une section de programme d’entraînement de 2 à 7 jours pour tout les groupes musculaires.

Là mon entraînement complètement changé, c’est plus dur, j’ai de nouvelles sensations, j’ai l’impression de travailler des muscles que je ne connaissais pas, tout est nouveau. Je savais que je devais faire attention à la nutrition car il y a avait des articles dans les magasines mais je ne faisais pas attention. Mon but était de prendre du muscle, prendre du muscle, prendre du muscle.

muscle and fitness cover flex cover

Après 3-4 mois, je ne trouvais pas que mon corps avait spécialement changé mais mes exercices étaient plus facile et je transpirait moins. Les entraînements commençaient un peu à m’ennuyer. Tout d’un coup je vois un gars qui fait un exercice avec beaucoup de poids (j’ai oublié l’exercice), le gars pars et fais l’exercice juste après lui sans changer les poids. Et là les amis, c’est le drame, j’arrive pas a soulevé, j’essaye, j’essaye et je n’arrive pas.

C’est là que je me demande comment il fait pour soulevé aussi lourd ? Il était plus musclé que moi ce qui voulait dire qu’il s’entraînait depuis longtemps déjà. Alors je lui ai demandé après sa série comment il faisait parce que je voulais soulevé aussi lourd que lui. Il me sort un carnet et une stylo de sa poche, il me le montre et je vois qu’il note tout ce qu’il fait depuis plusieurs mois. Il me dit : « Tu notes tout et tous les 2 mois tu changes le nombres de répétition ou de poids et tu verras tu vas progresser. Si tu fais pas ça t’es pas un vrai athlète ». Etre un « vrai athlète », je ne sais pas pourquoi mais ça m’avait vexé, il me traitais de petit con qui traînais à la salle pour frimer.

Le lendemain, j’étais à la salle avec une feuille A4 et un stylo, j’avais tout préparé, voilà ce que j’avais noté sur la feuille (j’avais check de exemples sur Goolge) :

Date

Nom de l’exercice

Nombre de série

Poids x répétitions

journal workout

Et depuis ce jour là, mon entraînement a monté de niveau et je te garanti que ton entraînement est de meilleur qualité et c’est motivant de voir ses progrès parce que tes amis ou ton entourage ne vont pas te félicité pour ça, ils vont devenir jaloux, je te prévient ça fait mal mais chacun vis sa vie et t’es le seul à prendre soin de la tienne.

Et il y a des gens que tu ne connais même pas qui vont de créer des problèmes, pourtant je ne suis pas spécialement musclé mais est-ce que tu connais une célébrité qui ne se fait pas critiquer ? Bon ça c’est une autre sujet peut-être que j’en te parlerais une autre fois.

Avoir un journal ou un cahier d’entraînement est vraiment pratique parce que tu n’oublies rien, tout est noté, tu sais ce que tu as fait et tu sais ce que tu vas faire pour la prochaine séance. Planifier sa progression, visualiser ce que tu vas soulevé dans 2 mois (augmenter les reps ou les poids), ressentir les muscle pendant l’entraînement pour savoir si c’est le moment ou pas d’augmenter car il faut éviter les blessures. Changer un élément d’un exercice comme la position des main ou changer l’exercice pour travailler le même muscle mais d’une façon différente. Il pleins de chose trop cool faire avec et c’est excellent

Mais un jour tu sais, j’ai perdu des feuilles. J’ai pété une plombs parce que j’étais un train de faire un fichier Excel pour pouvoir faire un graphique de mon évolution. Oui, oui, en fait j’ai une formation de développeur et analyser les données j’aime ça. Donc j’ai décider de numériser mes données pour faire des graphiques de performances et c’est devenu un peu obsessionnel.

digital workout journal

digital workout journal

Et j’ai eu mon nouveau smartphone le Sony Xperia Z3 compact, je l’ai choisi, c’était en février 2015 pour ses images en 4K et la qualité du son pour la musique. Je fais ma spécial playlist pour l’entraînement, je me prend en photo pour voir mon évolution tout est cool. Et un jour j’oublie mon carnet alors je fais une note sur mon sony, c’est long et la me je dis « putain mais merde, il y a pas une app qui fait ça ?!? ».

Je rentre chez moi, j’écris les données dans mon fichier Excel et je cherche une app pour la musclu. A le scandale, le scandale, des millions d’app pour ceux qui font des marathons mais rien pour la salle. Je cherche, je cherche et j’en trouve que 2 Jefit et Bodyspace (Bodybuilding.com). Je ne comprends pas pourquoi il y en a si peu d’app de bonne qualité. Il y a Touchfit : GSP qui à l’air bien mais c’est que des exercices au poids du corps.

Franchement quand je vois le nombre de personnes à la salle qui entraine toujours les même muscles, je me dis qu’il vont être déformé. Un fois j’ai vu un mec dans la rue il avait un dos énorme mais pas de pectoraux, je te dis dans sont t-shirt moulant c’était dégulasse à voir, oh mon Dieu. Je te parle même des mec style brosse à chiotte. Ça, ça m’énerve, j’en peux plus, faut arrêter de se foutre de ma gueule.

Je sais que pas tout le monde a les mêmes objectif mais quand tu t’entraîne c’est pour avoir un corps symétrique, vrai ? Un corps bien équilibré peu importe ta masse musculaire, alors déconne pas ? Arrête arrête tout de suite.

asymetric body

Fais toi un carnet, un fichier Excel, Google sheet, une app et fait bien le suivie de ta progression et tu verras, tu seras meilleur que hier.

-Steph

P.S. Tout ça m’a donné envie de faire une platforme sur internet ou une app pour faire le suivie des performances, suivie du poids du corps, circonférence et % de graisse corporelle. Avec les réseaux social les membres pourrait se lancer des challenges, ça va être marrant.

 

Please follow, like and share: