use pivot_longer and pivot_wider in combination

user_8039978 0
Viewed 144

I have a dataframe with lots of NaNin various columns.

df <- data.frame(
  Data1 = c(3,2,1,NaN, NaN, NaN),
  Data2  = c(NaN, NaN, NaN, 3,5,3),
  Data3 = c(NaN, NaN, 7,5,1, NaN)
)

I'm trying to get rid of the NaN values by using pivot_longer, filtering on the NaNvalues and using pivot_wider to put the positive numbers back again into their original columns, which, however, fails:

library(tidyr)
df %>%
  pivot_longer(c("Data1","Data2","Data3")) %>%
  filter(!is.na(value)) %>%
  pivot_wider(names_from = name,
              values_from = value)
# A tibble: 1 x 3
  Data1     Data3     Data2    
  <list>    <list>    <list>   
1 <dbl [3]> <dbl [3]> <dbl [3]>
Warning message:
Values are not uniquely identified; output will contain list-cols.
* Use `values_fn = list` to suppress this warning.
* Use `values_fn = length` to identify where the duplicates arise
* Use `values_fn = {summary_fun}` to summarise duplicates

What's wrong with the code and how can this output be achieved?

  Data1 Data2 Data3
      3     3     7
      2     5     5
      1     3     1
tidyr r
3 Answers

This particular problem could be neatly solved using purrr:

map_dfr(df, na.omit)

  Data1 Data2 Data3
  <dbl> <dbl> <dbl>
1     3     3     7
2     2     5     5
3     1     3     1
user_59645570

The code doesn't necessarily fail but return a warning since you have more than one value in each cell. If the number of values in each column are going to be the same you can unnest the list output.

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(starts_with('Data'), values_drop_na = TRUE) %>%
  arrange(name) %>%
  pivot_wider(names_from = name,values_from = value, values_fn = list) %>%
  unnest()

#  Data1 Data2 Data3
#  <dbl> <dbl> <dbl>
#1     3     3     7
#2     2     5     5
#3     1     3     1
user_39629140

Base R:

I prefer sapply with na.omit:

sapply(df, na.omit)

Output:

  Data1 Data2 Data3
  <dbl> <dbl> <dbl>
1     3     3     7
2     2     5     5
3     1     3     1
user_87083640
Related