Importing CSV file (from API) into STATA

[Shikha Sharma]

I have a file from an api that gets updated on a daily basis, hence, I do not wish to download the csv frequently and rather prefer to use the API (csv format file) link directly for importing in Stata . My data currently looks like-

District_Key	Key	District	16/01/2021	16/01/2021	16/01/2021
			First Dose Administered	second Dose Administered	total
an_n	name_one	one	0
an_ns	name_two	two	0
an_s	name_three	three	2

and I wish to import data in the following format-

Date	District_Key	Key	District	First Dose Administered	second Dose Administered	total
16/01/2021	an_n	name_one	one	2	0
16/01/2021	an_ns	name_two	two	2	0
16/01/2021	an_s	name_three	three	5	2
17/01/2021	an_n	name_one	one	4	1
17/01/2021	an_ns	name_two	two	5	2
17/01/2021	an_s	name_three	three	7	3

Is there anyway I can do this neatly in Stata without manually adjusting the excel- it is a huge data being updated on a daily basis. Can't reshape. Any guidance is greatly appreciated.

Thanks.

[Leonardo Guizzetti]

Stata can accept URLs as input file locations for some commands such as -use- and -import-, so as long as your API key and table selection details are a part of the URL, I think you'll be able to use that directly. A second wrinkle is the 2-level header, which you can get around if the structure remains fixed. Something like what I sketch below may be useful starting point for you, but it is not tested and may have bugs.

import delimited "url", varnames(1) numericcols(4 5 6) rowrange(3) clear
rename v4 firstdoses
label var firstdoses "First dose administered"
rename v5 seconddoses
label var seconddoses "Second dose administered"
rename v6 total
label var total "Total doses administered"

[Shikha Sharma]

Thanks Leonardo for reply, this is a very helpful starting point but what should I do with the dates? They are column-wise in the top table, I want to bring this across rows.