Skip to content

Instantly share code, notes, and snippets.

@kaito834
Created March 21, 2020 12:47
Show Gist options
  • Save kaito834/8ac1b8f166460032c4d2e9e1cad7922d to your computer and use it in GitHub Desktop.
Save kaito834/8ac1b8f166460032c4d2e9e1cad7922d to your computer and use it in GitHub Desktop.
Microsoft Power Query for Excel で Twitter アーカイブ:tweet.js を整形してみる

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

Background

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

  1. Office 365:Excel で空の Book を開く
  2. 「データ」タブ > データの取得 > 「ファイルから」 > 「JSON から」を実行し、tweet.js を開く
  3. Chris Webb 氏のブログ記事(関連リンク参照)をもとに、クエリエディターおよび詳細エディターを編集していく
  • クエリエディターで編集できる部分はクエリエディタで編集
  • クエリエディターでどう編集すればよいか分からない/できそうにない部分を詳細エディターで直接 Power Query を書く
  • Power Query を書く場合、ざっと Google 検索結果から先人の知恵を見つけて、それをもとに編集で今回は何とかなった
  1. 最終的に後述の Power Query を作り、クエリエディタを「閉じて読み込む」
  • クエリエディターで編集すると、変数名が日本語テキストになるため後で適宜編集した
  1. Excel シートに tweet.js から整形した Tweet 履歴が表示される
  2. この Book を Excel ファイルに保存する
  3. 最新の 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 実行時にエラーが発生する

関連リンク

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment