Ndtyjky

Excel allows you to switch rows and columns in its Chart functionality.

I am trying to replicate this in R. My data (shown) below, is showing production for each company in rows. I am unable to figure out how to show the Month-1, Month-2 etc in x-axis, and the series for each company in the same graph. Any help appreciated.

Data:

tibble::tribble( ~Company.Name, ~Month-1, ~Month-2, ~Month-3, ~Month-4, "Comp-1", 945.5438986, 1081.417009, 976.7388701, 864.309703, "Comp-2", 16448.87, 13913.19, 12005.28, 10605.32, "Comp-3", 346.9689321, 398.2297592, 549.1282647, 550.4207169, "Comp-4", 748.8806367, 949.463941, 1018.877481, 932.3773791 )

I'm going to skip the part where you want to transpose, and infer that your purpose for that was solely to help with plotting. The part I'm focusing on here is "show the Month-1, Month-2 etc in x-axis, and the series for each company in the same graph".

This is doable in base graphics, but I highly recommend using ggplot2 (or plotly or similar), due to its ease of dealing with dimensional plots like this. The "grammar of graphics" (which both tend to implement) really prefers data like this be in a "long" format, so part of what I'll do is convert to this format.

First, some data:

set.seed(2)

months <- paste0("Month", 1:30)

companies <- paste0("Comp", 1:5)

m <- matrix(abs(rnorm(length(months)*length(companies), sd=1e3)),

            nrow = length(companies))

d <- cbind.data.frame(

  Company = companies,

  m,

  stringsAsFactors = FALSE

)

colnames(d)[-1] <- months

str(d)

# 'data.frame': 5 obs. of  31 variables:

#  $ Company: chr  "Comp1" "Comp2" "Comp3" "Comp4" ...

#  $ Month1 : num  896.9 184.8 1587.8 1130.4 80.3

#  $ Month2 : num  132 708 240 1984 139

#  $ Month3 : num  418 982 393 1040 1782

#  $ Month4 : num  2311.1 878.6 35.8 1012.8 432.3

# (truncated)

Reshaping can be done with multiple libraries, including base R, here are two techniques:

library(data.table)

d2 <- melt(as.data.table(d), id = 1, variable.name = "Month", value.name = "Cost")

d2[,Month := as.integer(gsub("[^0-9]", "", Month)),]

d2

#      Company Month       Cost

#   1:   Comp1     1  896.91455

#   2:   Comp2     1  184.84918

#   3:   Comp3     1 1587.84533

#   4:   Comp4     1 1130.37567

#   5:   Comp5     1   80.25176

#  ---                         

# 146:   Comp1    30  653.67306

# 147:   Comp2    30  657.10598

# 148:   Comp3    30  549.90924

# 149:   Comp4    30  806.72936

# 150:   Comp5    30  997.37972



library(dplyr)

# library(tidyr)

d2 <- tbl_df(d) %>%

  tidyr::gather(Month, Cost, -Company) %>%

  mutate(Month = as.integer(gsub("[^0-9]", "", Month)))

I also integerized the Month, since it made sense with an ordinal variable. This isn't strictly necessary, the plot would just treat them as discretes.

The plot is anti-climactically simple:

library(ggplot2)

ggplot(d2, aes(Month, Cost, group=Company)) +

  geom_line(aes(color = Company))

Bottom line: I don't think you need to worry about transposing your data: doing so has many complications that can just confuse things. Reshaping is a good thing (in my opinion), but with this kind of data is fast enough that if your data is stored in the wide format, you can re-transform it without too much difficulty. (If you are thinking about putting this in a database, however, I'd strongly recommend you re-think "wide", your db schema will be challenging if you keep it.)