Skip to main content
 首页 » 编程设计

python之Pandas 读取列长度不均匀的文件

2024年12月31日9zhenyulu

我正在尝试读取如下所示的放电数据文件:

Station number: 420 
Location:       Kotagaon Shringe                                                   
Latitude: 27 45 00 
River:          Kali Gandaki                                                      
Longitude: 84 20 50 
 
Year:           2001 
 
                                Mean daily discharge in m3/s 
                                ============================ 
 
Day     Jan.   Feb.   Mar.   Apr.   May    Jun.   Jul.   Aug.   Sep.   Oct.   Nov.   Dec.   Year 
 01      118   99.3   85.9   75.5    119    182    656   2790   1690    402    232    158 
 02      123   97.4   82.9   74.3    134    251    514   2420   2180    397    230    158 
 03      118   95.5   80.7   73.1    168    377    466   2190   2190    386    226    157 
-------------------------------- Skipping some rows of no real interest 
 25     95.5   85.5   70.7   83.3    163    583    898   3230    485    257    177    123 
 26     94.1   88.6   69.9   84.6    167    579    996   2330    474    252    175    121 
 27     92.2   88.6   71.9   88.1    166    736   1180   2270    461    248    173    120 
 28     91.8   87.3   69.9   91.3    172    419   1020   2270    431    246    168    118 
 29     95.5          71.9   93.2    165    446   1670   2140    410    244    163    118 
 30     98.4          76.0    109    176    575   2040   2100    403    239    159    117 
 31     98.4          75.1           174          3330   1600           234           117 

我的问题是,当使用空格作为分隔符时,它确实会在第 29 天超过 3 月的值,因为 2 月没有第 29 天。对于其他具有空值/无值的地方也是如此。

有解决这个问题的好方法吗?

我在网上寻找解决方案,但我能找到的只是处理不均匀的行长度,而不是不均匀的列长度。

到目前为止,我的尝试产生了代码:

disc = pd.read_csv(filename,header = 6,sep = '\s+',nrows = 31) 
disc['Year'] = 2001 

数据框看起来像:

   Day  Jan.    Feb.    Mar.    Apr.    May Jun.    Jul.    Aug.    Sep.    Oct.    Nov.    Dec.    Year 
0   1   118.0   99.3    85.9    75.5    119 182     656   2790.0    1690.0  402.0   232.0   158.0   2001 
1   2   123.0   97.4    82.9    74.3    134 251     514   2420.0    2180.0  397.0   230.0   158.0   2001 
2   3   118.0   95.5    80.7    73.1    168 377     466   2190.0    2190.0  386.0   226.0   157.0   2001 
----------------------------------------------- Skipping some rows of no real interest 
28  29  95.5    71.9    93.2    165.0   446 1670    2140    410.0   244.0   163.0   118.0   NaN     2001 
29  30  98.4    76.0    109.0   176.0   575 2040    2100    403.0   239.0   159.0   117.0   NaN     2001 
30  31  98.4    75.1    174.0   3330.0  1600 234     117      NaN NaN        NaN    NaN     NaN     2001 

请您参考如下方法:

您可以使用 pd.read_fwf() 模块读取固定宽度的文件并利用 skiprows 关键字:

disc = pd.read_fwf('test.csv', skiprows=11) 

产量:

   Day   Jan.  Feb.  Mar.   Apr.  ...     Sep.  Oct.   Nov.  Dec.  Year 
0    1  118.0  99.3  85.9   75.5  ...   1690.0   402  232.0   158   NaN 
1    2  123.0  97.4  82.9   74.3  ...   2180.0   397  230.0   158   NaN 
2    3  118.0  95.5  80.7   73.1  ...   2190.0   386  226.0   157   NaN 
3   25   95.5  85.5  70.7   83.3  ...    485.0   257  177.0   123   NaN 
4   26   94.1  88.6  69.9   84.6  ...    474.0   252  175.0   121   NaN 
5   27   92.2  88.6  71.9   88.1  ...    461.0   248  173.0   120   NaN 
6   28   91.8  87.3  69.9   91.3  ...    431.0   246  168.0   118   NaN 
7   29   95.5   NaN  71.9   93.2  ...    410.0   244  163.0   118   NaN 
8   30   98.4   NaN  76.0  109.0  ...    403.0   239  159.0   117   NaN 
9   31   98.4   NaN  75.1    NaN  ...      NaN   234    NaN   117   NaN