1
year  Pink Floyd Metallica Rammstein Led Zeppelin Pantera     
2004  600        700        500        400        300
2005  700        300        400        200        500
2006  300        150        600        700        700

Hello to everyone. I have a similar data as I mentioned. Each of the groups is now a separate column. I want to open a new column under the name of groups and put the years into rows. I mean:

Bands       2004  2005  2006
Pink Floyd  600   700   300
Metallica   700   300   150
Rammstein

Instead of bands, there are 77 cities in my original data and 16 years. So I have 78 variables in my real data, 77 different cities and a year. Therefore, I can say that I am dealing with a large amount of data. So, I need your help.

Ilayda Velioglu
  • 27
  • 7
  • This link may help: https://stackoverflow.com/questions/6778908/transpose-a-data-frame? – Peter Jan 05 '21 at 20:15
  • I was really happy when I tried the following method suggested here, but when I try to get data$Metallica, I get null: df.aree <- as.data.frame(t(data)) colnames(df.aree) <- df.aree[1, ] df.areem <- df.aree[-1, ] – Ilayda Velioglu Jan 05 '21 at 20:32
  • It would probably help if you included a subset of your data so that a solution specific to your dataset can be tested and verified. Use `dput(your_dataframe)` or `dput(head(your_dataframe) )` to paste data into the question. Maybe this can help [mre] – Peter Jan 05 '21 at 21:28

3 Answers3

1

We can use pivot_longer and pivot_wider from the tidyr package.

library(tidyverse)
df %>%
    pivot_longer(cols = -year, names_to = "band") %>%
    pivot_wider(names_from = year, values_from = value)

#   band       `2004` `2005` `2006`
# 1 PinkFloyd     600    700    300
# 2 Metallica     700    300    150
# 3 Rammstein     500    400    600
# 4 LedZepelin    400    200    700
# 5 Pantera       300    500    700
bouncyball
  • 10,631
  • 19
  • 31
  • Thank you! But when I try to get data from f.e. Rammstein line like df$Rammstein, I get an error like: Unknown or uninitialised column. what could be the problem? – Ilayda Velioglu Jan 05 '21 at 21:13
  • if you want data from the Ramstein line, you would need to subset the data: `subset(data, band == "Ramstein")` – bouncyball Jan 05 '21 at 22:04
1

Here is a data.table option using transpose

> data.table::transpose(setDT(df), keep.names = "Band", make.names = "year")
           Band 2004 2005 2006
1:   Pink Floyd  600  700  300
2:    Metallica  700  300  150
3:    Rammstein  500  400  600
4: Led Zeppelin  400  200  700
5:      Pantera  300  500  700

Data

> dput(df)
structure(list(year = 2004:2006, `Pink Floyd` = c(600L, 700L, 
300L), Metallica = c(700L, 300L, 150L), Rammstein = c(500L, 400L,
600L), `Led Zeppelin` = c(400L, 200L, 700L), Pantera = c(300L,
500L, 700L)), class = c("data.table", "data.frame"), row.names = c(NA,
-3L))
ThomasIsCoding
  • 96,636
  • 9
  • 24
  • 81
0

You can do transpose data frame then change names. Something like this:

library(tidyr)
# Let be df your dataframe
df <- data.frame(year = c(2005,2005,2006), PinkFloyd = c(600, 700, 300), Metallica = 
c(700,300,150),  Ramstein =c(500,400,600), LedZepelin =c(400,200,700), Pantera=c(300,500,700) ) 

#Transpose data and change names
df_aux <- data.frame(t(df))
colnames(df_aux ) <- unique(df$year)
df_aux <- df_aux[-1,]
df_aux <- tibble::rownames_to_column(df_aux, "Band")

The result is:

> df_aux 
 Band        2005 2004 2006
 PinkFloyd   600  700  300
 Metallica   700  300  150
 Ramstein    500  400  600
 LedZepelin  400  200  700
 Pantera     300  500  700
Juan Camilo Rivera Palacio
  • 327
  • 2
  • 10