complex transposing using Reshape function from Wide to Long

Question

i would like to re shape merged file , below it the input table.

library(readr)
df2 <- read_table2("February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February    February
2020-Wk1    2020-Wk1    2020-Wk1    2020-Wk1    Sunday  Sunday  Sunday  Monday  Monday  Monday  Tuesday Tuesday Tuesday Wednesday   Wednesday   Wednesday   Thursday    Thursday    Thursday    Friday  Friday  Friday  Saturday    Saturday    Saturday
2020-Wk1    2020-Wk1    2020-Wk1    2020-Wk1    43863   43863   43863   43864   43864   43864   43865   43865   43865   43866   43866   43866   43867   43867   43867   43868   43868   43868   43869   43869   43869
Events  Events  Events  Events  HIGH    HIGH    HIGH    NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA
Coupon  Coupon  Coupon  Coupon  XYZ XYZ XYZ ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC
Degree  Degree  Degree  Degree  D2  D2  D2  NA  NA  NA  NA  NA  NA  NA  NA  NA  D8  D8  D8  D8  D8  D8  D8  D8  D8
Degree  Degree  Degree  Degree  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1
Degree  Degree  Degree  Degree  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2
Direct  4m  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  weekly  weekly  weekly  NA  NA  NA  NA  NA  NA
Direct  4m  NA  NA  NA  NA  NA  annual  annual  annual  annual  annual  annual  annual  annual  annual  quaterly    quaterly    quaterly    quaterly    quaterly    quaterly    quaterly    quaterly    quaterly
ENTERPRISE  127.0356495 -0.133328421    0.440191083 18.3207919  -0.151764777    0.423914708 13.63388989 -0.135213065    0.437225333 13.67001986 -0.151312727    0.441268748 14.15034022 -0.125308138    0.454903293 15.35746927 -0.141258572    0.437124251 19.42035378 -0.135101166    0.44071492  32.48278456 -0.112300122    0.444890248
2019-Wk1    2019-Wk1    2019-Wk1    2019-Wk1    43499   43499   43499   43500   43500   43500   43501   43501   43501   43502   43502   43502   43503   43503   43503   43504   43504   43504   43505   43505   43505
Events  Events  Events  Events  Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale    Sale
Coupon  Coupon  Coupon  Coupon  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF  15%OFF
Degrees Degrees Degrees Degrees D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2
Degrees Degrees Degrees Degrees NA  NA  NA  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1
Degrees Degrees Degrees Degrees D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4  D4
2020-Wk2    2020-Wk2    2020-Wk2    2020-Wk2    Sunday  Sunday  Sunday  Monday  Monday  Monday  Tuesday Tuesday Tuesday Wednesday   Wednesday   Wednesday   Thursday    Thursday    Thursday    valentine   valentine   valentine   Saturday    Saturday    Saturday
2020-Wk2    2020-Wk2    2020-Wk2    2020-Wk2    43870   43870   43870   43871   43871   43871   43872   43872   43872   43873   43873   43873   43874   43874   43874   43875   43875   43875   43876   43876   43876
Events  Events  Events  Events  NA  NA  NA  EFG EFG EFG EFG EFG EFG EFG EFG EFG EFG EFG EFG EFG EFG EFG HI  HI  HI
Coupon  Coupon  Coupon  Coupon  ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC MNO MNO MNO MNO MNO MNO
Degree  Degree  Degree  Degree  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8  D8
Degree  Degree  Degree  Degree  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  D8  D8  D8  D8  D8  D8  D8  D8  D8
Degree  Degree  Degree  Degree  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1  D1
Degree  Degree  Degree  Degree  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2  D2
Direct  4m  NA  NA  Annual  Annual  Annual  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA
Direct  4m  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  NA  quaterly    quaterly    quaterly    quaterly    quaterly    quaterly    quaterly    quaterly    quaterly")

shape of the data frame is 26,25

I have imputed the data to get desired output using the below code

col <- colnames(df2)
colnames(df2) <- as.character(unlist(df2[1,]))
df2 <- df2[, !duplicated(colnames(df2))] 
data2 <- reshape(df2, 
                 direction = "long",ids= 1:NROW(df2$`2020-Wk1`),
                 varying = 2:8,split = list(regexp ="2020",include = TRUE)
                 )

Two main things here:

Date is in number format and need to change into number format.
The reason for using regexp , there are 3 degree rows under 2020 wk-1 where as 4 under 2020 Wk-2. Below is the desired output:

Month       week        calendar    calendar    Event   Coupon  Degree  Degree  Degree  Degree  direct  direct  enterprise  week        calendar    Event   Coupon  Degrees Degrees Degrees
february    2020-Wk1    Sunday      2-Feb       HIGH    XYZ     D2      D1      D2                               $18.3      2019-Wk1    3-Feb       Sale    15% OFF D2              D4
february    2020-Wk1    Monday      3-Feb               ABC             D1      D2                      annual   $13.6      2019-Wk1    4-Feb       Sale    15% OFF D2      D1      D4
february    2020-Wk1    Tuesday     4-Feb               ABC             D1      D2                      annual   $13.7      2019-Wk1    5-Feb       Sale    15% OFF D2      D1      D4
february    2020-Wk1    Wednesday   5-Feb               ABC             D1      D2                      annual   $14.2      2019-Wk1    6-Feb       Sale    15% OFF D2      D1      D4
february    2020-Wk1    Thursday    6-Feb               ABC     D8      D1      D2              weekly  quaterly $15.4      2019-Wk1    7-Feb       Sale    15% OFF D2      D1      D4
february    2020-Wk1    Friday      7-Feb               ABC     D8      D1      D2                      quaterly $19.4      2019-Wk1    8-Feb       Sale    15% OFF D2      D1      D4
february    2020-Wk1    Saturday    8-Feb               ABC     D8      D1      D2                      quaterly $32.5      2019-Wk1    9-Feb       Sale    15% OFF D2      D1      D4
february    2020-Wk2    Sunday      9-Feb               ABC     D8              D1      D2      Annual                                  
february    2020-Wk2    Monday      10-Feb      EFG     ABC     D8              D1      D2                                      
february    2020-Wk2    Tuesday     11-Feb      EFG     ABC     D8              D1      D2                                      
february    2020-Wk2    Wednesday   12-Feb      EFG     ABC     D8              D1      D2                                      
february    2020-Wk2    Thursday    13-Feb      EFG     ABC     D8      D8      D1      D2      quaterly                                
february    2020-Wk2    Valentine   14-Feb      EFG     MNO     D8      D8      D1      D2      quaterly                                
february    2020-Wk2    Saturday    15-Feb      HI      MNO     D8      D8      D1      D2      quaterly

Please do provide your valuable inputs and the code from which I get the desired output.

I don't get the output you get from the input and code. And, I'm not quite sure what you want from us if you get the desired output using your own code. — DaveArmstrong, Sep 19 '20 at 11:22
@DaveArmstrong The above output is the desired which i would like to get, but Im not getting it. The above split function didnt work , i think i have coded it wrong i have been trying to get desired output. Hope you understood. — Aditya, Sep 19 '20 at 11:50
@Aditya Could you please use `dput` to share your data? I'm getting warnings and am not sure to reproduce your data adequately. — jay.sf, Sep 19 '20 at 11:56
@jay.sf Ignore warnings , if you can import 26 observations , 25 variables, then its good to go. — Aditya, Sep 19 '20 at 15:37
@Ben The contents in the above table are actually the contents which appear after importing merged excel file, import that table as it is you will get 26,25 dataframe — Aditya, Sep 19 '20 at 15:41

complex transposing using Reshape function from Wide to Long

0 Answers0

Linked