I want to calculate sum of a column, but categorised by other column in R

Question

I have a huge survey csv file, which I imported in Rstudio. Here is a sample data

User 1  User 2  Duration
5       2       7-11 months
5       5       Less than 1 month
5       5       4-6 months
5       5       4-6 months
5       4       4-6 months
5       5       2 years
4       6       1 year
4       4       4-6 months
4       8       7-11 months
4       4       2 years
3       3       Less than 1 month
3       3       7-11 months
3       3       1-3 months

I want to calculate sum of user1, filtering with duration, say 4-6 months.

Till now I have used :

df %>% group_by(Duration) %>% summarise(count=n_distinct(User1)) %>% 
  +     filter(Duration=="4-6 months")

But its not returning proper values. Please suggest me the solution.

If you want the sume of user1, use `sum(User1)` instead of `n_distinct(User1)`. `n_distinct` will give you the number of distinct values, not the sum of values. — Gregor Thomas, Aug 25 '17 at 06:21
Also, please save the RStudio tag for problems with the code editor RStudio (for example, if your code ran fine in the terminal or RGui, but didn't work in RStudio). — Gregor Thomas, Aug 25 '17 at 06:22
And be careful about using the right name. The way your data is printed, it looks like the column name is `User 1`. In your paragraph, you use `user1`, and in your code block you use `User1`. These are all different. They all should be the same. Capitalization matters. Spaces matter (and spaces in column names complicate things - it's easier to avoid them). — Gregor Thomas, Aug 25 '17 at 06:24
It has few blank values as well, how should avoid that in this code? — Ankita Mishra, Aug 25 '17 at 06:28
Have a look at `?sum`, you can set a parameter to remove `NA` values. — Gregor Thomas, Aug 25 '17 at 06:29
now it returns 4-6 months NA TRUE, I used na.rm=TRUE just after the Sum function. — Ankita Mishra, Aug 25 '17 at 06:43
df %>% group_by(Duration) %>% summarise(count=sum(User1), na.rm = TRUE) %>% filter(Duration.=="4-6 months") This is the code I used. — Ankita Mishra, Aug 25 '17 at 06:44

Josh Gilfillan · Answer 1 · 2017-08-25T06:45:45.583

1

Your column name may be incorrect:

df %>%
 group_by(Duration) %>%
 summarise(sum_user1 = sum(`User 1`, na.rm = TRUE)) %>%
 filter(Duration=="4-6 months")

edited Aug 25 '17 at 06:45

answered Aug 25 '17 at 06:24

Josh Gilfillan

4,348
2
24
26

edited to reflect correct column name, and removed ungroup() - thanks. – Josh Gilfillan Aug 25 '17 at 06:34
Okay, edited again. Thanks. – Josh Gilfillan Aug 25 '17 at 06:45

I want to calculate sum of a column, but categorised by other column in R

1 Answers1