Add extra row to dataframe based on values of other rows

0
votes

I have a dataframe with two rows of data. I would like to add a third row with values representing the difference between the previous two.

The data is in the following format:

Month   A   B   C   D   E   F
Jan     1   2   4   8   4   1
Feb     1   1   4   5   2   0

I would like to add an extra row with a calculation to give the change across the two months:

Month   A   B   C   D   E   F
Jan     1   2   4   8   4   1
Feb     1   1   4   5   2   0
Change  0   1   0   3   2   1

I've been looking at various functions to add additional rows including rbind and mutate but I'm struggling to perform the calculation in the newly created row.

3
rdataframerow
Self Promotion: You could use the developer version of a package I wrote: manymodelr::rowdiff(df[,-1])[1,]. – NelsonGon
Is Month column the 1st one? Can you check output of df[1, -1] - df[2, -1] ? Does it give you right numbers? – Ronak Shah
@RonakShah solved it, the issue was with the Month = "Change" argument - r didn't like have date and text values in the same column – Ant
@NelsonGon You could post that as a solution – akrun

3 Answers

2
votes

As it's just two rows you can subtract the individual rows and rbind the difference

rbind(df, data.frame(Month = "Change", df[1, -1] - df[2, -1]))

#   Month A B C D E F
#1    Jan 1 2 4 8 4 1
#2    Feb 1 1 4 5 2 0
#3 Change 0 1 0 3 2 1
1
votes
d1<-data.frame(Month = "Change" , df[1,-1] , df[2,-1])
newdf <- rbind(df,d1)

This will create a new data-frame with what you require

1
votes

An option with tidyverse

library(tidyverse)
df1 %>% 
    summarise_if(is.numeric, diff) %>%
    abs %>%
    bind_rows(df1, .) %>%
    mutate(Month = replace_na(Month, "Change"))
#   Month A B C D E F
#1    Jan 1 2 4 8 4 1
#2    Feb 1 1 4 5 2 0
#3 Change 0 1 0 3 2 1

data

df1 <- structure(list(Month = c("Jan", "Feb"), A = c(1L, 1L), B = 2:1, 
    C = c(4L, 4L), D = c(8L, 5L), E = c(4L, 2L), F = 1:0),
    class = "data.frame", row.names = c(NA, 
-2L))