Replace and Reshape in Loop

[Shouro Dasgupta]

I have daily data for the years 1960 - 2010. Each year's data is in a csv file, the files (in one directory) are named 1960, 1961,...2010. I would like to open the files and run the following command:

drop v1

foreach v of var day_* {
        replace `v' = "." if inlist(`v', "NA")
}

destring, replace

reshape long day_, i( iso3 lon lat) j (day)

rename day_ precip

I would also like to generate a year variable (can this be set equal to the file name?). Then save the file in dta.Can this be done in a loop? Thanks a lot!

[Clyde Schechter]

So something like this:

clear
tempfile building
save `building', emptyok
forvalues y = 1960/2010 {
    import delimited using `y'.csv, clear
    drop v1
    foreach v of varlist day_* {
        replace `v' = "." if `v' == "NA"
        destring `v', replace
    }
    reshape long day_, i(iso3 lon lat) j(day)
    rename day_ precip
    gen int year = `y'
    append using `building'
    save `"`building'"', replace
}

use `building', clear
// PERHAPS OTHER DATA MANAGEMENT TASKS
save my_combined_file, replace

Not tested: beware of typos, minor errors.

Note: there is no need to use the inlist() function with just two arguments as you have done: it is simpler and more transparent to write `v' == "NA".

[Shouro Dasgupta]

Thank you so much! This is the final code that I used. I wanted to destring, reshape, and finally convert the daily data to monthly data. The last part could probably be done more efficiently. Thanks again!

clear
cd "directory_path"
local allfiles : dir "." files "*.csv"
tempfile building
clear
save building, emptyok replace
forvalues y = 1960/2010 {
    insheet using `y'.csv, clear
    drop v1
    foreach v of varlist day_* {
        replace `v' = "." if `v' == "NA"
        destring `v', replace
    }
    reshape long day_, i(iso3 lon lat) j(day)
    rename day_ precip
    gen int year = `y'
    append using building
    save `"building"', replace
}

clear

use building, clear

sort iso3 lon lat year day 

local start = date("1960/01/01", "YMD")
local end = date("2010/12/31", "YMD")
local ob = `end' - `start' + 1
egen date = seq(), from(`start') to(`end')
format %td date
format date %d
gen month=month(date)

collapse precip, by(iso3 lon lat month year)

sort iso3 lon lat year month 

save building, replace

[Nick Cox]

foreach v of varlist day_* {
     replace `v' = "." if `v' == "NA"    
     destring `v', replace
}

could be just

destring day_*, replace ignore(NA)