from wmfdata.spark import create_session

spark = create_session(app_name='commons-analyzer', type='yarn-large', ship_python_env=True)
snapshot = '2024-02'
q = f"SELECT page_id, page_title FROM wmf_raw.mediawiki_page WHERE snapshot='{snapshot}' AND wiki_db='commonswiki' AND page_namespace=6 AND page_is_redirect=0"
commons = spark.sql(q)
q = f"SELECT cl_from AS page_id, cl_to AS cat_title FROM wmf_raw.mediawiki_categorylinks WHERE snapshot='{snapshot}' AND wiki_db='commonswiki' AND cl_type='file'"
cats = spark.sql(q)
ddf = commons.join(cats, on=['page_id'])
ddf = ddf.groupBy('cat_title').count().orderBy('count', ascending=False)

{'cat_title': 'Media_contributed_by_the_Digital_Public_Library_of_America', 'count': 3798743} --> misc library stuff like old postcards, handwritings, ...
{'cat_title': 'Media_contributed_by_the_National_Archives_and_Records_Administration', 'count': 1523908}, --> scans of old b/w picture, illustrations, several from wars 
{'cat_title': 'Files_from_Gallica', 'count': 1445458}, --> book scans
{'cat_title': 'Images_from_the_Rijksdienst_voor_het_Cultureel_Erfgoed', 'count': 486294},
{'cat_title': 'Images_from_the_National_Archives_and_Records_Administration', 'count': 468305},
{'cat_title': 'Media_contributed_by_the_North_Carolina_Digital_Heritage_Center', 'count': 461836},
{'cat_title': 'Media_contributed_by_National_Archives_at_Washington,_DC_-_Textual_Reference', 'count': 439863},
{'cat_title': 'Rijksmonumenten_with_known_IDs', 'count': 432815},
{'cat_title': 'Images_from_Nationaal_Archief', 'count': 417834},
{'cat_title': 'Images_from_Metropolitan_Museum_of_Art', 'count': 388831},
{'cat_title': 'Library_of_Congress-no_known_copyright_restrictions',  'count': 388388},
{'cat_title': 'Media_contributed_by_Abilene_Library_Consortium',  'count': 382345},
{'cat_title': 'Media_contributed_by_Columbus_Metropolitan_Library',  'count': 337246},
{'cat_title': 'Images_from_Paris_Musées', 'count': 326270},
{'cat_title': 'Media_contributed_by_National_Archives_at_College_Park_-_Textual_Reference',  'count': 326237},
{'cat_title': 'Maps_in_the_Library_of_Congress', 'count': 323319},
{'cat_title': 'Images_from_the_New_York_Public_Library', 'count': 318317},
{'cat_title': 'Files_from_the_Historic_American_Buildings_Survey',  'count': 309986},
{'cat_title': 'Files_from_the_Biodiversity_Heritage_Library',  'count': 304090},
{'cat_title': 'Media_donated_by_Naturalis_Biodiversity_Center',  'count': 277678},
{'cat_title': 'Media_contributed_by_Indiana_Memory', 'count': 267105},
{'cat_title': 'Scans_from_the_China_Academic_Digital_Associative_Library',  'count': 224845},
{'cat_title': 'Images_from_the_Swedish_National_Heritage_Board',  'count': 185584},

{'cat_title': 'Artworks_with_known_accession_number', 'count': 1916375}, --> scans of illustrations, book pages, drawings, old pictures
{'cat_title': 'Artworks_with_Wikidata_item, 'count': 691731},

{'cat_title': 'Images_from_Geograph_Britain_and_Ireland', 'count': 6192018} --> landscapes, monuments like castles & churches
{'cat_title': 'Photos_from_Panoramio', 'count': 2314923} --> similar to first cat

{'cat_title': 'Images_from_the_Earth_Science_and_Remote_Sensing_Unit,_Lyndon_B._Johnson_Space_Center', 'count': 3271990}, --> satellite pics
{'cat_title': 'ISS_Expedition_53_Crew_Earth_Observations_(dump)', 'count': 421630},
{'cat_title': 'ISS_Expedition_65_Crew_Earth_Observations_(dump)',  'count': 375243},
{'cat_title': 'Earth_at_night_seen_from_space', 'count': 302472},
{'cat_title': 'Daytime_Earth_viewed_from_space', 'count': 300470},
{'cat_title': 'ISS_Expedition_30_Crew_Earth_Observations_(dump)',  'count': 261039},
{'cat_title': 'ISS_Expedition_67_Crew_Earth_Observations_(dump)',  'count': 253049},
{'cat_title': 'ISS_Expedition_42_Crew_Earth_Observations_(dump)',  'count': 212592},

{'cat_title': 'Images_from_Wiki_Loves_Monuments_2012', 'count': 363123},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2013', 'count': 368468},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2014', 'count': 320471},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2015', 'count': 230214},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2016', 'count': 276014},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2017', 'count': 243192},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2018', 'count': 258989},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2019', 'count': 211605},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2020', 'count': 230408},
{'cat_title': 'Images_from_Wiki_Loves_Monuments_2023', 'count': 217110},

{'cat_title': 'Cultural_heritage_monuments_in_Russia_with_known_IDs',  'count': 330567},
{'cat_title': 'Cultural_heritage_monuments_in_France_with_known_IDs',  'count': 258774},
{'cat_title': 'Cultural_heritage_monuments_in_Italy_with_known_IDs',  'count': 243580},
{'cat_title': 'Cultural_heritage_monuments_in_Spain_with_known_IDs',  'count': 198330},
{'cat_title': 'Cultural_heritage_monuments_in_Austria_with_known_IDs',  'count': 186456},

{'cat_title': 'Buildings_with_addresses', 'count': 746835},

{'cat_title': 'Scans_from_the_Internet_Archive', 'count': 1580935}, --> book scans
{'cat_title': 'Images_uploaded_by_Fæ', 'count': 3632767}, --> misc projects, a lot of book scans
{'cat_title': 'Images_with_watermarks', 'count': 603229},
{'cat_title': 'Uploaded_with_Mobile/Android', 'count': 382066},
{'cat_title': 'Flickr_images_reviewed_by_trusted_users', 'count': 352658},
{'cat_title': 'Flickr_images_uploaded_by_Flickr_upload_bot', 'count': 251280},
{'cat_title': 'Media_lacking_author_information', 'count': 216763},
{'cat_title': 'With_trademark', 'count': 192469}