How to: spatial analysis of the readership of my publications

First, we want to fetch the data from your Academia profile.

  1. Browse to your Academia profile and go to the tab ‘Analytics’
  2. Press the downloads button on the top right of the page
  3. Change file name to a format that suits your convention. I used Academia.edu – Analytics export.csv
  4. Change the file extension to csv and file format to All files
  5. Choose a destination on your hard drive
  6. Save

Second, you want to tidy up the file. One of my favorite tools for tabular data is Microsoft Excel but you could equally use LibreOffice Calc.

  1. Open the file in Excel
  2. To display the data in a neat tabular format, use the text to colums command and use the ‘,’ as delimiter.
  3. Still there is some information hidden in the URL columns. To reveal this data we
    a. first copy this column to a new worksheet.
    b. Use the text to columns command, now using ‘/’ as delimiter. (and check the option for double delimiters)
    c. Add an extra column in front of the table and add an origOrderID. This will help you to do some sorting which will speed up the cleaning process.
    d. Sort your data using Column F(Descending) and column D (Ascending). This will result in a table whicht helps you to distuinguish downloads, profile- and article views.
    e. In column D, go to the first occurence of Download and Select all Cells below and right of this Cell. Cut the data and paste in the adjacent Cell in Column E
    f. Optionally, you might want to add a category to your profile views and article views.
    g. Now we want to clean up the article itself. Go to the final column (F).
  4. Once again use Text to Columns with ‘?’ as delimeter.
  5. Replace ‘_’ with ‘ ‘
  6. Remove ‘.pdf’

Third, we want to add coordinates to map the results:

  1. Use the site https://geocode.localfocus.nl/ and copy & paste the City and Country columns from your Excel sheet into the first text box.
  2. Review the proposed locations until your happy with the results. Sometimes you need to check the internet to find a larger administrative area which you can use to find the right coordinates.
  3. When you’re ready, use the Copy command and ascertain you have selected the copy coordinates with dots option.
  4. Replace ‘,’ with ‘#’ and replace tabs with ‘,’; and replace ‘.’ with ‘@’
  5. Paste the data in your worksheet
  6. Use text to colums and ‘,’ as delimeter.
  7. Replace ‘@’ with ‘,’
  8. Replace ‘#’ with ‘,’
  9. Save data as csv-file