<jupyter_start><jupyter_text>Unistats – Merging DatasetsOne of the advantages of working with the XML representation of the data is that each record may contain *all the data* associated with a particular provider. However, the same data is also split, perhaps less conveniently, across several CSV files. The contents of these files are "normalised" data tables (in the sense of *database normalisation*)  which means that data is not duplicated across different files although common identifiers do exist that allow data from different tables to be combined back together. *[__Database normalisation__](https://en.wikipedia.org/wiki/Database_normalization) is a technique for representing tabular dataset across several data tables to help improve data integrity. Different levels of normalisation (different "normal forms") are possible and offer different pros and cons when storing and working with data.* In the Unistats CSV data files, lots of the files contain references to the UKPRN, the UK Provider Reference Number. UKPRNs are unique identifiers that identify different providers, but how do we know *which* provider they refer to?We're going to work with the data using the *pandas* package, so let's import it in the conventional way:<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>As an example dataset, consider the location dataset that provides the central co-ordinates of university site locations:<jupyter_code>locations_df = pd.read_csv("on_2021_08_11_07_24_51/LOCATION.csv")

locations_df.head()<jupyter_output><empty_output><jupyter_text>One thing we notice about this dataset is that it gives us the name of site location, but not the institution.We also notice that the site name looks a bit tatty - ampersands are HTML encoded as `&amp;`, for example. We can tidy that up by creating a simple lambda function "formula" that uses a string `.replace(OLDSTRING, NEWSTRING)` function to replace the `&amp;` occurrences by the simpler `&`:<jupyter_code># Replace HTML encoded ampersand with a simple ampersand
# Also take the opportunity to .strip() any whitespace around the location name
clean_ampersand = lambda x: x.replace("&amp;", "&").strip()

clean_ampersand("Abingdon &amp; Witney College (Abingdon Campus) ")<jupyter_output><empty_output><jupyter_text>We can apply this function to the `LOCNAME` column in out locations dataframe, reassigning the cleaned location names back to the original column:<jupyter_code>locations_df["LOCNAME"] = locations_df["LOCNAME"].apply(clean_ampersand)

locations_df.head()<jupyter_output><empty_output><jupyter_text>*We might also note that the `LOCUKPRN` hsense of at as been cast as a decimal number rather than an integer because of the presence of at least one `NaN` null value in that column. We won't be referring to that column in this notebook, so we don't really need to figure out a way of cleaning it back to an integer value right now, but we note it nonetheless.* We don't necessarily need all the columns, so let's simplify the dataframe a little and update our reference to the data so that it only contains a subset of the original data:<jupyter_code>cols = ["UKPRN", "LOCID", "LOCNAME", "LATITUDE", "LONGITUDE"]

locations_df = locations_df[cols]
locations_df.head()<jupyter_output><empty_output><jupyter_text>Annotating the Data With Provider NamesFrom the previous data download conversation, you might remember that a lookup table of provider names against UKPRNs was provided as an additional dataset. Since we know what sheet the data is in in the original spreadsheet file, we can load that sheet in directly by calling the *pandas* `.read_excel(FILENAME, SHEETNAME)` function with just the filename and the sheetname:<jupyter_code>ukprns = pd.read_excel("UNISTATS_UKPRN_lookup_20160901.xlsx", "Lookup")

ukprns.head()<jupyter_output><empty_output><jupyter_text>How then can we enrich, or annotate, the location data with the provider names?In each dataframe, we note the presence of the common `UKPRN` column. In a Microsoft Excel spreadsheet, you might combine data from two sheets using a `=VLOOKUP` formula, `INDEX MATCH` construction or the *Merge Tables* wizard.In *pandas*, we can use the `pd.merge(LEFT_DATAFRAME, RIGHT_DATAFRAME, on=COMMON_COLNAME)` function to merge the values from two dataframes based on common values in a column with the same name in each dataframe. *We can also specify the columns used from merging from the left and right dataframes using the `left_on=COLNAME1` and `right_on=COLNAME2` parameters.*<jupyter_code>annotated_locations_df = pd.merge(locations_df, ukprns, on="UKPRN")

annotated_locations_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0647021140294683, 'fraction_numerical': 0.005124919923126201, 'mean_word_length': 6.12937595129376, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '194494', 'license': 'MIT', 'n_tokens_mistral': 1316, 'n_tokens_neox': 1254, 'n_words': 601, 'path': '02.1 unistats - merging datasets.ipynb', 'repo_name': 'ConversationsWithData/unistats', 'size': '4683'}