以列方式转换未结构化数据块(dataframe)

问题描述:

我有一个外部的*.xls文件,我已将其转换为包含以下数据块的*.csv文件:

"Legend number one";;;;Number of items;6
X;-358.6806792;-358.6716338;;;
Y;0.8767189;0.8966855;Avg;;50.1206378
Z;-0.7694626;-0.7520983;Std;;-0.0010354
D;8.0153902;8;Err;;1.010385
;;;;;

有许多街区。

每个块可以包含一些额外的行数据;

"Legend number six";;;;Number of items;19
X;-358.6806792;-358.6716338;;;
Y;0.8767189;0.8966855;Avg;;50.1206378
Z;-0.7654644;-0.75283;Std;;-0.0010354
D;8.0153902;8;Err;;1.010385
A;0;1;Value;;0
B;1;0;;;
;;;;;

结构是这样的:一个新的空行将每个bloc分隔开,这就是我的示例中的“;;;”行。

之后的第一行以块的唯一标识符开头。

似乎每行包含6个元素,如 key1;elem1;elem2;key2;elem3;elem4 最好用两个3元素向量表示 key1;elem1;elem2 和 key2;elem3;elem4 在两条独立的线上。第二个示例:

"Legend number six";;
;;Number of items;19
X;-358.6806792;-358.6716338;
;;
Y;0.8767189;0.8966855;
Avg;;50.1206378
Z;-0.7654644;-0.75283;
Std;;-0.0010354
D;8.0153902;8;
Err;;1.010385
A;0;1;
Value;;0
B;1;0;
;;
;;;;;

有些是空的,但我暂时不想丢弃它们。 但我希望最终得到一个数据帧,其中包含每个数据块的列元素。

到目前为止最干净的“预溶液”:

有了这段python代码,我最终得到了一个更有条理的“字典列表”:

import os, sys, re, glob
import pandas as pd
csvFile = os.path.join(workingDir,'file.csv')
h = 0 # Number of lines to skip in head
s = 2 # number of values per key
s += 1
str1 = 'Number of items' 

# Reading file in a global list and storing each line in a sublist:
A = [line.split(';') for line in open(csvFile).read().split('\n')]
# This code splits each 6-elements sublist in one new sublist 
# containing two-elements; each element with 3 values:
B = [(';'.join(el[:s])+'\n'+';'.join(el[s:])).split('\n') for el in A] 

# Init empty structures:
names = [] # to store block unique identifier (the name in the legend)
L = [] # future list of dictionnaries

for el in (B):
    for idx,elj in enumerate(el):
        vi = elj.split(';')[1:]
        # Here we grep the name only when the 2nd element of 
        # the first line contains the string "Number of items", 
        # which is constant all over the file:
        if len(vi)>1 and vi[0]==str1:
            name = el[idx-1].split(';')[0]
            names.append(name)
            #print(name)

# We loop again over B to append in a new list one dictionary 
# per vector of 3 elements because each vector of 3 elements 
 # is structured like ; key;elem1;elem2          
for el in (B):
    for elj in (el):
        k = elj.split(';')[0]
        v = elj.split(';')[1:]
        # Little tweak because the key2;elem3;elem4 of the 
        # first line (the one containing the name) have the 
        # key in the second place like "elem3;key2;elem4" :
        if len(v)>1 and v[0]==str1:            
            kp = v[0]
            v = [v[1],k]
            k = kp
        if k!='':
            dct = {k:v}
            L.append(dct)

到目前为止,我未能将名称提取为全局标识符,将bloc的所有值提取为变量。我不能玩一些基于模的技术,因为在每个单独的数据块中信息的数量是可变的,即使所有的块至少包含一些公共密钥。
我也试过 while 条件 for 把每本字典都翻遍了,但现在一团糟。
zip 可能是一个不错的选择,但我不知道如何正确使用它。

目标数据帧:

理想情况下,我希望得到的结果应该类似于包含的数据帧;

index                'Number of items'    'X'    ''  'Y'  'Avg'  'Z'   'Std' ...
"Legend number one"    6                  ...
"Legend number six"   19                  ...
"Legend number 11"     6                  ...
"Legend number 15"    18                  ...

列名是键,表在单独的行中包含每个数据块的值。
如果有一个带编号的索引和一个带有“图例名称”的新列,也可以。

要播放的CSV示例:

"Legend number one";;;;Number of items;6
X;8.6806792;8.6716338;;;
Y;0.1557;0.1556;Avg;;50.1206378
Z;-0.7859;-0.7860;Std;;-0.0010354
D;8.0153902;8;Err;;1.010385
;;;;;
"Legend number six";;;;Number of items;19
X;56.6806792;56.6716338;;;
Y;0.1324;0.1322;Avg;;50.1206378
Z;-0.7654644;-0.75283;Std;;-0.0010354
D;8.0153902;8;Err;;1.010385
A;0;1;Value;;0
B;1;0;;;
;;;;;
"Legend number 11";;;;Number of items;6
X;358.6806792;358.6716338;;;
Y;0.1324;0.1322;Avg;;50.1206378
Z;-0.7777;-0.7778;Std;;-0.0010354
D;8.0153902;8;Err;;1.010385
;;;;;
"Legend number 15";;;;Number of items;18
X;58.6806792;58.6716338;;;
Y;0.1324;0.1322;Avg;;50.1206378
Z;0.5555;0.5554;Std;;-0.0010354
D;8.0153902;8;Err;;1.010385
A;0;1;Value;;0
B;1;0;;;
C;0;0;k;1;0
;;;;;

我使用的是ubuntu和python 3.6,但脚本也必须在windows计算机上运行。