Data Preparation for National Culture Dashboard

This data science project develops and deploys an interactive visual dashboard to help scientists, researchers, leaders, and ordinary people to compare national cultures according to the Hofstede's six dimentional model of national culture. This accessible and friendly tool will help enbrace cultural diversity and inclusion and promote global connection and collaboration for sustainable peace and prosperity.

The Dashboard

http://culture.pythonanywhere.com

Technologies Used

Python
Pandas
Ployly & Ployly Express
Dash
Jupyter Notebook

Platforms Used

deepnote.com (Free Cloud-based Python/Jupyter environment used for development)
pythonanywhere.com (Free Cloud-based Python environment used for deplotment)

Data Sources Used

ISO Country Codes
- https://countrycode.org/customer/countryCode/downloadCountryCodes
National Culture Indexes
- https://geerthofstede.com/wp-content/uploads/2016/08/6-dimensions-for-website-2015-08-16.csv

Step 1 - Install and Import Libraries

import pandas as pd pd.options.display.max_rows = 300

Step 2 - Load and Prepare Data

The ISO country code dataset contains 240 countries with country name, capital, contient, two-digit code, three-digit code, currency, language, area, and GDP. We primarily use ISO3 country codd to support choropleth map but other data elements can also be used in the dashboard to provide geographical, demographic, and socioeconomic information.

2.1 Load and Prepare ISO Country Code Dataset

iso_df = pd.read_csv("../data/countrycode.csv") iso_df.sample(5)

iso_df.shape

Step 2.2 - Load and Prepare National Culture Data

data = pd.read_csv("../data/national_culture.csv", sep=";") data.sample(5)

data.shape

dimension_list = list(data.columns[2:8]) dimension_list

dimension_list[4] = "lto" dimension_list

dimension_list = [x.upper() for x in dimension_list] dimension_list

dimension_list.insert(0, "country") dimension_list.insert(0, "ctr") dimension_list

data.columns = dimension_list data.sample(5)

Step 3 - Merge National Culture Data with Country Code Data

The country code (ctr) in National Culture Data is not based on ISO standard. We need to use ISO3 format country code for generating choropleth map. We merge the two datasets to incorporate the ISO3 country code.

We take multipl steps:

First, merge based on the country name match. Name match takes the higher priority.
Second, merge based on country code match for those unmatched countries.
Since mataches based on code can be problemetic, we must eye-ball them to correct wrong matches

3.1 Merge Based on Country Name Match

merged_df = pd.merge(data, iso_df,left_on="country", right_on="Country Name", how='left', indicator=True) merged_df.shape

merged_df.sample(10)

# Find out how many matches were found # 92 out of 111 countries match. This is great new. # We trust name match but to be cautious, it would be better to eye-ball the results as well. # This manual check is not performed here. match_df = merged_df[merged_df["ISO3"].isna() == False] match_df.shape

# Find out how many countries are without matches # 19 out of 111 do not match unmatch_df = merged_df[merged_df["ISO3"].isna()] unmatch_df.shape

unmatch_df

3.2 Merge Based on Country Code Match

We take the unmatched rows to find match based on country code.

# drop the null columns due to unmatch unmatch_df.drop(columns=unmatch_df.columns[8:], inplace=True) unmatch_df

merged_df2 = pd.merge(unmatch_df, iso_df,left_on="ctr", right_on="ISO3", how='left', indicator=True) merged_df2

# Find out how many matches were found match_df2 = merged_df2[merged_df2["ISO3"].isna() == False] match_df2.shape

# Eye-ball the matched countries to spot wrong matches: # Conclusion: Row 0, 1, and 6 were matched wrongly match_df2[["ctr", "country", "ISO3", "Country Name", "Continent"]]

# Save the matched rows to match_df3. # match_df3 will be appended to match_df from step 2 match_df3 = match_df2.iloc[[2, 3, 4, 5, 7]] match_df3

unmatch_df2 = match_df2.iloc[[0,1,6]] unmatch_df2

# Find out how many countries are without matches unmatch_df3 = merged_df2[merged_df2["ISO3"].isna()] unmatch_df3

# append the previously identified three countries to the unmatched unmatch_df3 = unmatch_df3.append(unmatch_df2) unmatch_df3

unmatch_df3.sort_index(inplace=True) unmatch_df3

# Remove the columns unmatch_df3.drop(columns=unmatch_df3.columns[8:], inplace=True) unmatch_df3

unmatch_df3["ctr2"] = unmatch_df3["ctr"] unmatch_df3

# Only four countries can be mapped manually with ISO3 code. The rest either have no match or # only represent a part of a country. We ignore them so that our choropleth map can work which # requires ISO3 and ISO3 must be unique without duplicate entries code_map = { # "BEF" : "BEL", # "BEN" : "BEL", # "CAF" : "CAN", # "GEE" : "DEU", # "SWF" : "CHE", # "SWG" : "CHE", # "SAW" : "ZAF", "BOS" : "BIH", "SLK" : "SVN", "KYR" : "KGZ", "MAC" : "MKD" } unmatch_df3["ctr2"] = unmatch_df3["ctr2"].replace(code_map) unmatch_df3

# Merge merged_df3 = pd.merge(unmatch_df3, iso_df,left_on="ctr2", right_on="ISO3", how='left', indicator=True) merged_df3

code_map.keys()

# only keep these four counries merged_df3 = merged_df3[merged_df3["ctr"].isin(code_map.keys())] merged_df3

concat_df = pd.concat([match_df, match_df3, merged_df3]) concat_df.shape

concat_df.sample(5)

concat_df.drop(columns=["ctr2","_merge"], inplace=True) concat_df.sample(5)

concat_df

Step 4 - Save the Wide Dataframe to a File

concat_df.to_csv("../data/national_culture_iso3_wide.csv", index=False)

Step 5 - Transform the Wide Dataframe to Long Format

We will use Pandas melt() function to accomplish this.

concat_df.columns

value_vars = concat_df.columns[2:8] value_vars

id_vars = concat_df.columns[0:2].append(concat_df.columns[8:]) id_vars

concat_long_df = pd.melt(concat_df, id_vars=id_vars, value_vars=value_vars, var_name="Dimension", value_name="Index") concat_long_df.head()

concat_long_df.shape

concat_long_df.sort_values("ISO3", inplace=True) concat_long_df.head(10)

concat_long_df.reset_index(drop=True, inplace=True) concat_long_df

# Remove the rows with null index concat_long_df = concat_long_df[concat_long_df["Index"] != "#NULL!"] concat_long_df.shape

Step 6 - Save the Long Dataframe to a File

concat_long_df.to_csv("../data/national_culture_iso3_long.csv", index=False)

The End!