Microsoft Power Query for Excel で Twitter アーカイブ:tweet.js を整形してみる

ms-powerquery_twitter-tweet.js.md

Microsoft Power Query を使って、Twitter アーカイブで提供される tweet.js を表形式に整形してみました。 初めて Microsoft Power Query を使ったので、備忘録のために手順などを Gist にまとめておきます。

Background

• あるときから最近まで Twitter からダウンロードしたアーカイブデータが JSON 形式だけで提供されていました
  • https://oku.edu.mie-u.ac.jp/~okumura/python/tweetdata.html (Last modified: 2019-11-28 14:50:08)
  • https://blog.kotet.jp/2019/10/tweet-js-viewer/ (Created: 2019-10-12)
  • https://twitter.com/TwitterJP/status/1237883248899788804 (Posted on 2020-03-12)
• 「以前提供されていた CSV のように Excel で見たい」という相談を Microsoft Power Query で解決してみました
  • Twitter が公式にアーカイブデータ向けの HTML ファイルを提供したため、今回の Power Query は必要ないかもしれません

Microsoft Office 365:Excel における tweet.js 整形手順

1. Office 365:Excel で空の Book を開く
2. 「データ」タブ > データの取得 > 「ファイルから」 > 「JSON から」を実行し、tweet.js を開く
3. Chris Webb 氏のブログ記事(関連リンク参照)をもとに、クエリエディターおよび詳細エディターを編集していく

• クエリエディターで編集できる部分はクエリエディタで編集
• クエリエディターでどう編集すればよいか分からない/できそうにない部分を詳細エディターで直接 Power Query を書く
• Power Query を書く場合、ざっと Google 検索結果から先人の知恵を見つけて、それをもとに編集で今回は何とかなった

4. 最終的に後述の Power Query を作り、クエリエディタを「閉じて読み込む」

• クエリエディターで編集すると、変数名が日本語テキストになるため後で適宜編集した

5. Excel シートに tweet.js から整形した Tweet 履歴が表示される
6. この Book を Excel ファイルに保存する
7. 最新の tweet.js を取得したら、6. の Excel ファイルを開き tweet.js を読み込むことで、最新の Tweet 履歴を表示できる

Power Query

let
    TweetJsLines = Lines.FromBinary(File.Contents("<file path for tweet.js>")),
    // tweet.js を JSON 形式で読み込むため、冒頭の "window.YTD.tweet.part0 = " を削除
    RemoveVariable = List.ReplaceValue(TweetJsLines,"window.YTD.tweet.part0 = [ {","[ {",Replacer.ReplaceText),
    TweetJsJson = Json.Document(Lines.ToBinary(RemoveVariable)),
    ConvertToTable = Table.FromList(TweetJsJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandColumn1 = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"tweet"}, {"Column1.tweet"}),
    ExpandColumn1Tweet = Table.ExpandRecordColumn(ExpandColumn1, "Column1.tweet", {"id", "id_str", "created_at","full_text", "retweeted", "source", "entities", "display_text_range", "favorite_count", "truncated", "retweet_count", "possibly_sensitive", "favorited", "lang", "in_reply_to_status_id_str", "in_reply_to_user_id", "in_reply_to_status_id", "in_reply_to_screen_name", "in_reply_to_user_id_str"}, {"Column1.tweet.id", "Column1.tweet.id_str", "Column1.tweet.created_at", "Column1.tweet.full_text", "Column1.tweet.retweeted", "Column1.tweet.source", "Column1.tweet.entities", "Column1.tweet.display_text_range", "Column1.tweet.favorite_count", "Column1.tweet.truncated", "Column1.tweet.retweet_count", "Column1.tweet.possibly_sensitive", "Column1.tweet.favorited", "Column1.tweet.lang", "Column1.tweet.in_reply_to_status_id_str", "Column1.tweet.in_reply_to_user_id", "Column1.tweet.in_reply_to_status_id", "Column1.tweet.in_reply_to_screen_name", "Column1.tweet.in_reply_to_user_id_str"}),
    // CreateAt 列をテキスト型から DateTimeZone 型に変換するため、空白文字を区切り文字としてテキストを列に分割する
    SplitCreateAtColumn = Table.SplitColumn(ExpandColumn1Tweet, "Column1.tweet.created_at", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.tweet.created_at.1", "Column1.tweet.created_at.2", "Column1.tweet.created_at.3", "Column1.tweet.created_at.4", "Column1.tweet.created_at.5", "Column1.tweet.created_at.6"}),
    // スペースを区切り文字として、DateTimeZone 型に変換できる日時・タイムゾーンの並びで分割した列を連結する
    CreateNewCreateAtColumn = Table.CombineColumns(SplitCreateAtColumn,{"Column1.tweet.created_at.1", "Column1.tweet.created_at.2", "Column1.tweet.created_at.3", "Column1.tweet.created_at.6", "Column1.tweet.created_at.4", "Column1.tweet.created_at.5"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Column1.tweet.created_at"),
    // CreatedAt 列をテキスト型から DateTimeZone 型に変換する
    ChangeTypedatetimezone = Table.TransformColumnTypes(CreateNewCreateAtColumn,{{"Column1.tweet.created_at", type datetimezone}})
in
    ChangeTypedatetimezone

初めて Power Query を使うときに覚えておくとよいこと

• まず Power Query を使う前に「Power Query M 数式言語のクイック ツアー」を読むとよさそう
  • 僕はこの Gist をまとめるときに、そのクリックツアーを読んでしまって後悔しました...
• Power Query では一行一ステップで処理を実行する
  • クエリエディターで何か編集すると、その編集に該当する処理がステップとして追加される
  • 例えば、「ある行の Record をドリルダウンすること」も一ステップ
• 詳細エディターで Power Query の一ステップを追加し、その後もステップを追加したい場合、追加したステップの末尾に「,」を付与する
  • Power Query では末尾の「,」がステップの連結文字になっている
  • 「,」を付与せずに詳細エディターを閉じると、Power Query 実行時にエラーが発生する

関連リンク

• Chris Webb's BI Blog： Loading Twitter Archive Data In Power Query Chris Webb's BI Blog
• Power Query M 数式言語のリファレンス | Microsoft Docs
  • このページから「Power Query M 数式言語のクイック ツアー」がリンクしています
• Tweet object — Twitter Developers