Pivoting columns into rows

Question

df2 <- structure(list(location = c("Dayton", "Toledo"), total_voters = c(236L, 
332L), candidate_1 = c(49L, 61L), candidate_2 = c(33L, 78L), 
    candidate_3 = c(19L, 71L), candidate_5 = c(42L, 52L)), row.names = c(NA, 
-2L), class = "data.frame")

I have data coming from a SQL query that is shaped as such:

+----------+--------------+-------------+-------------+-------------+-------------+-------------+
| location | total_voters | candidate_1 | candidate_2 | candidate_3 | candidate_4 | candidate_5 |
+----------+--------------+-------------+-------------+-------------+-------------+-------------+
| Dayton   |          236 |          49 |          33 |          19 |          93 |          42 |
| Toledo   |          332 |          61 |          78 |          71 |          70 |          52 |
+----------+--------------+-------------+-------------+-------------+-------------+-------------+

The numbers represent the number of votes for each candidate. So what am I trying to do? I'd like to use R (I imagine through either dplyr or tidyr) to pivot this data so it looks as such:

+-------------+-------+----------+--------------+
|  candidate  | votes | location | total_voters |
+-------------+-------+----------+--------------+
| candidate_1 |    49 | Dayton   |          236 |
| candidate_2 |    33 | Dayton   |          236 |
| candidate_3 |    19 | Dayton   |          236 |
| candidate_4 |    93 | Dayton   |          236 |
| candidate_5 |    42 | Dayton   |          236 |
| candidate_1 |    61 | Toledo   |          332 |
| candidate_2 |    78 | Toledo   |          332 |
| candidate_3 |    71 | Toledo   |          332 |
| candidate_4 |    70 | Toledo   |          332 |
| candidate_5 |    52 | Toledo   |          332 |
+-------------+-------+----------+--------------+

What would be the most effective way to accomplish this in R?

Relevant: [What can R do about a messy data format?](https://stackoverflow.com/questions/52023709/what-can-r-do-about-a-messy-data-format) ;) — markus, Feb 20 '20 at 23:28

akrun · Answer 1 · 2020-02-20T23:29:03.073

Here is one option with pivot_longer

library(dplyr)
library(tidyr)
df1 %>%
   pivot_longer(cols = everything(), names_to = 'candidate', values_to = 'votes')
# A tibble: 5 x 2
#  candidate   votes
#  <chr>       <dbl>
#1 candidate_1    49
#2 candidate_2    33
#3 candidate_3    19
#4 candidate_4    93
#5 candidate_5    42

With the updated data,

df2 %>%
   pivot_longer(cols = -c(location, total_voters),
                 names_to = 'candidate', values_to = 'votes')
# A tibble: 8 x 4
#  location total_voters candidate   votes
#  <chr>           <int> <chr>       <int>
#1 Dayton            236 candidate_1    49
#2 Dayton            236 candidate_2    33
#3 Dayton            236 candidate_3    19
#4 Dayton            236 candidate_5    42
#5 Toledo            332 candidate_1    61
#6 Toledo            332 candidate_2    78
#7 Toledo            332 candidate_3    71
#8 Toledo            332 candidate_5    52

Or in base R, this can be done with stack

stack(df1)[2:1]

Or by converting to a table

as.data.frame.table(as.matrix(df1))[,-1]

Or as @markus suggested

reshape2::melt(df1)

data

df1 <- data.frame(candidate_1 = 49, candidate_2 = 33, 
         candidate_3 = 19, candidate_4 = 93, candidate_5 = 42)

df2 <- structure(list(location = c("Dayton", "Toledo"), total_voters = c(236L, 
332L), candidate_1 = c(49L, 61L), candidate_2 = c(33L, 78L), 
    candidate_3 = c(19L, 71L), candidate_5 = c(42L, 52L)), row.names = c(NA, 
-2L), class = "data.frame")

Thanks for the response. I realize I left some parts of my original question out, so if you have a moment, please see the revisions! — , Feb 20 '20 at 23:23

score 0 · Answer 2 · answered Feb 20 '20 at 22:53

0

Actually you can use data.frame + t to make it, i.e.,

dflong <- data.frame(t(dfwide))

answered Feb 20 '20 at 22:53

ThomasIsCoding

96,636
9
24
81

Note: `votes` will be coerced to characters doing it this way. – markus Feb 20 '20 at 23:08
Thanks for the response. I realize I left some parts of my original question out, so if you have a moment, please see the revisions! – Feb 20 '20 at 23:23

score 0 · Answer 3 · answered Feb 20 '20 at 23:19

0

If candidate_1 candidate_2 etc are the columns name you can just use melt function from reshape2 packages.

a=data.frame(candidate_1=49,
             candidate_2=33,
             candidate_3=19,
             candidate_4=93,
             candidate_5=42)

b=reshape2::melt(a)

answered Feb 20 '20 at 23:19

alb_alb

58
5

Thanks for the response. I realize I left some parts of my original question out, so if you have a moment, please see the revisions! – Feb 20 '20 at 23:23

Pivoting columns into rows

3 Answers3

data