zhtoolkit

In my Chinese studies, the Lancaster Corpus of Mandarin Chinese (LCMC) has been a useful source of data—word and character frequencies, collocations, phrase usage, parts of speech, etc. The corpus is freely available for non-commercial and research use. However, the native form of its data is in a set of XML files, which is not an easy format to work with. In addition, the XML data is slow to read data from, because all those XML tags and the entire data structure needs to be parsed. A much better format for the data is an SQL database. Stored in a database, many kinds queries and reports can be executed very efficiently. Depending on the software, these queries and reports can return results very quickly, much faster than in the XML format.

I have made available a Perl script and some other related tools to assist with extracting the LCMC files into a SQLite database. SQLite is a lightweight relational database management system intended for portability and ease of use. Because it functions as a standalone program (not client-server), it is easy to install and use. It’s more ubiquitous than you might think. It’s how the Firefox and Chrome browsers stores its history, cookies, and preferences. But it’s also used, for example, by the Anki program as the storage format for flashcard data, and by the Calibre e-reader program to store information on installed e-books.

Obtaining the Corpus

The home page for the LCMC is at http://www.lancs.ac.uk/fass/projects/corpus/LCMC/. Of the two links on the page for the distribution locations, the Oxford Text Archive link is the only one that allows for direct downloading. Follow the link to the Oxford Text Archive, browse the archives, and search for Lancaster Corpus of Mandarin Chinese in the “Corpora” tab. The LCMC is ID 2474. The download link is directly on the detail page. After reading the Terms and Conditions, enter your email to receive the link to the actual download location. Clicking on the link in the email message will immediately download the file, 2474.zip (6.5MB).

Creating the SQL Database

The scripts can be downloaded as a zip file from here, or as individual files in the archive. You will need Perl installed on your machine, along with a few necessary modules that may need to be downloaded separately. Full information on the required steps can be found in the README file.

If the data conversion was successful, you will now have a 297 MB file called lcmc.db3 in the current directory.

Entity-Relation diagram for the LCMC database

As for client access to the newly created SQLite database, you will need software that can handle Unicode, so that the Chinese characters can be displayed. For Windows, SQLiteSpy is my personal choice. Queries are fast, the interface is intuitive, and it has Unicode and support for regular expressions built in. For Linux, both Sqliteman and SQLite Manager (a Firefox plug-in) are adequate. One common software package for Linux, SQLite Database Browser is not suitable, because the LCMC database is large, and the program works by trying to load it all into memory at once.

Screenshot of SQLiteSpy for Windows

Some Sample Queries

In a relational database, information is organized into separate tables of data and linked by unique keys for each record. This arrangement makes it efficient to perform many kinds of queries. Below are just a few examples of the kind of reports that be done.

Raw frequency counts of all Chinese words in the corpus

SELECT characters, COUNT('x')
  FROM words
 WHERE token_type = 'w'
   AND is_cjk = 'Y'
GROUP BY characters ORDER BY count('x') DESC, characters ASC;

List all words tagged as prepositions

SELECT W.characters, P.id, P.description, COUNT(*) from words W
  JOIN pos P on W.part_of_speech = P.id
 WHERE P.description = 'preposition'
 GROUP BY W.characters, P.id, P.description
 ORDER BY COUNT(*) DESC;

List the average sentence length per text category

SELECT T.id, T.type, ROUND(AVG(LENGTH(F.characters)), 1) as avg_sentence_length
  FROM full_sentences F
  JOIN texts T on F.text_id = T.id 
 GROUP BY T.id, T.type;

List usage of words containing the character 给

SELECT W.characters, P.id, P.description, count(*) from words W
  JOIN pos P on W.part_of_speech = P.id
WHERE W.characters LIKE '%给%'
 GROUP BY W.characters, P.id, P.description
 ORDER BY COUNT(*) DESC;

All words and corresponding pinyin

select W.file_id, W.sentence_id, W.word_num, W.characters, P.characters AS pinyin from words W
left join pinyin_words P USING (file_id, sentence_id, word_num);

Pinyin usage of character 乐

  select W.character, P.character AS pinyin, count(*)
    from characters W
    join pinyin_characters P USING (file_id, sentence_id, word_num, char_num)
    where W.character = '乐'
group by W.character, P.character;

These are just a few examples of what you can do with the corpus data in the SQL database. Have fun!

http://www.zhtoolkit.com/apps/small_utilities/lcmc_xml_to_sqlite.zip (69Kb)