Message Boards Message Boards

5
|
17262 Views
|
4 Replies
|
14 Total Likes
View groups...
Share
Share this post:

Import online Google Docs spreadsheet data

I have some data in Googlespread

https://docs.google.com/spreadsheets/d/1BvqdIUxuMVS2kNsg4tl53iVpNyj1tnYnO3zXH5qW9Rk/edit?usp=sharing

How can I import them?

Thanks, Christian

4 Replies

You can modify your url to change the format to CSV for downloading:

url = "https://docs.google.com/spreadsheets/d/1BvqdIUxuMVS2kNsg4tl53iVpNyj1tnYnO3zXH5qW9Rk/export?format=csv";

Then you can import it (I needed to use the CharacterEncoding option to get the characters to import normally):

csv = Import[url, "CSV", CharacterEncoding -> "UTF8"];

Then you can make a Dataset, which makes data a little bit easier to reason with in many cases:

dataset = Dataset[Map[AssociationThread[First[csv], #] &, Rest[csv]]]

enter image description here

Now you can get all the species with pin equal to 7, for example:

dataset[Select[#Pin == 7 &]]

enter image description here

Or do the same and then sort by species, alphabetically:

dataset[Select[#Pin == 7 &] /* SortBy[#Species &]]

enter image description here

Or group by species, get a count of how many there are for each species (using Length) and sorting that in descending order:

dataset[GroupBy[#Species &] /* Sort /* Reverse, Length]    

enter image description here

Or group by station and then by species (the extra //Normal // Dataset should not be needed, but helps with formatting):

ds2 = dataset[GroupBy[#Plot &], GroupBy[#Species &] /* Sort /* Reverse, Length] // Normal // Dataset

enter image description here

Or, proceeding with data set 'ds2', make pie charts for each distribution:

ds2[All, PieChart[#, ChartLegends -> Keys[#]] &]

enter image description here

POSTED BY: Arnoud Buzing

You should look into URLFetch for potential alternatives, but simple import works too:

data = Import["https://docs.google.com/spreadsheets/d/1BvqdIUxuMVS2kNsg4tl53iVpNyj1tnYnO3zXH5qW9Rk", "Data"];

enter image description here

and then some sort of parsing or data manipulation:

data[[2, 1, 2]] // TableForm

enter image description here

POSTED BY: Vitaliy Kaurov

This is excellent, Arnoud!

POSTED BY: Vitaliy Kaurov

Thank you for the excellent discussion. This has been very helpful.
Next question, how to import multiple sheets (aka tabs) from Google Sheets?

POSTED BY: Stephen Wilkus
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract