Menu
In the below, the data frame index denotes the value while t1:t2 denotes the number of times that specific value was recorded at a specific point in time. For example index 10 at t1 equals 1 suggesting that it was made 1 records; at t2 there are 4 records, whole at t3 and t4 just 1. I would like to return the values from columns t1:t4 based on an index column
Input:
index t1 t2 t3 t4
10 1 4 1 1
20 2 5 1 0
30 3 6 1 0
40 0 0 0 2
Output:
t1 t2 t3 t4
10 10 10 10
20 10 20 40
20 10 30 40
30 10 NA NA
30 20 NA NA
30 20 NA NA
NA 20 NA NA
NA 20 NA NA
NA 30 NA NA
NA 30 NA NA
NA 30 NA NA
NA 30 NA NA
NA 30 NA NA
NA 30 NA NA
Sample data:
df<-structure(list(index=c (10,20,30,40),
t1 = c(1, 2, 3, 0),
t2 = c(4, 5, 6, 0),
t3 = c(1, 1,1, 0),
t4 = c(1, 0, 0, 2)), row.names = c(NA,4L), class = "data.frame")
df
426
In our example table below, we want to multiply all the numbers in column A by the number 3 in cell C2. The formula =A2*C2 will get the correct result (4500) in cell B2.
We can multiply several columns using the PRODUCT function. To do so, we will have to type =PRODUCT(value 1,value 2, value 3,……). In this case, we want to get the value of multiplication of Price/ Unit, Quantity, and Percentage after Discount. Pressing the ENTER key will get the result in the F5 cell.
To multiply more than two columns in Excel, you can use the multiplication formulas similar to the ones discussed above, but include several cells or ranges. For example, to multiply values in columns B, C and D, use one of the following formulas: Multiplication operator: =A2*B2*C2. PRODUCT function: =PRODUCT(A2:C2)
How to multiply a column by a number in Excel. 1 Enter the number to multiply by in some cell, say in A2. 2 Write a multiplication formula for the topmost cell in the column. 3 Double-click the fill handle in the formula cell (D2) to copy the formula down the column. Done!
Multiply two columns and then sum based on one condition with a useful feature. 1 Select Math from the Formula Type drop down list; 2 In the Choose a formula listbox, select SUMPRODUCT with criteria option; 3 Then, in the Arguments input section, select the Lookup_col, Lookup_value, Array 1 and Array 2 from the original table as you need.
How to multiply one column by another with multiplication operator. 1 Multiply two cells in the first row. 2 Double-click the small green square in the lower-right corner of D2 to copy the formula down the column, until the last cell with data. Done!
How to return an entire column article will explain you how it can be done. =OFFSET (anchor point before headers, 1, MATCH (search value, headers of data, 0), ROWS (any column of data or even data itself), 1) Type 0)) to select the exact match method and close both INDEX and MATCH formulas
Base R and one line of code.
Map(function(x) rep(df$index, x), df[,-1])
After update:
maxy <- max(apply(df[,-1], 2, sum))
data.frame(Map(function(x) c(rep(df$index, x), rep(NA, maxy - sum(x))), df[,-1]))
Using base R with lapply
lst1 <- lapply(df[-1], function(x) rep(df$index, x))
data.frame(lapply(lst1, `length<-`, max(lengths(lst1))))
-output
# t1 t2 t3 t4
#1 10 10 10 10
#2 20 10 20 40
#3 20 10 30 40
#4 30 10 NA NA
#5 30 20 NA NA
#6 30 20 NA NA
#7 NA 20 NA NA
#8 NA 20 NA NA
#9 NA 20 NA NA
#10 NA 30 NA NA
#11 NA 30 NA NA
#12 NA 30 NA NA
#13 NA 30 NA NA
#14 NA 30 NA NA
#15 NA 30 NA NA
One dplyr, tidyr and purrr solution could be:
map(.x = names(df)[-1],
~ df %>%
uncount(get(.x)) %>%
select(!!.x := index) %>%
rowid_to_column()) %>%
reduce(full_join)
rowid t1 t2 t3 t4
1 1 10 10 10 10
2 2 20 10 20 40
3 3 20 10 30 40
4 4 30 10 NA NA
5 5 30 20 NA NA
6 6 30 20 NA NA
7 7 NA 20 NA NA
8 8 NA 20 NA NA
9 9 NA 20 NA NA
10 10 NA 30 NA NA
11 11 NA 30 NA NA
12 12 NA 30 NA NA
13 13 NA 30 NA NA
14 14 NA 30 NA NA
15 15 NA 30 NA NA
Here is a base R option
list2DF(
lapply(
df[-1],
function(x) `length<-`(rep(df$index, x), max(colSums(df[-1])))
)
)
which gives
t1 t2 t3 t4
1 10 10 10 10
2 20 10 20 40
3 20 10 30 40
4 30 10 NA NA
5 30 20 NA NA
6 30 20 NA NA
7 NA 20 NA NA
8 NA 20 NA NA
9 NA 20 NA NA
10 NA 30 NA NA
11 NA 30 NA NA
12 NA 30 NA NA
13 NA 30 NA NA
14 NA 30 NA NA
15 NA 30 NA NA
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
