Reorganizing a dataframe so it can be graphed (Change column into a row 'category'?)-CodePudding

I have a dataframe like

year    lwg_pred    bon_pred    lwg_prey    bon_prey
1990    10          5           20           30
1991    1           2           3            4
1992    5           6           7            8
1993    9          10           11           12
1994    13         14           15           16

This is quite difficult to barplot as is, so I was trying to reorganize it so it would be like

year    location type       number
1990    lwg      pred       10
1990    bon      pred       5
1990    lwg      prey       20
1990    bon      prey       30

The end goal is to have a barplot that has x-axis as Location, y-axis with the number, fill with the type and faceted by the year.

I took a look at a previous post,

barplot_df3 <- barplot_df3 |>
  pivot_longer(everything(),
               names_sep = "_",
               names_to = c("location", ".value"))

but I couldn't figure out how to keep year nor made it 'categorize' pred/prey

Got this:

location pred prey
lwg      10   20
...

I also took a look at transpose, but I don't think it will be of much help. I would like some suggestions on either how to reorganize the data so it can be graphed or perhaps there was a way to graph it originally, and I was unaware. Thank you for your help in advance!

CodePudding user response：

You don't need the magic ".value", you can just use the new column names you want.

library(tidyr)
barplot_df3 %>% 
  pivot_longer(-year, names_to = c("location", "type"), names_sep = "_")

will give

   year location type  value
   <int> <chr>    <chr> <int>
 1  1990 lwg      pred     10
 2  1990 bon      pred      5
 3  1990 lwg      prey     20
 4  1990 bon      prey     30
 5  1991 lwg      pred      1
 6  1991 bon      pred      2
 7  1991 lwg      prey      3
 8  1991 bon      prey      4
 9  1992 lwg      pred      5
10  1992 bon      pred      6
11  1992 lwg      prey      7
12  1992 bon      prey      8
13  1993 lwg      pred      9
14  1993 bon      pred     10
15  1993 lwg      prey     11
16  1993 bon      prey     12
17  1994 lwg      pred     13
18  1994 bon      pred     14
19  1994 lwg      prey     15
20  1994 bon      prey     16