乱雑な Excel データセットを 1 つの数式で修正しました

私は、Excel でデータセットがどのように配置されるかを非常に気にする人間の 1 人です。それは私が少し強迫観念にとらわれているせいでもありますが、主に構造を正しく理解することで、将来の生活がずっと楽になるからです。インポートまたは受信したデータセットの形状を変更しなければならなかったことが何度あったか忘れましたが、Excel for Microsoft 365 を使用すると、驚くほど簡単にそれを行うことができます。

私は最初からオープンです。これには Python が関係します。でも、自分には合わないと判断する前に、話を聞いてください。 Python はプログラマーだけのものではありません。Python を Excel で使用するために必要なすべてを提供します。一度試してみれば、学習曲線は思っているほど急ではありません。

Excel データは、分析してみるまでは正常に見えました

読める＝使えるとは限らない

この気象データセットを例として考えてみましょう。これには、100 の場所、その国、およびその年の各月の平均気温が含まれています。一見すると何の問題もありません。実際、画面上でデータを読みたい場合は、おそらく多くの人がこのレイアウトを選択するでしょう。

問題は、Excel ツールを使用してデータを分析したいときに始まります。現時点では、月は分析に使用できる単一のフィールドではなく、12 の個別の列にまたがっています。これはワイドデータの例です。多くの種類の分析では、各列がフィールドを表し、各行がレコードを表す長いデータの方がはるかに便利だと思います。したがって、この例では、場所の列、国、月の列、そして気温の最後の列があります。場所は 1 月に 1 回表示され、2 月にもう一度表示されるというように、年間を通して表示されます。

1 つの Python 式がデータセット全体を再構成します

2 行で面倒な作業を行います

幸いなことに、これを行うのに Python プログラムは必要ありません。変換は、Excel で直接 2 行の Python コードを記述するだけです。次回誰かが間違った形式のスプレッドシートを送ってきたときに、また必要になるとわかっているので、数式を保存しておきます。

このプロセスはよく呼ばれます ピボットを解除する。 Power Query を使用したことがある場合は、この用語をすでにご存知かもしれません。 列のアンピボット 本質的に同じジョブを実行するコマンド。ここでそれを使用することもできますが、Python を使用すると、変換をデータと並行して配置される数式に変換できます。

まず、セルを選択し、次のいずれかに進みます。 数式 > Python の挿入 または入力してください =PY( 細胞の中へ。 Excel は Python エディターに切り替わり、次の 2 行を入力できます。

df = xl("WeatherData(#All)", headers=True)
df.melt(id_vars=("Location", "Country"), var_name="Month", value_name="Temperature")

最初の行は Excel テーブルを Python に読み込みます。

df = xl("WeatherData(#All)", headers=True)

WeatherData それは私のテーブルの名前です、 (#All) テーブル全体を参照し、 headers=True 最初の行に列見出しが含まれていることを Python に伝えます。

2 行目は再形成を行います。

df.melt(id_vars=("Location", "Country"), var_name="Month", value_name="Temperature")

私は言っています melt() 去る Location そして Country 一人で。次に、残りの列を取得し、その見出しを新しい列に変換します。 Month 列とその内容を新しいものに Temperature カラム。

便利な点は、1 月、2 月、3 月、その他すべての列を個別にリストする必要がないことです。保持したい列を指定したので、 melt() 残りは自動的に処理されます。

押す前に Ctrl+Enter 式をコミットするには、 Python 出力 に Excel の値。これにより、再形成された DataFrame がワークシートに直接配置され、ピボットテーブル、グラフ、数式、その他の Excel 機能で使用できるようになります。

その結果、100 の場所と 12 か月の 1,200 行を含む、より有用なデータセットが得られました。ブエノスアイレスが毎月 1 回表示されるようになったことに注目してください。

独自のワイドデータを使用して数式を再利用する

いくつかの詳細を交換するだけです

この式の良い点は、それを適用するために Python を理解する必要がないことです。いくつかの部分を変更するだけで済みます。

最後の 2 つの名前は完全にあなた次第です。これらは、によって作成された 2 つの新しい列です。 melt(): 1 つは元の列見出しを含み、もう 1 つは対応する値を含みます。必要な数の列を含めることができます id_vars、名前をリストする限り。

たとえば、Product、Department、Q1、Q2、Q3、および Q4 列を含む SalesData という名前の販売テーブルがあるとします。この式を次のように調整できます。

df = xl("SalesData(#All)", headers=True)
df.melt(id_vars=("Product", "Department"), var_name="Quarter", value_name="Sales")

同じ 2 行により、四半期ごとの 4 つの列が Quarter フィールドと Sales フィールドに変わります。

分析が実際に機能するようになりました

ソースデータが変更されるとすべてが更新されます

気象データを長い形式にすると、使い慣れた Excel ツールで使用できるようになります。たとえば、ピボットテーブルを使用して、特定の月の平均気温が最も高い国を確認し、その結果をピボットグラフに変換して視覚的に比較できます。

また、Python の数式からこぼれた範囲を使用して、ピボットテーブル ソースを動的にしました。 Excel に固定範囲を与える代わりに、数式を含むセルの後に # Spir range 演算子を使用しました。

'Wide Weather Data'!$P$1#

これは、ソース データが変更された場合に役立ちます。 WeatherData テーブルに別の場所を追加すると、テーブルが拡張され、Python の数式が新しい行を取得し、それに応じて流出した結果が増加します。

新しいデータを取得する前にピボットテーブルを更新する必要がありますが、一度更新すると、ピボットテーブルのソース範囲を再定義することなく新しい場所が表示されます。

私は Python プログラマーになっていません

私はまだ Python プログラマーではありません。しかし、私は今、Python コードの小さな部分を保存しており、次回誰かが間違った形式のスプレッドシートを送ってきたときに備えています。これが Excel での Python の気に入っている点です。すでに使い慣れている Excel ツールを置き換える必要がありません。ぎこちない部分には Python を使用し、その後、再形成したデータを Excel ワークフローに戻して、通常どおり作業を続行できます。