R reshape name value pairs from wide to long using pivot_longer

Question

I am trying to figure out how to reshape a dataset of the names of political parties from wide to long using dplyr and pivot_longer.

For each Party_ID, there is a number of constant columns attached (Party_Name_Short, Party_Name, Country, Party_in_orig_title) and a number of time changing factors as well: election, Date, Rename, Reason, Party_Title, alliance, member_parties, split, parent_party, merger, child_party, successor, predecessor. The time changing factors were recorded up to 11 times for each party, as reflected by the index in the colname.

In order to provide a sample I selected the first three time changing columns for each party and a sample of 5 random rows:

structure(list(Party_Name_Short = c("LZJ-PS", "ZiZi", "MNR", 
"MDP", "E200"), Party_Name = c("Lista Zorana Jankovica – Pozitivna Slovenija", 
"Živi zid", "Mouvement national républicain", "Movimento Democrático Português", 
"Erakond Eesti 200"), Country = c("SVN", "HRV", "FRA", "PRT", 
"EST"), Party_ID = c(1987, 2612, 1263, 1281, 2720), Party_in_orig_title = c(0, 
0, 0, 0, 0), Date1 = c(2011, NA, 1999, 1987, NA), Rename1 = c("Lista Zorana Jankovica – Pozitivna Slovenija", 
NA, "Mouvement national républicain", "ID", NA), Reason1 = c("foundation", 
NA, "split from FN", "split", NA), Party_Title1 = c(0, NA, 0, 
0, NA), alliance1 = c(0, NA, 0, 0, NA), member_parties1 = c(NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_), 
    split1 = c(0, NA, 1, 1, NA), parent_party1 = c(NA, NA, "FN", 
    "MDP", NA), merger1 = c(0, NA, 0, 0, NA), child_party1 = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), successor1 = c(0, NA, 0, 0, NA), predecessor1 = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), Date2 = c(2012, NA, NA, NA, NA), Rename2 = c("Pozitivna Slovenija", 
    NA, NA, NA, NA), Reason2 = c("renamed", NA, NA, NA, NA), 
    Party_Title2 = c(0, NA, NA, NA, NA), alliance2 = c(0, NA, 
    NA, NA, NA), member_parties2 = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), split2 = c(0, 
    NA, NA, NA, NA), parent_party2 = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), merger2 = c(0, 
    NA, NA, NA, NA), child_party2 = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), successor2 = c(0, 
    NA, NA, NA, NA), predecessor2 = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), Date3 = c(2014, 
    NA, NA, NA, NA), Rename3 = c("ZaAB", NA, NA, NA, NA), Reason3 = c("split", 
    NA, NA, NA, NA), Party_Title3 = c(0, NA, NA, NA, NA), alliance3 = c(0, 
    NA, NA, NA, NA), member_parties3 = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_), split3 = c(1, 
    NA, NA, NA, NA), parent_party3 = c("LZJ-PS", NA, NA, NA, 
    NA), merger3 = c(0, NA, NA, NA, NA), child_party3 = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), successor3 = c(0, NA, NA, NA, NA), predecessor3 = c(NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), election1 = structure(c(15309, 16740, 11839, 6390, 17956
    ), class = "Date"), election2 = structure(c(16252, NA, NA, 
    NA, NA), class = "Date"), election3 = structure(c(16344, 
    NA, NA, NA, NA), class = "Date")), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

I would like the data to follow a "long" structure where each party_id and the constant factors are repeated 11 times and there are single columns for the time changing factors. Following the top-rated answer formulated here I tried different variations of the following command:

  pivot_longer(cols = starts_with(c("election", "Date", "Rename", "Reason", "Party_Title",
                                    "alliance", "member_parties", "split", "parent_party",
                                    "merger", "child_party", "successor", "predecessor")), 
               names_to = c(".value", "election", "Date", "Rename", "Reason", "Party_Title",
                            "alliance", "member_parties", "split", "parent_party",
                            "merger", "child_party", "successor", "predecessor"), names_sep = "_") %>% 
    select(-matches("election[1-9]"), -matches("Date[1-9]"), -matches("Rename[1-9]"),
     -matches("Reason[1-9]"), -matches("alliance[1-9]"), -matches("member_parties[1-9]"),
     -matches("split[1-9]"), -matches("parent_party[1-9]"), -matches("merger[1-9]"),
     -matches("child_party[1-9]"), -matches("successor[1-9]"), -matches("predecessor[1-9]"),
     -matches("Party_Title[1-9]"), -matches("election1[0-2]"), -matches("Date1[0-2]"), -matches("Rename1[0-2]"),
     -matches("Reason1[0-2]"), -matches("alliance1[0-2]"), -matches("member_parties1[0-2]"),
     -matches("split1[0-2]"), -matches("parent_party1[0-2]"), -matches("merger1[0-2]"),
     -matches("child_party1[0-2]"), -matches("successor1[0-2]"), -matches("predecessor1[0-2]"),
     -matches("Party_Title1[0-2]"))

However, for some reason, I get a lot of missing values and do not achieve the shape of the data I would like to have. I'd appreciate any hint if you have an idea of how to do this. Thanks!

Update:

I would like the final output to look something like:

structure(list(Party_Name_Short = c("LZJ-PS", "ZiZi", "MNR", 
"MDP", "E200", "LZJ-PS", "ZiZi", "MNR", "MDP", "E200", "LZJ-PS", 
"ZiZi", "MNR", "MDP", "E200"), Party_Name = c("Lista Zorana Jankovica – Pozitivna Slovenija", 
"Živi zid", "Mouvement national républicain", "Movimento Democrático Português", 
"Erakond Eesti 200", "Lista Zorana Jankovica – Pozitivna Slovenija", 
"Živi zid", "Mouvement national républicain", "Movimento Democrático Português", 
"Erakond Eesti 200", "Lista Zorana Jankovica – Pozitivna Slovenija", 
"Živi zid", "Mouvement national républicain", "Movimento Democrático Português", 
"Erakond Eesti 200"), Country = c("SVN", "HRV", "FRA", "PRT", 
"EST", "SVN", "HRV", "FRA", "PRT", "EST", "SVN", "HRV", "FRA", 
"PRT", "EST"), Party_ID = c(1987, 2612, 1263, 1281, 2720, 1987, 
2612, 1263, 1281, 2720, 1987, 2612, 1263, 1281, 2720), Party_in_orig_title = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), time = c(1, 1, 1, 
1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3), Date = c(2011, NA, 1999, 
1987, NA, 2012, NA, NA, NA, NA, 2014, NA, NA, NA, NA), Rename = c("Lista Zorana Jankovica – Pozitivna Slovenija", 
NA, "Mouvement national républicain", "ID", NA, "Pozitivna Slovenija", 
NA, NA, NA, NA, "ZaAB", NA, NA, NA, NA), Reason = c("foundation", 
NA, "split from FN", "split", NA, "renamed", NA, NA, NA, NA, 
"split", NA, NA, NA, NA), Party_Title = c(0, NA, 0, 0, NA, 0, 
NA, NA, NA, NA, 0, NA, NA, NA, NA), alliance = c(0, NA, 0, 0, 
NA, 0, NA, NA, NA, NA, 0, NA, NA, NA, NA), member_parties = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), split = c(0, 
NA, 1, 1, NA, 0, NA, NA, NA, NA, 1, NA, NA, NA, NA), parent_party = c(NA, 
NA, "FN", "MDP", NA, NA, NA, NA, NA, NA, "LZJ-PS", NA, NA, NA, 
NA), merger = c(0, NA, 0, 0, NA, 0, NA, NA, NA, NA, 0, NA, NA, 
NA, NA), child_party = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), successor = c(0, NA, 0, 0, NA, 0, NA, 
NA, NA, NA, 0, NA, NA, NA, NA), predecessor = c(NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), election = structure(c(1322697600, 
1446336000, 1022889600, 552096000, 1551398400, 1404172800, NA, 
NA, NA, NA, 1412121600, NA, NA, NA, NA), class = c("POSIXct", 
"POSIXt"), tzone = "UTC")), row.names = c(NA, -15L), class = c("tbl_df", 
"tbl", "data.frame"))

Notice: the newly added time column and notice that this is only for example purposes, with three time changing factors, whereas in fact there are 11 in the data.

can you shsow your expected output – akrun Apr 13 '20 at 18:33 — akrun, Apr 13 '20 at 18:33
I added now the desired output. – eborbath Apr 13 '20 at 18:44 — eborbath, Apr 13 '20 at 18:44

akrun · Accepted Answer · 2020-04-13T18:59:20.563

Using pivot_longer with names_sep to split between a non-digit and a digit at the end of the string

library(tidyr)
library(dplyr)
df1 %>% 
  pivot_longer(cols =  matches('\\d+$'), names_to = c(".value", 'time'),
           names_sep="(?<=\\D)(?=\\d+$)") %>%
  arrange(time)
# A tibble: 15 x 19
#   Party_Name_Short Party_Name Country Party_ID Party_in_orig_t… time   Date Rename Reason Party_Title alliance member_parties split
#   <chr>            <chr>      <chr>      <dbl>            <dbl> <chr> <dbl> <chr>  <chr>        <dbl>    <dbl> <chr>          <dbl>
# 1 LZJ-PS           Lista Zor… SVN         1987                0 1      2011 Lista… found…           0        0 <NA>               0
# 2 ZiZi             Živi zid   HRV         2612                0 1        NA <NA>   <NA>            NA       NA <NA>              NA
# 3 MNR              Mouvement… FRA         1263                0 1      1999 Mouve… split…           0        0 <NA>               1
# 4 MDP              Movimento… PRT         1281                0 1      1987 ID     split            0        0 <NA>               1
# 5 E200             Erakond E… EST         2720                0 1        NA <NA>   <NA>            NA       NA <NA>              NA
# 6 LZJ-PS           Lista Zor… SVN         1987                0 2      2012 Pozit… renam…           0        0 <NA>               0
# 7 ZiZi             Živi zid   HRV         2612                0 2        NA <NA>   <NA>            NA       NA <NA>              NA
# 8 MNR              Mouvement… FRA         1263                0 2        NA <NA>   <NA>            NA       NA <NA>              NA
# 9 MDP              Movimento… PRT         1281                0 2        NA <NA>   <NA>            NA       NA <NA>              NA
#10 E200             Erakond E… EST         2720                0 2        NA <NA>   <NA>            NA       NA <NA>              NA
#11 LZJ-PS           Lista Zor… SVN         1987                0 3      2014 ZaAB   split            0        0 <NA>               1
#12 ZiZi             Živi zid   HRV         2612                0 3        NA <NA>   <NA>            NA       NA <NA>              NA
#13 MNR              Mouvement… FRA         1263                0 3        NA <NA>   <NA>            NA       NA <NA>              NA
#14 MDP              Movimento… PRT         1281                0 3        NA <NA>   <NA>            NA       NA <NA>              NA
#15 E200             Erakond E… EST         2720                0 3        NA <NA>   <NA>            NA       NA <NA>              NA
# … with 6 more variables: parent_party <chr>, merger <dbl>, child_party <chr>, successor <dbl>, predecessor <chr>, election <date>

this is interesting, but it's not what I want, see the updated post. — eborbath, Apr 13 '20 at 18:53

R reshape name value pairs from wide to long using pivot_longer

1 Answers1

Linked

Related