PythonのPandasでのデータ集計、カラム名の一部の値を抽出し、groupbyで集計したい

Question

実現したいこと

Python（Jupyter notebookを使っています)、Pandasでデータ集計をしています。
CSVで取り込んだデータのうち、カラム名の一部（先頭の2文字と、3つ目の_（アンダーバー）移行の文字）のみを抽出し、
それが共通する単位で値を合計したいです。
例では抽出する文字は、01_100m、01_1000m、02_100m、02_1000ｍで、それぞれ2列が共通になるため2列分の値は合計して表出するイメージです。

• 元データ
• 実現したいこと

前提

カラム名のルールに例外はなく、先頭の数字は必ず2文字、_は3つあります。

発生している問題・エラーメッセージ

調べたコードを書いてみましたが、カラム名は先頭文字と_が連続する形で表出されてしまいます。

例　01___、01___、02___、02___

エラーメッセージは出ていません

該当のソースコード

import pandas as pd

# DataFrameの作成（サンプル）
data = read.csv(ファイル名）
df = pd.DataFrame(data)

# カラム名の最初の2文字と3つ目の_以降の文字列を抜き出してrename
new_columns = {col: col[:2] + '_' + col.split('_', 2)[-1] if '_' in col else col for col in df.columns}
df.rename(columns=new_columns, inplace=True)

#カラム名が共通する列をグループ化し、各グループの合計を計算
summary = df.groupby(df.columns).sum()
summary

試したこと

renameする方法はいくつか試してみましたが、どれもうまくいきません。最後の5文字を抜く方法でもやってみましたが、できませんでした。
また、Stack関数で一度縦持ちにして処理することも検討しましたが、縦持ちにした際にカラム名が空になり、どの列に対する処理なのかを指定する部分がうまくいきませんでした。

最終的なgroupbyもこれだけで集計できるのかはわかりません。。

補足情報（FW/ツールのバージョンなど）

体系的な学習をしたことがなく、独学で調べながらpythonを使っています。
初歩的なところの理解が足りていない可能性がありますが、教えていただけましたら幸いです。
よろしくお願いいたします。

Comments

[meg_]

> 調べたコードを書いてみましたが、カラム名は先頭文字と_が連続する形で表出されてしまいます。
> 例　01___、01___、02___、02___
「該当のソースコード」でそうなりますか？「01_有効_100m」みたいになりませんか？

[more83]

コメントありがとうございます、そうなってしまうのです、outputでファイルも確認したのですが…
他のコードが影響しているのでしょうか・・

[meg_]

> 他のコードが影響しているのでしょうか・・
「該当のソースコード」以外の事はこちらには分かりません。
「他のコード」と言われましても何も分かりません。

[more83]

上記ではできませんでしたが、下記で回答いただいた方法で解消しました。ありがとうございました。

Answer 1

pandas の groupby は axis=1 でカラム方向の集計ができます。
また、カラムを rename してから集計するのではなく、rename用の dict を groupby のキーに渡して集計することができます。

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html

(質問のコードの col.split の引数は 2 ですが、正しくは 3 じゃないかと思います。)

import pandas as pd

df = pd.DataFrame(
    [[0,1,0,0,0,0,2,1],
     [0,3,1,3,0,1,0,3],
     [1,0,0,2,1,3,0,1]],
    index=[101,102,103],
    columns=['01xx_aa_aa_100m','01xx_bb_bb_100m','02xx_aa_aa_100m','02xx_bb_bb_100m',
             '01xx_aa_aa_1000m','01xx_bb_bb_1000m','02xx_aa_aa_1000m','02xx_bb_bb_1000m']
)

new_columns = {col: col[:2] + '_' + col.split('_', 3)[-1] for col in df.columns}

df_out = df.groupby(new_columns, axis=1, sort=False).sum()
print(df_out)
#      01_100m  02_100m  01_1000m  02_1000m
# 101        1        0         0         3
# 102        3        4         1         3
# 103        1        2         4         1

Comments

[more83]

早速ありがとうございました！！groupbyはaxis=1でカラム方向の集計ができるんですね…！
勉強不足でWebでもうまく調べられなかったのでとても助かりました、ありがとうございます。

>質問のコードの col.split の引数は 2 ですが、正しくは 3 じゃないかと思います。
引数は4でやりたいことができました。

非常に簡潔なコードで実現できる点が良かったのでベストアンサーにさせていただきます。
ありがとうございました。今後ともぜひよろしくお願いいたします。

Answer 2

元データから作ったDataFrame を df とします。（ただしUID はdfのインデクスに使用するものとします。）

まず、'01男性_有効_有効_100m' という文字列から '01_100m' '01_100' という文字列を得る関数を作っておきます。

import re

def get_key(x):
    return re.sub(r'^(\d{2})[^\d]+(\d+)m$', r'\1_\2', x)

この get_key(x) を使って列をグループ化し、同じキーとなる列を合計したSeriesを各列とするDataFarame total_df を作るには以下のようにします。

from itertools import groupby

col_groups = groupby(sorted(df.columns, key=get_key), key=get_key)
total_df = pd.DataFrame(
    {f'{key}m': df[cols].sum(axis=1) for key, cols in col_groups}
)

追記1

以下の2点

1. DataFrame.groupby メソッドを使うこと
2. 最終的に得たい DataFrame のカラムの並び順を質問にあるように 01_100m, 01_1000m, 02_100m, 02_1000m とすること

が求められているのであれば、下記のようにします。(get_key関数は先述したものをそのまま使います)

cols_map = {col: get_key(col) for col in df.columns}
total_df = df.groupby(cols_map, axis=1, sort=True).sum().rename(columns=lambda col: f'{col}m')

上記のようにdf.groupby のキーワード引数 sort にTrueを指定しますが、このときに 上記の 2. のような列の並びを得るためには、get_key関数で得られる各列名のキーには末尾のm を含めないようにしておく必要があります。そのため集計してから、あらためて各列の末尾に m を付加しています。

上記の実行例:

追記2

df.groupby の第1引数には、各列名に対応するグループ化するときのキーを得る関数をそのまま渡せるので、追記1に書いた2行

cols_map = {col: get_key(col) for col in df.columns}
total_df = df.groupby(cols_map, axis=1, sort=True).sum().rename(columns=lambda col: f'{col}m')

は、dict cols_map を作らずとも以下の1行で済むところでした。

total_df = df.groupby(get_key, axis=1, sort=True).sum().rename(columns=lambda k: f'{k}m')

備考

さらにリファクタの思いつきですが、グループ化のキーに末尾の'm' を含めるか含まないかでソート結果が変わってしまうのを気にしたコードを書くのはやや煩雑なので

• get_key関数は '01男性_有効_有効_100m' に対して ('01', 100) というタプルを返させるようにして、

• 集計したDataFrameの列を rename するときの columns に、このタプルから '01_100m' を返すlambdaを指定する

というのもアリかなと思いました。