2a  Importing Data from Files

* Stata: Load CSV files

* Basic CSV load from URL
import delimited "https://raw.githubusercontent.com/datasets/gdp/master/data/gdp.csv", clear

* Load from local file
import delimited "data/my_data.csv", clear

* Handle common issues:
import delimited "data.csv", ///
    encoding(utf-8) ///           // or latin1, windows-1252
    delimiter(",") ///            // try ";" or tab
    varnames(1) ///               // first row = headers
    stringcols(1) ///              // force col 1 as string
    clear

* Verify the load
describe, short
list in 1/5

# R: Load CSV with tidyverse
library(tidyverse)

# Basic CSV load from URL
df <- read_csv("https://raw.githubusercontent.com/datasets/gdp/master/data/gdp.csv")

# Load from local file
df <- read_csv("data/my_data.csv")

# Handle common issues:
df <- read_csv("data.csv",
    locale = locale(encoding = "UTF-8"),
    na = c("", "NA", ".", "-999"),
    col_types = cols(id = col_character())
)

# Verify the load
cat(sprintf("Loaded %s rows, %d columns\n",
    format(nrow(df), big.mark=","), ncol(df)))
head(df)

* Stata: Load different file formats

* Stata native format (.dta)
use "data.dta", clear

* Load from URL
use "https://example.com/data.dta", clear

* Load only specific variables
use country year gdp using "data.dta", clear

* Load with condition (faster than loading all then dropping)
use "data.dta" if year >= 2000, clear

* Excel files
import excel "data.xlsx", firstrow clear
import excel "data.xlsx", sheet("Sheet2") firstrow clear

* View available sheets
import excel "data.xlsx", describe

# R: Load different file formats
library(tidyverse)
library(readxl)      # For Excel files
library(haven)       # For Stata files

# Excel files
df <- read_excel("data.xlsx")                       # First sheet
df <- read_excel("data.xlsx", sheet = "Sheet2")     # Specific sheet

# List available sheets
excel_sheets("data.xlsx")

# Load all sheets into a list
sheets <- excel_sheets("data.xlsx")
all_data <- map(sheets, ~read_excel("data.xlsx", sheet = .x))
names(all_data) <- sheets

# Stata files (preserves labels!)
df <- read_dta("data.dta")

# R native formats
df <- readRDS("data.rds")       # Single object
load("workspace.RData")         # Multiple objects

* Stata: Merging datasets

* Load master dataset first
use "gdp.dta", clear

* Basic 1:1 merge (one observation per key in both)
merge 1:1 country year using "population.dta"

* Check merge results (ALWAYS DO THIS!)
tab _merge

* m:1 merge (many-to-one: many in master, one in using)
* Example: household data merged with region characteristics
merge m:1 region using "region_data.dta"

* 1:m merge (one-to-many)
merge 1:m country using "yearly_data.dta"

* Keep only matched observations
keep if _merge == 3
drop _merge

* Or assert all should match
assert _merge == 3
drop _merge

# R: Merging datasets with dplyr
library(tidyverse)

# Create example datasets
gdp <- tibble(
    country = c("USA", "CHN", "DEU"),
    year = c(2020, 2020, 2020),
    gdp = c(21000, 14700, 3800)
)

pop <- tibble(
    country = c("USA", "CHN", "JPN"),
    year = c(2020, 2020, 2020),
    population = c(331, 1400, 126)
)

# Inner join (only matches)
merged <- inner_join(gdp, pop, by = c("country", "year"))

# Left join (keep all from gdp)
merged_left <- left_join(gdp, pop, by = c("country", "year"))

# Full/outer join (keep all)
merged_full <- full_join(gdp, pop, by = c("country", "year"))

# When key columns have different names
merged <- left_join(gdp, pop, by = c("country" = "iso3", "year" = "yr"))

# Check merge quality
cat(sprintf("GDP: %d, Pop: %d, Merged: %d\n",
    nrow(gdp), nrow(pop), nrow(merged)))

* Stata: Reshaping data

* Example: Start with wide data
* Variables: country, gdp2018, gdp2019, gdp2020

* Wide to Long
reshape long gdp, i(country) j(year)

* i() = identifier variables (rows)
* j() = new variable to hold the suffix (2018, 2019, 2020)
* 'gdp' = stub of the variables to reshape

* Long to Wide
reshape wide gdp, i(country) j(year)

* Verify the reshape
list in 1/6

* Multiple variables at once
* If you have gdp2018 gdp2019 pop2018 pop2019:
reshape long gdp pop, i(country) j(year)

# R: Reshaping with tidyr
library(tidyverse)

# Example wide data
wide <- tibble(
    country = c("USA", "CHN"),
    gdp_2018 = c(20500, 13900),
    gdp_2019 = c(21400, 14300),
    gdp_2020 = c(21000, 14700)
)

# Wide to Long using pivot_longer()
long <- wide %>%
  pivot_longer(
    cols = starts_with("gdp_"),   # Columns to pivot
    names_to = "year",             # New column for names
    values_to = "gdp",             # New column for values
    names_prefix = "gdp_"          # Remove this prefix
  ) %>%
  mutate(year = as.integer(year))

# Long to Wide using pivot_wider()
wide_again <- long %>%
  pivot_wider(
    names_from = year,             # Column to spread
    values_from = gdp,             # Values to fill
    names_prefix = "gdp_"          # Add prefix to column names
  )

print(long)