Double Loop with unique combinations

[David Crawford]

Dear Statalist,

I am trying to append a series of csv file within several folders using a double loop. In particular my folder structure is the following:

---rawdata---wheat---Italy
--- UK
--- USA
--- Germany
---Maize---Italy
--- UK
--- USA
--- Germany

and so on. To this end I am using the following double loop:

* Define Agricultural Products
local products ""wheat" "maize" "Sunflower Oil" "Bovine Meat" "Olive Oil""
* Define the countries to process
local countries "Italy France UK USA Germany"
foreach product in `products' {
local filepath = "$path/rawdata/comtrade/`product'"
* Outer loop over countries
foreach country in `countries' {

local filepath = "`filepath'/`country'" // Save path to current folder in a local

local files : dir "`filepath'" files "*.csv" // Save name of all files in folder ending with .csv in a local
di `"`files'"' // Display list of files to import data from

tempfile master // Generate temporary save file to store data in
save `master', replace empty
foreach x of local files {
* Import the file
import delimited "`filepath'/`x'", clear
* Save as a temporary .dta file
tempfile temp_dta
save "`temp_dta'", replace
* Append to the master file
append using `master'
save `master', replace
save "$path/data/`country'_`product'_1962_2023", replace

}
}
}

The loop runs, hovever, instead of obtaining datasets for each combination of country and product, I get that the data for different products of the same country end up in the same dataset. In practice a mess. Could you please help me in sorting it out?

Thanks in advance

[Clyde Schechter]

Because I do not have, and cannot readily create, a file system structure like yours, I have to troubleshoot this all trying to visualize in my head what is going on. So I'm not 100% certain I'm getting this right. But I think the problem is that you have the command -save "$path/data/`country'_`product'_1962_2023", replace- located inside the -foreach x of local files {- loop. It should be immediately outside that loop just before the close brace of the -foreach country in `countries' {- loop.

As an aside, I'm not sure your -local products- command is correct. It seems to rely on nesting quotes inside of quotes. Stata does not allow that and you may find that once you hit Sunflower Oil things go screwy. You need to use compound double quotes for the outer quotes. Even if you are getting satisfactory results, this is a risky way to program this kind of list. It should be

local products `""wheat" "maize" "Sunflower Oil" "Bovine Meat" "Olive Oil""'

Added: I see another possible problem with your code. When you create tempfile master for the first time, the data set in memory will be empty, and this is fine. But when you come back the next time through the country loop, it won't be, so information left behind from the previous iteration will be "contaminating" the new information you create in it. I think you can fix this by putting a -clear- command after the -tempfile master- command.

Also your tempfile temp_dta serves no purpose that I can see. It keeps getting overwritten, and its contents are never read in. I would remove both the command that creates it and the command that saves it.

[David Crawford]

Hi Clyde, thank you very much for your answer. So, I tried to use your suggestions as well as the one from the reddit statlist forum, I am now running this

local products `""wheat" "maize" "Sunflower Oil" "Bovine Meat" "Olive Oil""'
local countries "Italy France UK USA Germany"
foreach product in `products' {
foreach country in `countries' {
local filepath = "$path/rawdata/comtrade/`product'/`country'"
local files : dir "`filepath'" files "*.csv"
tempfile master
save `master', replace empty
foreach file of local files {
import delimited "`filepath'/`file'", clear
append using `master'
save `master', replace

}
save "$path/data/`country'_`product'_1962_2023", replace
}

}


but I am getting the same issue. Regarding the file structure, each /`product'/`country' folder contains data for a specific product in a specific country. They are a bunch of csv files for different times about the same product within a given country. My goal is to obtain a separate dataset for each product-country combination by appending them.The problem is that I get a file with data containing every product for a specific country. I am 100% sure the CSV files are correct as I checked them manually. In each of these files there is a variable for country, one indicating export import, one for the code of the commodity and the second the value of the trade for that commodity. I hope this clarifies

[David Crawford]

Problem solved!! For reference, this is the solution:

local products `""wheat" "maize" "Sunflower Oil" "Bovine Meat" "Olive Oil""'
local countries "Italy France UK USA Germany"
foreach product in `products' {
foreach country in `countries' {
local filepath = "$path/rawdata/comtrade/`product'/`country'"
local files : dir "`filepath'" files "*.csv"

// Initialize the master file for each product-country combination
tempfile master
clear
save `master', replace empty

foreach file of local files {
import delimited "`filepath'/`file'", clear
append using `master'
save `master', replace
}

// Save the final dataset for the product-country combination
save "$path/data/`country'_`product'_1962_2023", replace
}
}

[Clyde Schechter]

So, I decided that rather than trying to mark up your code, I would just write something from scratch, using only the details of your local macros. See if this works for you:

local products `""wheat" "maize" "Sunflower Oil" "Bovine Meat" "Olive Oil""'
local countries Italy France UK USA Germany
tempfile master

foreach p of local products {
    foreach c of local countries {
        clear
        save `master', emptyok
        local filepath "$path/rawdata/comtrade/`product'/`country'"
        local files: dir "`filepath'" files "*.csv"
        foreach f of local files {
            import delimited "`filepath'/`f'", clear
            append using `master'
            save `master', replace
        }
        save "$path/data/`country'_`product'_1962_2023", replace
    }
}

Added: Crossed with #4. And thank you for posting your solution so that others can learn from it. I note that our solutions are essentially the same.