16

I have data from an excel sheet imported like this: 

F4_Off <- readxl::read_xlsx("myExcel.xlsx", sheet = "Offline", col_names = TRUE, range = "I1:L285")
F4_Off

F4_On <- readxl::read_xlsx("myExcel.xlsx", sheet = "Online", col_names = TRUE, range = "J1:M68")
F4_On

This is combined using:

F4_Gesamt <- rbind(F4_Off, F4_On)

and the data looks like this:

A tibble: 351 x 4
    nein Smartphone `Computer / Laptop / Tablet` `keine Angabe`
   <dbl>      <dbl>                        <dbl>          <dbl>
 1   NA         NA                            1.             NA
 2   NA          1.                          NA              NA
 3   NA          1.                          NA              NA
 4   NA          1.                          NA              NA
 5   NA          1.                           1.             NA
 6   NA          1.                           1.             NA
 7   NA          1.                           1.             NA
 8   NA          1.                          NA              NA
 9   NA         NA                            1.             NA
10    1.        NA                           NA              NA

Actually i want this to be summed up per Column (summed up, count the "1.") and transformed into a table like this:

    Type                                    Value
    <chr>                                   <dbl>
1   nein                                    162
2   Smartphone                              120
3   `Computer / Laptop / Tablet`            93
4   `keine Angabe`                          16

i can achieve the summing up by doing: 

F4_Gesamt_sum <- colSums(F4_Gesamt, na.rm = TRUE, dims = 1)

then it looks like this:

                   nein                 Smartphone Computer / Laptop / Tablet               keine Angabe 
                    162                        120                         93                         16

now:

str(F4_Gesamt_sum)

gives: 

Named num [1:4] 162 120 93 16
 - attr(*, "names")= chr [1:4] "nein" "Smartphone" "Computer / Laptop / Tablet" "keine Angabe"

And this is the point where i fails miserably for days now, how do i get it to look like I "faked" it above?

At the end I want to feed this data to ggplot and make a nice barchart.

Rajat Jain
  • 1,339
  • 2
  • 16
  • 29
xBarns
  • 291
  • 1
  • 3
  • 12
  • `F4_Gesamt %>% gather(Type, Value) %>% group_by(Type) %>% summarise(Value = sum(Value, na.rm = TRUE))` perhaps? – markus Jun 18 '18 at 06:33
  • @markus Thanks, this works – xBarns Jun 18 '18 at 07:59
  • @markus, sorry if i do this wrong, i am new to stackoverflow, how would i go about adding a column "age" to F4_Off (i know cbind...) and then get a resulting set that has 3 columns, "age, "Type" and Value where the count in value is grouped by "Age" and "Type"? (Please let me know if this should be a question of its own, i will then create a new one) – xBarns Jun 18 '18 at 12:30
  • You can add a column in many ways. Use `F4_Off$age <- ...`, `mutate` from `dplyr` or `add_column` from the `tibble` package (if you prefer `tidyverse` solutions). I guess it depends on where `age` is coming from. You might be better off asking a new question with a minimal, reproducible example. ps Use `dput()` to share your data. Regards – markus Jun 18 '18 at 19:43
  • @markus, Thanks – xBarns Jun 19 '18 at 07:28

1 Answers1

29

Try 

as.data.frame(F4_Gesamt_sum)

or

(function(x)data.frame(Type=names(x), Value=x))(F4_gesamt_sum)
lebatsnok
  • 6,329
  • 2
  • 21
  • 22