Creating Computable Data For SDG Metrics From PDFs, Video, and Images

Maureen Baehr, Ben Kickert

(RowDefault[, (Spacer1), , (Spacer1), ])  #WolframTechConf

ABSTRACT

For the past six years we've been working on using the Wolfram stack to advance computable data for Sustainable Development Goal (SDG) metrics. In 2020 - 2021 both the need and the technology to address the need have become more critically important.

The NEED is greater than ever for using non-traditional sources to obtain  computable data for the SDGs:
	1. Estimates are that COVID has set the world off-track for reaching SDG targets by at least 5 years and likely many more.
	2. COVID has severely disrupted regular methods for keeping track of metrics.
	
WOLFRAM CAPABILITIES have improved in areas that facilitate generating computable data from non-traditional sources:
	1. PDF — Significant improvements make it possible to extract data, text and images from PDF documents and into workflows in order to use various other Wolfram tools.
	2. Video — new capabilities make it possible to extract text and audio from video, in order to use text and audio recognition techniques 
	3. Web Session —  allows for more systematic data extraction from web-based sources.
	4. Improvements in TextRecognize, ImageIdentify, TextContents, Classifiers, Interpreters, etc. allow for more robust semantic interpretation
	5. BERT, GPT2, GPT3 and other neural net models to create summaries, etc.
	6. WebSearch, WebImageSearch, WikiDataSearch and ExternalServices to find supporting information sources.
	
Using various combinations of these tools we are working through formal documents (e.g. SDG Voluntary National Reviews) and less formal sources (e.g. social media, videos, news reports, etc).

Results will be presented on the use and success of several proof-of-concept projects.

The Situation : SDG Metrics & COVID

SDGCounting's 6th year = new and different approaches... by necessity

The SDG targets were already significantly off-track -- major theme of 2019 SDG UN conference.

Estimates are that COVID has set back results against some SDG targets by as much as 10 years.

COVID caused the shut-down of most Country Statistics offices and the stoppage of much standard data collection.

COVID data collection has become critical -- further taxing limited capacity, especially in poor countries.

For those who work with statistics, data collection and analysis, the COVID disruptor has meant:

disrupted conferences and report delivery schedules;

more reports based on forecasts and projections rather than results;

more missing data;

mixing of old data with new to generate reports.

Opportunities to test many of Wolfram's capabilities to try to find / make computable data.

Sources & Types of Raw Information

PDF -- Wolfram is much-improved but badly-formed PDF-maker issues increase degree of difficulty.

The prettier the infographic, the more difficult it is to capture computable data.

Raw, basic data sources are deteriorating (mix old and new, more missing data, etc.)

No image-identifier / AI has mastered extraction of computable data from image charts and graphs.

GPT3, etc. are not (quite) ready for prime time.

We've used -- WebSearch, WebImageSearch, Wikipedia, Wikidata, Twitter, Audio, Video etc. to explore.

Mixed results on getting to data we're confident making conclusions on.

Ben will give you some examples of how we're used the Wolfram stack to approach this difficult year.

Textual Analysis on 6 years of Voluntary National Reviews for the SDGs

VNRs are country specific reports submitted to the UN as a way of tracking progress and providing more nuanced feedback on how development work is unfolding in a more local setting.  All VNRs submitted to date can be found online: https://sustainabledevelopment.un.org/vnrs/ .  Collectively, these reports represent thousand of pages of data and analysis across 273 documents and over 30,000 pages.

Using WebSession and some cleaning in Wolfram, we were able to obtain the meta data on these reports.

vnrdataset=Dataset[CloudGet["https://www.wolframcloud.com/obj/ben.kickert/vnrdataset"],MaxItems->10]

Let’s look at submissions by countries and by year ignoring multiple languages and giving credit to countries that do not have PDFs available

submissions=DeleteDuplicatesBy[vnrdataset,StringJoin[#[[1]],#[[2]]]&][[All,1;;2]];

groupbyyear=GroupBy[submissions,"Year"][[All,All,"Country"]]

groupbycountry=Dataset[KeySort[GroupBy[submissions,"Country"]][[All,All,"Year"]],MaxItems->10]

Since we want to do textual analysis, using a combination of URLDownload, Import, and WordCounts we were able to create a new dataset that includes the full plaintext of a report, word counts, and total words for the report (useful when looking at proportional representation).  For this work we focused only on reports in English.

vnrtextds=Dataset[CloudGet["https://www.wolframcloud.com/obj/ben.kickert/vnrtextds"],MaxItems->3]

Dataset[vnrtextds,MaxItems->3]

Sentiment Analysis

The first idea we wanted to explore was to see how the sentiment of the language of the reports may have changed over the years, especially with the challenges related to COVID-19.  We can run this on a single report to see what percentage of non-neutral statements are positive.

sentiment1=Counts[Classify["Sentiment",#]&/@TextSentences[StringReplace[vnrtextds[[1,"Plaintext"]],"\n"->" "]]]
N[sentiment1[["Positive"]]/(sentiment1[["Positive"]]+sentiment1[["Negative"]])]

Here we see a “Positivity Rating” of 65%. Let’s run this figure for all the reports and add it to our dataset.

sentimentAnalysis[text_]:=Module[{sentiment},sentiment=Counts[Classify["Sentiment",#]&/@TextSentences[StringReplace[text,"\n"->" "]]];
N[sentiment[["Positive"]]/(sentiment[["Positive"]]+sentiment[["Negative"]])]];

sentimentds=Dataset[CloudGet["https://www.wolframcloud.com/obj/ben.kickert/sentimentds"],MaxItems->3]

Let’s look at how sentiment may have changed over the years by examining a scatter plot and averages by year.

ListPlot[Normal[Values[sentimentds[[All,{"Year","Positivity Rating"}]][All,{"Year"->ToExpression}]]],PlotLabel->"Positivity Rating",ImageSize->Large]

BarChart[Mean/@sentimentds[GroupBy["Year"],All,"Positivity Rating"],ChartLabels->Range[2016,2021]]

Based on this, there appears to be little change.  Let’s look sentiments by country.

ReverseSort[Mean/@sentimentds[GroupBy["Country"],All,"Positivity Rating"]]

While there is a large range, we are not seeing any obvious key themes.  This approach may not have shown us anything for this particular set of data, however, we have seen it prove effective in other deployments.

Tracking Keywords in VNRs

Let’s turn our attention to the words within the reports.  Since we have already pulled in the WordCounts when we imported the plaintext, we can jump right in.

Start by defining words that are common in these types of documents, but not particularly useful (stop words have already been removed)

deletewords={"sustainable","development","goals","sdg","sdgs","percent","government","implementation","national","%","","\[UnknownGlyph]","","","cent","s","indicators","2010","2011","2012","2013","2014","2015","2016","2017","2018","2019","2020","2021","2022","2023","2024","2025","2026","2027","2028","2029","2030"};

yearlywords=KeyDrop[ReverseSort[Merge[#,Total]],deletewords]&/@sentimentds[GroupBy["Year"],All,"Word Counts"]

Create a word cloud of top words for each year

Grid[Transpose[{Range[2016,2021],WordCloud[Take[#,100],ImageSize->300]&/@Normal[Values[yearlywords]]}],Frame->All]

We see that “Health” takes front and center in 2021 which is not surprising given the focus on the pandemic.

While word clouds are useful for quick visual summaries, what if we want to track how a words usage has changed over time.  To do this, we will want to look at proportionality and a ListLinePlot can help us visualize that.

ListLinePlot[(#[["covid-19"]]/._Missing->0)/Total[#]&/@yearlywords]

We can create a defined function to automate this for other words.

wordpopularity[word_]:=ListLinePlot[(#[[word]]/._Missing->0)/Total[#]&/@yearlywords,PlotLabel->Style[word,Bold],ImageSize->Large]

wordpopularity[#]&/@{"health","environment","climate","gender","women","data","water"}

{,,,,,,,}

These reports are released as part of the High Level Political Forum on Sustainable Development and variances in term popularity may be influenced by the thematic goals for each year:

2016: No Theme

2017: Poverty, Hunger, Health, Gender Equality, Infrastructure, Oceans

2018: Water, Energy, Cities, Consumption, Life on Land

2019: Education, Employment, Inequality, Climate, Peace, Partnerships

2020: All

2021: Poverty, Hunger, Health, Employment, Inequality, Consumption, Climate, Peace, Partnerships

As part of our research into the SDGs, we have created several documents outlining key terms, reports, and agencies for each goal.  Using this document we can actually expand our searches to look more broadly at themes within each goal.

keywords=CloudGet["https://www.wolframcloud.com/obj/ben.kickert/sdgkeywordsandreports"];

We can import the column containing relevant words and transform those  into lists we can cross reference against.

sdgwords=StringTrim[StringSplit[#[[4]],","]]&/@Normal[keywords];
sdgwords[[1]]

So if we want to know how many times a goal related word or phrase is used, we can do that.  Here is an example looking at SDG1 (No Poverty) and the first report

StringCases[sentimentds[[1,"Plaintext"]],sdgwords[[1]],IgnoreCase->True]//Length

Using Table we can find matches across all reports and all goals.

matches=Table[Length[StringCases[#[["Plaintext"]],sdgwords[[n]],IgnoreCase->True]]&/@Normal[sentimentds],{n,1,17}];
Shallow@matches

This can be turned into new columns for our dataset

keywordmatches=Dataset[Association/@Transpose[Thread["SDG"<>ToString[#]<>" Matches"->matches[[#]]]&/@Range[17]]];

Because report lengths vary significantly (from less than 20 pages to over 700 pages), we want to also translate those hits into proportional figures.

countvalues=Flatten/@Transpose[{Normal[sentimentds[[All,"Word Count"]]],Normal[Values[keywordmatches]]}];
proportional[list_]:=N[#/First[list]]&/@Rest[list];
prolabels="SDG"<>ToString[#]<>" Proportional"&/@Range[17];
proportioncolumns=Dataset[AssociationThread[prolabels->#]&/@(proportional[#]&/@countvalues)];

All of these can be combined into our final dataset.

finalvnrds=Join[sentimentds,keywordmatches,proportioncolumns,2];

finalvnrds=Dataset[CloudGet["https://www.wolframcloud.com/obj/ben.kickert/finalvnrds"],MaxItems->3]

Let’s look at SDG keyword usage by goal across the years.

BarChart[N[Mean[#]]&/@Normal[Values[finalvnrds[GroupBy["Year"],All,#]]],PlotLabel->#,ChartLabels->Range[2016,2021]]&/@prolabels

Looking at this visual representation, we could ask which goals have been talked about less from 2018-2021 to gauge how attention has been diverted because of the pandemic.

In this case we see substantial drop offs in SDG 4 (Education) and SDG 6 (Clean Water) while Health (SDG 3) has seen a steady increase in attention.

If we want to look at individual country focus areas, we can do that as well:

countryfocus=BarChart[Values[#[[-17;;]]],ChartLabels->Range[17],PlotLabel->#[[2]]<>" - "<>ToString[#[[1]]]]&/@Normal[finalvnrds];
RandomSample[countryfocus,10]

To account for country specific language and writing styles, we can compare how reports have changes over the years between countries who have submitted 3 reports

multiplereporters=Normal[Keys[Select[groupbycountry,Length[#]>2&]]]

Grid[Partition[BarChart[Values[#[[-17;;]]],ChartLabels->Range[17],PlotLabel->#[[2]]<>" - "<>ToString[#[[1]]],ImageSize->300]&/@Normal[Select[SortBy[finalvnrds,"Country"],MemberQ[multiplereporters,#[["Country"]]]&]],3]]

The tools displayed here are not intended for drawing definitive conclusions, but rather can be used as a tool for finding key areas to focus on when exploring large textual data.  In this case, it would be impossible to adequately summarize over 30,000 pages of PDFs, but with summarization and string analytics, it is possible.

Summarizing Videos with Wolfram

Earlier in the month, the UN held its annual World Data Forum that included over 80 sessions with top experts.  Even if you listened non-stop to streams, you would only be able to catch about 25% of the content.  However, videos of most of the sessions have been made available opening up more possibilities.

We have manually downloaded the videos and can now reference them using the Video function.  NOTE: Importing  the videos directly was tried, but the approach introduced instability and errors most likely due to files size.

vid1=Video["Z:\\OneDrive\\Documents\\Downloads\\(TA3.02) Leaving no one behind_ adopting a systematic approach of using small area estimation for SD.mp4"];

vid2=;

vid3=;

vid4=;

We can create a list of those videos with their names

vids={Last[StringSplit[First[Information[#,"ResourcePath"]],{"\\"}]],#}&/@{vid1,vid2,vid3,vid4};

Scrubbing through videos is an effective way to get the gist of what a video is about.  Using VideoFrameExtract, we can simulate that process for a large number of files.

To extract a frame each minute, we need to figure out how long each video is and set the time spec to increment ever 60 seconds for the duration of the video.  Those steps can be combined to create a visual summary of the presentation.

frames=VideoExtractFrames[#[[2]],Range[60,IntegerPart[QuantityMagnitude[UnitConvert[Information[#[[2]],"Duration"],"Seconds"]]],60]]&/@vids;

Display a random sample of each

Grid[Transpose[{vids[[All,1]],GraphicsGrid[Partition[RandomSample[#,12],3],ImageSize->500]&/@frames}],Frame->All,ItemSize->{{20,Full},Automatic},Alignment->Top]

By doing this, we can get a quick feel for each presentation to know where to pay attention .  The first video appears to be concept heavy due to the large amount of text based slides shown .  Presentations 2 and 3 appear to be more of the talking head variety .  And the last one on violence against women appears to be the most data driven .

Based on this, let’s focus more on the fourth presentation.  If we want a visual tool, we can animate a list that includes timestamps to identify places we may want to pay more attention.

ListAnimate[ImageCompose[ImageResize[#[[1]],500],Rasterize[Style[ToString[#[[2]]],10]],Scaled[{.03,.95}]]&/@Transpose[{frames[[4]],Range[Length[frames[[4]]]]}],AnimationRate->1]

If we make a list of interesting frames, we can create a video with 30 second splits of each section.

times={6,27,39,41};

VideoJoin[VideoTrim[vid4,{#[[1]],#[[2]]}]&/@({#*60,#*60+30}&/@times)];

Summarizing Audio

In addition to video summarization, we can also process videos like this using the audio.  The most direct implementation of this is using transcription services.

Wolfram

While we would like to be able to use Wolfram as much as possible, in our experience, the SpeechRecognize functionality is still limited in relation to other online services.  In the example below, to extracted a 50 second clip from the video we are interested in.  We made sure the speaker had clear annunciation and good pauses.

audio=AudioTrim[Audio[vid4],{1850,1900}]

Unfortunately, even with this short clip, we ran into numerous crashes and the output revealed some transcription errors.   Additionally, timestamps were not available.

wolframtranscript=SpeechRecognize[audio]

YouTube Hack

An alternative approach when dealing with videos that are on YouTube is to open the transcript window that is used for closed captioning and copy and paste the string to a TXT document and then manipulated it.  Here we can see some of the errors are corrected from Wolfram (covid 19 instead of coded nineteen).  One draw back to this approach is lack of punctuation and formatting in the string.  However, with some additional cleaning, timestamps can be included.

youtubetranscript=Import["Z:\\OneDrive\\Documents\\Downloads\\vid4-yt.txt"];
StringRiffle[Partition[StringSplit[youtubetranscript,"\n"],2][[All,2]]," "]

Column[Partition[StringSplit[youtubetranscript,"\n"],2]]

Otter.ai

A third external option, and one we have had the most success with is using the paid service Otter.ai.  This approach allows exporting in multiple formats and speaker matching which can be useful for recorded meetings.  We also see the text is formatted and the results have slightly fewer errors.  This too can be structured to include time stamps as well as speakers

ottertranscript=Import["Z:\\OneDrive\\Documents\\Downloads\\vid4-otter.txt"];

Partition[StringSplit[ottertranscript,"\n"],3][[52,2]]

StringTrim/@Partition[StringSplit[ottertranscript,"\n"],3][[52]]

Analyzing the Full text with Otter.ai

fulltranscript=StringRiffle[Partition[StringSplit[ottertranscript,"\n"],3][[All,2]]," "];

WordCloud[DeleteStopwords[ToLowerCase[fulltranscript]]]

Since we know the entire conference is about data, let’s exclude that as well some other common words and rerun on all 4 video transcripts.

WordCloud[KeyDrop[WordCounts[DeleteStopwords[ToLowerCase[StringRiffle[Partition[StringSplit[Import[#],"\n"],3][[All,2]]," "]]]],{"think","data","information","know","really","important","people","just","really","actually","lot","like"}]]&/@

Simply looking at these quick word clouds, it is easy to see that the fourth video is the most focused and we know exactly what the speakers are talking about.

More detailed analysis of these transcripts may prove useful, but in terms of simply being able to identify the most relevant and important content, the above tools provide some quick short cuts when wading through hours of video.

Where do we go from here: Taking Video and PDF Summarization Further

Given our focus on SDG Data and how it is being collected, we would like to be able to get better visual summaries of key conferences and events.  Tools being introduced into Wolfram may provide opportunities for that.  For instance, VideoIntervals will allow users to find constant frames in a video and extract those for further analysis.  Unfortunately, the time required to perform these on long clips is still too long.

Another approach we want to explore is using machine learning as a way to help identify and extract charts, maps, and diagrams from frames.  This would also prove useful for extracting visual data from PDFs.  Unfortunately, after many hours of trying, our efforts to use Classify, Object Detection, and several Neural Networks has provided us with more ways to not do things instead of concrete results.  We will continue to explore.

Thanks!

bk@datavelopment.com