Pythonを用いてExcelのデータを整理したい。

実現したいこと・前提

以下のような事を実行するPythonファイルを作成してほしい。

ある名前のExcelファイルがあり、各列に情報が並んでいる。
各列には必ず、「学歴」、「キャンパス情報」、「大学時代の経験」、「経歴」、「学生にお話できること」、「学生からのコメント」の文字列がありますが、必ずしも同じ行にはありません。

上記の文字列は、情報の境目であり、その文字列の前後に情報があります。

これを見やすくするため、「学歴」、「キャンパス情報」、「大学時代の経験」、「経歴」、「学生にお話できること」、「学生からのコメント」の文字列を同じ行に揃えたいです。

具体的な作業イメージは、以下のような感じです。
１．各列の「学歴」の文字列を探し、一番下の「学歴」の文字列がある行の位置を確認する。
２．各列の「学歴」の文字列より下の情報を、１．で確認した行で先頭にするように移す。（例：１．で確認した行が37行目であるとする。A列の情報が、1行目から120行目まであるとする。A列の「学歴」が21行目だとする。その場合、A列の「学歴」を先頭とする100行の情報を、先頭の「学歴」が37行目になるように下に移す。その場合、A列は21-36行目は空白になる。これを、情報がある全ての列で行う。）
３．「キャンパス情報」、「大学時代の経験」、「経歴」、「学生にお話できること」、「学生からのコメント」の文字列でも、１．及び２．を実行する。

これを実行するPythonコードを作成してください。

発生している問題

以下のコードを実行したのですが、元のファイルと全く同じファイルが出力されます。原因がどこにあるかが分かりません。

該当のソースコード

Python
1import openpyxl
2
3# Excelファイルを開く
4wb = openpyxl.load_workbook("sample.xlsx")
5ws = wb.worksheets[1] # 左から2番目のシートに目的情報がある。
6
7# 検索するキーワード
8keywords = ['学歴', 'キャンパス情報', '大学時代の経験', '経歴', '学生にお話できること', '学生からのコメント']
9
10# シート内の各列に対して処理を実行する
11for col in ws.iter_cols():
12    # 列の先頭セルの値がキーワードのリストに含まれる場合
13    if col[0].value in keywords:
14        keyword = col[0].value
15
16        # キーワードのある行を検索
17        keyword_row = None
18        for cell in col:
19            if cell.value == keyword:
20                keyword_row = cell.row
21                break
22
23        # キーワードが見つからない場合はスキップ
24        if keyword_row is None:
25            continue
26
27        # 各列の情報をキーワードがある行に移動する
28        for column in ws.iter_cols():
29            # 列の先頭セルの値がキーワードである場合、スキップ
30            if column[0].value == keyword:
31                continue
32
33            # キーワードがある列に情報がない場合、スキップ
34            if column[keyword_row - 1].value is None:
35                continue
36
37            # 情報を移動する
38            move_rows = keyword_row - column[0].row
39            for i, cell in enumerate(column):
40                # キーワードのある行より上にある情報はスキップする
41                if i < keyword_row - 1:
42                    continue
43
44                # 移動先の行がシートの範囲外の場合はスキップする
45                if i + move_rows >= len(column) or i + move_rows < 0:
46                    continue
47
48                # 情報を移動する
49                column.move_range(start_index=i, end_index=i, rows=move_rows)
50
51# Excelファイルを上書き保存する
52wb.save("output_new.xlsx")
53

試したこと

下記の修正コードも実行したのですが、結果は変わりませんでした。

Python
1
2import openpyxl
3
4# Excelファイルを開く
5wb = openpyxl.load_workbook("sample.xlsx")
6ws = wb.worksheets[1] # 左から2番目のシートに目的情報がある。
7
8# 検索するキーワード
9keywords = ['学歴', 'キャンパス情報', '大学時代の経験', '経歴', '学生にお話できること', '学生からのコメント']
10
11# シート内の各列に対して処理を実行する
12for col in ws.columns:
13    # 列の先頭セルの値がキーワードのリストに含まれる場合
14    if col[0].value in keywords:
15        keyword = col[0].value
16
17        # キーワードのある行を検索
18        keyword_row = None
19        for cell in col:
20            if cell.value == keyword:
21                keyword_row = cell.row
22                break
23
24        # キーワードが見つからない場合はスキップ
25        if keyword_row is None:
26            continue
27
28        # 各列の情報をキーワードがある行に移動する
29        for column in ws.columns:
30            # 列の先頭セルの値がキーワードである場合、スキップ
31            if column[0].value == keyword:
32                continue
33
34            # キーワードがある列に情報がない場合、スキップ
35            if column[keyword_row - 1].value is None:
36                continue
37
38            # 情報を移動する
39            move_rows = keyword_row - 1 - column[0].row
40            for i, cell in enumerate(column):
41                # キーワードのある行より上にある情報はスキップする
42                if cell.row < keyword_row:
43                    continue
44                # 移動先の行がシートの範囲外の場合はスキップする
45                if cell.row + move_rows > ws.max_row:
46                    continue
47                # 情報を移動する
48                ws.cell(row=cell.row+move_rows, column=cell.column).value = cell.value
49
50# Excelファイルを上書き保存する
51wb.save("output_new.xlsx")
52

行動規範の内容に同意します

回答1件

ちょっと問題が把握しずらかったので再定義してみました。
うまく元の問題を把握できていないかもしれないので参考までの回答です。

問題

ある学校でクラス毎に「好き」「嫌い」「得意」な教科という３つの設問についてのアンケートを数人の生徒から取った。
各設問に対する回答は０（なし）、複数可である。
以下はその結果をまとめた表である。

	A	B	C
0	(1)好き	(2)嫌い	(1)好き
1	国語	国語	国語
2	(2)嫌い	(2)嫌い	数学
3	数学	英語	社会
4	(3)得意	数学	音楽
5	音楽	音楽	体育
6	(2)嫌い	体育	(1)好き
7	英語	(1)好き	英語
8	国語	体育	(2)嫌い
9	(3)得意	音楽	国語
10	体育	(1)好き	(1)好き
11	音楽	国語	英語
12		(3)得意	数学
13		英語	社会
14			(3)得意
15			体育

各列はクラスをあらわしており、クラス内の各生徒の結果を順に格納している。
「(1)好き」などが設問のタイトルであり、そのあとにその回答が格納されている。
ただし未回答の設問についてはタイトルを含めて格納していない。
しかし設問の順番はつねに「好き」「嫌い」「得意」の順で格納されていることが保証されているため、各生徒の回答の「区切り」は判定できるはずである。

たとえばA列では「国語が好き、数学が嫌い、音楽が得意」「英語と国語が嫌い、体育と音楽が得意」の２人の結果が格納されている。

実現したいこと

上記の結果を各生徒、各設問の「行を揃えるように」整形したい。
すなわち以下のような表を得たい。

	A	B	C
0	(1)好き	(1)好き	(1)好き
1	国語		国語
2			数学
3			社会
4			音楽
5			体育
6	(2)嫌い	(2)嫌い	(2)嫌い
7	数学	国語
8	(3)得意	(3)得意	(3)得意
9	音楽
10	(1)好き	(1)好き	(1)好き
11			英語
12	(2)嫌い	(2)嫌い	(2)嫌い
13	英語	英語	国語
14	国語	数学
15		音楽
16		体育
17	(3)得意	(3)得意	(3)得意
18	体育
19	音楽
20	(1)好き	(1)好き	(1)好き
21		体育	英語
22		音楽	数学
23			社会
24	(2)嫌い	(2)嫌い	(2)嫌い
25	(3)得意	(3)得意	(3)得意
26			体育
27	(1)好き	(1)好き	(1)好き
28		国語
29	(2)嫌い	(2)嫌い	(2)嫌い
30	(3)得意	(3)得意	(3)得意
31		英語

回答

まずは元の表から各列（クラス）毎、各生徒毎の回答をそれぞれ取得する。
取得できた回答を、各クラスの先頭から順に走査して整形されたデータを作成していく。
そのさいzip_longestにて最長の要素まで走査しNoneな要素については空データ扱いとする。

Python
1import pandas as pd
2from io import StringIO
3from itertools import zip_longest
4
5# テストデータ
6s = """A,B,C
7(1)好き,(2)嫌い,(1)好き
8国語,国語,国語
9(2)嫌い,(2)嫌い,数学
10数学,英語,社会
11(3)得意,数学,音楽
12音楽,音楽,体育
13(2)嫌い,体育,(1)好き
14英語,(1)好き,英語
15国語,体育,(2)嫌い
16(3)得意,音楽,国語
17体育,(1)好き,(1)好き
18音楽,国語,英語
19,(3)得意,数学
20,英語,社会
21,,(3)得意
22,,体育"""
23df = pd.read_csv(StringIO(s), keep_default_na=False)
24
25
26TITLES = ['(1)好き','(2)嫌い','(3)得意'] # 各設問のタイトル
27
28# 列から回答を取得
29def get_answer(lines):
30
31    answers = []
32    cur_q = -1 # 現在の設問位置
33    ans = [[],[],[]] # 回答。好き, 嫌い, 得意な教科を格納
34    for line in lines:
35        if line in TITLES: # 設問（タイトル）行
36            q = TITLES.index(line)
37            if q <= cur_q: # 現在より手前の設問＝次の人の回答になった
38                answers.append(ans)
39                ans = [[],[],[]]
40            cur_q = q
41        elif cur_q >= 0 and line: # （空ではない）回答
42            ans[cur_q].append(line)
43
44    answers.append(ans)
45    return answers
46
47# 列毎に回答を取得
48col_answers = []
49for c in df.columns:
50    answers = get_answer(df[c])
51    col_answers.append(answers)
52
53# 各列の回答から結果データを作成
54data = []
55for cols in zip_longest(*col_answers):
56    cols = [c if c else [[],[],[]] for c in cols] # 要素（人数）が足りない列はNone＝空の回答とする
57
58    # 設問毎にデータ行を追加
59    for q in range(3):
60        col_ans = [c[q] for c in cols]
61
62        # 設問タイトル行
63        data.append([TITLES[q] for _ in range(len(col_ans))])
64
65        for ans in zip_longest(*col_ans):
66            data.append(ans)
67
68df = pd.DataFrame(data=data, columns=df.columns)
69df = df.fillna('')