View source for Lab: Semantic Lifting - CSV

== Topic ==
* Reading non-semantic data tables into semantic knowledge graphs
* Specifically, reading data in CSV format via Pandas dataframes into RDF graphs

== Useful materials ==
The textbook (Allemang, Hendler & Gandon): 
* chapter 3 on RDF (from section 3.1 on ''Distributing Data across the Web'')
* [https://github.com/fivethirtyeight/data/tree/master/russia-investigation Information about the dataset]

Pandas:
* [https://towardsdatascience.com/pandas-dataframe-playing-with-csv-files-944225d19ff Article about working with pandas.DataFrames and CSV]
* [https://pandas.pydata.org/docs/user_guide/io.html#parsing-options User Guide for Pandas]
* class: DataFrame (methods: read_csv, set_index, apply, iterrows, astype)

rdflib:
* classes/interfaces from earlier (such as Graph, Namespace, URIRef, Literal, perhaps BNode)
* also vocabulary classes like RDF (e.g., type), RDFS (e.g., label) and XSD (for various datatypes)

== Tasks ==
We will be working with the same dataset as in the SPARQL exercise: [https://projects.fivethirtyeight.com/russia-investigation/ FiveThirtyEight's Russia Investigation]. It contains data about special investigations conducted by the United States from the Watergate-investigation until May 2017. [[Russian investigation KG | This page explains]] the Russia Investigation dataset a bit more.

'''Task:'''
In the SPARQL exercise, you downloaded the data as a Turtle file ([[File:russia_investigation_kg.txt]], which you renamed to ''.ttl''). This time you will [https://github.com/fivethirtyeight/data/tree/master/russia-investigation download the data as a CSV file from GitHub].

''(Unfortunately, the earlier exercises used a wrong and very unfinished version of the russia_investigation_kg.txt file. It has been updated now with the correct version!)''

'''Task:'''
Install Pandas in your virtual environment, for example
 pip install pandas
Write a Python program that imports the ''pandas'' API and uses Pandas' ''read_csv'' function to load the ''russia-investigation.csv'' dataset into a Pandas ''dataframe''.

'''Task:'''
''(Pandas basics)'' Inspect the Pandas dataframe. If you have called your dataframe ''df'', you can check out the expressions below. Use the [https://pandas.pydata.org/docs/user_guide/io.html#parsing-options documentation] to understand what each of them does.
 df.shape
 df.index  # ...and list(df.index)
 df.columns
 df['name']
 df.name
 df.loc[3]
 df.loc[3]['president']
(Pandas offers many ways of picking out rows, columns, and values. These are just examples to get started.)

'''Task:''' 
''(Pandas basics)'' Pandas' apply method offers a compact way to process all the rows in a dataframe. This line lists all the rows in your dataframe as a Python dict():
 df.apply(lambda row: print(dict(row)), axis=1)
What happens if you drop the ''axis'' argument, or set ''axis=0''?

'''Task:'''
Instead of the ''lambda'' function, you can use a named function. Write a function that prints out only the ''name'' and ''indictment-days'' in a row, and use it to print out the ''name'' and ''indictment-days'' for all rows in the dataframe.

''Alternative to df.apply():''
Pandas offers several ways to iterate through data. You can also use the ''itertuples'' methods in a simple ''for''-loop to iterate through rows.

'''Task:'''
Modify your function so it adds ''name'' and ''indictment-days'' triples to a global rdflib ''Graph'' for each row in the dataframe. The subject in each triple could be the numeric index of the row.

You can use standard terms from RDF, RDFS, XSD, and other vocabularies when you see fit. Otherwise, just use an example-prefix.

Things may be easier if you copy ''df.index'' into an ordinary column of the dataframe:
 df['id'] = df.index
You can use this index, along with a prefix, as the subject in your triples.

'''Task:'''
Continue to extend your function to convert the non-semantic CSV dataset into a semantic RDF one. Here is an example of how the data for one investigation could look like in the end:
 muellerkg:investigation_0 a muellerkg:Indictment ;
     muellerkg:american true ;
     muellerkg:cp_date "1973-01-30"^^xsd:date ;
     muellerkg:cp_days -109 ;
     muellerkg:indictment_days -246 ;
     muellerkg:investigation muellerkg:watergate ;
     muellerkg:investigation_days 1492 ;
     muellerkg:investigation_end "1977-06-19"^^xsd:date ;
     muellerkg:investigation_start "1973-05-19"^^xsd:date ;
     muellerkg:name muellerkg:James_W._McCord ;
     muellerkg:outcome muellerkg:conviction ;
     muellerkg:overturned false ;
     muellerkg:pardoned false ;
     muellerkg:president muellerkg:Richard_Nixon .

== If you have more time ==

'''Task:'''
If you have not done so already, you should include checks to ensure that you do not add empty columns to your graph.

'''Task:'''
If you have more time, you can use DBpedia Spotlight to try to link the people (and other "named entities") mentioned in the dataset to DBpedia resources. 
 pip install pyspotlight
You can start with the code example below, but you will need exception-handling when DBpedia is unable to find a match. For instance:
<syntaxhighlight>
import spotlight

ENDPOINT = 'https://api.dbpedia-spotlight.org/en/annotate'
CONFIDENCE = 0.5  # filter out results with lower confidence

def annotate_entity(entity_name, filters={'types': 'DBpedia:Person'}):
    annotations = []
    try:
	annotations = spotlight.annotate(ENDPOINT, entity_name, confidence=CONFIDENCE, filters=filters)
    except spotlight.SpotlightException as e:
        # catch exceptions thrown from Spotlight, for example when no DBpedia resource is found
	print(e)
	# handle exceptions here
    return annotations
</syntaxhighlight>
The example uses the types-filter with DBpedia:Person, because we only want it to match with people. You can choose to only implement the URIs in the response, or the types as well.

Useful materials:
* [https://www.dbpedia-spotlight.org/api Spotlight Documentation]
* [https://pypi.org/project/pyspotlight/ pyspotlight 0.7.2 at PyPi.org]