データベースやCSVファイルなど、一覧形式のデータを保持するのによく使われるDataTableですが、Pivot（縦横変換）しようとした場合、Excelの様な便利な機能が用意されていません。
データ集計やデータ分析では縦持ちのデータを横持に並べ替えることが多いので、メソッドを作ってみました。
もしDataTableをPivotしたくなったら、是非この記事を参考にして下さい。
目次
Pivotの変換イメージ
下記はサンプルプログラムを使った元データとPivotの比較画面です。
ランダムなテストデータを生成したので、売上年月日で並んでいませんが、逆に上から順番に縦横変換されていることが分かるかと思います。
今回は Pivot という名前でメソッドを作成しています。
まずはPivotの引数が縦横変換する上でどのような役割があるのかを確認しておきます。
| dataTable | 変換元のDataTable |
| ヘッダカラム | Pivotの左に転記したいカラム名をカンマ区切りで記述 例："住所,氏名,年齢" ヘッダカラムが必要ない場合は "" を指定する。 ヘッダカラムが複数あり、任意の位置にY軸カラムを入れたい場合は、その位置にY軸カラムを 記述する。 |
| Y軸カラム | Y軸に展開したいカラムを１つだけ記述 |
| X軸カラム | X軸に展開したいカラムを１つ記述。 カンマ区切りで複数指定すると、それらがアンダースコアで結合されるた結果がカラム名となる。 例： "担当者,地域" と記述した場合、担当者に”山田"、地域に”大阪”が格納されていると、 "山田_大阪" でカラム名が作られる |
| 値カラム | Y軸とX軸でPivotする際に展開したい値が登録されているカラムを１つだけ記述 |
| 戻り値 | PivotされたDataTable |
処理手順
処理手順は次の通りです。今回はPivotという名前でメソッドを作成しました。
基本的な考え方は、元のDataTableからDataRowを取得するループを作り、空のDataTableに対してY軸カラムとX軸カラムを随時追加しながら、Y軸とX軸で特定されるセルに値カラムの内容を書き込んでいくというものです。
- 結果を格納するために空のDataTable（resultDataTable)を作成
- ヘッダで指定されたカラムをresultDataTableに登録
- Y軸の値と対応する行を格納するための空の辞書(dict)を作成
- 元のDataTableから１行づつ読み込んで dr 変数に格納
- dr からX軸カラムの値を取り出し、resultDataTableのカラムとして存在しなければ
その値をカラム名として追加 - dr からY軸カラムの値を取得し、dict に登録されているか確認。
登録されていればdict をキー（Y軸カラムの値）にしてバリュー（row)を取得
無ければ新しいDataRowを作成し、row 変数に格納 - ヘッダで指定されたカラムの値をdr から rowにコピーする
- drから取得したY軸カラムの値をキー、上記で作成したrowをバリューとしてdictに登録
- row を resultDataTableに登録
- row[Y軸カラム」 に 値カラムの内容を代入
- 上記④以降を繰り返す
ループの中でY軸とＸ軸で特定されるセルに値を書き込むということは、Y軸とX軸の組み合わせが複数ある場合、同じ場所に何度も異なる値が上書きされることになります。
本来は合計するとか平均値を取るなどの処置が必要ですが、どのような処置が必要かはケースバイケースになるため、今回紹介したソースコードは単純に上書きするだけにしています。
後ほど紹介する1か所に変更を加えるだけなので、皆さんの利用方法に合わせて修正して下さい。
ソースコード
以下にPivotメソッドのソースコードを紹介しておきます。
Ｙ軸とＸ軸の組み合わせが複数存在する場合、メソッドの最後にある return 分の少し上に 下記の記述があります。
// ピボットテーブル内のyとxの交差点に値を設定します
row[x] = val;
ここでＹ軸とＸ軸で特定されたセルに値を書き込んでいますので、何らかの集計がしたい場合は、ここを修正して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
/// <summary> /// 指定されたパラメータに基づいてDataTableをピボットします。 /// </summary> /// <param name="dataTable">ピボットする入力のDataTable。</param> /// <param name="HeaderColumns">ヘッダーとして使用する列のカンマ区切りのリスト。</param> /// <param name="yColumnName">ピボットテーブルのy軸を表す列の名前。</param> /// <param name="xColumnNames">ピボットテーブルのx軸として使用する列のカンマ区切りのリスト。</param> /// <param name="valueColumnName">ピボットテーブルを埋める値が含まれる列の名前。</param> /// <returns>ピボットされたDataTable。</returns> public DataTable Pivot(DataTable dataTable, string HeaderColumns, string yColumnName, string xColumnNames, string valueColumnName) { // ピボットされたデータを保持する新しいDataTableを作成します DataTable resultTable = new DataTable(); // HeaderColumnsで指定された列を新しいDataTableに追加します var headers = HeaderColumns.Split(',').ToList(); // もしyColumnNameが既にヘッダーに含まれていない場合は、末尾に追加します if (! headers.Contains(yColumnName)) { headers.Add(yColumnName); } // 元のDataTableの仕様でヘッダに含まれるカラムを追加 foreach(var header in headers) { resultTable.Columns.Add(header,dataTable.Columns[header].DataType); } // ヘッダーとxColumnNamesで指定された列のインデックスを取得します var headers_idx = headers.Select(i => dataTable.Columns.IndexOf(i)).ToArray(); var x_column_idx = xColumnNames.Split(',').Select(i => dataTable.Columns.IndexOf(i)).ToArray(); // yColumnNameをキーとしてDataRowオブジェクトを格納する辞書を作成します Dictionary<object, DataRow> dict = new Dictionary<object, DataRow>(); // 入力のDataTableの行を反復処理します foreach (DataRow dr in dataTable.Rows) { // yColumnName、xColumnNames、およびvalueColumnNameの値を取得します var y = dr[yColumnName]; var x = string.Join("_",x_column_idx.Select(i => dr[i].ToString()).ToArray()); var val = dr[valueColumnName].ToString(); // 新しい列をresultTableに追加します（存在しない場合） if (!resultTable.Columns.Contains(x)) { resultTable.Columns.Add(x); } // 現在のy値のためのDataRowを取得または作成します if (!dict.TryGetValue(y, out DataRow? row)) { row = resultTable.NewRow(); row.ItemArray = headers_idx.Select(i => dr[i]).ToArray(); resultTable.Rows.Add(row); dict[y] = row; } // ピボットテーブル内のyとxの交差点に値を設定します row[x] = val; } return resultTable; } |
テストプログラムのソースコード
動作確認用のテストプログラムのソースコードを掲載しておきます。 WPF で作成しており、.NET Core 6.0を使用しました。
以下がXAMLのコードです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<Window x:Class="DataTableToPivot.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:local="clr-namespace:DataTableToPivot" mc:Ignorable="d" Title="MainWindow" Height="450" Width="800"> <Grid> <Grid.RowDefinitions> <RowDefinition Height="40"/> <RowDefinition Height="395*"/> </Grid.RowDefinitions> <Grid.ColumnDefinitions> <ColumnDefinition Width="*" /> <ColumnDefinition Width="*" /> </Grid.ColumnDefinitions> <!-- 元のテーブルを表示するDataGrid --> <DataGrid x:Name="originalDataGrid" Grid.Column="0" AutoGenerateColumns="True" Grid.Row="1" /> <!-- Pivotの結果を表示するDataGrid --> <DataGrid x:Name="pivotDataGrid" Grid.Column="1" AutoGenerateColumns="True" Grid.Row="1" /> <!-- データ生成ボタン --> <Button Content="Generate Data" Click="GenerateDataButton_Click" HorizontalAlignment="Center" Margin="0,10,0,10" Grid.Row="0" /> <!-- ピボット実行ボタン --> <Button Content="Pivot Data" Click="PivotButton_Click" HorizontalAlignment="Center" Margin="0,10,0,10" Grid.Column="1" Grid.Row="0" /> </Grid> </Window> |
以下がC#のコードです。Pivotのソースコードは含まれていませんので、動作させたい場合は任意の位置にPivotのソースコードを追加して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
using System.Data; using System.Linq; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; namespace DataTableToPivot { /// <summary> /// Interaction logic for MainWindow.xaml /// </summary> public partial class MainWindow : Window { private DataTable _dataTable = null; public MainWindow() { InitializeComponent(); } // データ生成ボタンがクリックされたときの処理 private void GenerateDataButton_Click(object sender, RoutedEventArgs e) { // DataTableにテストデータをロード _dataTable = GenerateTestData(); // DataGridにテストデータを表示 originalDataGrid.ItemsSource = _dataTable.DefaultView; } private void PivotButton_Click(object sender, RoutedEventArgs e) { // Pivotメソッドを呼び出してデータをピボット DataTable pivotedTable = Pivot(_dataTable, "売上年月日,月", "売上年月日", "担当者,地域", "売上"); // 前回表示していた内容を一旦消去 pivotDataGrid.ItemsSource = null; // DataGridにピボットされた結果を表示 pivotDataGrid.ItemsSource = pivotedTable.DefaultView; } // テストデータを生成するメソッド private DataTable GenerateTestData() { DataTable dataTable = new DataTable(); dataTable.Columns.Add("売上年月日", typeof(DateTime)); // 年を売上年月日に変更 dataTable.Columns.Add("月", typeof(int)); // 月の代わりに月を追加 dataTable.Columns.Add("担当者", typeof(string)); // 月の代わりに担当者を追加 dataTable.Columns.Add("地域", typeof(string)); dataTable.Columns.Add("商品", typeof(string)); dataTable.Columns.Add("売上", typeof(double)); dataTable.Columns.Add("在庫", typeof(int)); dataTable.Columns.Add("コスト", typeof(double)); // テストデータを追加 Random rnd = new Random(); for (int year = 2020; year <= 2022; year++) { for (int month = 1; month <= 12; month++) { for (int region = 1; region <= 3; region++) { for (int product = 1; product <= 2; product++) { double sales = rnd.Next(1000, 10000); int stock = rnd.Next(10, 100); double cost = rnd.NextDouble() * 100; DateTime salesDate = new DateTime(year, month, rnd.Next(1, 28)); // ランダムな日付を生成 string monthName = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month); // 月の英語表記を取得 string salesPerson = $"担当者{rnd.Next(1, 5)}"; // ランダムな担当者を生成 dataTable.Rows.Add(salesDate, month, salesPerson, $"地域{region}", $"商品{product}", sales, stock, cost); } } } } return dataTable; } } } |
まとめ
今回は DataTable をPivot（縦横変換）するためのC#のメソッドのソースコードと、テストプログラムを紹介しました。
ロジックはシンプルです。
- 元のDataTableからDataRowを取り出すループの中で、空のDataTableに対して不足しているカラムと行を追加していく。
- 既にカラムと行が存在すれば、そこに値を上書きする。
元のDataTableの各カラムのデータ型によっては、うまくいかないケースがあるかもしれませんが、データ型を合わせてあげれば動作するはずです。
皆さんのニーズに合わせてカスタマイズして使っていただければ幸いです。
コメント