Add a row to a tibble to sum some, but not all, columns in the tibble

dplyr
tidyverse
tibble
#1

I honestly don't think that what I want to do is that difficult. However, I've been beating my head against this issue for several hours without finding a solution, so I guess I'll ask for help.

I need to add a new row to a tibble, The newly-added row will contain the sums of some, but not all, of the columns in the tibble.

https://stackoverflow.com/questions/33565949/add-row-to-data-frame-with-dplyr, suggests this code snippet:

"mydata %>%
rbind(list(c("00000", "Region Total", sum(GDP),sum(Percent))))"

However, I receive the error message "Error in xi[[j]] : subscript out of bounds"

Thanks in advance for your help.


This is my starting tibble:

FIPS County GDP Percent
1 29007 Audrain 698358 0.5
2 29019 Boone 7521903 4.5
3 29027 Callaway 1456387 -3.2
4 29029 Camden 1152673 -1.1

This is my desired output containing the new row #5, which contains observations for the 2 character variables (FIPS, County) and the sums of the two numeric variables (GDP, Percent):

FIPS County GDP Percent
1 29007 Audrain 698358 0.5
2 29019 Boone 7521903 4.5
3 29027 Callaway 1456387 -3.2
4 29029 Camden 1152673 -1.1
5 00000 Region Total 10829321 0.7

FIPS <- c("29007", "29019", "29027", "29029")
County <- c("Audrain", "Boone", "Callaway", "Camden")
GDP <- c(698358, 7521903, 1456387, 1152673)
Percent <- c(0.5, 4.5, -3.2, -1.1)
mydata <- cbind.data.frame(FIPS, County, GDP, Percent)

mydata <-as_tibble(data)

mydata$FIPS <- as.character(mydata$FIPS)
mydata$County <- as.character(mydata$County)

class(mydata$FIPS)
class(mydata$County)
class(mydata$GDP)
class(mydata$Percent)

# suggested by https://stackoverflow.com/questions/33565949/add-row-to-data-frame-with-dplyr
mydata %>% 
  rbind(list(c("00000", "Region Total", sum(GDP),sum(Percent))))

#error:  Error in xi[[j]] : subscript out of bounds
0 Likes

#2

The second (as of this writing) answer in that stack overflow thread has a better solution than the accepted answer; using the add_row function of tibble.

library(tibble)
library(magrittr)
FIPS <- c("29007", "29019", "29027", "29029")
County <- c("Audrain", "Boone", "Callaway", "Camden")
GDP <- c(698358, 7521903, 1456387, 1152673)
Percent <- c(0.5, 4.5, -3.2, -1.1)
mydata <- cbind.data.frame(FIPS, County, GDP, Percent)

mydata <- as_tibble(mydata)

mydata$FIPS <- as.character(mydata$FIPS)
mydata$County <- as.character(mydata$County)

class(mydata$FIPS)
class(mydata$County)
class(mydata$GDP)
class(mydata$Percent)

mydata <- mydata %>% 
  add_row(FIPS = "00000", County = "Region Total", 
          GDP = sum(GDP), Percent = sum(Percent))

Frankly, I would not expect the accepted answer in that thread to work. I do not see how the sum function can know that GDP and Percent refer to columns in the mydata tibble. There may be some tibble magic in there that I am unaware of.

2 Likes

#3

@landonphd
if your question's been answered (even by you), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

0 Likes

#4

@FJCC, Thanks for your help.

Your answer was very helpful and gave me the exact result that I wanted.

One of the solutions that I tried before I asked for help applied add_row. Your suggestion helped me to troubleshoot and to fix a different error that I believe was my problem all along. Add_row in my original code apparently wasn't working because an error in a different portion of the code inexplicably changed one of my variables to an object. I can't explain that error, but, after I fixed it, your suggestion for add_row worked like a charm.

I really appreciate your time and suggestion.

1 Like

closed #5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes