Pivot_wider : tydr Pivot table filled with NA values

Question

My data looks like this :

structure(
  list(
    ID = c(1, 2, 3, 4, 5, 6),
    Compagny = c("x", "x", "x", "y", "y", "y"),
    Variable = c("size", "lenght", "diameter", "size", "lenght", "diameter"),
    Score = c(12, 15, 8, 20, 4, 7)
  ),
  row.names = c(NA, -6L),
  class = "data.frame"
)

ID	Compagny	Variable	Score
1	x	size	12
2	x	lenght	15
3	x	diameter	8
4	y	size	20
5	y	lenght	4
6	y	diameter	7

i want to pivot wider so that variables are columns :

ID	Compagny	size	lenght	diameter
1	x	12	15	8
2	y	20	4	7

I've Followed this tutorial because i had the same problem pivot_wider issue "Values in `values_from` are not uniquely identified; output will contain list-cols"

I Copy/paste this lines of codes found above :

d %>%
  group_by(name) %>%
  mutate(row = row_number()) %>%
  tidyr::pivot_wider(names_from = name, values_from = val) %>%
  select(-row)

That became

  PivoTable <- LongTable %>%
  group_by(score) %>%
  mutate(row = row_number()) %>%
  tidyr::pivot_wider(names_from = score, values_from = mean) %>%
  select(-row)

And I also have a special identifier for each row. It still doesn't work even though I dont have a propre table, but a matrix with NA values instead (cf. picture)

Final results

This is a pretty common logic error with `pivot_wider` caused when the original columns left (`compagne`, `variable`) are still unique identifiers of each original record. I don't know what column you're using in the `names_from` section but why is it not `variable`? That column is the issue. — geoff, Jan 12 '22 at 14:51
Your data has IDs that go from 1 to 6, but the output you're trying to get has just IDs 1 or 2—how do you intend to get from one to the other? One of the problems with simply copying code from a tutorial is that you don't actually have the same column names in your data as in your code. Not sure if that's actually an issue you're having or you just forgot to change things for your post, but it's unclear what you're actually doing since the code doesn't match the data — camille, Jan 12 '22 at 15:04
Hello Camille, You are right so I added a buch of code i ran, in case of example — Mathias Lauber, Jan 13 '22 at 13:03
Thank you Geoff, I removed one of the two colums (VARIABLES, SCORE). The two columns were basically were redundent. That, associated with Maël answer made a decent dataframe without NA, thank you very much — Mathias Lauber, Jan 13 '22 at 13:04

score 2 · Accepted Answer · answered Jan 12 '22 at 14:51

2

You can do this:

df %>% 
  pivot_wider(id_cols = -ID, names_from = Variable, values_from = Score) %>% 
  mutate(ID = row_number(), .before = Compagny)

# A tibble: 2 x 5
     ID Compagny  size lenght diameter
  <int> <chr>    <int>  <int>    <int>
1     1 x           12     15        8
2     2 y           20      4        7

answered Jan 12 '22 at 14:51

Maël

45,206
3
29
67

1

Hello I implemented your code, that was quiet similar to what I had before, and its worked perfectly ! I also delete one column that was totally redundant with an other (Geoff advice). It was also required to create and index (ID) of each rows to help identify them – Mathias Lauber Jan 13 '22 at 13:07

score 0 · Answer 2 · answered Jan 13 '22 at 13:16

ESGTable <- select(EU_ESG_PILLARS,compagny, variable_name, mean_value)
ESGTable <- tibble::rowid_to_column(ESGTable, "ID")
# Petittable <- tibble::rowid_to_column(Petittable, "ID")
StackTry <- ESGTable %>% 
  pivot_wider(id_cols = -ID, names_from = variable_name, values_from = mean_value) %>% 
  mutate(ID = row_number(), .before = compagny)
## --> worked

Adding and index column (1-->n rows)
delete one colomn that was redundant
pivot_wider(id_cols --> identify each row uniquely

Thank you very much for such quick help ! Output

Pivot_wider : tydr Pivot table filled with NA values

2 Answers2