在python中，将具有不同报头的多个CSV读入一个datafram

Question

我有几十个csv文件头相似（但并不总是完全相同）。例如，有：

Year Month Day Hour Minute Direct Diffuse D_Global D_IR Zenith Test_Site

其中之一是：

^{pr2}$

（请注意，一个缺少“UüGlobal”和“UüIR”，另一个没有“Diffuse2”而不是“Diffuse2”）

我知道如何将多个csv传递到我的脚本中，但是如何让csv的唯一传递值传递给它们当前有值的列？或许可以将“Nan”传递给该行中的所有其他列。在

理想情况下，我会有一些类似的东西：

'Year','Month','Day','Hour','Minute','Direct','Diffuse','Diffuse2','D_Global','D_IR','U_Global','U_IR','Zenith','Test_Site'
1992,1,1,0,3,-999.00,-999.00,"Nan",-999.00,-999.00,"Nan","Nan",122.517,"BER"
2013,5,30,15,55,812.84,270.62,"Nan",1078.06,-999.00,"Nan","Nan",11.542,"BER"
2004,9,1,0,1,1.04,79.40,"Nan",78.67,303.58,61.06,310.95,85.142,"ALT"
2014,12,1,0,1,0.00,0.00,"Nan",-999.00,226.95,0.00,230.16,115.410,"ALT"

另一个警告是，需要附加此数据帧。它需要保留，因为有多个csv文件传入它。我想我可能会在最后把它写成自己的csv（它最终会变成NETCDF4）。在

Answer 1

假设您有以下CSV文件：

测试1.csv：

year,month,day,Direct 
1992,1,1,11
2013,5,30,11
2004,9,1,11

测试2.csv：

^{pr2}$

测试3.csv：

year,month,day,File3
1992,1,1,text1
2013,5,30,text2
2004,9,1,text3
2016,1,1,unmatching_date

解决方案：

import glob
import pandas as pd

files = glob.glob(r'd:/temp/test*.csv')

def get_merged(files, **kwargs):
    df = pd.read_csv(files[0], **kwargs)
    for f in files[1:]:
        df = df.merge(pd.read_csv(f, **kwargs), how='outer')
    return df

print(get_merged(files))

输出：

   year  month  day  Direct   Direct  Direct2            File3
0  1992      1    1     11.0    21.0    201.0            text1
1  2013      5   30     11.0    21.0    202.0            text2
2  2004      9    1     11.0    21.0    203.0            text3
3  2016      1    1      NaN     NaN      NaN  unmatching_date

更新：通常惯用的pd.concat(list_of_dfs)解决方案在这里不起作用，因为它是通过索引连接的：

In [192]: pd.concat([pd.read_csv(f) for f in glob.glob(file_mask)], axis=0, ignore_index=True)
Out[192]:
   Direct  Direct   Direct2            File3  day  month  year
0     NaN     11.0      NaN              NaN    1      1  1992
1     NaN     11.0      NaN              NaN   30      5  2013
2     NaN     11.0      NaN              NaN    1      9  2004
3    21.0      NaN    201.0              NaN    1      1  1992
4    21.0      NaN    202.0              NaN   30      5  2013
5    21.0      NaN    203.0              NaN    1      9  2004
6     NaN      NaN      NaN            text1    1      1  1992
7     NaN      NaN      NaN            text2   30      5  2013
8     NaN      NaN      NaN            text3    1      9  2004
9     NaN      NaN      NaN  unmatching_date    1      1  2016

In [193]: pd.concat([pd.read_csv(f) for f in glob.glob(file_mask)], axis=1, ignore_index=True)
Out[193]:
       0    1     2     3       4    5     6     7      8     9   10  11               12
0  1992.0  1.0   1.0  11.0  1992.0  1.0   1.0  21.0  201.0  1992   1   1            text1
1  2013.0  5.0  30.0  11.0  2013.0  5.0  30.0  21.0  202.0  2013   5  30            text2
2  2004.0  9.0   1.0  11.0  2004.0  9.0   1.0  21.0  203.0  2004   9   1            text3
3     NaN  NaN   NaN   NaN     NaN  NaN   NaN   NaN    NaN  2016   1   1  unmatching_date

或显式使用index_col=None：

In [194]: pd.concat([pd.read_csv(f, index_col=None) for f in glob.glob(file_mask)], axis=0, ignore_index=True)
Out[194]:
   Direct  Direct   Direct2            File3  day  month  year
0     NaN     11.0      NaN              NaN    1      1  1992
1     NaN     11.0      NaN              NaN   30      5  2013
2     NaN     11.0      NaN              NaN    1      9  2004
3    21.0      NaN    201.0              NaN    1      1  1992
4    21.0      NaN    202.0              NaN   30      5  2013
5    21.0      NaN    203.0              NaN    1      9  2004
6     NaN      NaN      NaN            text1    1      1  1992
7     NaN      NaN      NaN            text2   30      5  2013
8     NaN      NaN      NaN            text3    1      9  2004
9     NaN      NaN      NaN  unmatching_date    1      1  2016

In [195]: pd.concat([pd.read_csv(f, index_col=None) for f in glob.glob(file_mask)], axis=1, ignore_index=True)
Out[195]:
       0    1     2     3       4    5     6     7      8     9   10  11               12
0  1992.0  1.0   1.0  11.0  1992.0  1.0   1.0  21.0  201.0  1992   1   1            text1
1  2013.0  5.0  30.0  11.0  2013.0  5.0  30.0  21.0  202.0  2013   5  30            text2
2  2004.0  9.0   1.0  11.0  2004.0  9.0   1.0  21.0  203.0  2004   9   1            text3
3     NaN  NaN   NaN   NaN     NaN  NaN   NaN   NaN    NaN  2016   1   1  unmatching_date

以下更惯用的解决方案有效，但它改变了列和行/数据的原始顺序：

In [224]: dfs = [pd.read_csv(f, index_col=None) for f in glob.glob(r'd:/temp/test*.csv')]
     ...:
     ...: common_cols = list(set.intersection(*[set(x.columns.tolist()) for x in dfs]))
     ...:
     ...: pd.concat((df.set_index(common_cols) for df in dfs), axis=1).reset_index()
     ...:
Out[224]:
   month  day  year  Direct   Direct  Direct2            File3
0      1    1  1992     11.0    21.0    201.0            text1
1      1    1  2016      NaN     NaN      NaN  unmatching_date
2      5   30  2013     11.0    21.0    202.0            text2
3      9    1  2004     11.0    21.0    203.0            text3