Tidyr spread columns based on certain values (contains loop?)

[ozzy257]

Hi guys,

I have a df with only 1 column from players bought and sold during a specific season:
The order is: Type of Transfer and Season, Player Name, Market Value

Arrivals 21/22
Mbwana Samatta
€6.00m
Allahyar Sayyadmanesh
€1.00m
Departures 21/22
Deniz Türüc
€0.00m
Arrivals 20/21
Irfan Can Kahveci
€7.00m
Marcel Tisserand
€4.00m

Targeted format:
| Player Name      | Season         | Type of Transfer | Value |
| --------         | -------------- | -------------- | -------------- |
| Mbwana Samatta   | 20/21          |Arrival         |€6.00m
| Allahyar Sayyadmanesh      | 20/21          |Departures      |€1.00m

I need to spread this data with some function that recognizes that the bunch of players bought or sold belongs to a season.

I tried with rownames_to_column, spread function, and contains function of tidyr.

I am an absolute beginner and grateful for any hint how to approach this.

Thanks!

[FJCC]

Here is one method.

DF <- data.frame(INFO = c('Arrivals 21/22',
 'Mbwana Samatta',
 '€6.00m',
 'Allahyar Sayyadmanesh',
 '€1.00m',
 'Departures 21/22',
 'Deniz Türüc',
 '€0.00m',
 'Arrivals 20/21',
 'Irfan Can Kahveci',
 '€7.00m',
 'Marcel Tisserand',
 '€4.00m'))
library(stringr)
library(tidyr)
library(dplyr)
DF <- DF %>% mutate(Category = case_when(
   str_detect(INFO, "Arrivals|Departures") ~ "Type",
   str_detect(INFO, "€") ~ "Value",
   TRUE ~ "Name"
 ))
 
DF$Type <- NA
DF$Name <- NA
for (i in 1:nrow(DF)) {
  if(DF[i, "Category"] == "Type") StoredType <- DF[i, "INFO"]  
  if(DF[i, "Category"] == "Name") StoredName <- DF[i, "INFO"]  
  if(DF[i, "Category"] == "Value") {
    DF[i, "Type"] <- StoredType
    DF[i, "Name"] <- StoredName
  }  
}
DF <- DF %>% filter(Category == "Value")
DF <- DF %>% separate(Type, into = c("Type", "Season"), sep = " ")
DF <- DF %>% select(Name, Season, Type, Value = INFO)
DF
                   Name Season       Type  Value
1        Mbwana Samatta  21/22   Arrivals €6.00m
2 Allahyar Sayyadmanesh  21/22   Arrivals €1.00m
3           Deniz Türüc  21/22 Departures €0.00m
4     Irfan Can Kahveci  20/21   Arrivals €7.00m
5      Marcel Tisserand  20/21   Arrivals €4.00m