mat_vinther
Reputation: 45
Combine values from duplicated rows into one based on condition (in R)
I have a dataset with the name of Danish ministers and their position from 1990 to 2020 (data comes from dataset called WhoGovern; https://politicscentre.nuffield.ox.ac.uk/whogov-dataset/). The dataset consists of the ministers name, the ministers position, the prestige of that position, and the year in which the minister had that given position.
My problem is that some ministers are counted twice in the same year (i.e., the rows aren't unique in terms of name and year). See the example in the picture below, where "Bertel Haarder" was both Minister of Health and Minister of Interior Affairs in 2010 and 2021.
I want to create a dataset, where all the rows are unique combinations of name and year. However, I do not want to remove any information from the dataset. Instead, I want to use the information in the prestige column to combine the duplicated rows into one. The observations with the highest prestige should be the main observations, where the other information should be added in a new column, e.g., position2 and prestige2. In the example with Bertel Haarder the data should look like this:
(PS: Sorry for bad presenting of the tables, but didn't know how to create a nice looking table...)
Here's the dataset for creating a reproducible example with observations from 2010-2020:
structure(list(year = c(2010L, 2010L, 2010L, 2010L, 2010L, 2010L,
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L,
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2011L,
2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L,
2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L, 2011L,
2011L, 2011L, 2011L, 2011L, 2012L, 2012L, 2012L, 2012L, 2012L,
2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L,
2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L, 2012L,
2012L, 2012L, 2012L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L,
2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L,
2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L, 2013L,
2013L, 2013L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2016L, 2016L,
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L,
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2017L, 2017L,
2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L,
2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2017L,
2017L, 2017L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L,
2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L,
2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2019L, 2019L, 2019L,
2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L,
2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 2019L), name = c("Lars Loekke Rasmussen",
"Lykke Friis", "Per Stig Moller", "Gitte Lillelund Bech", "Soren Pind",
"Birthe Ronn Hornbech", "Brian Mikkelsen", "Tina Nedergaard",
"Inger Stojberg", "Karen Ellemann", "Claus Hjort Frederiksen",
"Henrik Hoegh", "Lene Espersen", "Lykke Friis", "Bertel Haarder",
"Bertel Haarder", "Lars Barfoed", "Karen Ellemann", "Birthe Ronn Hornbech",
"Charlotte Sahl Madsen", "Benedikte Kiaer", "Troels Lund Poulsen",
"Hans Christian Schmidt", "Lars Loekke Rasmussen", "Lykke Friis",
"Per Stig Moller", "Gitte Lillelund Bech", "Soren Pind", "Per Stig Moller",
"Brian Mikkelsen", "Troels Lund Poulsen", "Inger Stojberg", "Karen Ellemann",
"Claus Hjort Frederiksen", "Henrik Hoegh", "Lene Espersen", "Lykke Friis",
"Bertel Haarder", "Bertel Haarder", "Lars Barfoed", "Karen Ellemann",
"Soren Pind", "Charlotte Sahl Madsen", "Benedikte Kiaer", "Peter Christensen",
"Hans Christian Schmidt", "Helle Thorning-Schmidt", "Mette Gjerskov",
"Ole Sohn", "Christine Antorini", "Martin Lidegaard", "Uffe Elbaek",
"Nick Haekkerup", "Christian Friis Bach", "Manu Sareen", "Margrethe Vestager",
"Mette Frederiksen", "Ida Auken", "Manu Sareen", "Nicolai Wammen",
"Bjarne Corydon", "Villy Sovndal", "Astrid Krag", "Margrethe Vestager",
"Morten Bodskov", "Manu Sareen", "Morten Ostergaard", "Karen Haekkerup",
"Thor Moger Pedersen", "Carsten Hansen", "Pia Olsen Dyhr", "Henrik Dam Kristensen",
"Helle Thorning-Schmidt", "Mette Gjerskov", "Annette Vilhelmsen",
"Christine Antorini", "Martin Lidegaard", "Marianne Jelved",
"Nick Haekkerup", "Christian Friis Bach", "Manu Sareen", "Margrethe Vestager",
"Mette Frederiksen", "Ida Auken", "Manu Sareen", "Nicolai Wammen",
"Bjarne Corydon", "Villy Sovndal", "Astrid Krag", "Margrethe Vestager",
"Morten Bodskov", "Manu Sareen", "Morten Ostergaard", "Karen Haekkerup",
"Holger Nielsen", "Carsten Hansen", "Pia Olsen Dyhr", "Henrik Dam Kristensen",
"Helle Thorning-Schmidt", "Dan Jorgensen", "Henrik Sass Larsen",
"Manu Sareen", "Rasmus Helveg Petersen", "Marianne Jelved", "Nicolai Wammen",
"Mogens Jensen", "Marianne Jelved", "Margrethe Vestager", "Christine Antorini",
"Mette Frederiksen", "Kirsten Brosbol", "Bjarne Corydon", "Martin Lidegaard",
"Nick Haekkerup", "Margrethe Vestager", "Karen Haekkerup", "Carsten Hansen",
"Sofie Carsten Nielsen", "Manu Sareen", "Morten Ostergaard",
"Carsten Hansen", "Mogens Jensen", "Magnus Heunicke", "Helle Thorning-Schmidt",
"Dan Jorgensen", "Henrik Sass Larsen", "Manu Sareen", "Rasmus Helveg Petersen",
"Marianne Jelved", "Nicolai Wammen", "Mogens Jensen", "Marianne Jelved",
"Morten Ostergaard", "Christine Antorini", "Henrik Dam Kristensen",
"Kirsten Brosbol", "Bjarne Corydon", "Martin Lidegaard", "Nick Haekkerup",
"Morten Ostergaard", "Mette Frederiksen", "Carsten Hansen", "Sofie Carsten Nielsen",
"Manu Sareen", "Benny Engelbrecht", "Carsten Hansen", "Mogens Jensen",
"Magnus Heunicke", "Lars Loekke Rasmussen", "Troels Lund Poulsen",
"Ellen Trane Norby", "Bertel Haarder", "Peter Christensen", "Jorn Neergaard Larsen",
"Lars Christian Lilleholt", "Esben Lunde Larsen", "Claus Hjort Frederiksen",
"Kristian Jensen", "Sophie Lohde", "Ulla Toernaes", "Inger Stojberg",
"Soren Pind", "Carl Holst", "Karen Ellemann", "Karsten Lauritzen",
"Hans Christian Schmidt", "Lars Loekke Rasmussen", "Brian Mikkelsen",
"Mai Mercado", "Mette Bock", "Claus Hjort Frederiksen", "Ulla Toernaes",
"Simon Emil Ammitzboll", "Merete Riisager", "Thyra Frank", "Troels Lund Poulsen",
"Lars Christian Lilleholt", "Esben Lunde Larsen", "Karen Ellemann",
"Kristian Jensen", "Anders Samuelsen", "Ellen Trane Norby", "Soren Pind",
"Inger Stojberg", "Soren Pape Poulsen", "Sophie Lohde", "Karsten Lauritzen",
"Ole Birk Olesen", "Lars Loekke Rasmussen", "Brian Mikkelsen",
"Mai Mercado", "Mette Bock", "Claus Hjort Frederiksen", "Ulla Toernaes",
"Simon Emil Ammitzboll", "Merete Riisager", "Thyra Frank", "Troels Lund Poulsen",
"Lars Christian Lilleholt", "Jakob Ellemann-Jensen", "Eva Kjer Hansen",
"Kristian Jensen", "Anders Samuelsen", "Ellen Trane Norby", "Tommy Ahlers",
"Inger Stojberg", "Soren Pape Poulsen", "Sophie Lohde", "Karsten Lauritzen",
"Ole Birk Olesen", "Mette Frederiksen", "Jeppe Kofod", "Nicolai Wammen",
"Astrid Krag", "Nick Haekkerup", "Simon Kollerup", "Trine Bramsen",
"Peter Hummelgaard Thomsen", "Mattias Tesfaye", "Pernille Rosenkrantz-Theil",
"Magnus Heunicke", "Joy Mogensen", "Ane Halsboe-Joergensen",
"Kaare Dybvad", "Benny Engelbrecht", "Morten Bodskov", "Dan Jorgensen",
"Rasmus Prehn", "Lea Wermelin", "Mogens Jensen"), position = c("Prime Min.",
"Min. Of Climate & Energy", "Min. Of Cultural Affairs", "Min. Of Defense",
"Min. For Development Cooperation", "Min. Of Ecclesiastical Affairs",
"Min. Of Economic & Business Affairs", "Min. Of Education", "Min. Of Employment",
"Min. Of Environment", "Min. Of Finance", "Min. Of Food, Agriculture & Fisheries",
"Min. Of Foreign Affairs", "Min. Of Gender Equality", "Min. Of Health",
"Min. Of Interior Affairs", "Min. Of Justice", "Min. Of Nordic Cooperation",
"Min. Of Refugees, Immigration & Integration", "Min. Of Science, Technology & Innovation",
"Min. Of Social Affairs", "Min. Of Taxation", "Min. Of Transport",
"Prime Min.", "Min. Of Climate & Energy", "Min. Of Cultural Affairs",
"Min. Of Defense", "Min. For Development Cooperation", "Min. Of Ecclesiastical Affairs",
"Min. Of Economic & Business Affairs", "Min. Of Education", "Min. Of Employment",
"Min. Of Environment", "Min. Of Finance", "Min. Of Food, Agriculture & Fisheries",
"Min. Of Foreign Affairs", "Min. Of Gender Equality", "Min. Of Health",
"Min. Of Interior Affairs", "Min. Of Justice", "Min. Of Nordic Cooperation",
"Min. Of Refugees, Immigration & Integration", "Min. Of Science, Technology & Innovation",
"Min. Of Social Affairs", "Min. Of Taxation", "Min. Of Transport",
"Prime Min.", "Min. Of Agriculture, Food & Fisheries", "Min. Of Business Affairs & Growth",
"Min. Of Children & Education", "Min. Of Climate, Energy & Construction",
"Min. Of Cultural Affairs", "Min. Of Defense", "Min. For Development Cooperation",
"Min. Of Ecclesiastical Affairs", "Min. Of Economic Affairs",
"Min. Of Employment", "Min. Of Environment", "Min. Of Equality",
"Min. Of European Cooperation", "Min. For Finance", "Min. Of Foreign Affairs",
"Min. Of Health & Prevention", "Min. Of Interior Affairs", "Min. Of Justice",
"Min. Of Nordic Cooperation", "Min. Of Research, Innovation & Continuing Education",
"Min. Of Social Affairs & Integration", "Min. Of Taxation", "Min. Of Towns, Housing & Rural Affairs",
"Min. Of Trade & Investment", "Min. Of Transport", "Prime Min.",
"Min. Of Agriculture, Food & Fisheries", "Min. Of Business Affairs & Growth",
"Min. Of Children & Education", "Min. Of Climate, Energy & Construction",
"Min. Of Cultural Affairs", "Min. Of Defense", "Min. For Development Cooperation",
"Min. Of Ecclesiastical Affairs", "Min. Of Economic Affairs",
"Min. Of Employment", "Min. Of Environment", "Min. Of Equality",
"Min. Of European Cooperation", "Min. For Finance", "Min. Of Foreign Affairs",
"Min. Of Health & Prevention", "Min. Of Interior Affairs", "Min. Of Justice",
"Min. Of Nordic Cooperation", "Min. Of Research, Innovation & Continuing Education",
"Min. Of Social Affairs & Integration", "Min. Of Taxation", "Min. Of Towns, Housing & Rural Affairs",
"Min. Of Trade & Investment", "Min. Of Transport", "Prime Min.",
"Min. Of Agriculture, Food & Fisheries", "Min. Of Business Affairs & Growth",
"Min. Of Children & Equality", "Min. Of Climate, Energy & Construction",
"Min. Of Cultural Affairs", "Min. Of Defense", "Min. For Development Cooperation",
"Min. Of Ecclesiastical Affairs", "Min. Of Economic Affairs",
"Min. Of Education", "Min. Of Employment", "Min. Of Environment",
"Min. For Finance", "Min. Of Foreign Affairs", "Min. Of Health & Prevention",
"Min. Of Interior Affairs", "Min. Of Justice", "Min. Of Nordic Cooperation",
"Min. Of Research, Innovation & Continuing Education", "Min. Of Social Affairs & Integration",
"Min. Of Taxation", "Min. Of Towns, Housing & Rural Affairs",
"Min. Of Trade & Investment", "Min. Of Transport", "Prime Min.",
"Min. Of Agriculture, Food & Fisheries", "Min. Of Business Affairs & Growth",
"Min. Of Children & Equality", "Min. Of Climate, Energy & Construction",
"Min. Of Cultural Affairs", "Min. Of Defense", "Min. For Development Cooperation",
"Min. Of Ecclesiastical Affairs", "Min. Of Economic Affairs",
"Min. Of Education", "Min. Of Employment", "Min. Of Environment",
"Min. For Finance", "Min. Of Foreign Affairs", "Min. Of Health & Prevention",
"Min. Of Interior Affairs", "Min. Of Justice", "Min. Of Nordic Cooperation",
"Min. Of Research, Innovation & Continuing Education", "Min. Of Social Affairs & Integration",
"Min. Of Taxation", "Min. Of Towns, Housing & Rural Affairs",
"Min. Of Trade & Investment", "Min. Of Transport", "Prime Min.",
"Min. For Business Affairs & Growth", "Min. For Children, Education & Gender Equality",
"Min. For Culture & Ecclesiastical Affairs", "Min. Of Defense",
"Min. For Employment", "Min. For Energy, Utilities & Climate",
"Min. For Environment & Food", "Min. For Finance", "Min. For Foreign Affairs",
"Min. For Health", "Min. For Higher Education & Science", "Min. For Immigration, Integration & Housing",
"Min. For Justice", "Min. For Nordic Cooperation", "Min. For Social Affairs & The Interior",
"Min. For Taxation", "Min. For Transport & Building", "Prime Min.",
"Min. For Business Affairs", "Min. For Children & Social Affairs",
"Min. For Culture & Ecclesiastical Affairs", "Min. Of Defense",
"Min. For Development Cooperation", "Min. For Economics & The Interior",
"Min. For Education", "Min. For Elder Affairs", "Min. For Employment",
"Min. For Energy, Utilities & Climate", "Min. For Environment & Food",
"Min. For Equality & Nordic Cooperation", "Min. For Finance",
"Min. For Foreign Affairs", "Min. For Health", "Min. For Higher Education & Science",
"Min. For Immigration & Integration", "Min. For Justice", "Min. For Public Innovation",
"Min. For Taxation", "Min. For Transport, Building & Housing",
"Prime Min.", "Min. For Business Affairs", "Min. For Children & Social Affairs",
"Min. For Culture & Ecclesiastical Affairs", "Min. Of Defense",
"Min. For Development Cooperation", "Min. For Economics & The Interior",
"Min. For Education", "Min. For Elder Affairs", "Min. For Employment",
"Min. For Energy, Utilities & Climate", "Min. For Environment & Food",
"Min. For Equality & Fisheries", "Min. For Finance", "Min. For Foreign Affairs",
"Min. For Health", "Min. For Higher Education & Science", "Min. For Immigration & Integration",
"Min. For Justice", "Min. For Public Innovation", "Min. For Taxation",
"Min. For Transport, Building & Housing", "Prime Minister", "Min. For Foreign Affairs",
"Min. For Finance", "Min. Of Social Affairs & The Interior",
"Min. For Justice", "Min. For Industry, Business & Financial Affairs",
"Min. For Defence", "Min. For Employment", "Min. For Immigration & Integration",
"Min. For Children & Education", "Min. For Health & Elderly Affairs",
"Min. For Culture & Church", "Min. For Science, Technology, Information & Higher Education",
"Min. For Building & Housing", "Min. For Transport", "Min. For Taxation",
"Min. For Climate, Energy & Utilities", "Min. For Development Cooperation",
"Min. For The Environment", "Min. For Food, Fishery, Equality & Min. For Nordic Cooperation"
), prestige_1 = c(NA, "Medium", "Medium", "High", "Medium", "Medium",
"Medium", "Medium", "Medium", "Medium", "High", "Medium", "High",
"Low", "Medium", "High", "Medium", "High", "Low", "Low", "Medium",
"Medium", "Medium", NA, "Medium", "Medium", "High", "Medium",
"Medium", "Medium", "Medium", "Medium", "Medium", "High", "Medium",
"High", "Low", "Medium", "High", "Medium", "High", "Low", "Low",
"Medium", "Medium", "Medium", NA, "Medium", "Medium", "Medium",
"Medium", "Medium", "High", "Medium", "Medium", "Medium", "Medium",
"Medium", "Low", "High", "High", "High", "Medium", "High", "Medium",
"High", "Medium", NA, "Medium", "Medium", "Medium", "Medium",
NA, "Medium", "Medium", "Medium", "Medium", "Medium", "High",
"Medium", "Medium", "Medium", "Medium", "Medium", "Low", "High",
"High", "High", "Medium", "High", "Medium", "High", "Medium",
NA, "Medium", "Medium", "Medium", "Medium", NA, "Medium", "Medium",
"Low", "Medium", "Medium", "High", "Medium", "Medium", "Medium",
"Medium", "Medium", "Medium", "High", "High", "Medium", "High",
"Medium", "High", "Medium", NA, "Medium", "Medium", "Medium",
"Medium", NA, "Medium", "Medium", "Low", "Medium", "Medium",
"High", "Medium", "Medium", "Medium", "Medium", "Medium", "Medium",
"High", "High", "Medium", "High", "Medium", "High", "Medium",
NA, "Medium", "Medium", "Medium", "Medium", NA, "Medium", "Medium",
"Medium", "High", "Medium", "Medium", "Medium", "High", "High",
"Medium", "Medium", "Medium", "Medium", "High", "High", "Medium",
"Medium", NA, "Medium", "Low", "Medium", "High", "Medium", "High",
"Medium", "Low", "Medium", "Medium", "Medium", "High", "High",
"High", "Medium", "Medium", "Low", "Medium", "Low", "Medium",
"Medium", NA, "Medium", "Low", "Medium", "High", "Medium", "High",
"Medium", "Low", "Medium", "Medium", "Medium", "Medium", "High",
"High", "Medium", "Medium", "Low", "Medium", "Low", "Medium",
"Medium", NA, "High", "High", "High", "Medium", "High", "High",
"Medium", "Low", "Medium", "Medium", "Medium", "Medium", "Medium",
"Medium", "Medium", "Medium", "Medium", "Medium", "High")), row.names = c(NA,
-230L), class = c("tbl_df", "tbl", "data.frame"))
Upvotes: 0
Views: 76
Answers (4)
camille
Reputation: 16832
This is similar to some other options, but you can make prestige a factor (to order by high, medium, low), arrange and number based on that, then reshape to wide format using both position and prestige as values.
One issue is that by default, the names will come out as the position_* columns, then the prestige_* columns. With an additional step to do some string extraction, you can arrange columns based on the number in them. You might be able to instead use tidyr::pivot_wider_spec to develop the order you want, but it's probably not worth the effort.
library(dplyr)
df %>%
rename(prestige = prestige_1) %>%
mutate(prestige = as.factor(prestige) %>% forcats::fct_relevel("High", "Medium")) %>%
arrange(prestige) %>%
group_by(year, name) %>%
mutate(row = row_number()) %>%
tidyr::pivot_wider(id_cols = c(year, name), names_from = row, values_from = c(position, prestige)) %>%
select(year, name, names(.) %>% readr::parse_number() %>% order())
#> # A tibble: 205 x 8
#> # Groups: year, name [205]
#> year name position_1 prestige_1 position_2 prestige_2 position_3 prestige_3
#> <int> <chr> <chr> <fct> <chr> <fct> <chr> <fct>
#> 1 2010 Gitt… Min. Of D… High <NA> <NA> <NA> <NA>
#> 2 2010 Clau… Min. Of F… High <NA> <NA> <NA> <NA>
#> 3 2010 Lene… Min. Of F… High <NA> <NA> <NA> <NA>
#> 4 2010 Bert… Min. Of I… High Min. Of H… Medium <NA> <NA>
#> 5 2010 Kare… Min. Of N… High Min. Of E… Medium <NA> <NA>
#> 6 2011 Gitt… Min. Of D… High <NA> <NA> <NA> <NA>
#> 7 2011 Clau… Min. Of F… High <NA> <NA> <NA> <NA>
#> 8 2011 Lene… Min. Of F… High <NA> <NA> <NA> <NA>
#> 9 2011 Bert… Min. Of I… High Min. Of H… Medium <NA> <NA>
#> 10 2011 Kare… Min. Of N… High Min. Of E… Medium <NA> <NA>
#> # … with 195 more rows
Upvotes: 2
Yuriy Saraykin
Reputation: 8880
data.table
setDT(df)
setnames(df, "prestige_1", "prestige")
dcast(data = df, formula = year + name ~ rowid(year, name), value.var = c("position", "prestige"))[order(year, name)]
Upvotes: 1
Martin Gal
Reputation: 16978
Similar to Rui Barradas:
library(dplyr)
library(tidyr)
data %>%
rename(prestige=prestige_1) %>%
arrange(year, name, match(prestige, c("High", "Medium", "Low"))) %>%
group_by(year, name) %>%
mutate(n=row_number()) %>%
pivot_wider(names_from=n, values_from=c(position, prestige)) %>%
ungroup()
returns
# A tibble: 205 x 8
year name position_1 position_2 position_3 prestige_1 prestige_2 prestige_3
<int> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 2010 Benedikte Kiaer Min. Of Social Affairs NA NA Medium NA NA
2 2010 Bertel Haarder Min. Of Interior Affairs Min. Of Health NA High Medium NA
3 2010 Birthe Ronn Hornbech Min. Of Ecclesiastical Affairs Min. Of Refugees, Immigration & Integ~ NA Medium Low NA
4 2010 Brian Mikkelsen Min. Of Economic & Business Affairs NA NA Medium NA NA
5 2010 Charlotte Sahl Madsen Min. Of Science, Technology & Inno~ NA NA Low NA NA
6 2010 Claus Hjort Frederik~ Min. Of Finance NA NA High NA NA
7 2010 Gitte Lillelund Bech Min. Of Defense NA NA High NA NA
8 2010 Hans Christian Schmi~ Min. Of Transport NA NA Medium NA NA
9 2010 Henrik Hoegh Min. Of Food, Agriculture & Fisher~ NA NA Medium NA NA
10 2010 Inger Stojberg Min. Of Employment NA NA Medium NA NA
# ... with 195 more rows
Upvotes: 1
Rui Barradas
Reputation: 76402
Reshape the data to wide format twice, once for position and the other for prestige_1, and join the two results.
library(dplyr)
library(tidyr)
df1 %>%
group_by(year, name) %>%
mutate(group = row_number()) %>%
pivot_wider(
id_cols = c(year, name),
names_from = group,
names_prefix = "position",
values_from = position
) %>%
inner_join(
df1 %>%
group_by(year, name) %>%
mutate(group = row_number()) %>%
pivot_wider(
id_cols = c(year, name),
names_from = group,
names_prefix = "prestige",
values_from = prestige_1
),
by = c("year", "name")
)
Upvotes: 2
Related Questions
- merge values based on duplicates in column
- combine values from different rows of same column when repeat values occur in a different column
- R function to combine rows based on duplicate times
- Rstudio: Combining row values into one row based on duplicate values in column
- Combining and duplicating rows based on a condition
- Merge rows and keep values based on another column
- Merge Values of Columns if Another Column is Duplicated
- Combine rows based on multiple columns and keep all unique values
- Consolidate "duplicate" rows in R
- How can I combine rows within the same data frame in R (based on duplicate values under a specific column)?