处理联合国2030议程数据时，R语言Inner_join与筛选操作后观测值消失的问题求助

Hey there, let's break down why your 12.1.1 indicator is disappearing after the inner_join—this is a common gotcha for folks new to dplyr joins, so don't worry, we'll figure it out!

The Root Cause of Missing Data #

First, remember how inner_join works: it only keeps rows where the matching keys (in your case, Country/GeoAreaName and Year/TimePeriod) exist in both data frames. So if your 12.1.1 indicator vanished after joining, that means none of the country-year combinations for 12.1.1 in your df have a matching entry in the gdp data frame. All those rows get dropped during the inner join.

Step-by-Step Troubleshooting #

Let's verify this and find exactly what's missing:

1. Isolate the 12.1.1 data first
   Grab all rows for indicator 12.1.1 from your original SDG data:

   library(dplyr)
   df_1211 <- df %>% filter(Indicator == "12.1.1")
   

   Then check which country-year combinations exist here:

   unique(df_1211[, c("GeoAreaName", "TimePeriod")])
   

2. Find which combinations are missing from GDP data
   Use anti_join to get rows from df_1211 that have no match in gdp (this is the key tool for debugging join issues!):

   missing_matches <- df_1211 %>% 
     anti_join(gdp, by = c("GeoAreaName" = "Country", "TimePeriod" = "Year"))
   
   # View the missing country-year pairs
   print(missing_matches)
   

   If this returns rows, those are exactly the entries that got dropped during your inner join—because there's no corresponding GDP data for those countries/years.

3. Check for country name mismatches
   Often, the problem is tiny differences in country names (e.g., "U.S.A." vs "United States", "Côte d'Ivoire" vs "Ivory Coast", or extra spaces). Let's find countries that exist in one data frame but not the other:

   # Countries in SDG data but not GDP data
   sdg_only_countries <- setdiff(unique(df$GeoAreaName), unique(gdp$Country))
   print(sdg_only_countries)
   
   # Countries in GDP data but not SDG data
   gdp_only_countries <- setdiff(unique(gdp$Country), unique(df$GeoAreaName))
   print(gdp_only_countries)
   

   You might need to clean up country names (e.g., using stringr functions to trim spaces, standardize abbreviations) to get better matches.

4. Verify year data types
   Sometimes years are stored as strings in one data frame and numbers in the other, which can break matching even if the values look the same. Check the classes:

   class(df$TimePeriod)
   class(gdp$Year)
   

   If they don't match, convert them to the same type. For example, if TimePeriod is a string and Year is numeric:

   df$TimePeriod <- as.numeric(df$TimePeriod)
   # OR
   gdp$Year <- as.character(gdp$Year)
   

How to Fix It #

• If you want to keep all SDG data (even if there's no matching GDP), use left_join instead of inner_join—this will retain all rows from df and fill missing GDP values with NA:

  joinedf <- left_join(df, gdp, by = c("GeoAreaName" = "Country", "TimePeriod" = "Year"))
  

• If the issue is name/type mismatches, clean up those fields first before joining.

内容的提问来源于stack exchange，提问作者Giando