Skip to content

03 Working with Spreadsheets Part 2

Jump to bottom
Duncan Paterson edited this page Jul 30, 2021 · 2 revisions

Putting it all together

RepoState.ods allows you to quickly generate charts of our dataset, without interfering with the actual data. Some examples are inside LO/img. The spreadsheet is linked to the csv data files, which is why you will see the following prompt when first opening it in LibreOffice.

prompt

  • yesoverride all existing filters, and gets the latest additions.
  • no will make sure you see the files as the last person saving it

There are three types of sheets:

  • sheets linked to csv files, with basic filters to derive unique values (red)
  • lookup sheets that resolve ID column with their matching headings using functions(blue)
  • display sheets for easy copying into your favorite chart generating apps with all functions removed and just the values (green)

sheets

If you find a useful way of looking at the data, feel free to share it by adding new sheets, and a graphic of how you display it. You can look at the non-color coded sheet for some ideas how functions can help you to prepare your data for quick visualizations.

The links are one way. That means that changes to the data tables made in the spreadsheet will not update the csv files. Because the lookup tables are linked to the first tables, new data will populate all other sheets. However, you need to updated (using paste-special) the display sheets manually.

The current graphics are displaying a) all primary sources that appear in readings acts, and how often they are mentioned, and b) the authors which appear in reading acts, with the number of distinct titles, and how often they are mentioned.

Linking a LibreOffice sheet to the csv data.

Instead of copying data between csv and ods you can also link a sheet in LibreOffice to the corresponding table online.

  1. Create a new empty sheet and select Sheet > Link to External Data in the menu bar.

  2. Switch to your browser and select the csv file you want to import.

    • Make sure you are selecting the correct branch
    • Click on the raw button.

  3. A new window or tab should be open in you browser. Copy the URL from the browser address bar, it should look something like this: https://raw.githubusercontent.com/readchina/ReadAct/2.0-fiction/data/ActType.csv

  4. Back in LibreOffice paster this URL into the URL of external Data Source form and hit enter.

    • adjust the csv column type as necessary, i.e. use text for all columns instead of standard

  5. Confirm by clicking ok, you should now see all the data of the csv in your spreadsheet.

Clone this wiki locally