如何读取空格分隔的数据、两行类型、没有固定宽度和大量缺少的值？

Question

如果数据是固定宽度，那么有很多关于如何读取缺少值的空格分隔数据的好信息

• http://jonathansoma.com/lede/foundations-2017/pandas/opening-fixed-width-files/
• Reading space delimited file in Python/Pandas with missing values
• ASCII table with consecutive white-spaces as separators and missing data python pandas

我目前正试图阅读日本气象局的台风历史数据，该数据应该有this format，但实际上没有：

# Header rows:
    5    10   15   20   25   30   35   40   45   50   55   60   65   70   75   80
::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|
AAAAA BBBB  CCC DDDD EEEE F G HHHHHHHHHHHHHHHHHHHH              IIIIIIII

# Data rows:
    5    10   15   20   25   30   35   40   45   50   55   60   65   70   75   80
::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|::::+::::|
AAAAAAAA BBB C DDD EEEE FFFF     GGG     HIIII JJJJ KLLLL MMMM         P

它与美国国家海洋和大气管理局的飓风最佳路径数据非常相似，只是它用逗号分隔，缺少的值是-999或NaN，这简化了数据的读取。此外，日本的数据实际上并不遵循广告的格式。例如，数据行中的列FFFF并不总是具有宽度4。有时它的宽度为3

我必须说，我完全不知道如何将这些数据处理成数据帧。我研究了pd.read_fwf方法，最初它看起来很有希望，直到我发现格式错误的列和两种不同的行类型

我的问题：

我如何清理这些数据并将其放入数据框？我只想找到一个不同的数据集，但老实说，我在其他地方找不到任何全面的台风数据

Answer 1

以下是我最终如何做到这一点的。密钥意识到数据中有两种类型的行，但在每种类型中，列的宽度是固定的：

header_fmt = "AAAAA BBBB  CCC DDDD EEEE F G HHHHHHHHHHHHHHHHHHHH              IIIIIIII"
track_fmt  = "AAAAAAAA BBB C DDD EEEE FFFF     GGG     HIIII JJJJ KLLLL MMMM         P"

所以，事情是这样的。我编写了这两个函数来帮助我重新格式化文本文件int CSV格式：

def get_idxs(string, char):
    idxs = []
    for i in range(len(string)):
        if string[i - 1].isalpha() and string[i] == char:
            idxs.append(i)
    return idxs
    
def replace(string, idx, replacement):
    string = list(string)
    try:
        for i in idx: string[i] = replacement
    except TypeError:
        string[idx] = replacement
    return ''.join(string)

# test it out
header_fmt = "AAAAA BBBB  CCC DDDD EEEE F G HHHHHHHHHHHHHHHHHHHH              IIIIIIII"
track_fmt  = "AAAAAAAA BBB C DDD EEEE FFFF     GGG     HIIII JJJJ KLLLL MMMM         P"

header_idxs = get_idxs(header_fmt, ' ')
track_idxs  = get_idxs(track_fmt, ' ')
print(replace(header_fmt, header_idxs, ','))
print(replace(track_fmt, track_idxs, ','))

在格式字符串上测试函数时，我们看到逗号被放在适当的位置：

AAAAA,BBBB, CCC,DDDD,EEEE,F,G,HHHHHHHHHHHHHHHHHHHH,             IIIIIIII
AAAAAAAA,BBB,C,DDD,EEEE,FFFF,    GGG,    HIIII,JJJJ,KLLLL,MMMM,        P

因此，接下来将这些函数应用于.txt，并使用输出创建一个.csv文件：

from contextlib import ExitStack
from tqdm.notebook import tqdm

with ExitStack() as stack:
    
    read_file  = stack.enter_context(open('data/bst_all.txt', 'r'))
    write_file = stack.enter_context(open('data/bst_all_clean.txt', 'a'))
    
    for line in tqdm(read_file.readlines()):
        if ' ' in line[:8]: # line is header data
            write_file.write(replace(line, header_idxs, ',') + '\n')
        else: # line is track data
            write_file.write(replace(line, track_idxs, ',') + '\n')

下一个任务是向所有行添加标题数据，以便所有行具有相同的格式：

header_cols = ['indicator', 'international_id', 'n_tracks', 'cyclone_id', 'international_id_dup', 
               'final_flag', 'delta_t_fin', 'name', 'last_revision']

track_cols = ['date', 'indicator', 'grade', 'latitude', 'longitude', 'pressure', 'max_wind_speed', 
              'dir_long50', 'long50', 'short50', 'dir_long30', 'long30', 'short30', 'jp_landfall']


data = pd.read_csv('data/bst_all_clean.txt', names=track_cols, skipinitialspace=True)

data.date = data.date.astype('string')

# Get headers. Header rows have variable 'indicator' which is 5 characters long.
headers = data[data.date.apply(len) <= 5] 
data[['storm_id', 'records', 'name']] = headers.iloc[:, [1, 2, 7]]

# Rearrange columns; bring identifiers to the first three columns. 
cols = list(data.columns[-3:]) + list(data.columns[:-3])
data = data[cols]

# front fill NaN's for header data
data[['storm_id', 'records', 'name']] = data[['storm_id', 'records', 'name']].fillna(method='pad')

# delete now extraneous header rows
data = data.drop(headers.index)

这会产生一些格式良好的数据，如：

    storm_id    records name    date        indicator   grade   latitude    longitude
15  5102.0      37.0    GEORGIA 51031900    2           2       67.0        1614
16  5102.0      37.0    GEORGIA 51031906    2           2       70.0        1625
17  5102.0      37.0    GEORGIA 51031912    2           2       73.0        1635

Answer 2

有人可能有同样的问题，并为此创建了一个库，您可以在此处查看： https://github.com/miniufo/besttracks

它还包括一个快速启动笔记本，可以加载相同的数据集

Answer 3

我在这里对你们有点深入，因为我假设你们是以科学的名义做这件事的，如果我能帮助一些试图理解气候变化的人，那么这是一个很好的理由

在查看数据之后，我注意到问题与存储在非规范化结构中的数据有关。有两种方法可以让你从我的头脑中直接解决这个问题。我将展示将文件重新写入另一个文件以加载到pandas或dask中，因为这可能是思考它的最简单的方法（但对于那些在评论中不可避免地会烤到我的人来说肯定不是最有效的方法）

将其视为两个独立的表，具有一对多的关系。1个台风表和另一个台风数据表

一种体面但并非真正有效的方法是将其重写为更好的嵌套结构，如JSON。然后使用它加载数据。请注意两种不同类型的列

步骤1：映射出数据

这里一张桌子里有两张桌子。每个台风都将显示为一行，如下所示：
66666 9119 150 0045 9119 0 6 MIRREILE 19920701

而该台风的记录将跟随该行（将其视为单独的一行：
20080100 002 3 178 1107 994 035 00000 0000 30600 0200

加载文件，将其作为原始行读取。通过使用.readlines() method，我们可以将中的每一行作为列表中的一项读取

# load the file as raw input
with open('./test.txt') as f:
    lines = f.readlines()

现在我们已经读入了，我们需要执行一些逻辑来将一些行与其他行分开。似乎每次有台风记录时，该行前面都会有一个“66666”，所以让我们把它去掉。因此，考虑到我们在一个效率极低的循环中查看每一行，我们可以编写一些if/else逻辑来查看：

if row[:5] == '66666':
   # do stuff
else:
   # do other stuff

现在，这将是一个非常可靠的分离逻辑的方法，这将有助于指导拆分逻辑。现在，我们需要编写一个循环来检查每一行的逻辑：

# initialize list of dicts
collection = []

def write_typhoon(row: str, collection: Dict) -> Dict:
    if row[:5] == '66666':
       # do stuff
    else:
       # do other stuff

# read through lines list from the .readlines(), looping sequentially
for line in lines:
    write_typhoon(line, collection)

最后，我们需要编写一些逻辑，以便在write_typhone（）函数中的if/then循环中以某种方式提取数据。我不想在这里做很多思考，而是选择了我能做的最简单的方法：自己定义fwf元数据。因为“yolo”：

def write_typhoon(row: str, collection: Dict) -> Dict:
    if row[:5] == '66666':
        typhoon = {
            "AA":row[:5],
            "BB":row[6:11],
            "CC":row[12:15],
            "DD":row[16:20],
            "EE":row[21:25],
            "FF":row[26:27],
            "GG":row[28:29],
            "HH":row[30:50],
            "II":row[51:],
            "data":[]
        }
        # clean that whitespace
        for key, value in typhoon.items():
            if key != 'data':
                typhoon[key] = value.strip()
        collection.append(typhoon)
    else:
        sub_data = {
            "A":row[:9],
            "B":row[9:12],
            "C":row[13:14],
            "D":row[15:18],
            "E":row[19:23],
            "F":row[24:32],
            "G":row[33:40],
            "H":row[41:42],
            "I":row[42:46],
            "J":row[47:51],
            "K":row[52:53],
            "L":row[54:57],
            "M":row[58:70],
            "P":row[71:]
        }
        # clean that whitespace
        for key, value in sub_data.items():
             sub_data[key] = value.strip()
        collection[-1]['data'].append(sub_data)
    return collection

好吧，我花了比我愿意承认的时间更长的时间。我不会说谎。给了我写COBOL程序的PTSD闪回

不管怎样，现在我们有了一个很好的嵌套数据结构，它是本机python类型的。乐趣就可以开始了

步骤2：将其加载到可用格式中

为了分析它，我假设你会想在熊猫身上看到它（如果它太大的话，也许是达斯克）。下面是我在这方面的想法：

import pandas as pd
df = pd.json_normalize(
    collection, 
    record_path='data', 
    meta=["AA","BB","CC","DD","EE","FF","GG","HH","II"] 
)

在本question的答案中可以找到一个很好的参考（特别是第二个，而不是选定的一个）

现在就把它们放在一起：

from typing import Dict
import pandas as pd

# load the file as raw input
with open('./test.txt') as f:
    lines = f.readlines()

# initialize  list of dicts
collection = []

def write_typhoon(row: str, collection: Dict) -> Dict:
    if row[:5] == '66666':
        typhoon = {
            "AA":row[:5],
            "BB":row[6:11],
            "CC":row[12:15],
            "DD":row[16:20],
            "EE":row[21:25],
            "FF":row[26:27],
            "GG":row[28:29],
            "HH":row[30:50],
            "II":row[51:],
            "data":[]
        }
        for key, value in typhoon.items():
            if key != 'data':
                typhoon[key] = value.strip()
        collection.append(typhoon)
    else:
        sub_data = {
            "A":row[:9],
            "B":row[9:12],
            "C":row[13:14],
            "D":row[15:18],
            "E":row[19:23],
            "F":row[24:32],
            "G":row[33:40],
            "H":row[41:42],
            "I":row[42:46],
            "J":row[47:51],
            "K":row[52:53],
            "L":row[54:57],
            "M":row[58:70],
            "P":row[71:]
        }
        for key, value in sub_data.items():
             sub_data[key] = value.strip()
        collection[-1]['data'].append(sub_data)
    return collection
    

# read through file sequentially
for line in lines:
    write_typhoon(line, collection)

# load to pandas df using json_normalize
df = pd.json_normalize(
    collection, 
    record_path='data', 
    meta=["AA","BB","CC","DD","EE","FF","GG","HH","II"] 
)
print(df.head(20)) # lets see what we've got!