Read an excel table-CodePudding

Code an excel table into a data frame in R. I don't want to export the table, I would like to code it as a data frame in R

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Period
1   0   0   0   0   0   0   0   0   0   0   0   1
0   1   0   0   0   0   0   0   0   0   0   0   2
0   0   1   0   0   0   0   0   0   0   0   0   3
0   0   0   1   0   0   0   0   0   0   0   0   4
0   0   0   0   1   0   0   0   0   0   0   0   5
0   0   0   0   0   1   0   0   0   0   0   0   6
0   0   0   0   0   0   1   0   0   0   0   0   7
0   0   0   0   0   0   0   1   0   0   0   0   8
0   0   0   0   0   0   0   0   1   0   0   0   9
0   0   0   0   0   0   0   0   0   1   0   0   10
0   0   0   0   0   0   0   0   0   0   1   0   11
0   0   0   0   0   0   0   0   0   0   0   1   12
1   0   0   0   0   0   0   0   0   0   0   0   13
0   1   0   0   0   0   0   0   0   0   0   0   14
0   0   1   0   0   0   0   0   0   0   0   0   15
0   0   0   1   0   0   0   0   0   0   0   0   16
0   0   0   0   1   0   0   0   0   0   0   0   17
0   0   0   0   0   1   0   0   0   0   0   0   18
0   0   0   0   0   0   1   0   0   0   0   0   19
0   0   0   0   0   0   0   1   0   0   0   0   20
0   0   0   0   0   0   0   0   1   0   0   0   21
0   0   0   0   0   0   0   0   0   1   0   0   22
0   0   0   0   0   0   0   0   0   0   1   0   23
0   0   0   0   0   0   0   0   0   0   0   1   24

CodePudding user response：

I think that what you want is:

DF = read.table(text="Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Period
1   0   0   0   0   0   0   0   0   0   0   0   1
0   1   0   0   0   0   0   0   0   0   0   0   2
0   0   1   0   0   0   0   0   0   0   0   0   3
0   0   0   1   0   0   0   0   0   0   0   0   4
0   0   0   0   1   0   0   0   0   0   0   0   5
0   0   0   0   0   1   0   0   0   0   0   0   6
0   0   0   0   0   0   1   0   0   0   0   0   7
0   0   0   0   0   0   0   1   0   0   0   0   8
0   0   0   0   0   0   0   0   1   0   0   0   9
0   0   0   0   0   0   0   0   0   1   0   0   10
0   0   0   0   0   0   0   0   0   0   1   0   11
0   0   0   0   0   0   0   0   0   0   0   1   12
1   0   0   0   0   0   0   0   0   0   0   0   13
0   1   0   0   0   0   0   0   0   0   0   0   14
0   0   1   0   0   0   0   0   0   0   0   0   15
0   0   0   1   0   0   0   0   0   0   0   0   16
0   0   0   0   1   0   0   0   0   0   0   0   17
0   0   0   0   0   1   0   0   0   0   0   0   18
0   0   0   0   0   0   1   0   0   0   0   0   19
0   0   0   0   0   0   0   1   0   0   0   0   20
0   0   0   0   0   0   0   0   1   0   0   0   21
0   0   0   0   0   0   0   0   0   1   0   0   22
0   0   0   0   0   0   0   0   0   0   1   0   23
0   0   0   0   0   0   0   0   0   0   0   1   24", 
header=T)

CodePudding user response：

as.data.frame.matrix(table(1:24, factor(rep(month.abb, 2), month.abb)))

   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1    1   0   0   0   0   0   0   0   0   0   0   0
2    0   1   0   0   0   0   0   0   0   0   0   0
3    0   0   1   0   0   0   0   0   0   0   0   0
4    0   0   0   1   0   0   0   0   0   0   0   0
5    0   0   0   0   1   0   0   0   0   0   0   0
6    0   0   0   0   0   1   0   0   0   0   0   0
7    0   0   0   0   0   0   1   0   0   0   0   0
8    0   0   0   0   0   0   0   1   0   0   0   0
9    0   0   0   0   0   0   0   0   1   0   0   0
10   0   0   0   0   0   0   0   0   0   1   0   0
11   0   0   0   0   0   0   0   0   0   0   1   0
12   0   0   0   0   0   0   0   0   0   0   0   1
13   1   0   0   0   0   0   0   0   0   0   0   0
14   0   1   0   0   0   0   0   0   0   0   0   0
15   0   0   1   0   0   0   0   0   0   0   0   0
16   0   0   0   1   0   0   0   0   0   0   0   0
17   0   0   0   0   1   0   0   0   0   0   0   0
18   0   0   0   0   0   1   0   0   0   0   0   0
19   0   0   0   0   0   0   1   0   0   0   0   0
20   0   0   0   0   0   0   0   1   0   0   0   0
21   0   0   0   0   0   0   0   0   1   0   0   0
22   0   0   0   0   0   0   0   0   0   1   0   0
23   0   0   0   0   0   0   0   0   0   0   1   0
24   0   0   0   0   0   0   0   0   0   0   0   1

CodePudding user response：

df <-
  setNames(
    as.data.frame(rbind(diag(12), diag(12))),
    month.abb
  )

df$Period <- seq(nrow(df))

df
#>    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Period
#> 1    1   0   0   0   0   0   0   0   0   0   0   0      1
#> 2    0   1   0   0   0   0   0   0   0   0   0   0      2
#> 3    0   0   1   0   0   0   0   0   0   0   0   0      3
#> 4    0   0   0   1   0   0   0   0   0   0   0   0      4
#> 5    0   0   0   0   1   0   0   0   0   0   0   0      5
#> 6    0   0   0   0   0   1   0   0   0   0   0   0      6
#> 7    0   0   0   0   0   0   1   0   0   0   0   0      7
#> 8    0   0   0   0   0   0   0   1   0   0   0   0      8
#> 9    0   0   0   0   0   0   0   0   1   0   0   0      9
#> 10   0   0   0   0   0   0   0   0   0   1   0   0     10
#> 11   0   0   0   0   0   0   0   0   0   0   1   0     11
#> 12   0   0   0   0   0   0   0   0   0   0   0   1     12
#> 13   1   0   0   0   0   0   0   0   0   0   0   0     13
#> 14   0   1   0   0   0   0   0   0   0   0   0   0     14
#> 15   0   0   1   0   0   0   0   0   0   0   0   0     15
#> 16   0   0   0   1   0   0   0   0   0   0   0   0     16
#> 17   0   0   0   0   1   0   0   0   0   0   0   0     17
#> 18   0   0   0   0   0   1   0   0   0   0   0   0     18
#> 19   0   0   0   0   0   0   1   0   0   0   0   0     19
#> 20   0   0   0   0   0   0   0   1   0   0   0   0     20
#> 21   0   0   0   0   0   0   0   0   1   0   0   0     21
#> 22   0   0   0   0   0   0   0   0   0   1   0   0     22
#> 23   0   0   0   0   0   0   0   0   0   0   1   0     23
#> 24   0   0   0   0   0   0   0   0   0   0   0   1     24

^{Created on 2021-10-22 by the reprex package (v2.0.1)}

Or with tidyverse packages you could make a list of months and row numbers then pivot wider

library(dplyr, warn.conflicts = F)
library(tidyr)

df <- 
  tibble(month = rep(month.abb, 2)) %>% 
    mutate(Period = row_number(),
           val = 1)

df
#> # A tibble: 24 × 3
#>    month Period   val
#>    <chr>  <int> <dbl>
#>  1 Jan        1     1
#>  2 Feb        2     1
#>  3 Mar        3     1
#>  4 Apr        4     1
#>  5 May        5     1
#>  6 Jun        6     1
#>  7 Jul        7     1
#>  8 Aug        8     1
#>  9 Sep        9     1
#> 10 Oct       10     1
#> # … with 14 more rows

df %>% 
  pivot_wider(names_from = month,
              id_cols = Period,
              values_from = val,
              values_fill = 0) %>% 
  relocate(Period, .after = everything()) %>% 
  print(width = Inf)
#> # A tibble: 24 × 13
#>      Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec
#>    <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#>  1     1     0     0     0     0     0     0     0     0     0     0     0
#>  2     0     1     0     0     0     0     0     0     0     0     0     0
#>  3     0     0     1     0     0     0     0     0     0     0     0     0
#>  4     0     0     0     1     0     0     0     0     0     0     0     0
#>  5     0     0     0     0     1     0     0     0     0     0     0     0
#>  6     0     0     0     0     0     1     0     0     0     0     0     0
#>  7     0     0     0     0     0     0     1     0     0     0     0     0
#>  8     0     0     0     0     0     0     0     1     0     0     0     0
#>  9     0     0     0     0     0     0     0     0     1     0     0     0
#> 10     0     0     0     0     0     0     0     0     0     1     0     0
#>    Period
#>     <int>
#>  1      1
#>  2      2
#>  3      3
#>  4      4
#>  5      5
#>  6      6
#>  7      7
#>  8      8
#>  9      9
#> 10     10
#> # … with 14 more rows

^{Created on 2021-10-22 by the reprex package (v2.0.1)}