CODEWITHSUNDEEP
rtidyversetidyr
benalbert342
benalbert342

Reputation: 71

tidyr:Pivot_wider replace values with data type

I have a data frame with variables in the rows and the columns that both contain variables, so I am trying to use pivot wide tidy the data. My data looks like the following: 

head(df)
# A tibble: 6 x 4
  State    Year Var                                                           X
  <chr>   <dbl> <chr>                                                     <dbl>
1 ALABAMA  2001 APPALACHIAN REGIONAL COMMISSION (ARC)                   3048031
2 ALABAMA  2001 CORPORATION FOR NATIONAL AND COMMUNITY SERVICE (CNCS)   1765835
3 ALABAMA  2001 DEPARTMENT OF AGRICULTURE (USDA)                      282530429
4 ALABAMA  2001 DEPARTMENT OF COMMERCE (DOC)                           17838084
5 ALABAMA  2001 DEPARTMENT OF DEFENSE (DOD)                            21160159
6 ALABAMA  2001 DEPARTMENT OF EDUCATION (ED)                          174634348

Where state is the entity, Year is the time dimension, Var is a list of the variables I am trying to pivot, and X is a list of values for each variable. When I use the following code: 

library(tidyverse)

df %<>% 
  pivot_wider(names_from = Var, values_from = X)

R returns a warning message stating that:

Warning message:
Values in `X` are not uniquely identified; output will contain list-cols.
* Use `values_fn = list(X = list)` to suppress this warning.
* Use `values_fn = list(X = length)` to identify where the duplicates arise
* Use `values_fn = list(X = summary_fun)` to summarise duplicates

and my data replaces all of the values with the data, as shown below.

head(df)
# A tibble: 6 x 35
  State  Year `APPALACHIAN RE~ `CORPORATION FO~ `DEPARTMENT OF ~ `DEPARTMENT OF ~ `DEPARTMENT OF ~ `DEPARTMENT OF ~ `DEPARTMENT OF ~ `DEPARTMENT OF ~
  <chr> <dbl>      <list<dbl>>      <list<dbl>>      <list<dbl>>      <list<dbl>>      <list<dbl>>      <list<dbl>>      <list<dbl>>      <list<dbl>>
1 ALAB~  2001              [1]              [1]              [1]              [1]              [1]              [1]              [1]              [1]
2 ALAS~  2001              [0]              [1]              [1]              [1]              [1]              [1]              [1]              [1]
3 ARIZ~  2001              [0]              [1]              [1]              [1]              [1]              [1]              [1]              [1]
4 ARKA~  2001              [0]              [1]              [1]              [1]              [1]              [1]              [1]              [1]
5 CALI~  2001              [0]              [1]              [1]              [1]              [1]              [1]              [1]              [1]
6 COLO~  2001              [0]              [1]              [1]              [1]              [1]              [1]              [1]              [1]
# ... with 25 more variables: `DEPARTMENT OF HOUSING AND URBAN DEVELOPMENT (HUD)` <list<dbl>>, `DEPARTMENT OF JUSTICE (DOJ)` <list<dbl>>, `DEPARTMENT OF
#   LABOR (DOL)` <list<dbl>>, `DEPARTMENT OF THE INTERIOR (DOI)` <list<dbl>>, `DEPARTMENT OF TRANSPORTATION (DOT)` <list<dbl>>, `ENVIRONMENTAL PROTECTION
#   AGENCY (EPA)` <list<dbl>>, `FEDERAL EMERGENCY MANAGEMENT AGENCY (FEMA)` <list<dbl>>, `INSTITUTE OF MUSEUM AND LIBRARY SERVICES (IMLS)` <list<dbl>>,
#   `NATIONAL AERONAUTICS AND SPACE ADMINISTRATION (NASA)` <list<dbl>>, `NATIONAL ENDOWMENT FOR THE ARTS (NEA)` <list<dbl>>, `NATIONAL ENDOWMENT FOR THE
#   HUMANITIES (NEH)` <list<dbl>>, `NATIONAL SCIENCE FOUNDATION (NSF)` <list<dbl>>, `SMALL BUSINESS ADMINISTRATION (SBA)` <list<dbl>>, `FEDERAL MEDIATION
#   AND CONCILIATION SERVICE (FMCS)` <list<dbl>>, `NATIONAL ARCHIVES AND RECORDS ADMINISTRATION (NARA)` <list<dbl>>, `AGENCY FOR INTERNATIONAL DEVELOPMENT
#   (USAID)` <list<dbl>>, `JAPAN-UNITED STATES FRIENDSHIP COMMISSION (JUSFC)` <list<dbl>>, `UNITED STATES INSTITUTE OF PEACE (USIP)` <list<dbl>>, `CORPS OF
#   ENGINEERS - CIVIL WORKS (USACE)` <list<dbl>>, `DEPARTMENT OF STATE (DOS)` <list<dbl>>, `NATIONAL LABOR RELATIONS BOARD (NLRB)` <list<dbl>>, `NUCLEAR
#   REGULATORY COMMISSION (NRC)` <list<dbl>>, `SOCIAL SECURITY ADMINISTRATION (SSA)` <list<dbl>>, `SELECTIVE SERVICE SYSTEM (SSS)` <list<dbl>>,
#   `NA` <list<dbl>>

I am wondering why the original values are being erased from the pivot, and also what I can do to stop this from happening.

Upvotes: 4

Views: 4884

Answers (2)

stevec
stevec

Reputation: 52358

I arrived here because the result of pivot_wider() was a wildly different to what I expected (it was producing NULLs and lists, instead of simple numbers).

In my case, it was simply because I had duplicate rows, which can easily be removed with

df %>% distinct(x, y, .keep_all = TRUE)

See here

Upvotes: 0

akrun
akrun

Reputation: 887291

We may need a sequence column as there are duplicates. Grouped by 'State', 'Year', 'Var', create a sequence column with row_number() and then apply the pivot_wider

library(dplyr)
library(tidyr)
df %>% 
  group_by(State, Year, Var) %>%
  mutate(rn = row_number()) %>%
  pivot_wider(names_from = Var, values_from = X)

Upvotes: 4

Related Questions