考慮到我有大致像這樣的 CSV 檔案
df = pd.DataFrame({'Col1': ['A', 'B', 'C', 'D'],
'ColB': [80, 75, 70, 65]})
我正在使用這里建議的以下腳本
import pandas as pd
import glob
path = r'path/' # use your path
all_files = glob.glob(path "/*.csv")
fields = ['ColA', 'ColB', 'ColC']
first_one = True
for filename in all_files:
if not first_one: # if it is not the first csv file then skip the header row (row 0) of that file
skip_row = [0]
else:
skip_row = []
# works with this version: '1.3.4'
# combine into one
mode = "w"
header = True
for filename in all_files:
with pd.read_csv(
filename,
engine="python",
iterator=True,
chunksize=10_000,
usecols = fields
) as reader:
for df in reader:
filename = os.path.basename(filename)
df["username"] = filename
df.to_csv("New_File.csv", index=False, mode=mode, header=header)
mode = "a"
header = False
大多數檔案都有所有三列，而其中很少有ColC。這將給出一個錯誤（可以理解），如下所示：
ValueError: Usecols do not match columns, columns expected but not found: ['ColC']
如何在ColC保持columns串列不變的情況下放入 nan？
uj5u.com熱心網友回復：
這是預先檢查列的另一種方法：
# (...)
for filename in all_files:
# Check available columns first
cols = pd.read_csv(filename, engine='python', nrows=0, header=0).columns
fields_ = cols.intersection(fields)
missed = [i for i in fields if i not in cols]
with pd.read_csv(
filename,
engine="python",
iterator=True,
chunksize=10_000,
header=0,
usecols = fields_ # Use the "dynamic" one
) as reader:
for df in reader:
# Manually append missed cols
if missed:
for col in missed:
df[col] = np.nan
# Make sure the order is kept
df = df[fields]
# (proceed...)
filename = os.path.basename(filename)
df["username"] = filename
# (...)
uj5u.com熱心網友回復：
將您的示例列串列更改為包含多個缺失列。但無需使用增強的示例檔案更改解決方案中的列。
import pandas as pd
import re
columns = ['Col1','ColB','ColC','ColD']
try:
df = pd.read_csv('test.csv',usecols=columns)
except ValueError as e:
if 'Usecols' not in str(e): raise e
missing = re.findall(r"'(.*?)'", str(e))
df = pd.read_csv('test.csv', usecols=set(columns) - set(missing))
df[missing] = np.nan
df
輸出
Col1 ColB ColC ColD
0 A 80 NaN NaN
1 B 75 NaN NaN
2 C 70 NaN NaN
3 D 65 NaN NaN
創建包含額外和缺失列的示例 csv 檔案
import pandas as pd
df = pd.DataFrame({'Col1': ['A', 'B', 'C', 'D'],
'ColB': [80, 75, 70, 65],
'ColE': [100, 20, 1, 23]})
df.to_csv('test.csv', index=False)
測驗.csv
Col1,ColB,ColE
A,80,100
B,75,20
C,70,1
D,65,23
uj5u.com熱心網友回復：
如果不需要使用 'usecols'，您可以通過使用 .reindex() 而不是 'usecols' 來實作這一點，如下所示：
columns = ['Col1','ColB','ColC']
df = pd.read_csv('test.csv').reindex(columns=columns)
輸出
Col1 ColB ColC
0 A 80 NaN
1 B 75 NaN
2 C 70 NaN
3 D 65 NaN
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/417014.html
標籤：
下一篇：當我從CSV匯入時，熊貓添加.0