dplyr mutate by row names

1
votes

I have some data and I am trying to run some very basic calculations based on row names. I basically want to add together Total Cash Flow From Operating Activities + Capital Expenditures from the row names using dplyr and just store them as a new row. The below code eventually does what I want but its quite bulky and I am trying to create it in one pipe function not two. 

CFt <- as.data.frame(t(CF))
 library(dplyr)
 FCF <- NULL
 FCF <- CFt %>%
  select(`Total Cash Flow From Operating Activities`, `Capital Expenditures`)

 FCF <- FCF %>% 
   rownames_to_column('Firms') %>%
   mutate(FCF = `Total Cash Flow From Operating Activities` + `Capital Expenditures`) %>% 
   column_to_rownames('Firms') %>%
   t()

Data:

CF <- structure(list(GE2017 = c(-5786000, 7359000, 5568000, 1551000, 
987000, 747000, NA, 10426000, -7371000, NA, 5891000, 2322000, 
-8650000, NA, -8952000, 1006000, -19146000, 891000, -5507000), 
    GE2016 = c(8831000, 7139000, -19373000, 1514000, 3034000, 
    -1389000, NA, -244000, -7199000, NA, -10250000, 49202000, 
    -8806000, NA, -58411000, -485000, -89131000, -1146000, -41319000
    ), GE2015 = c(-6126000, 6509000, 21411000, -52000, -1537000, 
    -314000, NA, 19891000, -7309000, NA, -5316000, 59488000, 
    -9295000, NA, -57546000, -8114000, -76054000, -3464000, -138000
    ), GE2014 = c(15233000, 6423000, 8788000, -1913000, 50000, 
    -872000, NA, 27709000, -7134000, NA, 406000, -5034000, -8852000, 
    NA, -30190000, 23304000, -16956000, -3492000, 2224000), GOOG2017 = c(12662000, 
    6899000, 8284000, -3768000, 1121000, NA, 3682000, 37091000, 
    -13184000, -19448000, 1419000, -31401000, NA, NA, -86000, 
    -3366000, -8298000, 405000, -2203000), GOOG2016 = c(19478000, 
    6100000, 7158000, -2578000, 333000, NA, 2420000, 36036000, 
    -10212000, -18229000, -1978000, -31165000, NA, NA, -1335000, 
    -3304000, -8332000, -170000, -3631000), GOOG2015 = c(16348000, 
    5024000, 5609000, -2094000, 246000, NA, 1618000, 26572000, 
    -9950000, -13635000, 75000, -23711000, NA, NA, -23000, -2422000, 
    -4225000, -434000, -1798000), GOOG2014 = c(14136000, 4601000, 
    3615000, -1641000, 261000, NA, 1461000, 23024000, -11014000, 
    -6222000, 628000, -21055000, NA, NA, -18000, -2069000, -2087000, 
    -433000, -551000), NVDA2018 = c(3047000, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
    NVDA2017 = c(1666000, 187000, 473000, -321000, 184000, -375000, 
    -167000, 1672000, -176000, -619000, -5000, -793000, -261000, 
    NA, 1315000, -24000, 291000, NA, 1170000), NVDA2016 = c(614000, 
    197000, 386000, -32000, -11000, 66000, -74000, 1175000, -86000, 
    -339000, 18000, -4e+05, -213000, 120000, 1315000, 4000, -676000, 
    NA, 99000), NVDA2015 = c(631000, 220000, 230000, -49000, 
    -27000, -95000, -32000, 906000, -122000, -625000, -1000, 
    -727000, -186000, 154000, 1315000, 12000, -834000, NA, -655000
    )), .Names = c("GE2017", "GE2016", "GE2015", "GE2014", "GOOG2017", 
"GOOG2016", "GOOG2015", "GOOG2014", "NVDA2018", "NVDA2017", "NVDA2016", 
"NVDA2015"), row.names = c("Net Income", "Depreciation", "Adjustments To Net Income", 
"Changes In Accounts Receivables", "Changes In Liabilities", 
"Changes In Inventories", "Changes In Other Operating Activities", 
"Total Cash Flow From Operating Activities", "Capital Expenditures", 
"Investments", "Other Cash flows from Investing Activities", 
"Total Cash Flows From Investing Activities", "Dividends Paid", 
"Sale Purchase of Stock", "Net Borrowings", "Other Cash Flows from Financing Activities", 
"Total Cash Flows From Financing Activities", "Effect Of Exchange Rate Changes", 
"Change In Cash and Cash Equivalents"), class = "data.frame")
2
rdplyr
Do you need colSums(CF[c( "Total Cash Flow From Operating Activities" , "Capital Expenditures" ),], na.rm = TRUE) - akrun
Or ` CF %>% rownames_to_column('rn') %>% filter(rn %in% c( "Total Cash Flow From Operating Activities" , "Capital Expenditures" )) %>% summarise_at(-1, sum, na.rm = TRUE)` - akrun
Yes both work as I hoped! Just need to now store the newely created data under a row name as opposed to CF$Colname. - user113156
So, isn't it just to bind (getting lost with auto complete) the rows - akrun
Well, I am putting rbind() or add_row() at the end of the dplyr code you gave me but somehow its not storing the values in the CF data frame. (I can store the values outside of the pipe function and then use rbind(x, y) but just wondering if it could all be done in one pipe). - user113156

2 Answers

3
votes

If we use bind_rows, it will strip off the row names and assigns default row numbers. So, we are using rbind

library(tidyverse)
CF %>%
   rownames_to_column('rn') %>% 
   filter(rn %in% c( "Total Cash Flow From Oprating Activities" , 
         "Capital Expenditures" )) %>% 
   summarise_at(-1, sum, na.rm = TRUE) %>% 
   `row.names<-`(., 'TotalCashFlowOPAc+CapitalExpend') %>% 
   rbind(CF, .)
1
votes

Slightly different solution than @akrun's

library(tibble)
library(dplyr)

CF %>% 
  rownames_to_column('rn') %>%
  filter(rn %in% c('Total Cash Flow From Operating Activities',
                   'Capital Expenditures')) %>%
  group_by(rn) %>%
  summarize_all(sum) %>%
  as.data.frame() %>%
  column_to_rownames('rn') %>%
  rbind(., FCF = colSums(.))