Download data by looping

[Meerim Asanbekova]

Dear Stata Users,
I hope this message finds you in good spirits.

As I'm relatively new to Stata, despite reading related manuals and posts I could not create a workable syntax.

I'm unsure about the best approach to rectify this issue and would greatly appreciate any guidance or suggestions you could offer in this regard.

I'm reaching out to seek assistance with a task involving data retrieval through looping. I have a dataset containing a list of cities structured as follows:

Code	Region	Name	Latitude	Longitude
GB	Europe	London	51.5074	-0.1278
US	North America	New York	40.7128	-74.006
JP	Asia	Tokyo	35.6895	139.6917
FR	Europe	Paris	48.8566	2.3522
AE	Middle East	Dubai	25.2769	55.2962
SG	Asia	Singapore	1.3521	103.8198
DE	Europe	Berlin	52.52	13.405

However, in my case, I actually have a list of 226 locations.

I aim to download climate data by leveraging the latitude and longitude coordinates of each city. Specifically, I need to download data by accessing the API link, looping for each city. For instance, for the first city (for London), the link will be utilized: https://power.larc.nasa.gov/api/temp...=1981&end=2022.
For this, I use the following code:

 
 import delimited "https://power.larc.nasa.gov/api/temporal/monthly/point?parameters=T2M,PRECTOTCORR_SUM,PRECTOTCORR&community=AG&longitude=-0.1278&latitude=51.5074&format=CSV&start=1981&end=2022", rowrange(12) varnames(12)

Then, I need to add a column containing the Code, Region, and Name of the city in question. As a result, I need to create this table:

Code	Region	Name	Latitude	Longitude	PARAMETER	YEAR	JAN	FEB	…	NOV	DEC	ANN
GB	Europe	London	51.5074	-0.1278	T2M	1981	3.53	2.28	…	6.58	0.19	9.15
GB	Europe	London	51.5074	-0.1278	T2M	1982	2.71	4.19	…	7.75	3.48	9.87
GB	Europe	London	51.5074	-0.1278	T2M	1983	5.73	1.24	…	6.61	4.83	9.77
…	…	…	…	…	…	…	…	…	…	…	…	…
GB	Europe	London	51.5074	-0.1278	T2M	2020	5.95	6.36	…	8.55	5.02	11
GB	Europe	London	51.5074	-0.1278	T2M	2021	2.81	4.6	…	6.62	6.4	10.21
GB	Europe	London	51.5074	-0.1278	T2M	2022	3.91	6.58	…	9.3	4.07	11.56

After that, I need to save this table if it is created for the first city in my list of cities, suppose as "climate_data.dta".
Then, in the second loop, I need to download data for the next city in my list (in my case, it is New York) using the same API link but only changing the longitude and latitude within the link:

 
 import delimited "https://power.larc.nasa.gov/api/temporal/monthly/point?parameters=T2M,PRECTOTCORR_SUM,PRECTOTCORR&community=AG&longitude=-74.006&latitude=40.7128&format=CSV&start=1981&end=2022", rowrange(12) varnames(12)

Then, I need to add a column containing the Code, Region, and Name of the city in question (New York) as in the previous loop. After that, I need to append this table to the table in the "climate_data.dta" and save, replacing the existing file if it is created for the second and ongoing cities in my list of cities.
As a result, I need to create this table:

Code	Region	Name	Latitude	Longitude	PARAMETER	YEAR	JAN	FEB	…	NOV	DEC	ANN
GB	Europe	London	51.5074	-0.1278	T2M	1981	3.53	2.28	…	6.58	0.19	9.15
GB	Europe	London	51.5074	-0.1278	T2M	1982	2.71	4.19	…	7.75	3.48	9.87
GB	Europe	London	51.5074	-0.1278	T2M	1983	5.73	1.24	…	6.61	4.83	9.77
…	…	…	…	…	…	…	…	…	…	…	…	…
GB	Europe	London	51.5074	-0.1278	T2M	2020	5.95	6.36	…	8.55	5.02	11
GB	Europe	London	51.5074	-0.1278	T2M	2021	2.81	4.6	…	6.62	6.4	10.21
GB	Europe	London	51.5074	-0.1278	T2M	2022	3.91	6.58	…	9.3	4.07	11.56
US	North America	New York	40.7128	-74.006	T2M	1981	3.53	2.28	…	6.58	0.19	9.15
US	North America	New York	40.7128	-74.006	T2M	1982	2.71	4.19	…	7.75	3.48	9.87
US	North America	New York	40.7128	-74.006	T2M	1983	5.73	1.24	…	6.61	4.83	9.77
…	…	…	…	…	…	…	…	…	…	…	…	…
US	North America	New York	40.7128	-74.006	T2M	2020	5.95	6.36	…	8.55	5.02	11
US	North America	New York	40.7128	-74.006	T2M	2021	2.81	4.6	…	6.62	6.4	10.21
US	North America	New York	40.7128	-74.006	T2M	2022	3.91	6.58	…	9.3	4.07	11.56

Any guidance or assistance you could provide would be greatly appreciated.

Thank you in advance for your support.

Warm regards,

Meerim

[George Ford]

I saved the country data as master.dta

use master, clear

** GET DATA
levelsof code, local(levels)
foreach v of local levels {
     qui summ latitude if code=="`v'"
    local lat = r(mean)
    qui summ longitude if code=="`v'"
    local lon = r(mean)
    preserve
    import delimited "https://power.larc.nasa.gov/api/temporal/monthly/point?parameters=T2M,PRECTOTCORR_SUM,PRECTOTCORR&community=AG&longitude=`lon'&latitude=`lat'&format=CSV&start=1981&end=2022", rowrange(12) varnames(12) clear
    g code = "`v'"
    save climate_`v', replace
    restore
}

** MASTER CLIMATE DATA
clear
set obs 0
local files : dir . files "climate_*.dta"
foreach f of local files {
    append using "`f'"
}
save climate_all, replace

** JOIN TO MASTER
use master, clear
joinby code using climate_all

[George Ford]

Note that joinby will only keep matches. There are options to modify that (help joinby).

[George Ford]

If code is duplicated, which it may be, then use name instead.

Or, you can create an identifier and use that.

egen id = group(code city)

[Meerim Asanbekova]

If code is duplicated, which it may be, then use name instead.

Or, you can create an identifier and use that.

egen id = group(code city)

Dear Mr. George Ford,

I wanted to express my gratitude for your swift attention to the issue.

Your provided code and insights were immensely helpful, and I'm pleased to report that everything is now working perfectly.

Thank you for your invaluable support and expertise.

Warm regards,
Meerim