CODEWITHSUNDEEP
roffset
kuka
kuka

Reputation: 57

Use of EXCEL OFFSET IN R for a range of values and multiple times

This is file I want to append my data in

Collection A
Jan
Feb
March
April

Collection B
Jan
Feb
March
April

Revenue A
Jan
Feb
March
April

Revenue B
Jan
Feb
March
April

The file I want to pull my data from looks like this:

Collection    Month      Collection A    Collection B Revenue Month Revenue A   Revenue B
Collection  January     1                 5         Revenue  January    4    8
Collection  February    2                 6         Revenue  February   3    7
Collection  March       3                 7         Revenue  March      2    6
Collection  April       4                 8         Revenue  April      1    5

I want the final output to look like this:

Collection A
Jan           1
Feb           2
March         3         
April         4

Collection B
Jan          5
Feb          6
March        7
April        8

Revenue A
Jan         4
Feb         3
March       2 
April       1

Revenue B
Jan         8
Feb         7
March       6
April       5

I am able to run this on excel using OFFSET and INDIRECT function. But I want to automate it better for future purposes so I am trying it on R.

I am really stuck on how to combine the two datasets to find the desired output. It seems like an impossible task for me. I have played around with several functions like select, subset and arrange by none of them have helped me progress.

I will be glad if someone can help me out with this.

Upvotes: 1

Views: 184

Answers (1)

Calum You
Calum You

Reputation: 15072

Here's a way to achieve that output. Note that I removed spaces from the column names in the sample data in order to make it easier to read into R. You didn't specify what you wanted the column names of the output dataframe to be so as given they make little sense.

library(tidyverse)
tbl <- read_table2(
"Collection    Month      CollectionA    CollectionB Revenue Month RevenueA   RevenueB
Collection  January     1                 5         Revenue  January    4    8
Collection  February    2                 6         Revenue  February   3    7
Collection  March       3                 7         Revenue  March      2    6
Collection  April       4                 8         Revenue  April      1    5"
)
#> Warning: Duplicated column names deduplicated: 'Month' => 'Month_1' [6]
tbl %>%
  select(-Collection, -Revenue, -Month_1) %>%
  gather(variable, value, -Month) %>%
  group_by(variable) %>%
  group_modify(~ add_row(.x, Month = .y$variable, value = NA, .before = 1)) %>%
  ungroup() %>%
  select(-variable)
#> # A tibble: 20 x 2
#>    Month       value
#>    <chr>       <dbl>
#>  1 CollectionA    NA
#>  2 January         1
#>  3 February        2
#>  4 March           3
#>  5 April           4
#>  6 CollectionB    NA
#>  7 January         5
#>  8 February        6
#>  9 March           7
#> 10 April           8
#> 11 RevenueA       NA
#> 12 January         4
#> 13 February        3
#> 14 March           2
#> 15 April           1
#> 16 RevenueB       NA
#> 17 January         8
#> 18 February        7
#> 19 March           6
#> 20 April           5

Created on 2019-06-18 by the reprex package (v0.3.0)

Upvotes: 1

Related Questions