Actualização das páginas órfãs

A pedido do Lijealso, aqui vai uma actualização das estatísticas incompletas da Wikipédia lusófona para o caso das páginas órfãs.

Constatou-se que o dump utilizado anteriormente era insuficiente, pelo que se descarregou a tabela pagelinks, desta vez do dump de 20091015. Para se excluir os redireccionamentos, importou-se também a tabela redirect.

Fartei-me entretanto de alternar entre o que estava a fazer e a lista de códigos de domínios, pelo que criei uma pequena tabela auxiliar:

[mysql]
mysql> create table _namespaces ( id tinyint not null, namespace varchar(50), primary key (id) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into _namespaces values (-2,'Media'),(-1,'Especial'),(0,''),(1,'Discussão'),(2,'Usuário'),(3,'Usuário Discussão'),(4,'Wikipedia'),(5,'Wikipedia Discussão'),(6,'Ficheiro'),(7,'Ficheiro Discussão'),(8,'MediaWiki'),(9,'MediaWiki Discussão'),(10,'Predefinição'),(11,'Predefinição Discussão'),(12,'Ajuda'),(13,'Ajuda Discussão'),(14,'Categoria'),(15,'Categoria Discussão'),(100,'Portal'),(101,'Portal Discussão'),(102,'Anexo'),(103,'Anexo Discussão');
Query OK, 22 rows affected (0.00 sec)
Records: 22 Duplicates: 0 Warnings: 0
[/mysql]

O resultado deu-me um incrível total de 769854 páginas órfãs, pelo que decidi separá-las por namespace para permitir prioritizar a análise:

[mysql]
mysql> select p.page_namespace,count(1) from page p
-> LEFT JOIN redirect r
-> ON p.page_id = r.rd_from
->
-> LEFT JOIN pagelinks pl
-> on pl.pl_namespace = p.page_namespace
-> and pl.pl_title = p.page_title
->
-> WHERE r.rd_from IS NULL
-> AND pl.pl_from IS NULL
->
-> group by p.page_namespace;
+----------------+----------+
| page_namespace | count(1) |
+----------------+----------+
| 0 | 12958 |
| 1 | 103645 |
| 2 | 16592 |
| 3 | 568675 |
| 4 | 1954 |
| 5 | 856 |
| 8 | 773 |
| 9 | 17 |
| 10 | 7522 |
| 11 | 1014 |
| 12 | 3 |
| 13 | 27 |
| 14 | 51735 |
| 15 | 1315 |
| 100 | 1190 |
| 101 | 117 |
| 102 | 173 |
| 103 | 1288 |
+----------------+----------+
18 rows in set (20.90 sec)
[/mysql]

O resultado do cruzamento das duas tabelas foi afixado aqui, com uma listagem de 15M para os 12958 artigos no domínio principal. Na verdade, esta listagem foi feita para colar numa página wiki, no entanto tenham em atenção que são 15M, pelo que não recomendo fazê-lo. Têm outras listas (como a mais simples, em formato pageid,namespace,title) nessa directoria.

Revisita aos dumps da Wikipédia

Desta vez em português, decidi dar [alguma] continuidade ao que comecei há uns dias com a importação dos dumps da Wikipédia. Graças à dica do Rei-artur foi fácil extrair a lista de robôs, para excluir das estatísticas.

[code]
[myself@speedy ~]# wget 'http://pt.wikipedia.org/w/api.php?action=query&list=allusers&aufrom=A&augroup=bot&aulimit=500&format=txt' -q -O - > bots.tmp

[myself@speedy ~]# cat bots.tmp | grep '\[name\]' | sed 's,^.*\[name\] => ,,' > /tmp/bots.txt
[/code]
Aproveitei e repesquei os user_id para simplificar as pesquisas sem fazer alterações na tabela user.
[mysql]
mysql> create table user_bots ( bot_name varchar(25) );
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/tmp/bots.txt' into table user_bots;
Query OK, 136 rows affected (0.00 sec)
Records: 136 Deleted: 0 Skipped: 0 Warnings: 0


mysql> alter table user_bots add column bot_user_id int;
Query OK, 136 rows affected (0.01 sec)
Records: 136 Duplicates: 0 Warnings: 0

mysql> alter table user add index idx_t ( user_name );
Query OK, 119134 rows affected (2.63 sec)
Records: 119134 Duplicates: 0 Warnings: 0

mysql> update user_bots ub join user u on user_name = bot_name set ub.bot_user_id = u.user_id;
Query OK, 134 rows affected (0.00 sec)
Rows matched: 134 Changed: 134 Warnings: 0

mysql> alter table user_bots add primary key (bot_user_id);
Query OK, 136 rows affected, 1 warning (0.00 sec)
Records: 136 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1265 | Data truncated for column 'bot_user_id' at row 71 |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> update user_bots set bot_user_id = -1 where bot_user_id = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[/mysql]

Não tinha reparado que havia um utilizador/robô com o nome "MediaWiki default" mas, bem, depois de criar a Primary Key ficou com o bot_user_id=0 e, para evitar que coincidisse com o agregado para anonymous, dei-lhe o bot_user_id=-1.

Então agora já estamos prontos a completar a query onde ficámos no último dia (número de edições em artigos distintos em cada namespace por utilizador):
[mysql]
mysql> explain SELECT epn.user_name,epn.page_namespace,epn.edits
-> FROM edits_per_namespace epn
-> LEFT JOIN user_bots ub ON epn.user_id = ub.bot_user_id
-> WHERE ub.bot_user_id IS NULL
-> AND epn.user_id <> 0
-> ORDER BY edits desc limit 20;
+----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
| 1 | SIMPLE | epn | ALL | NULL | NULL | NULL | NULL | 187624 | Using where; Using filesort |
| 1 | SIMPLE | ub | eq_ref | PRIMARY | PRIMARY | 4 | ntavares.epn.user_id | 1 | Using where; Using index; Not exists |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+--------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT epn.user_name,epn.page_namespace,epn.edits
-> FROM edits_per_namespace epn
-> LEFT JOIN user_bots ub ON epn.user_id = ub.bot_user_id
-> WHERE ub.bot_user_id IS NULL
-> AND epn.user_id <> 0
-> ORDER BY edits desc limit 10;
+----------------+----------------+-------+
| user_name | page_namespace | edits |
+----------------+----------------+-------+
| EMP,Nice poa | 0 | 58138 |
| Dantadd | 0 | 44767 |
| João Carvalho | 3 | 44533 |
| OS2Warp | 0 | 43396 |
| Yanguas,Sonlui | 0 | 37020 |
| Lijealso | 0 | 34157 |
| Rei-artur | 0 | 33863 |
| Tumnus | 3 | 33213 |
| Nuno Tavares | 0 | 31910 |
| Bisbis | 0 | 29886 |
+----------------+----------------+-------+
10 rows in set (0.76 sec)
[/mysql]
Os resultados completos estão aqui.

Já agora, para finalizar, a tão afamada lista de wikipedistas por número de edições:

[mysql]
mysql> create table edits_per_user select rev_user,count(1) as counter from revision group by rev_user;
Query OK, 119134 rows affected (12.61 sec)
Records: 119134 Duplicates: 0 Warnings: 0

mysql> select u.user_name,epu.counter
-> from edits_per_user epu
-> left join user_bots ub on ub.bot_user_id = epu.rev_user
-> join user u on epu.rev_user = u.user_id
-> where ub.bot_user_id IS NULL order by counter desc limit 10;
+----------------+---------+
| user_name | counter |
+----------------+---------+
| anonymous | 3119758 |
| EMP,Nice poa | 176338 |
| OS2Warp | 163751 |
| Dantadd | 105657 |
| Lijealso | 90025 |
| Yanguas,Sonlui | 89152 |
| Rei-artur | 83662 |
| Mschlindwein | 75680 |
| Bisbis | 75361 |
| Nuno Tavares | 73141 |
+----------------+---------+
10 rows in set (0.05 sec)
[/mysql]
Os resultados completos estão aqui.

Revisita aos dados estruturados

Há alguns dias num mergulho profundo sobre a utilização de wikis em campos específicos deparei-me com uma "foto" da Wikipédia muito interessante, aqui, que ilustra, entre outras coisas, a actividade na Wikipédia, a vários níveis: Visualizing Science & Tech Activity in Wikipedia:


Fonte: A Beatiful WWW

O website, A Beatiful WWW, dedica-se à extracção e representação dos volumes de informação distintos que conhecemos hoje. Eu já tinha falado nisto e descobri, entretanto, que o Google disponibiliza uma API de representação de dados estruturados.

Consigo pensar numa série de brincadeiras para isto :) Imaginem, por exemplo, juntar isto tudo, logo agora que a Wikimedia vai estar empenhada em manter os conteúdos disponíveis no Amazon Public Data Sets!..

Olhem aqui um exemplo do que pode ser feito, desta vez com Hadoop e Hive: Hot Wikipedia Topics, Served Fresh Daily.