<jupyter_start><jupyter_text>Data: Pivoting Data*Purpose*: Data is easiest to use when it is *tidy*. In fact, grama is specifically designed to use tidy data. But not all data we'll encounter is tidy! To that end, in this exercise we'll learn how to tidy our data by *pivoting*.As a result of learning how to quickly *tidy* data, you'll vastly expand the set of datasets you can analyze. Rather than fighting with data, you'll be able to quickly wrangle and extract insights.  Setup<jupyter_code>import grama as gr
DF = gr.Intention()
%matplotlib inline

# For assertion
from pandas.api.types import is_integer_dtype<jupyter_output><empty_output><jupyter_text>Tidy Data*Tidy Data* is a very simple---but *very powerful*---concept for structuring a dataset.  ![Stylized text providing an overview of Tidy Data. The top reads “Tidy data is a standard way of mapping the meaning of a dataset to its structure. - <NAME>.” On the left reads “In tidy data: each variable forms a column; each observation forms a row; each cell is a single measurement.” There is an example table on the lower right with columns ‘id’, ‘name’ and ‘color’ with observations for different cats, illustrating tidy data structure.](./images/tidydata_1.jpg)Artwork by @allison_horst  Tidy Data: DefinitionA tidy dataset has three properties:- each variable forms a column- each observation forms a row- each cell is a single measurement As an example, the following dataset is tidy:<jupyter_code>from grama.data import df_stang
df_stang.head()<jupyter_output><empty_output><jupyter_text>The observations are all measured material properties taken at a particular angle (with respect to the direction in which the specimens were rolled). Each column reports values for just one variable, each row corresponds to a single observation, and every cell reports just one measurement.However, the following form of the same dataset is *not* tidy:<jupyter_code>from grama.data import df_stang_wide
df_stang_wide<jupyter_output><empty_output><jupyter_text>This dataset is *not* tidy: The angle of each measurement `00, 45, 90` is a variable, but these numerical values are expressed as column names. Put differently, some of the values are not in cells, but rather in the column names.  Why tidy data?Tidy data makes analysis *easier*. Putting our data in tidy form means we can use a *consistent* set of tools to work with *any* dataset. ![On the left is a happy cute fuzzy monster holding a rectangular data frame with a tool that fits the data frame shape. On the workbench behind the monster are other data frames of similar rectangular shape, and neatly arranged tools that also look like they would fit those data frames. The workbench looks uncluttered and tidy. The text above the tidy workbench reads “When working with tidy data, we can use the same tools in similar ways for different datasets…” On the right is a cute monster looking very frustrated, using duct tape and other tools to haphazardly tie data tables together, each in a different way. The monster is in front of a messy, cluttered workbench. The text above the frustrated monster reads “...but working with untidy data often means reinventing the wheel with one-time approaches that are hard to iterate or reuse.”](./images/tidydata_3.jpg)Artwork by @allison_horst Note that untidy data is not *bad* data; untidy data are simply harder to work with when doing data analysis. Data often come in untidy form when they are reported, say in a paper or a presentation. For instance, the wide form of the Stang et al. dataset can easily fit on one page:<jupyter_code>df_stang_wide<jupyter_output><empty_output><jupyter_text>However, the tidy form of the same dataset is far less compact:<jupyter_code>df_stang<jupyter_output><empty_output><jupyter_text>ExercisesLet's practice identifying tidy and untidy data.  __q1__ IdentifyInspect the following dataset; answer the questions under *observations* below.<jupyter_code>## TASK: No need to edit; run and inspect
df_cases = gr.df_make(
    country=["FR", "DE", "US"],
    year2011=[7000, 5800, 15000],
    year2012=[6900, 6000, 14000],
    year2013=[7000, 6200, 13000],
)
df_cases<jupyter_output><empty_output><jupyter_text>*Observations*- What are the *variables* in this dataset?  - (Your response here)- Is this dataset *tidy*? Why or why not?  - (Your response here)- What are the *variables* in this dataset?  - Country, year, and some unknown quantity (n, count, etc.)- Is this dataset *tidy*? Why or why not?  - No; the year values are in the column names.  __q2__ IdentifyInspect the following dataset; answer the questions under *observations* below.<jupyter_code>## TASK: No need to edit; run and inspect
df_alloys1 = gr.df_make(
    thick=[0.022, 0.022, 0.032, 0.032],
    E_00=[10600, 10600, 10400, 10300],
    mu_00=[0.321, 0.323, 0.329, 0.319],
    E_45=[10700, 10500, 10400, 10500],
    mu_45=[0.329, 0.331, 0.318, 0.326],
    rep=[1, 2, 1, 2],
)
df_alloys1<jupyter_output><empty_output><jupyter_text>*Observations*- What are the *variables* in this dataset?  - (Your response here)- Is this dataset *tidy*? Why or why not?  - (Your response here)- What are the *variables* in this dataset?  - Thickness `thick`, elasticity `E`, poisson's ratio `mu`, angle (in column names), replication `rep`- Is this dataset *tidy*? Why or why not?  - No; the angle values are in the column names.  __q3__ IdentifyInspect the following dataset; answer the questions under *observations* below.<jupyter_code>## TASK: No need to edit; run and inspect
df_alloys2 = gr.df_make(
    thick=[0.022, 0.022, 0.032, 0.032],
    var=["E", "mu", "E", "mu"],
    value=[10700, 0.321, 10500, 0.323],
    rep=[1, 1, 2, 2],
    angle=[0, 0, 0, 0],
)
df_alloys2<jupyter_output><empty_output><jupyter_text>*Observations*- What are the *variables* in this dataset?  - (Your response here)- Is this dataset *tidy*? Why or why not?  - (Your response here)- What are the *variables* in this dataset?  - Thickness `thick`, elasticity `E`, poisson's ratio `mu`, `angle`, replication `rep`- Is this dataset *tidy*? Why or why not?  - No; the column `value` contains values of two *different* variables `E` and `mu`.  Pivoting DataThe untidy datasets above fail to be tidy because they have the wrong *shape*; we can tidy these datasets by *pivoting* the data. There are just two pivots we need to learn about: `tf_pivot_longer()` and `tf_pivot_wider()`.  Pivot LongerLet's take another look at the `df_cases` example dataset:<jupyter_code>## NOTE: No need to edit
df_cases<jupyter_output><empty_output><jupyter_text>This dataset is *too wide*; the column names `year2011,year2012,year2013` should really be numbers inside a single `year` column. We can *pivot longer* to move from a wide structure to a longer one:<jupyter_code>## NOTE: No need to edit
(
    df_cases
    >> gr.tf_pivot_longer(
        columns=["year2011", "year2012", "year2013"],
        names_to="year",
        values_to="count",
    )
)<jupyter_output><empty_output><jupyter_text>Note what we had to provide as arguments to `gr.tf_pivot_longer()`:- `columns` specifies the columns to involve in the pivoting- `names_to` specifies what new column will contain the previous column names- `value_to` specifies what new column will contain the previous cell valuesOften, a pivot alone is not enough to fully clean a dataset; for instance, we would still need to remove the `year` string from each cell entry. However, we can do this much more easily once the data are pivoted into a longer format:<jupyter_code>## NOTE: No need to edit
(
    df_cases
    >> gr.tf_pivot_longer(
        columns=["year2011", "year2012", "year2013"],
        names_to="year",
        values_to="count",
    )
    >> gr.tf_mutate(year=gr.str_replace(DF.year, "year", ""))
)<jupyter_output><empty_output><jupyter_text>__q4__ Pivot the dataPivot the dataset `df_alloys1` to place each of `E_00, mu_00, E_45, mu_45` as values in a new `variable` column, and move the original values to a new `value` column. Answer the questions under *observations* below.<jupyter_code>## TASK: Tidy the data, make sure to provide `variable` and `value` columns
df_res1 = (
    df_alloys1
# task-begin
# task-end
# solution-begin
    >> gr.tf_pivot_longer(
        columns=["E_00", "mu_00", "E_45", "mu_45"],
        names_to="variable",
        values_to="value",
    )
# solution-end
)

## NOTE: Use this to check your work
print(df_res1)

assert \
    {"thick", "rep", "variable", "value"} == set(df_res1.columns), \
    'columns of df_res1 are not ["thick", "rep", "variable", "value"]'

assert \
    {"E_00", "mu_00", "E_45", "mu_45"} == set(df_res1.variable), \
    'Entries in df_res1.variable are incorrect'<jupyter_output>thick  rep variable      value
0   0.022    1     E_00  10600.000
1   0.022    2     E_00  10600.000
2   0.032    1     E_00  10400.000
3   0.032    2     E_00  10300.000
4   0.022    1    mu_00      0.321
5   0.022    2    mu_00      0.323
6   0.032    1    mu_00      0.329
7   0.032    2    mu_00      0.319
8   0.022    1     E_45  10700.000
9   0.022    2     E_45  10500.000
10  0.032    1     E_45  10400.000
11  0.032    2     E_45  10500.000
12  0.022    1    mu_45      0.329
13  0.022    2    mu_45      0.331
14  0.032    1    mu_45      0.318
15  0.032    2    mu_45      0.326<jupyter_text>*Observations*- Is this dataset tidy? Why or why not?  - (Your response here)- Is this dataset tidy? Why or why not?  - This dataset is not tidy; the entries in the `value` column include values of `E` and `mu` (two different variables). Also, the `variable` column contains both variable names and angle values.  Using selection helpersOne useful feature of `gr.tf_pivot_longer()` is that we can use [selection helpers](https://zdelrosario.github.io/evc-course/exercises_solution/d02-e-data01-isolate-solution.htmlselection-helpers) to choose which columns to use in a pivot. This can save us a lot of typing, and allows us to write code that can react to multiple different datasets. Let's practice using selection helpers by re-doing the previous task.  __q5__ Simplify with selection helpersUse a single [selection helper](https://zdelrosario.github.io/evc-course/exercises_solution/d02-e-data01-isolate-solution.htmlselection-helpers) to simplify the `columns` argument to `gr.tf_pivot_longer()`.*Hint*: There are multiple ways to do this: The string `"E|mu"` will `match` the strings `"E"` or `"mu"`. The string `"\\d+"` will `match` *any string of consecutive digits*.<jupyter_code>## TASK: Replace the `columns` argument with a selection helper
df_simplified = (
    df_alloys1
    >> gr.tf_pivot_longer(
# task-begin
        # columns=["E_00", "mu_00", "E_45", "mu_45"],
# task-end
# solution-begin
        columns=gr.matches("E|mu"), 
# solution-end
        names_to="variable",
        values_to="value",
    )
)

## NOTE: Use this to check your work
print(df_simplified)

assert \
    {"thick", "rep", "variable", "value"} == set(df_simplified.columns), \
    'columns of df_res1 are not ["thick", "rep", "variable", "value"]'

assert \
    {"E_00", "mu_00", "E_45", "mu_45"} == set(df_simplified.variable), \
    'Entries in df_res1.variable are incorrect'<jupyter_output>thick  rep variable      value
0   0.022    1     E_00  10600.000
1   0.022    2     E_00  10600.000
2   0.032    1     E_00  10400.000
3   0.032    2     E_00  10300.000
4   0.022    1    mu_00      0.321
5   0.022    2    mu_00      0.323
6   0.032    1    mu_00      0.329
7   0.032    2    mu_00      0.319
8   0.022    1     E_45  10700.000
9   0.022    2     E_45  10500.000
10  0.032    1     E_45  10400.000
11  0.032    2     E_45  10500.000
12  0.022    1    mu_45      0.329
13  0.022    2    mu_45      0.331
14  0.032    1    mu_45      0.318
15  0.032    2    mu_45      0.326<jupyter_text>Pivot WiderJust as a dataset can be "too wide", it can also be "too long." In this case, we can use `gr.tf_pivot_wider()`. Let's look at an illustrative example:<jupyter_code>## NOTE: No need to edit
df_long = gr.df_make(
    variable=["x", "y", "f", "x", "y", "f"],
    value=[0, 0, 1, 0, 1, 0],
    observation=[0, 0, 0, 1, 1, 1],
)
df_long<jupyter_output><empty_output><jupyter_text>Here, the `value` column has mixed variables; values of `x`, `y`, and `f` comingle in the `value` column. Really we should have the *columns* `x`, `y`, and `f`: In this sense the data is *too long*. Let's pivot wider to tidy the data.<jupyter_code>## NOTE: No need to edit
(
    df_long
    >> gr.tf_pivot_wider(
        names_from="variable",
        values_from="value",
    )
)<jupyter_output><empty_output><jupyter_text>Note that we had to provide just two inputs to `gr.tf_pivot_wider()`:- `names_from` specifies which column will provide the new column names- `value_from` specifies which column will provide the new column valuesLet's get some practice!  __q6__ Pivot the dataPivot the dataset `df_alloys2` to provide `E` and `mu` as columns in `df_res2`. Answer the questions under *observations* below.<jupyter_code>## TASK: Pivot the dataset wider
df_res2 = (
    df_alloys2
# solution-begin
    >> gr.tf_pivot_wider(
        names_from="var",
        values_from="value",
    )
# solution-end
)

## NOTE: Use the following to check your work
print(df_res2)

assert \
    {"thick", "rep", "angle", "E", "mu"} == set(df_res2.columns), \
    'df_res2 does not have the columns ["thick", "rep", "angle", "E", "mu"]'<jupyter_output>thick  rep  angle        E     mu
0  0.022    1      0  10700.0  0.321
1  0.032    2      0  10500.0  0.323<jupyter_text>*Observations*- Is this dataset tidy? Why or why not?  - (Your response here)- Is this dataset tidy? Why or why not?  - This dataset is tidy! Every column is one variable, every row is one observation, and every cell contains one value.  Warning: No observation identifierWhile the arguments to `gr.tf_pivot_wider()` only target two columns, we *do* need to think carefully about what other columns are in the dataset. It is important that the other columns help identify which values are associated with the same observation---the easiest way to do this is with some sort of observation identifier column.Let's take a look at what happens when we *do not* have a way to associate values with the same observation:<jupyter_code>## NOTE: No need to edit
(
    gr.df_make(
        variable=["x", "y", "f", "x", "y", "f"],
        value=[0, 0, 1, 0, 1, 0],
        # observation=[0, 0, 0, 1, 1, 1], # Remove the observation identifier
    )
    >> gr.tf_pivot_wider(
        names_from="variable",
        values_from="value",
    )
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10030790283954841, 'fraction_numerical': 0.05685939103660623, 'mean_word_length': 3.7454545454545456, 'pattern_counts': {'":': 0, '<': 67, '<?xml version=': 0, '>': 83, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, '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': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '566611', 'license': 'MIT', 'n_tokens_mistral': 5229, 'n_tokens_neox': 4665, 'n_words': 1876, 'path': 'exercises/e-data03-pivot-master.ipynb', 'repo_name': 'cstakeda/evc-course', 'size': '14623'}