python - 如何将多个 csv 文件中的行复制到 pandas 中的新文件？

Question

我有 10 个 csv 文件。我想从所有 csv 文件中复制第一行并保存为新的 csv 文件，然后从所有 csv 文件中复制第二行并保存为第二个 csv 文件等等。我在下面的代码只对第一行和其他 rwos 显示 NaN。我的错误在哪里？

代码

import pandas as pd
import datetime
import glob

path = r'/Jupyter_Works/new_csv'
all_files = glob.glob(path + "/*.csv")

date_time = datetime.datetime(2018, 1, 1)
index = pd.date_range(start='1/1/2018', periods= 8760, freq='H')

columns = ['Lat','Lon','Alt','Temperature','Relative Humidity','Wind speed','Wind direction','Short-wave irradiation']
dfcsv = pd.DataFrame(index=index, columns=columns)

for filename in all_files:
    df = pd.read_csv(filename, index_col='time', header=0)
    dfcsv.iloc[0] = df.iloc[0]

dfcsv

结果

Lat Lon Alt Temperature Relative Humidity   Wind speed  Wind direction  Short-wave irradiation
2018-01-01 00:00:00 31.03   49.36   99  285.56  52.82   2.95    128.5   0
2018-01-01 01:00:00 NaN NaN NaN NaN NaN NaN NaN NaN
2018-01-01 02:00:00 NaN NaN NaN NaN NaN NaN NaN NaN
2018-01-01 03:00:00 NaN NaN NaN NaN NaN NaN NaN NaN
2018-01-01 04:00:00 NaN NaN NaN NaN NaN NaN NaN NaN

Answer 1

首先创建一个带有列表理解和concat 的大DataFrame , 按 loc 选择的唯一值循环并按 DataFrame.to_csv 写入文件.它有效，因为每个 DataFrame 都有唯一索引，所以如果按唯一值选择，则选择所有文件中具有相同位置的行。

path = r'/home/nickan/Jupyter_Works/new_csv'
all_files = glob.glob(path + "/*.csv")

dfs = [pd.read_csv(fp, index_col='time', parse_dates=['time']) for fp in all_files]
df = pd.concat(dfs)

for x in df.index.unique():
    #removed duplicated index by index=False
    df.loc[x].to_csv(f'csv/file_{x.strftime("%Y-%m-%d_%H")}.csv', index=False)

编辑:

因为可能存在内存问题，所以可以使用替代解决方案，在数据帧中按每一行循环并以追加模式写入:

for i, fp in enumerate(all_files):
    df = pd.read_csv(fp, index_col='time', parse_dates=['time']) 
    for x in df.index:
        f = f'out/file_{x.strftime("%Y-%m-%d_%H")}.csv'
        if i == 0:
            df.loc[[x]].to_csv(f, index=False)

        else:
            df.loc[[x]].to_csv(f, index=False,header=None, mode='a')