How do I summarise all columns except one(s) I specify?
dplyr sum all columns except one
sum all columns except one in r
r summary multiple columns
dplyr summarise keep columns
r aggregate multiple columns
r sum multiple columns by group
I want to sum up all but one numerical column in this dataframe.
Group, Registered, Votes, Beans A, 111, 12, 100 A, 111, 13, 200 A, 111, 14, 300
I want to group this by
Group, summing up all the columns except
summarise_if( .tbl = group_by( .data = x, Precinct ), .predicate = is.numeric, .funs = sum )
Problem here is the result is a data frame that sums ALL the numeric columns, including
Registered. How do I sum all but
The output I want would look like this
Group, Registered, Votes, Beans A, 111, 39, 600
How to apply summarise_each to all columns except one?, Manipulating, analyzing and exporting data with tidyverse, Use summarize , group_by , and count to split a data frame into groups of package to read the data and avoid having to set stringsAsFactors to FALSE To select all columns except certain ones, put a “-” in front of the variable to exclude it. Free install Kutools for Excel and begin to sum data of each column. 1. Click Enterprise > Paging Subtotal. See screenshot: 2. The in the Paging Subtotals dialog, all the columns in active worksheet are listing, and in default, all the columns are checked and you can only check the columns you need to sum. See screenshot:
dt = read.table(text = " Group Registered Votes Beans A 111 12 100 A 111 13 200 A 111 14 300 ", header=T) library(dplyr) # specify grouping variables v1 = "Group" v2 = "Registered" dt %>% group_by_(v1, v2) %>% summarise_all(sum) %>% ungroup() # # A tibble: 1 x 4 # Group Registered Votes Beans # <fct> <int> <int> <int> # 1 A 111 39 600
Note that I have to assume that within each
Group value there's a unique
Registered value, so you can group by both variables, instead of grouping only by
Group and keeping the unique value of
How do I summarise all columns except one(s) I specify?, For example, I can summarise one column multiple ways (e.g. using summarise_at() completely and define a function which returns a one row tibble. summarise_all operates on all columns except the grouping ones, Sum entire column except header use Function in Excel. Using a Function also can sum the entire column except headers in Excel. 1. Select a cell of the column you want to select and press Alt + F11 to open the Microsoft Visual Basic for Applications window. 2. In the pop-up window, click Insert > Module, then paste the following VBA code into the module.
We can use
library(dplyr) df %>% select(-Registered) %>% summarise_if(is.numeric, sum) # Votes Beans #1 39 600
Summarise multiple columns using multiple functions in a tidy way , Use summarize , group_by , and count to split a data frame into groups of package to read the data and avoid having to set stringsAsFactors to FALSE To select all columns except certain ones, put a “-” in front of the variable to exclude it. = SUM (A:A) // sum all of column A = SUM (3: 3) // sum all of row 3 You can see how this works yourself by typing "A:A", "3:3", etc. into the name box (left of the formula bar) and hitting return — Excel will select the entire column or row.
Manipulating, analyzing and exporting data with tidyverse, Download the msleep data set in CSV format from here, and then load into R: name genus vore order conservation ## 1 Cheetah Acinonyx carni Carnivora lc ## 2 summarise(), summarise values To select all the columns except a specific column, use the “-“ (subtraction) operator (also known as negative indexing). Sum values in same column across multiple sheets by Combine function. If you want to combine the tables across sheets and sum the values based on columns as below screenshot shown, you can apply Kutools for Excel’s Combine function.
dplyr tutorial, summarise_at() affects variables selected with a character vector or vars() if there is only one unnamed function (i.e. if .funs is an unnamed list of length one), It is the following one: Sales by Year and Color summarize = SUMMARIZE ( Sales, 'Date'[Calendar Year], 'Product'[Color] ) You must specify the optional filter table argument after the columns you aggregate, and before any additional calculated column (not present in this case):
Summarise multiple columns, summarise() collapses a group into a single row. It takes a data frame, and a set of column names (or more complicated expressions) to order by. with 83 more rows # Select all columns except those from hair_color to eye_color (inclusive) In Excel the formula would be something like =sumifs(state column, specific state, year column, specific year, number of crashes). Hopefully that makes sense, I'm new to asking for help with data stuff. I could use something like that to make a table with the information I want. I have no clue how to do it in Stata.
- Are the commas part of the values? It looks like you don't specify anywhere the column you want to leave out. Not clear how general you want this solution to be.
- @AntoniosK Commas are not part of the data frame. I just thought they would make it easier to read
- Some columns don't have commas though. Not a consistent pattern.
- In the second solution, I get this error: "Evaluation error: ‘sum’ not meaningful for factors."
- That means one of your numeric columns is a factor, so you'll have to convert it to a double or int before taking the sum, because sum is not meaningful for factors.
Groupcolumn is the factor.
- two options: replace
function(x) is.numeric(x) & !is.factor(x)or, convert it to character
- But if you put a
summarise_at, you can leave it as-is