R studio

The goal is to convert this dataset to the tidy format shown below. You will solve this in two different ways in (b) and (c)

Location	Year	Employee_Contribution	Employer_Contribution
United States	2013	1170	4401
United States	2014	1234	4598
United States	2015	1255	4708
United States	2016	1325	4776
United States	2017	1415	4953
United States	2018	1427	5288
Alabama	2013	1379	3825
Alabama	2014	1362	4164
Alabama	2015	1228	4505
Alabama	2016	1510	4026
Alabama	2017	1593	4482
Alabama	2018	1453	4636

1. Approach 1: Pivot and separate the two sets of columns (Employee_Contribution, Employer_Contribution) and create two temporary tables. Then join the two tables. Specifically, do the following steps. Show your code for each step and any summary output from the code.

   1. Read the .csv file using read_csv (NOT read.csv) and store it in a table.

   2. Select only the Location column and the columns containing the word Employee

   3. Pivot the data in the Employee columns into a pair of names_to and values_to columns called Year and Employee_Contribution

   4. The Year column has values such as 2013__Employee_Contribution. It should contain only the year (e.g., 2013). Use separate() to separate the values into the year component and discard the remaining portion. [Hint: use __ as the separator. Specifying NA in the into parameter discards the variable.]

   5. Store the result of the above pipeline in a table.

   6. Repeat the above steps for the Employer columns and store the result in another table.

   7. Join the two tables. Check that the resulting table matches the desired tidy format.

2. Approach 2: Pivot all columns into two names_to columns, then pivot again! Specifically, do the following steps. Show your code for each step.

   1. Read the .csv file using read_csv (NOT read.csv) and store it in a table.

   2. Pivot_longer all columns (except Location) into two names_to columns. This requires a names_sep to be specified. Read the help for pivot_longer().

   3. Pivot_wider a pair of columns from the previous step. Check that the resulting table matches the desired tidy format.