Transposing the column names and add calculated values

General
1

Hi Experts here. I am trying to transpose the column names and add calculated values. Is there a way to achieve this. For example

df
Date               ColA      ColB        ColC
27/8/2019         5             6             10
28/8/2019         4             6             10
29/8/2019         1             2             10

Required output below. Actually the values should be 1.4 instead of 14/10. Just to make the user understand I have put the ratios. Is there a way to achieve this

                ColA                ColB             ColC
ColA             1                 10/14            10/30
ColB          14/10                  1              14/30 
ColC          30/10                30/14             1

The values above are means of respective Columns. For example mean of ColA is 10, mean of ColB is 14 and mean of ColC is 30

Dput below

df <- structure(list(Date = structure(1:3, .Label = c("27/8/2019", 
"28/8/2019", "29/8/2019"), class = "factor"), ColA = c(5, 4, 
1), ColB = c(6, 6, 2), ColC = c(10, 10, 10)), class = "data.frame", row.names = c(NA, 
-3L))
2

When you said the column means where 10, 14 and 30, I take you meant the column sums.

df <- structure(list(Date = structure(1:3, .Label = c("27/8/2019", "28/8/2019", "29/8/2019"), 
                                      class = "factor"), 
                     ColA = c(5, 4, 1), ColB = c(6, 6, 2), ColC = c(10, 10, 10)), 
                class = "data.frame", row.names = c(NA, -3L))

SUMS <- colSums(df[,2:4])
t(outer(c(ColA = 1, ColB = 1,ColC = 1), SUMS)/SUMS)
#>      ColA      ColB      ColC
#> ColA  1.0 0.7142857 0.3333333
#> ColB  1.4 1.0000000 0.4666667
#> ColC  3.0 2.1428571 1.0000000

Created on 2019-10-15 by the reprex package (v0.3.0.9000)

Edited - Explanation of a less silly way to do this.
It is obvious from the way you laid out your question that you needed to take your three column sums and make a 3x3 matrix containing all possible ways to divide the three values. The outer product function takes vectors of length n and m and makes a n x m matrix, so I knew I could use it and a vector of ones to make matrices of the three values. Changing the order of the arguments in the outer product "rotates" the result. You can get all combinations of the ratios by dividing the two matrices produced by outer().
The less silly way:

df <- structure(list(Date = structure(1:3, .Label = c("27/8/2019", "28/8/2019", "29/8/2019"), 
                                      class = "factor"), 
                     ColA = c(5, 4, 1), ColB = c(6, 6, 2), ColC = c(10, 10, 10)), 
                class = "data.frame", row.names = c(NA, -3L))

SUMS <- colSums(df[,2:4])
SUMS
#> ColA ColB ColC 
#>   10   14   30

vecOf1 <- c(ColA = 1, ColB = 1,ColC = 1)
vecOf1
#> ColA ColB ColC 
#>    1    1    1

Numerator <- outer(SUMS, vecOf1)
Numerator
#>      ColA ColB ColC
#> ColA   10   10   10
#> ColB   14   14   14
#> ColC   30   30   30

Denominator <- outer(vecOf1, SUMS)
Denominator
#>      ColA ColB ColC
#> ColA   10   14   30
#> ColB   10   14   30
#> ColC   10   14   30

Numerator/Denominator
#>      ColA      ColB      ColC
#> ColA  1.0 0.7142857 0.3333333
#> ColB  1.4 1.0000000 0.4666667
#> ColC  3.0 2.1428571 1.0000000

Created on 2019-10-15 by the reprex package (v0.2.1)

1 Like
3

Perfect thanks a lot. But can i ask another question please. I should be in a position to code in future for a solution like this. So could you please spend 5 mins to explain me the logic of this. It should not be like I only copy paste it. So

4

I made my first answer more complicated than it needed to be. I have appended a better answer to that post with some explanation. If you are not familiar with the outer product of vectors, the first part of the wikipedia article on outer products explains the calculation.

5

In my dataframe there are more columns. So I am trying to optimize 2nd step here but not getting.

vecOf1 <- c(names(df[2:4]),rep(1,3))
6

Let's say you have a data frame with 6 column and you want to process columns 2 through 5 as we did above. To make vecOf1 and give its elements the same names as the columns in the data frame, you can do this:

DF <- data.frame(Label = LETTERS[1:5],
                 ColA = sample(5),
                 ColB = sample(5),
                 ColC = sample(5),
                 ColD = sample(5),
                 ColE = sample(5))
DF
#>   Label ColA ColB ColC ColD ColE
#> 1     A    1    1    1    5    3
#> 2     B    3    2    4    3    2
#> 3     C    5    5    3    4    4
#> 4     D    4    3    2    1    1
#> 5     E    2    4    5    2    5
vecOf1 <- rep(x = 1, times = 5)
names(vecOf1) <- colnames(DF)[2:6]
vecOf1
#> ColA ColB ColC ColD ColE 
#>    1    1    1    1    1

Created on 2019-10-15 by the reprex package (v0.3.0.9000)

The colnames() function gets or sets the names of columns in a data frame and the names() function does the same for vectors.

1 Like
7

Very perfect . Thanks a lot

8

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