fırst second and third coutry names in the columns

[osman3144]

hi friends,
ı have a table and ı want to report fırst, second and third countries name in the columns. so ı need your help. thanks.
dataframe is :

df <- data.frame(
SPORTS=c("soccer", "basketball","tennis","baseball","golf","running","volleyball","badminton","swimming","boxing","table tennis","skiing","ice skating","roller skating","cricket","rugby"),
Germany=c(1, NA, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, NA, 2, NA, NA),
Austria=c(2, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3, NA, NA, 1, NA),
Belgium=c(NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3, 3, NA, NA),
Bulgaria=c(3, 2, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, NA, NA, NA, NA),
Czechia=c(1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, 4, 3, 1),
Denmark=c(4, 3, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, 4, 5, NA, 2),
Estonia=c(5, NA, 1, NA, NA, NA, NA, NA, NA, NA, 6, NA, NA, 6, 4, NA),
Finland=c(6, 4, 2, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, 7, 5, 3),
France=c(7, 5, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, 6, NA),
Croatia=c(8, 7, NA, 2, NA, NA, NA, NA, NA, NA, NA, 6, NA, NA, NA, 5),
Netherlands=c(NA, NA, 4, NA, 1, NA, NA, NA, NA, NA, NA, NA, 6, NA, 8, 6),
Ireland=c(NA, 8, 5, 3, 2, NA, NA, NA, NA, NA, 8, NA, NA, NA, NA, 7)
)

screenshot of df:

how can ı do that... thanks.

[technocrat]

DF <- data.frame(
SPORTS=c("soccer", "basketball","tennis","baseball","golf","running","volleyball","badminton","swimming","boxing","table tennis","skiing","ice skating","roller skating","cricket","rugby"),
Germany=c(1, NA, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, NA, 2, NA, NA),
Austria=c(2, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3, NA, NA, 1, NA),
Belgium=c(NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3, 3, NA, NA),
Bulgaria=c(3, 2, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, NA, NA, NA, NA),
Czechia=c(1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, 4, 3, 1),
Denmark=c(4, 3, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, 4, 5, NA, 2),
Estonia=c(5, NA, 1, NA, NA, NA, NA, NA, NA, NA, 6, NA, NA, 6, 4, NA),
Finland=c(6, 4, 2, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, 7, 5, 3),
France=c(7, 5, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, 6, NA),
Croatia=c(8, 7, NA, 2, NA, NA, NA, NA, NA, NA, NA, 6, NA, NA, NA, 5),
Netherlands=c(NA, NA, 4, NA, 1, NA, NA, NA, NA, NA, NA, NA, 6, NA, 8, 6),
Ireland=c(NA, 8, 5, 3, 2, NA, NA, NA, NA, NA, 8, NA, NA, NA, NA, 7))

cbind(DF$SPORT,DF[which(DF[1,] %in% 1:3)])
#>          DF$SPORT Germany Austria Bulgaria Czechia
#> 1          soccer       1       2        3       1
#> 2      basketball      NA      NA        2      NA
#> 3          tennis      NA      NA       NA      NA
#> 4        baseball      NA      NA       NA      NA
#> 5            golf      NA      NA       NA      NA
#> 6         running      NA      NA       NA      NA
#> 7      volleyball      NA      NA       NA      NA
#> 8       badminton      NA      NA       NA      NA
#> 9        swimming      NA      NA       NA      NA
#> 10         boxing      NA      NA       NA      NA
#> 11   table tennis       4      NA        5      NA
#> 12         skiing      NA       3       NA       4
#> 13    ice skating      NA      NA       NA      NA
#> 14 roller skating       2      NA       NA       4
#> 15        cricket      NA       1       NA       3
#> 16          rugby      NA      NA       NA       1

Then format using {gt} or similar.

[osman3144]

thanks for your answer but we need to write country names not the numbers.also new columns are FIRST, SECOND and THIRD. this table says that xxx counrty get degree in yyy sports "1" place then "2" place.... and go on..... so we need country names under FIRST, SECOND and THIRD columns.

[EconProf]

One solution is to pivot_longer() then pivot_wider(). An apparent tie between Germany and Czechia is soccer was a problem so I gave it to Germany.

library(tidyverse)

DF <- data.frame(
  Sport=c("soccer", "basketball","tennis","baseball","golf","running","volleyball","badminton","swimming","boxing","table tennis","skiing","ice skating","roller skating","cricket","rugby"),
  Germany=c(1, NA, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, NA, 2, NA, NA),
  Austria=c(2, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3, NA, NA, 1, NA),
  Belgium=c(NA, 1, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3, 3, NA, NA),
  Bulgaria=c(3, 2, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, NA, NA, NA, NA),
  Czechia=c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, 4, 3, 1),
  Denmark=c(4, 3, NA, NA, NA, NA, NA, NA, NA, NA, 4, NA, 4, 5, NA, 2),
  Estonia=c(5, NA, 1, NA, NA, NA, NA, NA, NA, NA, 6, NA, NA, 6, 4, NA),
  Finland=c(6, 4, 2, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, 7, 5, 3),
  France=c(7, 5, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 5, NA, 6, NA),
  Croatia=c(8, 7, NA, 2, NA, NA, NA, NA, NA, NA, NA, 6, NA, NA, NA, 5),
  Netherlands=c(NA, NA, 4, NA, 1, NA, NA, NA, NA, NA, NA, NA, 6, NA, 8, 6),
  Ireland=c(NA, 8, 5, 3, 2, NA, NA, NA, NA, NA, 8, NA, NA, NA, NA, 7))

DF_long <- DF |> 
  pivot_longer(-Sport, 
               names_to = "Country", 
               values_to = "Place") 

DF_long |> 
  group_by(Sport) |> 
  filter(!is.na(Place) & Place <=3) |> 
  ungroup() |> 
  pivot_wider(everything(),
              names_from = Place, 
              values_from = Country) |> 
  rename(First = `1`, Second = `2`, Third = `3`)
#> # A tibble: 10 × 4
#>    Sport          First       Second   Third   
#>    <chr>          <chr>       <chr>    <chr>   
#>  1 soccer         Germany     Austria  Bulgaria
#>  2 basketball     Belgium     Bulgaria Denmark 
#>  3 tennis         Estonia     Finland  <NA>    
#>  4 baseball       <NA>        Croatia  Ireland 
#>  5 golf           Netherlands Ireland  <NA>    
#>  6 skiing         <NA>        <NA>     Austria 
#>  7 ice skating    <NA>        <NA>     Belgium 
#>  8 roller skating <NA>        Germany  Belgium 
#>  9 cricket        Austria     <NA>     Czechia 
#> 10 rugby          Czechia     Denmark  Finland

^{Created on 2022-11-20 with reprex v2.0.2}