Consider accepting "format=csv"

[krivard]

The API is very inconvenient for one of our users because they don’t use R or Python and they’re literally running API queries manually, then running the JSONs they find through online converters to get CSVs.

For now, we can put up a python server somewhere and have it do the transformation as a middleman, to make their workflow a little less precarious.

Long-term we should consider supporting CSV formatted output directly. What might make it tricky is the tight integration with the rest of Epidata, because this is how api.php currently ends:

// send the response as a json object
header('Content-Type: application/json');
echo json_encode($data);
?>

[korlaxxalrok]

@krivard Do you have an example of how they are calling the API?

Assuming something like cURL to make requests, this is a cool example of using jq to format responses in CSV.

You'd pretty much pipe into jq with this bit of magic:

jq -r '.epidata | (map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'

An ex. against our API (truncated a wee bit):

❯ curl -s -X GET "https://delphi.cmu.edu/epidata/api.php?source=covidcast&cached=true&data_source=jhu-csse&signal=confirmed_incidence_num&geo_type=county&time_values=20200501&time_type=day&geo_value=*&as_of=20200509" | jq -r '.epidata | (map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'
"direction","geo_value","issue","lag","sample_size","stderr","time_value","value"
0,"01001",20200507,6,,,20200501,-2
0,"01003",20200507,6,,,20200501,1
0,"01005",20200507,6,,,20200501,3
0,"01007",20200507,6,,,20200501,0
0,"01009",20200507,6,,,20200501,2
0,"01011",20200507,6,,,20200501,1
0,"01013",20200507,6,,,20200501,12
0,"01015",20200507,6,,,20200501,-1
0,"01017",20200507,6,,,20200501,-5
0,"01019",20200507,6,,,20200501,0
0,"01021",20200507,6,,,20200501,-1
...

Redirected to a file and viewed in something fancy:

[krivard]

They type the query by hand into a web browser, and do all their analysis in a spreadsheet program. I love jq but anything requiring a command line is probably out.

…

On Fri, Sep 4, 2020 at 8:07 PM Brian Clark ***@***.***> wrote: @krivard <https://github.com/krivard> Do you have an example of how they are calling the API? Assuming something like cURL to make requests, this is a cool example of using *jq* to format responses in CSV <https://www.sanity.io/blog/exporting-your-structured-content-as-csv-using-jq-in-the-command-line> . You'd pretty much pipe into *jq* with this bit of magic: jq -r '.epidata | (map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' An ex. against our API (truncated a wee bit): ❯ curl -s -X GET "https://delphi.cmu.edu/epidata/api.php?source=covidcast&cached=true&data_source=jhu-csse&signal=confirmed_incidence_num&geo_type=county&time_values=20200501&time_type=day&geo_value=*&as_of=20200509" | jq -r '.epidata | (map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' "direction","geo_value","issue","lag","sample_size","stderr","time_value","value" 0,"01001",20200507,6,,,20200501,-2 0,"01003",20200507,6,,,20200501,1 0,"01005",20200507,6,,,20200501,3 0,"01007",20200507,6,,,20200501,0 0,"01009",20200507,6,,,20200501,2 0,"01011",20200507,6,,,20200501,1 0,"01013",20200507,6,,,20200501,12 0,"01015",20200507,6,,,20200501,-1 0,"01017",20200507,6,,,20200501,-5 0,"01019",20200507,6,,,20200501,0 0,"01021",20200507,6,,,20200501,-1 ... Redirected to a file and viewed in something fancy: [image: image] <https://user-images.githubusercontent.com/3670389/92291970-0791ae80-eee9-11ea-8959-f2da7548be2d.png> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#208 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAI24CQN7372ZPXH6N3CLH3SEF6LDANCNFSM4QZOK2YA> .

[krivard]

Fixed in #210