はじめに
データアナリティクス事業本部 BIチームのkariyaです。
BIチームでは、アンケートデータの可視化をテーマにブログリレーを行っています。
当記事では、Power Queryを用いてアンケートデータの加工を行います。
使用するデータ
東京都の丸の内仲通りで、自動運転バス走行実証実験が実施されています。 実証実験の詳細に関してはこちらをご覧ください。
実証実験の結果がオープンデータとして公開されていますので、今回はその中から以下のデータを使用します。
アンケート結果(歩行者)データ例はこちらです。
赤枠内に質問が記載されており、質問ごとに1列を使用しています。
アンケート結果(試乗体験者)のデータ例はこちらです。
歩行者と同様に、質問ごとに1列を使用していますが、質問の数が異なります。
取得したい結果
下記のレイアウトになるように、データを加工していきます。 歩行者と試乗体験者で分かれている2ファイルを合わせた結果になるため、どちらのアンケートに回答した結果かが分かるように回答者IDなどの項目を追加しています。 (★は元々のデータには列としては存在しない項目です)
- 会員No
- 回答ID：回答者が増える度に採番されるID。歩行者、試乗体験者それぞれで採番される
- 回答者フラグ(★)：歩行者 or 試乗体験者のどちらか
- 回答者ID(★)：回答ID＆回答者フラグを組み合わせた、回答者を一意に特定するもの
- 年齢
- 性別
- 職業
- 登録日
- 質問(★)
- 回答(★)
加工後のイメージはこちらです。
レイアウトの異なる歩行者と試乗体験者のアンケートを、同じレイアウトに変換しています。
データ加工(クエリ)の流れ
Power Queryのクエリ作成により、以下の流れでデータ加工をしていきます。
- 歩行者のデータを加工する
- 試乗体験者のデータを加工する
- 歩行者・試乗体験者の加工結果を1つにまとめる
それではクエリを作成してみましょう。
1.歩行者データの加工
1-1.CSVファイル取込
Power BIを開き、ホーム > データを取得 > テキスト/CSVを選択します。
歩行者アンケートの結果が入っているauto_driving_bus_never_20210314.csvを選択して開きます。
データの一部が表示されます。ここで読み込みを選択するとそのままデータを取込みますが、今回は加工をしたいのでデータの変換を選択します。
Power Queryエディターが開きますので、クエリ名を歩行者アンケートに変更しておきます。
1-2.不要列削除
以下の列は可視化で使用しないため、削除します。
- ユーザーエージェント
- 登録サイト
- キャリア
削除する列の列名を選択します。複数列の選択は下記方法で可能です。
- 削除したい先頭列をクリック > Shiftを押しながら削除したい最後の列をクリック
- Ctrlを押しながら列をクリック
列名を右クリック > 列の削除を選択します。
1-3.回答者フラグ作成
試乗体験者のアンケートと1つのテーブルにした時に歩行者のアンケート結果とわかるよう、回答者フラグの列を作成します。
列の追加 > 例からの列を選択します。
列名に回答者フラグを入力します。
1行目のデータに、歩行者と入力します。
2行目のデータにも歩行者と入力すると、全ての行に歩行者が自動入力されます。
式を自分で入力して列を作成することも可能ですが、欲しい結果がわかっているけれど式が分からないという場合は、このように例を使って新たな列を作成することもできます。
クエリのステップ名が挿入されたリテラルと少し分かりにくい名前になっているため、右クリック > 名前の変更を選択して回答者フラグを追加に変更しておきます。
Power Queryで何か操作をする度にクエリにステップが追加されるため、どのステップで何をやっているかが後から見て分かるような名前をつけておくと良いでしょう。
1-4.回答者ID作成
同じように例からの列を使用して、回答者IDという列を作成します。
歩行者_001のように、どのアンケートの何番目の回答者かわかるようにします。
1行目の回答IDが428ですので、1行目に歩行者_428と入力すると、2行目以降が自動入力されます。
回答者フラグでは2行の入力が必要だったように、Power Queryが式を推測できたタイミングで式が作成されるため、欲しい結果が得られるまで複数行にデータを入力する必要があります。
1-5.ピボット解除(行列変換)
アンケートの質問の数だけ列がある(横持ちになっている)ため、質問列と回答列の2列に回答をまとめます。 以下の列は別の列として残しておきます。
- 【⑤年齢】
- 【⑥性別】
- 【⑦職業】
上記以外の質問の列を選択し、変換 > 列のピボット解除を選択します。
属性に質問が、値に回答が入りました。データが縦持ちになっている状態です。
1-6.列名変更
最後に列名を変更します。列名をダブルクリックすると、列名を入力できます。
以下のように列名を変更します。
- 【⑤年齢】：年齢
- 【⑥性別】：性別
- 【⑦職業】：職業
- 属性：質問
- 値：回答
列名の変更後はこのようになります。
2.試乗体験者データの加工
歩行者データと同様に以下の処理を行います。
- CSVファイル
auto_driving_bus_tried_20210314.csvを取込み > データの変換 - クエリ名を試乗体験者アンケートに変更
- 不要列(ユーザーエージェント・登録サイト・キャリア)を削除
- 回答者フラグ(全ての行のデータが試乗体験者)の列を追加
- 回答者IDの列を追加(回答者フラグ_回答ID)
- ピボット解除して質問と回答を縦持ちにする
- 列名を変更
歩行者アンケートとの違いとしては、年齢・性別・職業の後の列にも質問の列が続いています。 ピボット解除の際に、Ctrlを押しながら列を選択すると選択のON/OFFが切り替えられます。
3.歩行者と試乗体験者のクエリを1つのクエリにまとめる
3-1.クエリをまとめる
ホーム > クエリの追加 > クエリを新規クエリとして追加を選択します。
歩行者アンケートと試乗体験者アンケートを指定し、OKをクリックします。
クエリ名は全参加者アンケートとしておきます。
3-2.セミコロン区切りの回答を分割
複数の回答がセミコロン区切りで1つの行に入っている行があります。 複数回答可の質問でこのようになっているようですが、可視化の際には別々の行にしておきたいので、複数行に分割をします。
ホーム > 列の分割 > 区切り記号による分割を選択します。
区切り文字はセミコロンを、分割の方向は行を選択してOKをクリックします。
3-3.ブランク回答の非表示(自由回答)
自由回答の質問などでは回答がブランクの行がありました。
今回は可視化の対象外としたいので、列名の右側のドロップダウンリストを開き、空白のチェックボックスを外すことでフィルターをかけておきます。
3-4.ブランク回答の置換(年齢・性別・職業)
年齢・性別・職業についても回答がブランクの行があります。
可視化では未回答と表示したいので、値の変換をします。
年齢・性別・職業の列を選択し、変換 > 値の置換をクリックします。
検索する値は未入力のままで、置換後は未回答に設定してOKをクリックします。
3-5.データ型の変換
会員No・回答ID・回答者フラグのデータ型が、数値(123のアイコン)や未指定(ABC123のアイコン)になっています。 例えばIDが数値型になっていると、可視化の際にIDの合計値などの不要な計算がされる場合があります。
会員No・回答ID・回答者フラグについては、列名の左側のアイコンをクリックしてテキストを選択します。
3-6.列の並び替え
以下の順に列の順番を並び替えます。
- 会員No
- 回答ID
- 回答者フラグ
- 回答者ID
- 年齢
- 性別
- 職業
- 登録日
- 質問
- 回答
列をドラッグ＆ドロップすることで列を並び替えることができます。
4.クエリ結果を適用
最後にホーム > 閉じて適用をクリックしてクエリを実行します。
これでPower Queryエディターが閉じられ、クエリの結果がPower BIに返されます。
Power BIのデータビューにより、加工後のデータが確認できました。
おまけ：既存のクエリを貼り付けて作成する場合
ここまで手作業でクエリを作成しましたが、Power Queryのクエリはテキストで表現されているため、クエリ内容を貼り付けると同じ内容が再現できます。
1.歩行者データの加工
Power BIを開き、ホーム > データを取得 > 空のクエリを選択してPower Queryエディターを開きます。
クエリを右クリックして詳細エディターを開きます。
詳細エディターに以下のクエリを貼り付け、完了をクリックします。
C:\アンケートデータにcsvファイルを格納している前提のクエリのため、格納先は適宜読み替えて下さい。
let
ソース = Csv.Document(File.Contents("C:\アンケートデータ\auto_driving_bus_never_20210314.csv"),[Delimiter=",", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"会員No", Int64.Type}, {"回答ID", Int64.Type}, {"【①エリアとの関りについて】", type text}, {"【②ご利用頻度について】 2-a.丸の内仲通りアーバンテラス（歩行者専用時間帯のテラス席）のご利用頻度を教えてください。 ※テラス席に座る、キッチンカーで購入する等", type text}, {"2-b.通常の丸の内アーバンテラスに比べ、低速の自動運転モビリティが走行することは、ご利用の妨げになるでしょうか？", type text}, {"【③歩車共存の道路空間形成に向けて】 3-a.走行シーンをご覧になって、低速度のモビリティと歩行者が同じ道路空間上で安全に共存することは可能だと思いますか？", type text}, {"3-b.モビリティと歩行者の共存に向けて、モビリティへの親近性を高める工夫として、どのようなものが効果的だとお考えですか？（複数選択可）", type text}, {"3-c.歩行者と共存可能なモビリティの速度はどの程度だと思いますか？", type text}, {"【④エリア内の移動について】 4-a.大丸有エリア内の移動において、自動運転バスがあれば移動手段として利用されますか？", type text}, {"4-b.（4-aにて「はい」と回答された方のみ）具体的な利用シーンはどのような場面を想定されますか？（複数選択）", type text}, {"4-c.（4-aにて「はい」と回答されなかった方のみ）自動運転バスを利用しない理由をお聞かせください。", type text}, {"【⑤年齢】", type text}, {"【⑥性別】", type text}, {"【⑦職業】", type text}, {"登録日", type datetime}, {"ユーザーエージェント", type text}, {"登録サイト", type text}, {"キャリア", type text}}),
削除された列 = Table.RemoveColumns(変更された型,{"ユーザーエージェント", "登録サイト", "キャリア"}),
回答者フラグを追加 = Table.AddColumn(削除された列, "回答者フラグ", each "歩行者", type text),
回答者IDを追加 = Table.AddColumn(回答者フラグを追加, "回答者ID", each Text.Combine({Text.From([回答者フラグ]
, "ja-JP"),"_", Text.From([回答ID], "ja-JP")}), type text),
ピボット解除された列 = Table.UnpivotOtherColumns(回答者IDを追加, {"会員No", "回答ID", "【⑤年齢】", "【⑥性別】", "【⑦職業】", "登録日", "回答者フラグ","回答者ID"}, "属性", "値"),
#"名前が変更された列 " = Table.RenameColumns(ピボット解除された列,{{"属性", "質問"}, {"値", "回答"}, {"【⑤年齢】", "年齢"}, {"【⑥性別】", "性別"}, {"【⑦職業】", "職業"}}),
並べ替えられた列 = Table.ReorderColumns(#"名前が変更された列 ",{"会員No", "回答ID", "回答者フラグ", "回答者ID", "年齢", "性別", "職業", "登録日", "質問", "回答"})
in
並べ替えられた列
これで歩行者アンケートの取込クエリができました。
詳細エディターに貼り付けることで、別の環境のPower Queryで同じクエリを再現することができます。
貼り付ける内容にクエリ名は入っていないため、手動で歩行者アンケートにしておきます。
2.試乗体験者データの加工
同様に、試乗体験者アンケート用のクエリを作成します。
Power Queryエディターの何もない場所を右クリック > 新しいクエリ > 空のクエリをクリックします。
歩行者アンケートと同様に、詳細エディターを開き以下を貼り付けます。
let
ソース = Csv.Document(File.Contents("C:\アンケートデータ\auto_driving_bus_tried_20210314.csv"),[Delimiter=",", Columns=30, Encoding=65001, QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"会員No", Int64.Type}, {"回答ID", Int64.Type}, {"【①エリアとの関りについて】", type text}, {"【②エリア内の移動について】 2-a.大丸有エリア内の移動において、自動運転バスがあれば移動手段として利用されますか？", type text}, {"2-b.（2-aにて「はい」と回答された方のみ）具体的な利用シーンはどのような場面を想定されますか？（複数選択）", type text}, {"2-c.（2-aにて「はい」と回答されなかった方のみ）自動運転バスを利用しない理由をお聞かせください。", type text}, {"【③歩車共存の道路空間形成に向けて】 3-a.実証を体験し、低速度のモビリティと歩行者が同じ道路空間上で安全に共存することは可能だと思いましたか？", type text}, {"3-b. 「思わなかった」と回答の方へ、その理由を教えてください。", type text}, {"【④走行環境について】バス停間の距離は適切でしたか？", type text}, {"【⑤アプリについて】 5-a. 今回の実証実験アプリでは、周辺のバスを含め、走行位置情報が地図上で分かる機能を搭載しました。今後、スマートシティアプリとして、より多くのモビリティ情報やサービス情報と連携していくべきだと思いますか？", type text}, {"5-b. (5-a.にて「はい」と回答された方） 今後、アプリ上で連携されると便利な機能について教えてください。（複数選択）", type text}, {"【⑥乗車方法について】乗車前の工程として、どの程度であれば手間に感じないでしょうか？", type text}, {"【⑦価格について】乗車料金が発生する場合、適切な価格設定はいくらでしょうか？", type text}, {"【⑧年齢】", type text}, {"【⑨性別】", type text}, {"【⑩職業】", type text}, {"【⑪自動運転バスの走り方】 11-a.自動運転バスの走り方について、試乗前はどのような印象を持っていましたか？", type text}, {"11-b.自動運転バスの走り方について、実際に試乗してみて、印象はどのように変わりましたか？", type text}, {"【⑫自動運転バスでの止まり方】 12-a.自動運転バスでの止まり方について、試乗前はどのような印象を持っていましたか？", type text}, {"12-b.自動運転バスでの止まり方について、実際に試乗してみて、印象はどのように変わりましたか？", type text}, {"【⑬自動運転バスの良かった点】 13-a.自動運転バスの良かった点は？（複数回答可）", type text}, {"13-b.自動運転バスの良かった点は？（自由回答）", type text}, {"【⑭自動運転バスについて】 14-a.今回の自動運転バスを、実際に生活で使ってみたいと思いましたか？", type text}, {"14-b.「まだ不安が残る」「使いたくない」と答えた理由は？", type text}, {"14-c.「まだ不安が残る」「使いたくない」と答えた理由は？（自由回答）", type text}, {"⑮実証実験の満足度", type text}, {"登録日", type datetime}, {"ユーザーエージェント", type text}, {"登録サイト", type text}, {"キャリア", type text}}),
削除された列 = Table.RemoveColumns(変更された型,{"ユーザーエージェント", "登録サイト", "キャリア"}),
回答者フラグを追加 = Table.AddColumn(削除された列, "回答者フラグ", each "試乗体験者", type text),
回答者IDを追加 = Table.AddColumn(回答者フラグを追加, "回答者ID", each Text.Combine({Text.From([回答者フラグ], "ja-JP"),"_", Text.From([回答ID], "ja-JP")}), type text),
ピボット解除された列 = Table.UnpivotOtherColumns(回答者IDを追加, {"会員No", "回答ID", "【⑧年齢】", "【⑨性別】", "【⑩職業】", "登録日", "回答者フラグ","回答者ID"}, "属性", "値"),
#"名前が変更された列 " = Table.RenameColumns(ピボット解除された列,{{"属性", "質問"}, {"値", "回答"}, {"【⑧年齢】", "年齢"}, {"【⑨性別】", "性別"}, {"【⑩職業】", "職業"}}),
並べ替えられた列 = Table.ReorderColumns(#"名前が変更された列 ",{"会員No", "回答ID", "回答者フラグ", "回答者ID", "年齢", "性別", "職業", "登録日", "質問", "回答"})
in
並べ替えられた列こちらはクエリの名前を試乗体験者アンケートとしておきます。
3.1つのクエリにまとめる
最後に、歩行者・試乗体験者のアンケートを合わせて1つのテーブルにするクエリを作成します。 空のクエリを作成し、詳細エディターに以下を貼り付けます。
let
ソース = Table.Combine({歩行者アンケート, 試乗体験者アンケート}),
区切り記号による列の分割 = Table.ExpandListColumn(Table.TransformColumns(ソース, {{"回答", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "回答"),
未回答を除外 = Table.SelectRows(区切り記号による列の分割, each ([回答] <> "")),
属性ブランクを未回答へ = Table.ReplaceValue(未回答を除外,"","未回答",Replacer.ReplaceValue,{"年齢", "性別", "職業"}),
変更された型 = Table.TransformColumnTypes(属性ブランクを未回答へ,{{"回答ID", type text}, {"会員No", type text}}),
並べ替えられた列 = Table.ReorderColumns(変更された型,{"会員No", "回答ID", "回答者フラグ", "回答者ID", "年齢", "性別", "職業", "登録日", "質問", "回答"})
in
並べ替えられた列クエリの名前は全参加者アンケートとしておきます。
4.クエリ結果を適用
ホーム > 閉じて適用をクリックします。
データビューにて、手動で作成した時と同じようにクエリ結果を確認できました。
おわりに
今回はPower BIからPower Queryを実行していますが、Power QueryはExcelからも実行可能です。可視化はしないけれどデータの加工をしたいという場合にはExcelから実行すると便利な場合もあるかと思います。
最後までお読み頂きありがとうございました。
この記事がデータ加工をする際のヒントに少しでもなれば嬉しいです。
3
