Python：データフレームに読み込んだxlsx名（CSV名）を列に表記したい

Question

Python Jupyter Notebookにてデータクリーニングを行っております。

複数のxlsxを読み込みデータフレームを作成したいです。
読み込んだxlsx名をデータフレームに記述したいのですが、煮詰まっています。

例えば、とあるフォルダに格納されている

• data01.xlsx
• data2.xlsx
• data10.xlsx
• data17.xlsx

を読み込み一つのデータフレーム（例：df）にしたい

datafiles  = glob.glob("data/sample/*.xlsx")
df = [pd.read_excel(datafiles[i], parse_dates=[0]) for i in range(len(datafiles))]

# 結合
df_main= pd.concat(df, ignore_index=True)

#ファイル名取得
import os
file_name = os.path.basename(datafiles[1])
file_name
#OUT:data01.xlsx

で結合はできました。
データとして、書くエクセルの行に空白があるため、結合するとNaNが含まれます。

ID	name	area	・・・
10	佐藤	東京都
NaN	NaN	NaN
21	田中	千葉県
34	高山	栃木県
55	斎藤	東京都
NaN	NaN	NaN
NaN	NaN	NaN
100	山田	神奈川県
120	山田	神奈川県
NaN	NaN	NaN
181	山田	神奈川県

・
・
・

<<求めるdf>>
|ID|xlsx|name|area|・・・
|:--|:--:|--:|--:|
|10|data01.xlsx|佐藤|東京都|
|21|data01.xlsx|田中|千葉県|
|34|data02.xlsx|高山|栃木県|
|55|data02.xlsx|斎藤|東京都|
|100|data02.xlsx|山田|神奈川県|
|120|data10.xlsx|山田|神奈川県|
|181|data17.xlsx|山田|神奈川県|

・
・
・

エクセル数は固定ではありません。列・行ともに各エクセルで異なります。
アドバイスいただけたら幸いです。

Answer 1

import pandas as pd
import glob
import os

df = pd.DataFrame()
df_add = pd.DataFrame()

for datafiles in glob.glob("/temp/*.xlsx"):
    file_name = os.path.split(datafiles)[1]
    df = pd.read_excel(datafiles, parse_dates=[0])
    df= df.dropna(how='all')
    df = df.assign(xlsx=file_name)
    df_add = df_add.append(df)

print(df_add)

下記結果になります。

index ID name area xlsx
0 10.0 佐藤 東京都 aaa.xlsx
2 21.0 田中 千葉県 aaa.xlsx
3 34.0 高山 栃木県 aaa.xlsx
4 55.0 斎藤 東京都 aaa.xlsx
0 100.0 山田 神奈川県 bbb.xlsx
1 120.0 山田 神奈川県 bbb.xlsx
3 181.0 山田 神奈川県 bbb.xlsx

ご検討ください。

Comments

[Yeeeee]

ありがとうございます。
できました。

Answer 2

1ファイル毎にassignしてファイル名追加するだけではないですか？

追記　dfはエクセルから取り込んだものに読み替えて

import pandas as pd
df1 = pd.DataFrame({'name':['togashi', 'please', 'work']
                 ,'fan':[10, 20, 30]}
                )
df2 = pd.DataFrame({'name':['kida', 'please', 'work']
                 ,'fan':[60, 70, 40]}
                )
df1 = df1.assign(xlsx='ekusel1')
df2 = df2.assign(xlsx='ekusel2')
print(df1)
print(df2)
print(pd.concat([df1, df2]).reset_index(drop=True))

Comments

[Yeeeee]

例えば、df.assign(xlsx='エクセル名')という感じででしょうか？

今、考えているのは

1.エクセル名を取得したリストを作成→ datafiles

2.data01.xlsxを読み込み
3.xlsxカラムを追加し
4.xlsxカラムにdata01.xlsxを追記
5. 何かしらのデータフレームとして保管
　2~5をリストのdatafiles分、forで回す

6. 保管されたデータフレームを結合

なのですが、どういうコードで書けば煮詰まっています。

[hentaiman]

dropnaした後にassignしてconcatするだけだと思うけど

[Yeeeee]

assign、concatを使うと、
具体的にどんなコードになるのでしょうか？

data01.xlsxをdf01に読み込み、
df02.insert( 1, 'xlsx', datafiles[1])
みたいな感じでカラム作成、代入できるのですが、結合された後の代入方法がわかりません。

[hentaiman]

なのでconcatの前にassignをするんですよ
列追加した後にconcatするのでは要件を満たせないんですか？

[Yeeeee]

ごめんなさい。
それがわからないので質問させてもらっているのです。
よろしくお願いします。

[hentaiman]

元のコードでconcat使ってることと別の回答でdropnaは分かると言われている事からassignの使い方が分からないと判断して回答追記しました

Answer 3

df_main= df_main.dropna(how='all')

Comments

[Yeeeee]

すいません。
dropnaはわかるのですが、求めるdfのxlsx部分追加をpythonでどう記述すべきなのか・・・
と。