Let us look at the World Happiness Report. We create the following worksheet by placing
Start Performance Recording
Stop Performance Recording
Command
"
SELECT ""Extract"". ""Country"" AS ""Country""
FROM ""Extract"".""Extract"" ""Extract""
GROUP BY 1
"
Command
"
SELECT AVG( CAST( ""Extract"". ""Happiness Rank & Happiness.Rank""
AS DOUBLE PRECISION OR NULL
)
) AS ""avg:Happiness Rank & Happiness.Rank:ok""
FROM ""Extract"".""Extract"" ""Extract""
HAVING (COUNT(1) > 0)
"SELECT account_id, open_emp_id
^^^^ ^^^^
1 2
FROM account
GROUP BY 1;In above query GROUP BY 1 refers to the first column in select statement which is account_id
GROUP BY 1 together with ORDER BY 1. Next, please create a second worksheet called Score/Rank to analyze the scores
relative to the ranks
Start Performance Recording
Stop Performance Recording SELECT ['Happiness Report$'].[Happiness.Rank] AS [Happiness.Rank],
AVG( ['Happiness Report$'].[Happiness.Score] ) AS [avg:Happiness.Score:ok]
FROM [dbo].['Happiness Report$'] ['Happiness Report$']
GROUP BY ['Happiness Report$'].[Happiness.Rank]"Since the data source is from Happiness Report, so the file name is Happiness Report
dbo is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace.
The previous section taught us how we can manipulate data types in Tableau itself without touching the data source and its metadata itself. In the next section, we will take a closer look at table calculations.
In this section, we will explore how VizQL's table calculations can be used to add data to a dashboard without adding any data to the data source.
==>
WINDOW_AVG(SUM([Freedom]), -2, 0)
In the previous example, which can be viewed by opening Sheet 4 on this chapter's workbook, note that Freedom on the vertical axis is set to Quick Table Calculation and Moving Average. Calculating a Moving Average, Running Total, or other such comparison calculations can be quite challenging to accomplish in a data source. Not only must a data architect consider what comparison calculations to include in the data source, but they must also determine the dimensions for which these calculations are relevant.
Taking a look at the relevant portion of SQL generated by the preceding worksheet shows that the table calculation is not performed by the data source. Instead, it is performed in Tableau by the VizQL module.
Command
"SELECT ""Happiness Report_Full Data.csv"".""Happiness Rank"" AS ""Happiness Rank"",
SUM(""Happiness Report_Full Data.csv"".""Freedom"") AS ""sum:Freedom:ok""
FROM ""TableauTemp"".""Happiness Report_Full Data#csv"" ""Happiness Report_Full Data.csv""
GROUP BY 1"
In above query GROUP BY 1 refers to the first column in select statement which is Happiness Rank OR
SELECT SUM([Happiness Report$].[Freedom]) AS [sum:Freedom:ok],
[Happiness Report$].[Happiness.Rank] AS [Happiness.Rank]
FROM [dbo].[Happiness Report$] [Happiness Report$]
GROUP BY ['Happiness Report$'].[Happiness.Score] To reiterate, nothing in the preceding call to the data source generates the moving average. Only an aggregated total is returned, and Tableau calculates the moving average with VizQL.
Data modeling, data preparation, database design, data architecture—the question that arises is, how do these and other similar terms fit together? This is no easy question to answer! Terms may be used interchangeably in some contexts and be quite distinct in others. Also, understanding the interconnectivity of any technical jargon can be challenging.
In the data world, data mining and knowledge discovery process models attempt to consistently define terms and contextually position and define the various data sub-disciplines. Since the early 1990s, various models have been proposed.
In the following table, we can see a comparison of blueprints for conducting a data mining project with three data processing models, all of which are used to discover patterns and relationships in data in order to help make better business decisions.
Later on, we will see how Tableau comes into play and makes this process easier and faster for us.
Since CRISP-DM is used by four to five times the number of people as the closest competing model (SEMMA), it is the model we will consider in this chapter. For more information, see http://www.kdnuggets.com/2014/10/crisp-dm-top-methodology-analytics-data-mining-data-science-projects.html.
The important takeaway is that each of these models grapples[ˈɡræplez] with扭打 ; 与 ; 抓住 ; 与……搏斗(都解决了) the same problems, particularly concerning the understanding, preparing, modeling, and interpreting of data.
Cross Industry Standard Process for Data Mining (CRISP-DM) was created between 1996 and 2000 as a result of a consortium including SPSS, Teradata, Daimler AG, NCR Corporation, and OHRA. It divides the process of data mining into six major phases, as shown in the CRISP-DM reference model in the preceding comparison table.
This model provides a bird's-eye view of a data-mining project life cycle. The sequence of the phases are not rigid; jumping back and forth from phase to phase is allowed and expected. Data mining does not cease upon the completion of a particular project. Instead, it exists as long as the business exists, and should be constantly revisited重新审视 to answer new questions as they arise.
In the next section, we will consider each of the six phases that comprise CRISP-DM and explore how Tableau can be used throughout the life cycle. We will particularly focus on the data preparation phase, as that is the phase encompassing data cleaning, the focus of this chapter. By considering the following steps, you will be able to understand in more detail what a full data mining process circle looks like under CRISP-DM. This framework can be used to make your workflow in Tableau more efficient by working according to an established model.
In the following sections, we will briefly define each of the six CRISP-DM phases and include high-level information on how Tableau might be used.
Phase VI – deployment:
Now that we have learned what a full data mining circle looks like (and looked like pre-Tableau) and understood that every step can be executed in Tableau, we can see how it makes sense that data people celebrate Tableau Software products.
Tableau makes data mining so much easier and efficient, and the replication of steps is also easier than it was before, without Tableau. In the next section, we will take a look at a practical example to explore the content we've just learned with some hands-on examples.
As discussed earlier, Tableau can be used effectively throughout the CRISP-DM phases. Unfortunately, a single chapter is not sufficient to thoroughly explore how Tableau can be used in each phase. Indeed, such a thorough exploration may be worthy of an entire book! Our focus, therefore, will be directed to data preparation, since that phase has historically accounted for up to 60% of the data mining effort. Our goal will be to learn how Tableau can be used to streamline that effort.
Tableau can be a very effective tool for simply surveying data. Sometimes in the survey process, you may discover ways to clean the data or populate[ˈpɑːpjuleɪt]填充 incomplete data based on existing fields. Sometimes, regretfully, there are simply not enough pieces of the puzzle to put together an entire dataset. In such cases, Tableau can be useful to communicate exactly what the gaps are, and this, in turn, may incentivize[ɪnˈsentɪvaɪz]激励 the organization to more fully populate the underlying data.
In this exercise, we will explore how to use Tableau to quickly discover the percentage of null values for each field in a dataset. Next, we'll explore how the data might be extrapolated[ɪkˈstræpəleɪtɪd]推测 from existing fields to fill in the gaps.
The following are the steps for surveying the data:
SUM([Number of Records])/TOTAL( SUM([Number of Records]) )
VS
CASE [Select Field]
WHEN 1
THEN IF ISNULL([Country])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 2
THEN IF ISNULL([Region])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 3
THEN IF ISNULL([Economy (GDP per Capita)])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 4
THEN IF ISNULL([Family])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 5
THEN IF ISNULL([Freedom])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 6
THEN IF ISNULL([Happiness Rank])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 7
THEN IF ISNULL([Happiness Score])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 8
THEN IF ISNULL([Health (Life Expectancy)])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 9
THEN IF ISNULL([Standard Error])
THEN 'NULL Values'
ELSE
'Populated Values'
END
WHEN 10
THEN IF ISNULL ([Region Extrapolated])
THEN 'Null Values'
ELSE
'Populated Values'
END
END
Building on this exercise, let's explore how we might clean and extrapolate data from existing data using the same dataset.
This exercise will expand on the previous exercise by cleaning existing data and populating some of the missing data from known information. We will assume that we know which country belongs to which region. We'll use that knowledge to fix errors in the Region field and also to fill in the gaps using Tableau:
CASE [Country]
WHEN 'Afghanistan' THEN 'Southern Asia'
WHEN 'Albania' THEN 'Central and Eastern Europe'
WHEN 'Algeria' THEN 'Middle East and Northern Africa'
WHEN 'Angola' THEN 'Sub-Saharan Africa'
WHEN 'Argentina' THEN 'Latin America and Caribbean'
WHEN 'Armenia' THEN 'Central and Eastern Europe'
WHEN 'Australia' THEN 'Australia and New Zealand'
WHEN 'Austria' THEN 'Western Europe'
WHEN 'Azerbaijan' THEN 'Central and Eastern Europe'
WHEN 'Bahrain' THEN 'Middle East and Northern Africa'
WHEN 'Bangladesh' THEN 'Southern Asia'
WHEN 'Belarus' THEN 'Central and Eastern Europe'
WHEN 'Belgium' THEN 'Western Europe'
WHEN 'Belize' THEN 'Latin America and Caribbean'
WHEN 'Benin' THEN 'Sub-Saharan Africa'
WHEN 'Bhutan' THEN 'Southern Asia'
WHEN 'Bolivia' THEN 'Latin America and Caribbean'
WHEN 'Bosnia and Herzegovina' THEN 'Central and Eastern Europe'
WHEN 'Botswana' THEN 'Sub-Saharan Africa'
WHEN 'Brazil' THEN 'Latin America and Caribbean'
WHEN 'Bulgaria' THEN 'Central and Eastern Europe'
WHEN 'Burkina Faso' THEN 'Sub-Saharan Africa'
WHEN 'Burundi' THEN 'Sub-Saharan Africa'
WHEN 'Cambodia' THEN 'Southeastern Asia'
WHEN 'Cameroon' THEN 'Sub-Saharan Africa'
WHEN 'Canada' THEN 'North America'
WHEN 'Central African Republic' THEN 'Sub-Saharan Africa'
WHEN 'Chad' THEN 'Sub-Saharan Africa'
WHEN 'Chile' THEN 'Latin America and Caribbean'
WHEN 'China' THEN 'Eastern Asia'
WHEN 'Colombia' THEN 'Latin America and Caribbean'
WHEN 'Comoros' THEN 'Sub-Saharan Africa'
WHEN 'Congo (Brazzaville)' THEN 'Sub-Saharan Africa'
WHEN 'Congo (Kinshasa)' THEN 'Sub-Saharan Africa'
WHEN 'Costa Rica' THEN 'Latin America and Caribbean'
WHEN 'Croatia' THEN 'Central and Eastern Europe'
WHEN 'Cyprus' THEN 'Western Europe'
WHEN 'Czech Republic' THEN 'Central and Eastern Europe'
WHEN 'Denmark' THEN 'Western Europe'
WHEN 'Djibouti' THEN 'Sub-Saharan Africa'
WHEN 'Dominican Republic' THEN 'Latin America and Caribbean'
WHEN 'Ecuador' THEN 'Latin America and Caribbean'
WHEN 'Egypt' THEN 'Middle East and Northern Africa'
WHEN 'El Salvador' THEN 'Latin America and Caribbean'
WHEN 'Estonia' THEN 'Central and Eastern Europe'
WHEN 'Ethiopia' THEN 'Sub-Saharan Africa'
WHEN 'Finland' THEN 'Western Europe'
WHEN 'France' THEN 'Western Europe'
WHEN 'Gabon' THEN 'Sub-Saharan Africa'
WHEN 'Georgia' THEN 'Central and Eastern Europe'
WHEN 'Germany' THEN 'Western Europe'
WHEN 'Ghana' THEN 'Sub-Saharan Africa'
WHEN 'Greece' THEN 'Western Europe'
WHEN 'Guatemala' THEN 'Latin America and Caribbean'
WHEN 'Guinea' THEN 'Sub-Saharan Africa'
WHEN 'Haiti' THEN 'Latin America and Caribbean'
WHEN 'Honduras' THEN 'Latin America and Caribbean'
WHEN 'Hong Kong' THEN 'Eastern Asia'
WHEN 'Hong Kong S.A.R., China' THEN 'Eastern Asia'
WHEN 'Hungary' THEN 'Central and Eastern Europe'
WHEN 'Iceland' THEN 'Western Europe'
WHEN 'India' THEN 'Southern Asia'
WHEN 'Indonesia' THEN 'Southeastern Asia'
WHEN 'Iran' THEN 'Middle East and Northern Africa'
WHEN 'Iraq' THEN 'Middle East and Northern Africa'
WHEN 'Ireland' THEN 'Western Europe'
WHEN 'Israel' THEN 'Middle East and Northern Africa'
WHEN 'Italy' THEN 'Western Europe'
WHEN 'Ivory Coast' THEN 'Sub-Saharan Africa'
WHEN 'Jamaica' THEN 'Latin America and Caribbean'
WHEN 'Japan' THEN 'Eastern Asia'
WHEN 'Jordan' THEN 'Middle East and Northern Africa'
WHEN 'Kazakhstan' THEN 'Central and Eastern Europe'
WHEN 'Kenya' THEN 'Sub-Saharan Africa'
WHEN 'Kosovo' THEN 'Central and Eastern Europe'
WHEN 'Kuwait' THEN 'Middle East and Northern Africa'
WHEN 'Kyrgyzstan' THEN 'Central and Eastern Europe'
WHEN 'Laos' THEN 'Southeastern Asia'
WHEN 'Latvia' THEN 'Central and Eastern Europe'
WHEN 'Lebanon' THEN 'Middle East and Northern Africa'
WHEN 'Lesotho' THEN 'Sub-Saharan Africa'
WHEN 'Liberia' THEN 'Sub-Saharan Africa'
WHEN 'Libya' THEN 'Middle East and Northern Africa'
WHEN 'Lithuania' THEN 'Central and Eastern Europe'
WHEN 'Luxembourg' THEN 'Western Europe'
WHEN 'Macedonia' THEN 'Central and Eastern Europe'
WHEN 'Madagascar' THEN 'Sub-Saharan Africa'
WHEN 'Malawi' THEN 'Sub-Saharan Africa'
WHEN 'Malaysia' THEN 'Southeastern Asia'
WHEN 'Mali' THEN 'Sub-Saharan Africa'
WHEN 'Malta' THEN 'Western Europe'
WHEN 'Mauritania' THEN 'Sub-Saharan Africa'
WHEN 'Mauritius' THEN 'Sub-Saharan Africa'
WHEN 'Mexico' THEN 'Latin America and Caribbean'
WHEN 'Moldova' THEN 'Central and Eastern Europe'
WHEN 'Mongolia' THEN 'Eastern Asia'
WHEN 'Montenegro' THEN 'Central and Eastern Europe'
WHEN 'Morocco' THEN 'Middle East and Northern Africa'
WHEN 'Mozambique' THEN 'Sub-Saharan Africa'
WHEN 'Myanmar' THEN 'Southeastern Asia'
WHEN 'Namibia' THEN 'Sub-Saharan Africa'
WHEN 'Nepal' THEN 'Southern Asia'
WHEN 'Netherlands' THEN 'Western Europe'
WHEN 'New Zealand' THEN 'Australia and New Zealand'
WHEN 'Nicaragua' THEN 'Latin America and Caribbean'
WHEN 'Niger' THEN 'Sub-Saharan Africa'
WHEN 'Nigeria' THEN 'Sub-Saharan Africa'
WHEN 'North Cyprus' THEN 'Western Europe'
WHEN 'Norway' THEN 'Western Europe'
WHEN 'Oman' THEN 'Middle East and Northern Africa'
WHEN 'Pakistan' THEN 'Southern Asia'
WHEN 'Palestinian Territories' THEN 'Middle East and Northern Africa'
WHEN 'Panama' THEN 'Latin America and Caribbean'
WHEN 'Paraguay' THEN 'Latin America and Caribbean'
WHEN 'Peru' THEN 'Latin America and Caribbean'
WHEN 'Philippines' THEN 'Southeastern Asia'
WHEN 'Poland' THEN 'Central and Eastern Europe'
WHEN 'Portugal' THEN 'Western Europe'
WHEN 'Puerto Rico' THEN 'Latin America and Caribbean'
WHEN 'Qatar' THEN 'Middle East and Northern Africa'
WHEN 'Romania' THEN 'Central and Eastern Europe'
WHEN 'Russia' THEN 'Central and Eastern Europe'
WHEN 'Rwanda' THEN 'Sub-Saharan Africa'
WHEN 'Saudi Arabia' THEN 'Middle East and Northern Africa'
WHEN 'Senegal' THEN 'Sub-Saharan Africa'
WHEN 'Serbia' THEN 'Central and Eastern Europe'
WHEN 'Sierra Leone' THEN 'Sub-Saharan Africa'
WHEN 'Singapore' THEN 'Southeastern Asia'
WHEN 'Slovakia' THEN 'Central and Eastern Europe'
WHEN 'Slovenia' THEN 'Central and Eastern Europe'
WHEN 'Somalia' THEN 'Sub-Saharan Africa'
WHEN 'Somaliland region' THEN 'Sub-Saharan Africa'
WHEN 'South Africa' THEN 'Sub-Saharan Africa'
WHEN 'South Korea' THEN 'Eastern Asia'
WHEN 'South Sudan' THEN 'Sub-Saharan Africa'
WHEN 'Spain' THEN 'Western Europe'
WHEN 'Sri Lanka' THEN 'Southern Asia'
WHEN 'Sudan' THEN 'Sub-Saharan Africa'
WHEN 'Suriname' THEN 'Latin America and Caribbean'
WHEN 'Swaziland' THEN 'Sub-Saharan Africa'
WHEN 'Sweden' THEN 'Western Europe'
WHEN 'Switzerland' THEN 'Western Europe'
WHEN 'Syria' THEN 'Middle East and Northern Africa'
WHEN 'Taiwan' THEN 'Eastern Asia'
WHEN 'Taiwan Province of China' THEN 'Eastern Asia'
WHEN 'Tajikistan' THEN 'Central and Eastern Europe'
WHEN 'Tanzania' THEN 'Sub-Saharan Africa'
WHEN 'Thailand' THEN 'Southeastern Asia'
WHEN 'Togo' THEN 'Sub-Saharan Africa'
WHEN 'Trinidad and Tobago' THEN 'Latin America and Caribbean'
WHEN 'Tunisia' THEN 'Middle East and Northern Africa'
WHEN 'Turkey' THEN 'Middle East and Northern Africa'
WHEN 'Turkmenistan' THEN 'Central and Eastern Europe'
WHEN 'Uganda' THEN 'Sub-Saharan Africa'
WHEN 'Ukraine' THEN 'Central and Eastern Europe'
WHEN 'United Arab Emirates' THEN 'Middle East and Northern Africa'
WHEN 'United Kingdom' THEN 'Western Europe'
WHEN 'United States' THEN 'North America'
WHEN 'Uruguay' THEN 'Latin America and Caribbean'
WHEN 'Uzbekistan' THEN 'Central and Eastern Europe'
WHEN 'Venezuela' THEN 'Latin America and Caribbean'
WHEN 'Vietnam' THEN 'Southeastern Asia'
WHEN 'Yemen' THEN 'Middle East and Northern Africa'
WHEN 'Zambia' THEN 'Sub-Saharan Africa'
WHEN 'Zimbabwe' THEN 'Sub-Saharan Africa'
END 
To speed up the tedious creation of a long calculated field, you could download the data to an Excel file and create the calculated field by concatenating the separate parts, as shown here:
=$B$2&" "&$C$2&A4&$C$2&" "&$D$2&" "&$C$2&B4&$C$2=$B$2&" "&$C$2&A4&$C$2&" "&$D$2&" "&$C$2&B4&$C$2
=WHEN 'Country' THEN 'Region'
Besides, Single quotes(') in excel are to be escaped, so use 2 single quotes('') to represent a single quote
Country
Afghanistan
Albania
Algeria
Angola
Argentina
Armenia
Australia
Austria
Azerbaijan
Bahrain
Bangladesh
Belarus
Belgium
Belize
Benin
Bhutan
Bolivia
Bosnia and Herzegovina
Botswana
Brazil
Bulgaria
Burkina Faso
Burundi
Cambodia
Cameroon
Canada
Central African Republic
Chad
Chile
China
Colombia
Comoros
Congo (Brazzaville)
Congo (Kinshasa)
Costa Rica
Croatia
Cyprus
Czech Republic
Denmark
Djibouti
Dominican Republic
Ecuador
Egypt
El Salvador
Estonia
Ethiopia
Finland
France
Gabon
Georgia
Germany
Ghana
Greece
Guatemala
Guinea
Haiti
Honduras
Hong Kong
Hong Kong S.A.R., China
Hungary
Iceland
India
Indonesia
Iran
Iraq
Ireland
Israel
Italy
Ivory Coast
Jamaica
Japan
Jordan
Kazakhstan
Kenya
Kosovo
Kuwait
Kyrgyzstan
Laos
Latvia
Lebanon
Lesotho
Liberia
Libya
Lithuania
Luxembourg
Macedonia
Madagascar
Malawi
Malaysia
Mali
Malta
Mauritania
Mauritius
Mexico
Moldova
Mongolia
Montenegro
Morocco
Mozambique
Myanmar
Namibia
Nepal
Netherlands
New Zealand
Nicaragua
Niger
Nigeria
North Cyprus
Norway
Oman
Pakistan
Palestinian Territories
Panama
Paraguay
Peru
Philippines
Poland
Portugal
Puerto Rico
Qatar
Romania
Russia
Rwanda
Saudi Arabia
Senegal
Serbia
Sierra Leone
Singapore
Slovakia
Slovenia
Somalia
Somaliland region
South Africa
South Korea
South Sudan
Spain
Sri Lanka
Sudan
Suriname
Swaziland
Sweden
Switzerland
Syria
Taiwan
Taiwan Province of China
Tajikistan
Tanzania
Thailand
Togo
Trinidad and Tobago
Tunisia
Turkey
Turkmenistan
Uganda
Ukraine
United Arab Emirates
United Kingdom
United States
Uruguay
Uzbekistan
Venezuela
Vietnam
Yemen
Zambia
Zimbabwe
Region
Southern Asia
Central and Eastern Europe
Middle East and Northern Africa
Sub-Saharan Africa
Latin America and Caribbean
Central and Eastern Europe
Australia and New Zealand
Western Europe
Central and Eastern Europe
Middle East and Northern Africa
Southern Asia
Central and Eastern Europe
Western Europe
Latin America and Caribbean
Sub-Saharan Africa
Southern Asia
Latin America and Caribbean
Central and Eastern Europe
Sub-Saharan Africa
Latin America and Caribbean
Central and Eastern Europe
Sub-Saharan Africa
Sub-Saharan Africa
Southeastern Asia
Sub-Saharan Africa
North America
Sub-Saharan Africa
Sub-Saharan Africa
Latin America and Caribbean
Eastern Asia
Latin America and Caribbean
Sub-Saharan Africa
Sub-Saharan Africa
Sub-Saharan Africa
Latin America and Caribbean
Central and Eastern Europe
Western Europe
Central and Eastern Europe
Western Europe
Sub-Saharan Africa
Latin America and Caribbean
Latin America and Caribbean
Middle East and Northern Africa
Latin America and Caribbean
Central and Eastern Europe
Sub-Saharan Africa
Western Europe
Western Europe
Sub-Saharan Africa
Central and Eastern Europe
Western Europe
Sub-Saharan Africa
Western Europe
Latin America and Caribbean
Sub-Saharan Africa
Latin America and Caribbean
Latin America and Caribbean
Eastern Asia
Eastern Asia
Central and Eastern Europe
Western Europe
Southern Asia
Southeastern Asia
Middle East and Northern Africa
Middle East and Northern Africa
Western Europe
Middle East and Northern Africa
Western Europe
Sub-Saharan Africa
Latin America and Caribbean
Eastern Asia
Middle East and Northern Africa
Central and Eastern Europe
Sub-Saharan Africa
Central and Eastern Europe
Middle East and Northern Africa
Central and Eastern Europe
Southeastern Asia
Central and Eastern Europe
Middle East and Northern Africa
Sub-Saharan Africa
Sub-Saharan Africa
Middle East and Northern Africa
Central and Eastern Europe
Western Europe
Central and Eastern Europe
Sub-Saharan Africa
Sub-Saharan Africa
Southeastern Asia
Sub-Saharan Africa
Western Europe
Sub-Saharan Africa
Sub-Saharan Africa
Latin America and Caribbean
Central and Eastern Europe
Eastern Asia
Central and Eastern Europe
Middle East and Northern Africa
Sub-Saharan Africa
Southeastern Asia
Sub-Saharan Africa
Southern Asia
Western Europe
Australia and New Zealand
Latin America and Caribbean
Sub-Saharan Africa
Sub-Saharan Africa
Western Europe
Western Europe
Middle East and Northern Africa
Southern Asia
Middle East and Northern Africa
Latin America and Caribbean
Latin America and Caribbean
Latin America and Caribbean
Southeastern Asia
Central and Eastern Europe
Western Europe
Latin America and Caribbean
Middle East and Northern Africa
Central and Eastern Europe
Central and Eastern Europe
Sub-Saharan Africa
Middle East and Northern Africa
Sub-Saharan Africa
Central and Eastern Europe
Sub-Saharan Africa
Southeastern Asia
Central and Eastern Europe
Central and Eastern Europe
Sub-Saharan Africa
Sub-Saharan Africa
Sub-Saharan Africa
Eastern Asia
Sub-Saharan Africa
Western Europe
Southern Asia
Sub-Saharan Africa
Latin America and Caribbean
Sub-Saharan Africa
Western Europe
Western Europe
Middle East and Northern Africa
Eastern Asia
Eastern Asia
Central and Eastern Europe
Sub-Saharan Africa
Southeastern Asia
Sub-Saharan Africa
Latin America and Caribbean
Middle East and Northern Africa
Middle East and Northern Africa
Central and Eastern Europe
Sub-Saharan Africa
Central and Eastern Europe
Middle East and Northern Africa
Western Europe
North America
Latin America and Caribbean
Central and Eastern Europe
Latin America and Caribbean
Southeastern Asia
Middle East and Northern Africa
Sub-Saharan Africa
Sub-Saharan AfricaYou can then copy them(WHEN 'Country' THEN 'Region') from Excel into Tableau. However, for this exercise, I have created a backup field called Backup , which can be found in the Tableau Workbook associated with this chapter, which contains the full calculation needed for the Region Extrapolated field. Use this at your convenience. The Solutions dashboard also contains all of the countries. You can therefore copy the Region Extrapolated field from that file too.
WHEN 10
THEN IF ISNULL ([Region Extrapolated])
THEN 'Null Values'
ELSE
'% Populated Values'
END
Note that the Region Extrapolated field is not fully populated:
==>Full Data
append the following code to Region Extrapolated:
WHEN 'Somaliland Region' THEN 'Sub-Saharan Africa'
Another solution : CASE LOWER([Country]) and all country name use their lower case:
Now, that the Region Extrapolated field is fully populated:
Nulls are a part of almost every extensive real dataset. Understanding how many nulls are present in each field can be vital to ensuring that you provide accurate business intelligence. It may be acceptable to tolerate some null values when the final results will not be substantially impacted, but too many nulls may invalidate results. However, as demonstrated here, in some cases one or more fields can be used to extrapolate the values that should be entered into an underpopulated填充不足 or erroneously[ɪˈroʊniəsli]错误地,不正确 populated field.
As demonstrated in this section, Tableau gives you the ability to effectively communicate to your data team which values are missing, which are erroneous, and how possible workarounds can be invaluable to the overall data mining effort. Next, we will look into data that is a bit messier and not in a nice column format. Don't worry, Tableau has us covered.
The United States government provides helpful documentation for various bureaucratic[ˌbjʊrəˈkrætɪk]官僚政治的 processes. For example, the Department of Health and Human Services (HSS) provides lists of ICD-9 codes, otherwise known as International Statistical Classification of Diseases and Related Health Problems codes. Unfortunately, these codes are not always in easily accessible formats.
As an example, let's consider an actual HHS document known as R756OTN, which can be found at https://www.cms.gov/Regulations-and-Guidance/Guidance/Transmittals/downloads/R756OTN.pdf
File ==> Export To... ==> Text(Plain) ==>
Clean data by remove anything except 'Diagnosis Code' and 'Description'
For example:
Ctrl + F ==>
delete
and if you see
you need to check pdf document,
then process it
Cleaned up text
python code
==>strip()==>
filename='R756OTN.txt'
txt=[] #sometime is 'utf-8'
with open(filename, 'r', encoding='cp1252') as infile:
for line in infile:
line=line.strip() # remove space
if len( line ) ==0: # remove ''
continue
txt.append( line )
diagnosis_code=[]
description=[]
for i in range(0,len(txt),2):
diagnosis_code.append( txt[i] )
description.append(txt[i+1])
assert len(diagnosis_code) + len(description) == len(txt)
print(len(txt), len(diagnosis_code) )
import pandas as pd
dict = zip(diagnosis_code, description)
df = pd.DataFrame(dict, columns=['Diagnosis Code', 'Description'])
df.to_csv('code_description.csv',index=False)
df.head(n=10)
better than the author's dataset file
Navigate to the Cleaning the Data worksheet in this workbook and execute the following steps:
// delimiter, token number
SPLIT( [Diagnosis], " ", 1 )
To extract characters from the end of the string, the token number (that is, the number at the end of the function) must be negative.
INT( MID([DX],2,1) )
ISNULL([Null Hunting])
ISNULL is a Boolean function that simply returns TRUE in the case of Null 
REPLACE([Diagnosis], [DX]+" ", "")
The final output for this exercise could be to export the data from Tableau as an additional source of data. This data could then be used by Tableau and other tools for future reporting needs. For example, the DX field could be useful in data blending.
Does Tableau offer a better approach that might solve the issue of truncated data associated with the preceding solution? Yes! Let's turn our attention to the next exercise, where we will consider regular expression functions.
Although, as shown in the previous exercise, Cleaning the data, the SPLIT function can be useful for cleaning data, regular expression functions are far more powerful and represent a broadening of the scope from Tableau's traditional focus on visualization and analytics to also include data cleaning capabilities. Let's look at an example that requires us to deal with some pretty messy data in Tableau. Our objective will be to extract phone numbers.
The following are the steps:
\([0-9]{3}\)-[0-9]{3}-[0-9]{4}
\({0,1}[0-9]{3}\){0,1}-{0,1}[0-9]{3}-{0,1}[0-9]{4}
| {n,m} |
m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
[A-Za-z0-9\._+]+@[A-Za-z]+\.(com|org|edu|net)
REGEXP_EXTRACT( [String of Data (String of Data)],
'(\([0-9]{3}\)-[0-9]{3}-[0-9]{4})'
)
REGEXP_EXTRACT([String of Data (String of Data)],
'([A-Za-z0-9\._+]+@[A-Za-z]+\.(com|org|edu|net))'
)
After reviewing this exercise, you may be curious about how to return just the email address. According to http://www.regular-expressions.info/email.html, the regular expression for email addresses adhering to the RFC 5322 standard is as follows:
(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])Although I won't attempt a detailed explanation of this code, you can read all about it at http://www.regular-expressions.info/email.html , which is a great resource for learning more about regular expressions. Also, YouTube has several helpful regular expression tutorials.
The final output for this exercise should probably be used to enhance existing source data. Data dumps such as this example do not belong in data warehouses; however, even important and necessary data can be hidden in such dumps, and Tableau can be effectively used to extract it.
We began this chapter with a discussion of the Tableau data-handling engine. This illustrated the flexibility Tableau provides in working with data. The data-handling engine is important to understand in order to ensure that your data mining efforts are intelligently focused. Otherwise, your effort may be wasted on activities not relevant to Tableau.
Next, we discussed data mining and knowledge discovery process models, with an emphasis on CRISP-DM. The purpose of this discussion was to get an appropriate bird's-eye view of the scope of the entire data mining effort. Tableau authors (and certainly end users) can become so focused on the reporting produced in the deployment phase that they end up forgetting or short-changing the other phases, particularly data preparation.
Our last focus in this chapter was on the phase that can be the most time-consuming and labor-intensive, namely data preparation. We considered using Tableau for surveying and also cleaning data. The data cleaning capabilities represented by the regular expression functions are particularly intriguing[ɪnˈtriːɡɪŋ]非常有趣的, and are worth further investigation.
Having completed our first data-centric discussion, we'll continue with Chapter 3, Tableau Prep Builder, looking at one of the newer features Tableau has brought to the market. Tableau Prep Builder is a dedicated data pre-processing interface that is able to reduce the amount of time you need for pre-processing even more. We'll take a look at cleaning, merging, filtering, joins, and the other functionality Tableau Prep Builder has to offer.