<jupyter_start><jupyter_text>Variables are stored in both Rows and Columns  "Housekeeping"<jupyter_code>%load_ext lab_black
import numpy as np
import pandas as pd
pd.set_option("display.max_columns", 40)<jupyter_output><empty_output><jupyter_text>Example: WeatherThe [Global Historical Climatology Network](https://www.ncdc.noaa.gov/data-access/land-based-station-data/land-based-datasets/global-historical-climatology-network-ghcn) collects daily weather. For this example, data for one weather station (MX17004) in Mexico are used.  Load the DataThe raw dataset comes in a format that is a mixture of a fixed-width style with occasional usage of characters as seperators. Some tedious cleaning work is necessary.<jupyter_code># Extract the data as one column and
# use string slicing to obtain groups of columns.
weather = pd.read_csv("data/weather.txt", header=None, sep="^")

# First, remove the weird character seperators,
# then split the columns by whitespace, and
# finally name them appropriately.
days = (
    weather[0]
    .map(lambda x: x[21:])
    .str.replace("OI", "  ")
    .str.replace("OS", "  ")
    .str.replace("SI", "  ")
    .str.replace("I", " ")
    .str.replace("S", " ")
    .str.replace("B", " ")
    .str.replace("D", " ")
    .map(str.lstrip)
    .str.split(r"\s+", expand=True)
)[list(range(31))].rename(columns={i: f"d{i+1}" for i in range(31)})

# The non-temperature columns can be extracted as simple slices.
weather = pd.DataFrame(
    data={
        "id": weather[0].map(lambda x: x[:11]),
        "year": weather[0].map(lambda x: x[11:15]).astype(int),
        "month": weather[0].map(lambda x: x[15:17]).astype(int),
        "element": weather[0].map(lambda x: x[17:21]).str.lower(),
    }
)

# The temperatures were stored as whole integers
# with -9999 indicating missing values.
for i in range(1, 32):
    weather[f"d{i}"] = days[f"d{i}"].astype(float) / 10
weather = weather.replace(-999.9, np.NaN)

# Discard the non-temperature observations and
# sort the dataset as in the paper.
weather = (
    weather[weather["element"].isin(["tmax", "tmin"])]
    .sort_values(["id", "year", "month", "element"])
    .reset_index(drop=True)
)<jupyter_output><empty_output><jupyter_text>Messy DataBelow is a dataset assumed to have been provided like this as "raw", i.e., the data analyst did not do the above parsing work but some third party instead.> The most complicated form of messy data occurs when variables are stored in both rows and columns. Table 11 shows daily weather data from the Global Historical Climatology Network for one weather station (MX17004) in Mexico for five months in 2010. It has variables inindividual columns (`"id"`, `"year"`, `"month"`), spread across columns (day, `"d1"`–`"d31"`) and across rows (`"tmin"` and `"tmax"` for the minimum and maximum temperatures). Months with less than 31 days have missing values for the last day(s) of the month. The `"element"` column is not a variable: it stores the *names* of variables.<jupyter_code>weather[(weather["year"] == 2010)].head(10)<jupyter_output><empty_output><jupyter_text>Molten Data > To tidy this dataset we first melt it with colvars `"id"`, `"year"`, `"month"`, and the column that contains the actual variable names, `"element"` [...]. For presentation, we have dropped the missing values, making them implicit rather than explicit. This is permissible because we know how many days are in each month and can easily reconstruct the explicit missing values.<jupyter_code># Melt the dataset and extract a date column.
molten_weather = (
    pd.melt(weather, id_vars=["id", "year", "month", "element"], var_name="day")
    .assign(day=lambda x: x["day"].str.extract("(\d+)").astype(int))
    .assign(date=lambda x: pd.to_datetime(x[["year", "month", "day"]], errors="coerce"))
)
molten_weather = molten_weather[["id", "date", "element", "value"]]

# Make the missing values implicit.
molten_weather = molten_weather[molten_weather["value"].notnull()]

# Sort the data as in the paper.
molten_weather = molten_weather.sort_values(["id", "date", "element"])
molten_weather = molten_weather.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>> This dataset is mostly tidy, but we have two variables stored in rows: `"tmin"` and `"tmax"`, the type of observation.<jupyter_code>molten_weather[(molten_weather["date"].dt.year == 2010)].head(10)<jupyter_output><empty_output><jupyter_text>Tidy Data > Fixing this requires the cast, or unstack, operation. This performs the inverse of melting by rotating the element variable back out into the columnsBelow, [pd.DataFrame.unstack()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.unstack.html) uses a DataFrame's index as columns to unstack over.<jupyter_code>tidy_weather = molten_weather.set_index(["id", "date", "element"]).unstack()

# Make the column headers look as in the paper.
tidy_weather.columns = tidy_weather.columns.droplevel(0)
tidy_weather.columns.name = None
tidy_weather = tidy_weather.reset_index()<jupyter_output><empty_output><jupyter_text>> This form is tidy. There is one variable in each column, and each row represents a day’s observations.<jupyter_code>tidy_weather[(tidy_weather["date"].dt.year == 2010)].head(10)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1365788975184694, 'fraction_numerical': 0.015343815116499336, 'mean_word_length': 5.2559241706161135, 'pattern_counts': {'":': 4, '<': 29, '<?xml version=': 0, '>': 34, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '44061', 'license': 'MIT', 'n_tokens_mistral': 1776, 'n_tokens_neox': 1642, 'n_words': 526, 'path': '3_variables_are_stored_in_both_rows_and_columns.ipynb', 'repo_name': 'Danielahmo/tidy-data', 'size': '5279'}