如何在熊猫中读取非结构化的CSV

预处理

get_names()打开文件功能，检查拆分行的最大长度。然后，我阅读第一行并添加最大长度中的缺失值。

第一行的最后一个值是)，因此我将其删除firstline[:-1]，然后再将丢失的列添加到+1 rng = range(1, m - lenfirstline + 2)。+2是因为范围从值开始1。

然后，您可以使用function read_csv，跳过第一行，并使用name的输出作为名称get_names()。

import pandas as pd
import csv
 
#preprocessing
def get_names():
    with open('test/file.txt', 'r') as csvfile:
        reader = csv.reader(csvfile)
        num = []
        for i, row in enumerate(reader):
            if i ==0:
                firstline = ''.join(row).split()
                lenfirstline = len(firstline)
                #print firstline, lenfirstline
            num.append(len(''.join(row).split()))
        m = max(num)
        rng = range(1, m - lenfirstline + 2)
        #remove )
        rng = firstline[:-1] + rng
        return rng

#names is list return from function
df = pd.read_csv('test/file.txt', sep="\s+", names=get_names(), index_col=[0], skiprows=1)

#temporaly display 10 rows and 30 columns
with pd.option_context('display.max_rows', 10, 'display.max_columns', 30):
    print df
        
       car_type entry_gate entry_time(ms) exit_gate exit_time(ms)  \
car_id                                                              
24          Bus         25           4300        26         48520   
25          Car         25             20        26         45900   
26          Car          -              -         -             -   
27          Car          -              -         -             -   
28          Car          -              -         -             -   

        traveled_dist(m)  avg_speed(m/s)  trajectory(x[m]        y[m]  \
car_id                                                                  
24                118.47        2.678999        509552.78  5039855.59   
25                113.91        2.482746        509583.70  5039848.78   
26                109.68        8.859805        509572.75  5039862.75   
27                119.84        3.075936        509582.73  5039862.78   
28                129.64        4.347466        509591.07  5039862.90   

        speed[m/s]  a_tangential[ms-2]  a_lateral[ms-2]  timestamp[ms]  \
car_id                                                                   
24         10.0740              0.4290           0.2012              0   
25          4.5344             -0.1649           0.2398              0   
26          4.0734             -0.7164          -0.1066              0   
27          1.1910              0.5247           0.0005              0   
28          1.6473              0.1987          -0.0033              0   

                1           2        3       4       5   6          7  \
car_id                                                                  
24      509552.97  5039855.57  10.0821  0.3853  0.2183  20        NaN   
25      509583.77  5039848.71      NaN     NaN     NaN NaN        NaN   
26      509572.67  5039862.76   4.0593 -0.7021 -0.1141  20  509553.17   
27      509582.71  5039862.78   1.2015  0.5322     NaN NaN        NaN   
28      509591.04  5039862.89   1.6513  0.2015 -0.0036  20        NaN   

                 8        9      10      11  12  
car_id                                           
24             NaN      NaN     NaN     NaN NaN  
25             NaN      NaN     NaN     NaN NaN  
26      5039855.55  10.0886  0.2636  0.2356  40  
27             NaN      NaN     NaN     NaN NaN  
28             NaN      NaN     NaN     NaN NaN  

后期处理

首先，您必须估计最大列数N。我知道他们的真实数字是26，所以我估计N = 30。read_csv具有参数name = range(N)返回NaN列的函数，列的估计长度与实际长度之间有什么区别。

下降，您可以选择的列名，其中没有第一行后NaN（我去掉最后一列)的[:-1]） - df1.loc[0].dropna()[:-1]。然后，您可以NaN在第一行中追加范围从1到值长度的新系列。最后第一行被的子集删除df。

#set more as estimated number of columns
N = 30
    
df1 = pd.read_csv('test/file.txt', sep="\s+", names=range(N))

df1 = df1.dropna(axis=1, how='all')  #drop columns with all NaN

df1.columns = df1.loc[0].dropna()[:-1].append(pd.Series(range(1, len(df1.columns) - len(df1.loc[0].dropna()[:-1]) + 1 )))

#remove first line with uncomplete column names
df1 = df1.ix[1:]
print df1.head()