CODEWITHSUNDEEP
rtidyr
Chris Ruehlemann
Chris Ruehlemann

Reputation: 21442

use pivot_longer and pivot_wider in combination

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

Upvotes: 2

Views: 432

Answers (3)

U13-Forward
U13-Forward

Reputation: 71610

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

Upvotes: 1

tmfmnk
tmfmnk

Reputation: 40171

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

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 389235

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

Upvotes: 2

Related Questions