Python Pandas で複数の.xlsx ファイルをマージして必要のない行列や値を削除したい

Question

前提・実現したいこと

複数の.xlsx ファイルを読み込み、100件程のファイルを結合したいと思っています。
１）１つの.xlsx内に5～6個のグループ（テーブル？）があり、その間に空白行を入れないといけません。
２）指定した列（['伝票日付']）に空白がある場合は、その行を削除します。

発生している問題

１）空白行を残すため"-"（※適当に指定した値）を入れて最後に"-"を.xlsxファイルを出力時に削除しようと試みてますが実行結果に反映されません。
２）空白がある行はGoogleColab上の実行結果では削除されていますが、.xlsxファイルを出力、ダウンロード時には反映されていません。

該当のソースコード

import openpyxl
import pandas as pd
from glob import glob

filepaths = glob('/content/sample_data/*.xlsx')
filepath = filepaths[1]

def extract(filepath):
  _df = pd.read_excel(filepath,sheet_name='Sheet2', header=None)
  columns = _df.iloc[0,[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]]
  df = _df.iloc[1:38, [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]]
  df.columns = columns
  df.dropna(subset=['伝票日付'], how='any')
  return df

df = pd.DataFrame()

for filepath in filepaths:
  _df = extract(filepath)
  df = pd.concat([df, _df])

df.dropna(subset=['伝票日付'], how='any')
df.replace( '-', 'NaN')

df.to_excel('/content/sample_data/yen_master_***.xlsx', index=False )

ex.
1 2 3 4 5
1 index1 0 10 0 0
2 index2 0 30 0 0
3 index3 0 NaN 0 0　←　指定列"3"にNaNがあるので行を削除
4 NaN NaN - NaN NaN　←"-"の値は削除して行は残す
5 index1 0 10 0 0
6 index2 0 30 0 0
7 index3 0 NaN 0 0　←　指定列"3"にNaNがあるので行を削除
8 index3 0 NaN 0 0　←　指定列"3"にNaNがあるので行を削除
9 NaN NaN - NaN NaN　←"-"の値は削除して行は残す
10 index1 0 10 0 0
11 index2 0 30 0 0
12 NaN NaN - NaN NaN　←"-"の値は削除して行は残す

※こちらで１ファイル内のデータとなります。最大100程度を下方向にマージしたいと考えています。

特に
df.dropna(subset=['伝票日付'], how='any')
df.replace( '-', 'NaN')
の代入の仕方が分かりません…
上記コード内容にこだわっていませんが何か良い方法がありましたらご教示いただきたいです。

Answer 1

最後に"-"を.xlsxファイルを出力時に削除しよう

1 NaNは'NaN’ではなく、float('NaN')です。
2 データフレーム.replaceは置換したデータフレームを返し、元のデータフレームは変更されません。

df.replace( '-', 'NaN')
を以下に変更しましょう。
df = df.replace('-', float('NaN'))
あるいは、
df.replace('-', float('NaN'), inplace=True)

空白がある行は

1 データフレーム.dropnaは削除したデータフレームを返し、元のデータフレームは変更されません。

df.dropna(subset=['伝票日付'], how='any')
を以下に変更しましょう。
df = df.dropna(subset=['伝票日付'], how='any')
あるいは、
df.dropna(subset=['伝票日付'], how='any', inplace=True)

なお、
_df = pd.read_excel(filepath,sheet_name='Sheet2', header=None)
を以下に変更すれば、もっと簡単になります。
_df = pd.read_excel(filepath,sheet_name='Sheet2')

以下を参考にしてください。

pandas.DataFrame, Seriesの要素の値を置換するreplace
pandasで欠損値NaNを除外（削除）・置換（穴埋め）・抽出
pandasでExcelファイル（xlsx, xls）の読み込み（read_excel）

Comments

[repost]

ご丁寧にご回答いただきありがとうございました。
概ね解決できました。

def extract(filepath):
_df = pd.read_excel(filepath,sheet_name='円貨', header=None)
columns = _df.iloc[0,[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]]
df = _df.iloc[1:38, [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]]
df.columns = columns
df.dropna(subset=['伝票日付'], how='any', inplace=True)
return df

df = pd.DataFrame()

for filepath in filepaths:
_df = extract(filepath)
df = pd.concat([df, _df])

df.replace('-', float('NaN'), inplace=True)
df.to_excel('/content/sample_data/yen_master_***.xlsx', index=False )

あと１点、回答いただいたコードで出力すると
マージして残る連続する複数の空白行が出てきます。
そちらの空白行を１行ずつだけ残したい場合は解決策はありますでしょうか？
大変お手数をおかけしますがもし分かりましたらご回答いただけると助かります。

ex.
1 2 3 4 5
1 index1 0 10 0 0
2 index2 0 30 0 0
3 NaN NaN - NaN NaN
4 index1 0 10 0 0
5 index2 0 30 0 0
6 NaN NaN - NaN NaN　←この空白行は削除
7 NaN NaN - NaN NaN　←この空白行は削除
8 NaN NaN - NaN NaN　←"-"の値は削除して行は残す
9 index1 0 10 0 0
10 index2 0 30 0 0
11 NaN NaN - NaN NaN　←この空白行は削除
12 NaN NaN - NaN NaN　←この空白行は削除
13 NaN NaN - NaN NaN　←この空白行は残す

[ppaul]

df2 = df[df[3].shift(1) != df[3]]
とかで出来ると思います。

[repost]

ご確認ありがとうございます。
以下のようなエラーになってしまいます…
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
/usr/local/lib/python3.7/dist-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
2897 try:
-> 2898 return self._engine.get_loc(casted_key)
2899 except KeyError as err:

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas/_libs/hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 3

The above exception was the direct cause of the following exception:

KeyError Traceback (most recent call last)
2 frames
<ipython-input-35-37e57f7f7e3a> in <module>()
----> 1 df2 = df[df[3].shift(1) != df[3]]
2 df2

/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py in __getitem__(self, key)
2904 if self.columns.nlevels > 1:
2905 return self._getitem_multilevel(key)
-> 2906 indexer = self.columns.get_loc(key)
2907 if is_integer(indexer):
2908 indexer = [indexer]

/usr/local/lib/python3.7/dist-packages/pandas/core/indexes/base.py in get_loc(self, key, method, tolerance)
2898 return self._engine.get_loc(casted_key)
2899 except KeyError as err:
-> 2900 raise KeyError(key) from err
2901
2902 if tolerance is not None:

KeyError: 3

ちなみにdf.shift(3)を入れると3行の空白行が追加されますが、
そのあとにdf.shift()を実行しても1行の空白行が残らず3行の空白行すべて無くなって元のdfに戻ります。