Analyze ranked-choice data

[isaid]

Hi, I'm currently doing research and collecting a ranked-choice data. Basically people choosing their preferences in a topic.
E.g., people ranking their preference on fruits: orange, mango, apple, avocado

The clean data frame looks like this:

	Fruits							Color
1	orange;apple;banana;avocado		blue;yellow;red;green
2	avocado;apple;banana;orange		red;green;blue;yellow
3	apple;banana;orange;avocado		yellow;red;green;blue
4	banana;orange;apple;avocado		green;blue;red;yellow
5	apple;avocado;banana;orange		yellow;blue;green;red

The first person put orange as their first preference, then apple, banana, and avocado as the last preference.
and so on

Scores:
1st preference = 4
2nd preference = 3
3rd preference = 2
4th preference = 1

Desired result

	apple	avocado	banana	orange	blue	green	red	yellow
1	3		1		2		4		4		1		2		3
2	3		4		2		1		2		3		4		1
3	4		1		3		2		1		2		3		4
4	2		1		4		3		3		4		2		1
5	4		3		2		1		3		2		1		4

The part that I confused is to figure out how to give score for each column -> turn from semicolon separated string into column with numeric value.
If I can pass this, I can create the desired output dataframe.

I've found pmr package, but the documentation only a few. Moreover, this one too advance. I don't really need that for current state.

Please help me at the scoring stage

[Flm]

library(tidyverse)

df <- tribble(~Fruits,						    	~Color,
	"orange;apple;banana;avocado",		"blue;yellow;red;green",
	"avocado;apple;banana;orange"	,	"red;green;blue;yellow",
	"apple;banana;orange;avocado"	,	"yellow;red;green;blue",
	"banana;orange;apple;avocado"	,	"green;blue;red;yellow",
	"apple;avocado;banana;orange"	,	"yellow;blue;green;red")


df %>% 
  mutate(id = 1:nrow(.)) %>% 
  pivot_longer(-id) %>% 
  separate(value, c("v4", "v3", "v2", "v1")) %>% 
  pivot_longer(-c(id,name), names_to = "v") %>% 
  mutate(v = as.numeric(str_replace_all(v, "v", ""))) %>% 
  select(-name) %>% 
  pivot_wider(names_from = "value", values_from = "v") %>% 
  select(-id)

result

# A tibble: 5 × 8
  orange apple banana avocado  blue yellow   red green
   <dbl> <dbl>  <dbl>   <dbl> <dbl>  <dbl> <dbl> <dbl>
1      4     3      2       1     4      3     2     1
2      1     3      2       4     2      1     4     3
3      2     4      3       1     1      4     3     2
4      3     2      4       1     3      1     2     4
5      1     4      2       3     3      4     1     2

[fcas80]

Here is an alternate solution using base R. It's not more efficient than Flm's solution. If anyone has an idea how I can make mine more efficient, probably with an apply statement, please let me know.

fruits <- c(
"orange;apple;banana;avocado",
"avocado;apple;banana;orange",
"apple;banana;orange;avocado",
"banana;orange;apple;avocado",
"apple;avocado;banana;orange")
n <- length(fruits)

df1 <- data.frame((matrix(ncol=4,nrow=n)))
colnames(df1) <- c("4","3","2","1")
df2 <- data.frame((matrix(ncol=4,nrow=n)))
colnames(df2) <- c("apple","avocado","banana","orange")
for (i in 1:n){
df1[i,] <- unlist(strsplit(as.character(fruits[i]), split = ";"))
for (j in 1:4){
w <- which(df1[i,] == colnames(df2[j]))
c <- as.numeric(colnames(df1[w]))
df2[i,j] <- c
}
}
df2

apple avocado banana orange
1 3 1 2 4
2 3 4 2 1
3 4 1 3 2
4 2 1 4 3
5 4 3 2 1