Python テキスト形式の表をソートして出力

Question

テキスト形式の表があり、指定の条件を元に数字を大きい順にソートして
テキスト形式で返したい。

入力はテキストですが、表にするとこんなインプット

category0	category1	category2	total	unit
fish	dry	ika	-2018.34	-9
fish	raw	ika	-131.85	-3
fish	dry	aji	18363.41	117
fish	raw	aji	14542.39	49
¥subtotal	¥subtotal	¥subtotal	599051.63	2728
fish	¥subtotal	¥subtotal	330267.42	1406
fish	raw	¥subtotal	166186.02	864
fish	dry	¥subtotal	164081.39	542
meat	¥subtotal	¥subtotal	178959.8	742
meat	cooked	horse	29778.81	97
meat	raw	lam	23425.49	101
meat	cooked	duck	23874	82
meat	cooked	¥subtotal	99739.9	341
meat	raw	beef	382.4	2
meat	raw	¥subtotal	79219.9	401

アプトプットは、Categoryの後ろの数字が小さい順に大きな分類になっており、分類毎にsubtotalがあり、例えばTotalの数字の大きな順に分類毎に順番に表示したい。

• Category0のsubtotalは、一番上に表示
• Category1に複数subtotalがある場合は、Totalの中で数字が大きい項目を次に表示。 次にfish/rawとfish/dryのsubtotalの数字を比べてfish/rawの方が数字が大きいので、次に表示。
• fish/rawの中でTotalが大きい順にfish/raw/aji, fish/raw/ikaを順番に表示

以下は、表のイメージを抜粋しているので、subtotalの詳細の合計は、合っていません

category0	category1	category2	total	unit
¥subtotal	¥subtotal	¥subtotal	599051.63	2728
fish	¥subtotal	¥subtotal	330267.42	1406
fish	raw	¥subtotal	166186.02	864
fish	raw	aji	14542.39	49
fish	raw	ika	-131.85	-3
fish	dry	¥subtotal	164081.39	542
fish	dry	aji	18363.41	117
fish	dry	ika	-2018.34	-9
meat	¥subtotal	¥subtotal	178959.8	742
meat	cooked	¥subtotal	99739.9	341
meat	cooked	horse	29778.81	97
meat	cooked	duck	23874	82
meat	raw	¥subtotal	79219.9	401
meat	raw	lam	23425.49	101
meat	raw	beef	382.4	2

現状のコードは、テキストで返すようにしてませんが、まずはソートがうまくいことだけを試したが、うまくいきませんでした。どういう感じでソートを作成すればよいでしょうか？

input = """category0|category1|category2|total|unit
fish|dry|ika|-2018.34|-9
fish|dry|aji|18363.41|117
meat|cooked|pork|328|1
fish|dry|hokke|29090.73|125
fish|dry|sanma|7187.84|8
fish|raw|ika|-131.85|-3
meat|cooked|other|1688.8|5
fish|raw|aji|14542.39|49
meat|cooked|chicken|3311|10
fish|raw|hokke|48355|209
fish|raw|sanma|4436.4|17
fish|dry|saba|42454.92|121
meat|cooked|beef|7795.45|25
fish|raw|saba|1628|12
meat|cooked|lam|32963.85|121
fish|raw|shishamo|4443.75|31
meat|raw|pork|15703|79
meat|raw|chicken|39299|213
fish|dry|shishamo|12790.85|45
fish|raw|tai|7788.79|32
meat|cooked|duck|23874|82
fish|dry|tai|2164.8|8
meat|¥subtotal|¥subtotal|178959.8|742
fish|dry|kinki|19317.84|601
meat|cooked|¥subtotal|99739.9|341
fish|raw|kinki|26081.2|128
fish|raw|nodoguro|40678.91|272
fish|dry|nodoguro|39314.15|144
meat|cooked|horse|29778.81|97
fish|dry|iwashi|13778.59|40
fish|¥subtotal|¥subtotal|330267.42|1406
fish|raw|¥subtotal|166186.02|864
meat|raw|beef|382.4|2
meat|raw|¥subtotal|79219.9|401
¥subtotal|¥subtotal|¥subtotal|599051.63|2728
meat|raw|lam|23425.49|101
meat|raw|other|410|6
fish|dry|¥subtotal|164081.39|542"""

output="""category0|category1|category2|total|unit
¥subtotal|¥subtotal|¥subtotal|599051.63|2728
fish|¥subtotal|¥subtotal|330267.42|1406
fish|raw|¥subtotal|166186.02|864
fish|raw|hokke|48355|209
fish|raw|nodoguro|40678.91|272
fish|raw|kinki|26081.2|128
fish|raw|aji|14542.39|49
fish|raw|tai|7788.79|32
fish|raw|shishamo|4443.75|31
fish|raw|sanma|4436.4|17
fish|raw|saba|1628|12
fish|raw|ika|-131.85|-3
fish|dry|¥subtotal|164081.39|542
fish|dry|saba|42454.92|121
fish|dry|nodoguro|39314.15|144
fish|dry|hokke|29090.73|125
fish|dry|kinki|19317.84|601
fish|dry|aji|18363.41|117
fish|dry|iwashi|13778.59|40
fish|dry|shishamo|12790.85|45
fish|dry|sanma|7187.84|8
fish|dry|tai|2164.8|8
fish|dry|ika|-2018.34|-9
meat|¥subtotal|¥subtotal|178959.8|742
meat|cooked|¥subtotal|99739.9|341
meat|cooked|lam|32963.85|121
meat|cooked|horse|29778.81|97
meat|cooked|duck|23874|82
meat|cooked|beef|7795.45|25
meat|cooked|chicken|3311|10
meat|cooked|other|1688.8|5
meat|cooked|pork|328|1
meat|raw|¥subtotal|79219.9|401
meat|raw|chicken|39299|213
meat|raw|lam|23425.49|101
meat|raw|pork|15703|79
meat|raw|other|410|6
meat|raw|beef|382.4|2
"""


from collections import defaultdict

def sort_text(input_data, sort_func):
    data = input_data.split('\n')

    ref = defaultdict(lambda: defaultdict(dict))
    num_proverties = 0
    columns = []
    ans = ""
    for i, d in enumerate(data):
        tmp = d.split('|')
        if i == 0:
            columns += tmp
            for col in columns:
                if col.startswith('category'):
                    num_proverties += 1
            ans += ('|').join(tmp) + ('\n')
        else:
            for j, col in enumerate(columns):
                ref[i][col] = tmp[j]

　　#以下を色々試したがうまくいかなかった。
    res = sorted(ref.items(), key=lambda x: [x[1]['category2'], -float(x[1][sort_func])])
    res = sorted(res, key=lambda x: [x[1]['category1'], -float(x[1][sort_func])])
    res = sorted(res,
                   key=lambda x: [x[1]['category0'], -float(x[1][sort_func])])

    return res

if __name__ == '__main__':
    res = sort_text(input, 'total')
    for r in res:
        print(r)   

よろしくおねがいします。

Comments

[meg_]

> テキスト形式の表
csvなどてあればpandasモジュールを使用するのが簡単だとおもいます。

Answer 1

なんかめっちゃ長くなりましたが、コード2つのうち上はただのテストデータ作成（DataFrameに入れただけ）
下がソート処理です。
cat2のsubtotalを、total順に並び替えして、その時のcat1の並びをリスト化
cat1のsubtotalを、total順に並び替えして、その時のcat0の並びをリスト化
多次元リストをitertoolsで1次元にならしてから、pd.concatで一体化

input_data = """category0|category1|category2|total|unit
fish|dry|ika|-2018.34|-9
fish|dry|aji|18363.41|117
meat|cooked|pork|328|1
fish|dry|hokke|29090.73|125
fish|dry|sanma|7187.84|8
fish|raw|ika|-131.85|-3
meat|cooked|other|1688.8|5
fish|raw|aji|14542.39|49
meat|cooked|chicken|3311|10
fish|raw|hokke|48355|209
fish|raw|sanma|4436.4|17
fish|dry|saba|42454.92|121
meat|cooked|beef|7795.45|25
fish|raw|saba|1628|12
meat|cooked|lam|32963.85|121
fish|raw|shishamo|4443.75|31
meat|raw|pork|15703|79
meat|raw|chicken|39299|213
fish|dry|shishamo|12790.85|45
fish|raw|tai|7788.79|32
meat|cooked|duck|23874|82
fish|dry|tai|2164.8|8
meat|¥subtotal|¥subtotal|178959.8|742
fish|dry|kinki|19317.84|601
meat|cooked|¥subtotal|99739.9|341
fish|raw|kinki|26081.2|128
fish|raw|nodoguro|40678.91|272
fish|dry|nodoguro|39314.15|144
meat|cooked|horse|29778.81|97
fish|dry|iwashi|13778.59|40
fish|¥subtotal|¥subtotal|330267.42|1406
fish|raw|¥subtotal|166186.02|864
meat|raw|beef|382.4|2
meat|raw|¥subtotal|79219.9|401
¥subtotal|¥subtotal|¥subtotal|599051.63|2728
meat|raw|lam|23425.49|101
meat|raw|other|410|6
fish|dry|¥subtotal|164081.39|542"""

import io
import pandas as pd
df = pd.read_table(io.StringIO(input_data), delimiter="|")

import itertools

def sort_cat(cat, cat_num, df):
    return df.groupby(f"category{cat_num}").get_group(cat).sort_values(by="total", ascending=False)

def sorted1_df(df):
    cat_num = 1
    sub_sort = df.groupby(f"category{cat_num+1}").get_group("¥subtotal").sort_values(by="total", ascending=False)[f"category{cat_num}"]
    return  [sort_cat(cat, cat_num, df) for cat in sub_sort]

def sorted0_df(df):
    cat_num = 0
    sub_sort = df.groupby(f"category{cat_num+1}").get_group("¥subtotal").sort_values(by="total", ascending=False)[f"category{cat_num}"]
    # print(sub_sort)
    # 34    ¥subtotal
    # 30         fish
    # 22         meat
    return  [sorted1_df(sub_df) for sub_df in [sort_cat(cat, cat_num, df) for cat in sub_sort]]

new_df = pd.concat(list(itertools.chain.from_iterable(sorted0_df(df))))
# print(new_df)
#     category0  category1  category2      total  unit
# 34  ¥subtotal  ¥subtotal  ¥subtotal  599051.63  2728
# 30       fish  ¥subtotal  ¥subtotal  330267.42  1406
# 31       fish        raw  ¥subtotal  166186.02   864
# 9        fish        raw      hokke   48355.00   209
# 26       fish        raw   nodoguro   40678.91   272
# 25       fish        raw      kinki   26081.20   128
# 7        fish        raw        aji   14542.39    49
# 19       fish        raw        tai    7788.79    32
# 15       fish        raw   shishamo    4443.75    31
# 10       fish        raw      sanma    4436.40    17
# 13       fish        raw       saba    1628.00    12
# 5        fish        raw        ika    -131.85    -3
# 37       fish        dry  ¥subtotal  164081.39   542
# 11       fish        dry       saba   42454.92   121
# 27       fish        dry   nodoguro   39314.15   144
# 3        fish        dry      hokke   29090.73   125
# 23       fish        dry      kinki   19317.84   601
# 1        fish        dry        aji   18363.41   117
# 29       fish        dry     iwashi   13778.59    40
# 18       fish        dry   shishamo   12790.85    45
# 4        fish        dry      sanma    7187.84     8
# 21       fish        dry        tai    2164.80     8
# 0        fish        dry        ika   -2018.34    -9
# 22       meat  ¥subtotal  ¥subtotal  178959.80   742
# 24       meat     cooked  ¥subtotal   99739.90   341
# 14       meat     cooked        lam   32963.85   121
# 28       meat     cooked      horse   29778.81    97
# 20       meat     cooked       duck   23874.00    82
# 12       meat     cooked       beef    7795.45    25
# 8        meat     cooked    chicken    3311.00    10
# 6        meat     cooked      other    1688.80     5
# 2        meat     cooked       pork     328.00     1
# 33       meat        raw  ¥subtotal   79219.90   401
# 17       meat        raw    chicken   39299.00   213
# 35       meat        raw        lam   23425.49   101
# 16       meat        raw       pork   15703.00    79
# 36       meat        raw      other     410.00     6
# 32       meat        raw       beef     382.40     2

Comments

[nahi123]

ありがとうございます。Pandasで、順番にソートするんですね。助かりました。