Question

我正试图获得R(tidyverse)以检查国家(Abreviations在两栏中都需要更新)。以国家总清单为标题,以国家全名取代。我尝试了<代码>ifelse的声明,但正在取得令人信服的成果。数据集可以是:,在此可见。任何建议都非常有益。

# head of df1 
df1 <- structure(list(CountryCode = c("BF", "BG", "BM", "BR", "CA", 
"CE", "CH", "GH", "GM", "HA", "IC", "IN", "IR", "IT", "IZ", "JO", 
"KE", "KS", "LE", "MX", "NI", "NL", "NP", "PK", "QA", "SA", "SF", 
"SP", "TC", "TD", "TU", "TW", "UK", "US", "VM", "JA", "EI"), 
    CountryName = c("BAHAMAS, THE", "BANGLADESH", "MYANMAR", 
    "BRAZIL", "CANADA", "SRI LANKA", "CHINA", "GHANA", "GERMANY", 
    "HAITI", "ICELAND", "INDIA", "IRAN", "ITALY", "SYRIA", "JORDAN", 
    "KENYA", "KOREA, REPUBLIC OF (SOUTH )", "LEBANON", "MEXICO", 
    "NIGERIA", "NETHERLANDS, THE", "NEPAL", "PAKISTAN", "QATAR", 
    "SAUDI ARABIA", "SOUTH AFRICA", "SPAIN", "UNITED ARAB EMIRATES", 
    "TRINIDAD AND TOBAGO", "TURKEY", "CHINA (TAIWAN)", "UNITED KINGDOM", 
    "UNITED STATES", "VIETNAM", "JAPAN", "IRELAND")), row.names = c(NA, 
-37L), class = c("tbl_df", "tbl", "data.frame"))

# df2 (with the NAs removed)
df2 <- structure(list(ID = c("E23531197", "Q07441087", "U79148472", 
"Y43292349", "A40257720", "Y64624318", "B97628594", "T06694322", 
"J67643839", "B11219391", "V72937405", "C22564030", "B90485180", 
"B56635832", "J44870077", "Y05510846", "X82045887", "V14380989", 
"J87108024", "X61041595", "A60573885", "Y23860927", "T74687928", 
"G60127163", "P45475749", "D40096957", "F73581752", "M76164536", 
"X57076671", "K30511805", "B41693626", "E50532024", "H47908538"
), `MA Nation` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "IN", 
NA, NA, "CA", NA, NA, NA, NA), `PR Nation` = c("PK", "BG", "MX", 
"PK", "IN", "CH", "JA", "EI", "UK", "CH", "UK", "IN", "TU", "BG", 
"IN", "CA", "CA", "PK", "CH", "BG", "LE", "IN", "IN", "TW", "BG", 
"IN", "CH", "BG", "CA", "BF", "CH", "CH", "CH")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -33L))

Answer 1

页: 1 两个数据框架按国家代码分列。

library(tidyverse)
library(readxl)

df <- read_xlsx( ~Data SU23 Enroll R AY22-23 2023-08-23 2 Stack Overflow.xlsx )
countrydata <- read_xlsx( ~TBL Country codes.xlsx )

glimpse(df)
#> Rows: 542
#> Columns: 3
#> $ ID          <chr> "F31769765", "E23531197", "Q07441087", "Y92280507", "F2688…
#> $ `MA Nation` <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA…
#> $ `PR Nation` <chr> NA, "PK", "BG", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "M…
glimpse(countrydata)
#> Rows: 37
#> Columns: 2
#> $ CountryCode <chr> "BF", "BG", "BM", "BR", "CA", "CE", "CH", "GH", "GM", "HA"…
#> $ CountryName <chr> "BAHAMAS, THE", "BANGLADESH", "MYANMAR", "BRAZIL", "CANADA…

df %>%
  # Put all columns with country codes in a long layout
  pivot_longer(-ID) %>%
  filter(!is.na(value)) %>%
  # Join with the country code table
  left_join(countrydata,
            by = join_by(value == CountryCode)) %>%
  # Drop the country code column
  select(-value) %>%
  # Return to the two country columns layout
  pivot_wider(names_from = name,
              values_from = CountryName) %>%
  # Append the rows for IDs without country data
  bind_rows(df %>% filter(is.na(`PR Nation`) & is.na(`MA Nation`)))
#> # A tibble: 542 × 3
#>    ID        `PR Nation`    `MA Nation`
#>    <chr>     <chr>          <chr>      
#>  1 E23531197 PAKISTAN       <NA>       
#>  2 Q07441087 BANGLADESH     <NA>       
#>  3 U79148472 MEXICO         <NA>       
#>  4 Y43292349 PAKISTAN       <NA>       
#>  5 A40257720 INDIA          <NA>       
#>  6 Y64624318 CHINA          <NA>       
#>  7 B97628594 JAPAN          <NA>       
#>  8 T06694322 IRELAND        <NA>       
#>  9 J67643839 UNITED KINGDOM <NA>       
#> 10 B11219391 CHINA          <NA>       
#> # ℹ 532 more rows

^{Created on 2023-08-24 with}reprex v2.0.2.

Answer 2

df2 |> 
  pivot_longer(-ID, names_to = "Type", values_to = "CountryCode", values_drop_na = TRUE)|>
  left_join(df1) |> 
  select(-CountryCode) |> 
  pivot_wider(values_from = "CountryName")

产出:

# A tibble: 33 × 3
   ID        `PR Nation`    `MA Nation`
   <chr>     <chr>          <chr>      
 1 E23531197 PAKISTAN       NA         
 2 Q07441087 BANGLADESH     NA         
 3 U79148472 MEXICO         NA         
 4 Y43292349 PAKISTAN       NA         
 5 A40257720 INDIA          NA         
 6 Y64624318 CHINA          NA         
 7 B97628594 JAPAN          NA         
 8 T06694322 IRELAND        NA         
 9 J67643839 UNITED KINGDOM NA         
10 B11219391 CHINA          NA         
# ℹ 23 more rows

友情链接