Adding new rows to a data frame with values computed from existing rows

Question

With a dataframe df like below

text <- "
Parameter,Dec-17,Sep-17,Jun-17,Mar-17,Dec-16
Income,12112,13323.2,14655.52,16121.072,17733.1792
Cost,10900.8,11990.88,13189.968,14508.9648,15959.86128
"
df <- read.table(textConnection(text), sep=",", header = T, stringsAsFactors = F)
df

  Parameter  Dec.17   Sep.17   Jun.17   Mar.17   Dec.16
1    Income 12112.0 13323.20 14655.52 16121.07 17733.18
2      Cost 10900.8 11990.88 13189.97 14508.96 15959.86

I want to add two more rows, 1- Profit (Income - Cost) and 2 - Tax ( 0.2* Profit) for each of the quarters - Dec.17, Sep.17, Jun.17, Mar.17, Dec.16

How do I got about this - dplyr is preferred. Base R solution would be also helpful.

Expected output

  Parameter   Dec.17    Sep.17     Jun.17     Mar.17     Dec.16
1    Income 12112.00 13323.200 14655.5200 16121.0720 17733.1792
2      Cost 10900.80 11990.880 13189.9680 14508.9648 15959.8613
3    Profit  1211.20  1332.320  1465.5520  1612.1072  1773.3179
4       Tax   242.24   266.464   293.1104   322.4214   354.6636

see this, it may helpful. https://stackoverflow.com/questions/10150579/adding-a-column-to-a-data-frame — , Feb 04 '18 at 12:57
Try `rbind(df, data.frame(Parameter = c("Profit", "tax"), sapply(df[-1], function(x) c(x[1]- x[2], x[1]*0.2))))` — akrun, Feb 04 '18 at 13:09

score 2 · Accepted Answer · answered Feb 04 '18 at 13:56

Maybe not the most elegant, but this is a dplyr solution. The last select is to get the same column order as the original table.

df %>%
  gather(key = month, value = amount, -Parameter) %>%
  spread(Parameter, amount) %>%
  mutate(Profit = Income - Cost) %>%
  mutate(Tax = 0.2 * Profit) %>%
  gather(key = Parameter, value = amount, -month) %>%
  spread(month, value = amount) %>%
  select(names(df))

Parameter   Dec.17    Sep.17     Jun.17     Mar.17     Dec.16
1      Cost 10900.80 11990.880 13189.9680 14508.9648 15959.8613
2    Income 12112.00 13323.200 14655.5200 16121.0720 17733.1792
3    Profit  1211.20  1332.320  1465.5520  1612.1072  1773.3179
4       Tax   242.24   266.464   293.1104   322.4214   354.6636

Esteban PS · Answer 2 · 2018-02-04T13:11:43.813

1

This is a base R solution:

df <- data.frame(t(df[, 2:ncol(df)]))
names(df) <- c("Income", "Cost")

df$Profit <- (df$Income - df$Cost)
df$Tax <- ( 0.2* df$Profit)
df <- data.frame(t(df))

And to add again the "Parameter" column:

df$Parameter <- rownames(df)
df <- df[c(ncol(df), 2:ncol(df) -1)]

edited Feb 04 '18 at 13:11

answered Feb 04 '18 at 13:06

Esteban PS

929
1
8
12

score 1 · Answer 3 · answered Feb 04 '18 at 13:27

Using just root functions of R I tried to solve the problem with the gave data.

text <- "
Parameter,Dec-17,Sep-17,Jun-17,Mar-17,Dec-16
Income,12687.6,13980.1,14506.5,14299.8,15239.6
Cost,11418.84,12582.09,13055.85,12869.82,13715.64
"
df <- read.table(textConnection(text), sep=",", header = T, stringsAsFactors = F)

I transformed to a matrix and did the difference between both rows and I add the name of the row and rbind inside the matrix. For

df.matrix <- as.matrix(df)
new.row <- df[2,c(2,3,4,5,6)] - df[1,c(2,3,4,5,6)]
new.row <- cbind("Income - Cost", new.row)

Then, I renamed the first name row and used rbind to gather the new row with the dataframe, changing the format of newrow (matrix) to a dataframe.

colnames(new.row)[1] <- "Parameter"

df <- rbind(df,as.data.frame(new.row))

The result is as follow:

      Parameter   Dec.17   Sep.17   Jun.17   Mar.17   Dec.16
1         Income 12687.60 13980.10 14506.50 14299.80 15239.60
2           Cost 11418.84 12582.09 13055.85 12869.82 13715.64
21 Income - Cost -1268.76 -1398.01 -1450.65 -1429.98 -1523.96

For last, the names of the rows are 1,2 and 21. We can null them doing this:

rownames(df) <- NULL

or rename to the order you wish

rownames(df) <- c(1,2,3)

Hope this will be helpfull.

Adding new rows to a data frame with values computed from existing rows

3 Answers3