<jupyter_start><jupyter_text>Wikdata/Wikibase -> Pandas dataframe version 1.2 * This [Notebook](https://github.com/salgo60/open-data-examples/blob/master/Wikdata%20-%3E%20Panda%20dataset.ipynb)* Suggested as an option in WDQS see [T257117](https://phabricator.wikimedia.org/T257117)Example how Wikidata result can be transfered to a Pandas dataframe<jupyter_code>import json,sys
import pandas as pd 
from SPARQLWrapper import SPARQLWrapper, JSON
endpoint_url = "https://query.wikidata.org/sparql"

query = """# Museums in Barcelona with coordinates
SELECT ?item ?itemLabel ?coord ?lon ?lat
WHERE
{
 ?item wdt:P131 wd:Q1492.   # in the administrative territory of Barcelona
 ?item wdt:P31 wd:Q33506.   # is a museum
 ?item p:P625 ?coordinate.
 ?coordinate ps:P625 ?coord.
 ?coordinate psv:P625 ?coordinate_node.
 ?coordinate_node wikibase:geoLongitude ?lon.
 ?coordinate_node wikibase:geoLatitude ?lat.  
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
}"""

def get_sparql_dataframe(endpoint_url, query):
    """
    Helper function to convert SPARQL results into a Pandas data frame.
    """
    user_agent = "salgo60/%s.%s" % (sys.version_info[0], sys.version_info[1])
 
    sparql = SPARQLWrapper(endpoint_url, agent=user_agent)
    sparql.setQuery(query)
    sparql.setReturnFormat(JSON)
    result = sparql.query()

    processed_results = json.load(result.response)
    cols = processed_results['head']['vars']

    out = []
    for row in processed_results['results']['bindings']:
        item = []
        for c in cols:
            item.append(row.get(c, {}).get('value'))
        out.append(item)

    return pd.DataFrame(out, columns=cols)


results = get_sparql_dataframe(endpoint_url, query)
results.info()
results<jupyter_output><empty_output><jupyter_text>Another exampleI guess also pandas can be used to check quality as Python and Pandas is used much more that SPARQL federation etc...<jupyter_code>querySwedishParish = """SELECT ?item ?itemLabel ?img  ?coord ?NAD ?article WHERE {
  ?item wdt:P31/wdt:P279* wd:Q615980 .
  OPTIONAL {?item wdt:P5324  ?itemk} 
  OPTIONAL {?item wdt:P18 ?img}
  OPTIONAL {?item wdt:P625 ?coord}
  BIND (CONCAT("NAD ",?itemk) as ?itemKod)
  BIND (URI(CONCAT("https://sok.riksarkivet.se/?postid=ArkisRef",?itemk)) as ?NAD)
  OPTIONAL{ ?item wdt:P571 ?StartD .} 		
  OPTIONAL{ ?item wdt:P576 ?SlutD .} 		
  
  BIND(IF((!BOUND(?SlutD) || ?SlutD > "2008-01-01T00:00:00Z"^^xsd:dateTime ) ,
          URI(CONCAT("http://geodata.scb.se/reginawebmap/main/webapp/?typ=forsamling&f=",?SCBid)),?SCBid) AS ?SCB).

  OPTIONAL{?item wdt:P6192 ?Bygdebandid} 						
  BIND (URI(CONCAT("http://www.bygdeband.se/plats/",?Bygdebandid)) AS ?Bygdeband)

  OPTIONAL {
      ?article schema:about ?item .
      ?article schema:inLanguage "sv" .
      ?article schema:isPartOf <https://sv.wikipedia.org/> .
    }  
  OPTIONAL {?item wdt:P131 ?admin}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv". }
}
order by ?NAD"""
 
results = get_sparql_dataframe(endpoint_url, querySwedishParish)
results.info()
results.head()
queryAcademyWinners = """#Winner of the Academy Awards by Award and Time
SELECT DISTINCT ?item ?itemLabel ?awardLabel ?time
{
    ?item wdt:P106/wdt:P279* wd:Q3455803 ; # Items with the Occupation(P106) of Director(Q3455803) or a subclass(P279)
          p:P166 ?awardStat .              # ... with an awarded(P166) statement
    ?awardStat pq:P805 ?award ;            # Get the award (which is "subject of" XXth Academy Awards)
               ps:P166 wd:Q103360 .        # ... that has the value Academy Award for Best Director(Q103360)
    ?award wdt:P585 ?time .                # the "point of time" of the Academy Award
    SERVICE wikibase:label {               # ... include the labels
        bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en"
    }
}
ORDER BY DESC(?time)""" 

 
resultsAward = get_sparql_dataframe(endpoint_url, queryAcademyWinners)
resultsAward.info()
%matplotlib inline  
import matplotlib.pyplot as plt   
resultsAward.time.value_counts().sort_index()
%matplotlib inline  
import matplotlib.pyplot as plt   
plot = resultsAward.itemLabel.value_counts().plot.bar(y='counts', figsize=(25, 5)) 
plt.show()
plot = resultsAward.itemLabel.value_counts().plot.pie(y='counts', figsize=(25, 5)) 
plt.show()
plot = resultsAward.itemLabel.value_counts()[1:10].plot.pie(y='counts', figsize=(25, 5)) 
plt.show()
queryGermanStates = """
SELECT
	?state ?stateLabel
	?companies
	?population
	(?companies/?population*1000000 AS ?companiesPerM)
WHERE
{
	{ SELECT ?state (count(*) as ?companies) WHERE {
		{SELECT DISTINCT ?company ?state WHERE {
			?state wdt:P31 wd:Q1221156 .
			?company wdt:P31/wdt:P279* wd:Q4830453 .
			?company wdt:P159/wdt:P131* ?state .
			FILTER NOT EXISTS{ ?company wdt:P576 ?date } # don't count dissolved companies
		} }
	} GROUP BY ?state  }
    ?state wdt:P1082 ?population
	SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
}
ORDER BY DESC(?companiesPerM)"""   

resultsGermanStates = get_sparql_dataframe(endpoint_url, queryGermanStates)
resultsGermanStates.info() 
resultsGermanStates.head() 
resultsGermanStates.head() 
resultsGermanStates['companies'] = resultsGermanStates['companies'].astype(int)  
resultsGermanStates['population'] = resultsGermanStates['population'].astype(int)  
resultsGermanStates.set_index('stateLabel',inplace=True)
resultsGermanStates.info() 

resultsGermanStates.head() 
plotGerman = resultsGermanStates.plot.pie(title='Wikidata statistics using Pandas',x='stateLabel', y='companies', figsize=(5, 25)) 
plotGerman.legend([]);
plt.show()<jupyter_output><empty_output><jupyter_text>Wikibase Factgrid -> pandas -> Kepler.glGets letters sent and try to display it using [Kepler.gl](https://github.com/keplergl/kepler.gl) see [video](https://www.youtube.com/watch?v=dlBKzX69UH8) * [The Illuminati Correspondence Fast Forward](https://blog.factgrid.de/archives/1695) the blogpost we should try to do in Jupyter* see another [Notebook](https://github.com/salgo60/FactGrid/blob/master/FactGrid%20-%3E%20Kepler.gl.ipynb)<jupyter_code>endpointFactgrid_url = "https://database.factgrid.de/sparql"

queryFactGrid = """SELECT ?item ?itemLabel ?date ?origin ?originLabel ?origin_latitude ?origin_longitude ?destination ?destinationLabel ?destination_latitude ?destination_longitude  WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  ?item wdt:P2 wd:Q10671.
  ?item wdt:P95 ?origin.
  ?origin p:P48 ?origin_coordinate.
  ?origin_coordinate psv:P48 ?origin_node.
  ?origin_node wikibase:geoLatitude ?origin_latitude.
  ?origin_node wikibase:geoLongitude ?origin_longitude.
  ?item wdt:P97 wd:Q10677.
  ?item p:P28 ?recipient_statement.
  ?recipient_statement ps:P28 ?recipient.
  ?recipient_statement pq:P29 ?destination.
  ?destination p:P48 ?destination_coordinate.
  ?destination_coordinate psv:P48 ?destination_node.
  ?destination_node wikibase:geoLatitude ?destination_latitude.
  ?destination_node wikibase:geoLongitude ?destination_longitude.
  ?item wdt:P106 ?date.
}"""

resultsFactGrid = get_sparql_dataframe(endpointFactgrid_url, queryFactGrid)
resultsFactGrid['origin_latitude'] = resultsFactGrid[['origin_latitude']].astype(float).values
resultsFactGrid['origin_longitude'] = resultsFactGrid[['origin_longitude']].astype(float).values
resultsFactGrid['destination_latitude'] = resultsFactGrid[['destination_latitude']].astype(float).values
resultsFactGrid['destination_longitude'] = resultsFactGrid[['destination_longitude']].astype(float).values
resultsFactGrid['date'] = pd.to_datetime(resultsFactGrid['date'])
resultsFactGrid.info() 

resultsFactGrid
resultsFactGrid.to_json('Illuminati1.json', default_handler=str) 
resultsFactGrid.to_csv('Illuminati1.csv')
#Show data in Kepler.gl map is nor shown in GITHUB
import geopandas as gpd   
from keplergl import KeplerGl #importing KeplerGl
gdf = gpd.GeoDataFrame(resultsFactGrid, geometry=gpd.points_from_xy(resultsFactGrid.origin_latitude, resultsFactGrid.origin_latitude))

Illuminati1 = KeplerGl(height=600, width=800)
%run Illuminati1_config.py # read config of map 

Illuminati1.add_data(data=gdf, name="The Illuminati Correspondence ") 
Illuminati1 #show the map not seen when file uploaded to GITHUB 

#Save config 
with open('Illuminati1_config.py', 'w') as f:
   f.write('config = {}'.format(Illuminati1.config))
#local_file="Illuminati1.html"
#Illuminati1.save_to_html(file_name=local_file)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1248673505482844, 'fraction_numerical': 0.0316000471642495, 'mean_word_length': 5.287620459599704, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 18, 'https://': 10, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '451497', 'license': 'MIT', 'n_tokens_mistral': 3246, 'n_tokens_neox': 2950, 'n_words': 599, 'path': 'Wikdata -> Panda dataset.ipynb', 'repo_name': 'salgo60/open-data-examples', 'size': '8481'}