npl_mag.r

library(tidyverse)

df <- read_csv("~\\NPL_DOI_FOS.csv")

df_restructured <- df %>% 
  select(doi, matches("fos_\\d_name$")) %>% 
  group_by(doi) %>% 
  mutate(id = seq_along(doi)) %>% 
  pivot_longer(starts_with("fos")) %>% 
  filter(!is.na(value)) %>% 
  mutate(level = str_extract(name, "\\d") %>% as.numeric()) %>% 
  group_by(doi, id) %>% 
  arrange(desc(level)) %>% 
  slice(1) %>% 
  select(doi, id, top_fos = value) %>% 
  ungroup()


df_restructured %>% 
  write_csv("npl_cleaned.csv")