我們有來(lái)自測(cè)量多個(gè)部件的設(shè)備的數(shù)據(jù)，并將每個(gè)部件的多個(gè)測(cè)量結(jié)果輸出到一個(gè) CSV 文件中。我們將 CSV 文件讀入具有如下結(jié)構(gòu)的數(shù)據(jù)幀：PartNo 12Meas1 45Meas2 23!ENDPartNo 13Meas1 63Meas2 73!ENDPartNo 12Meas1 82Meas2 84!END“!END”標(biāo)志表示一個(gè)部分的數(shù)據(jù)在哪里結(jié)束，下一個(gè)部分從哪里開始。我們想重塑數(shù)據(jù)，使其看起來(lái)像：PartNo Meas1 Meas212 45 2313 63 7312 82 84（請(qǐng)注意，一個(gè)部分可能出現(xiàn)不止一次 - 因此沒有字段保證在所有記錄中都是唯一的。）樞軸產(chǎn)生：0 !END Meas1 Meas2 PartNo0 NaN NaN NaN 12.01 NaN 45.0 NaN NaN2 NaN NaN 23.0 NaN3 NaN NaN NaN NaN4 NaN NaN NaN 13.05 NaN 63.0 NaN NaN6 NaN NaN 73.0 NaN7 NaN NaN NaN NaN8 NaN NaN NaN 12.09 NaN 82.0 NaN NaN10 NaN NaN 84.0 NaN11 NaN NaN NaN NaN我如何壓縮這些行以按 PartNo 分組？轉(zhuǎn)置產(chǎn)生： 0 1 2 3 4 5 6 7 8 9 10 110 PartNo Meas1 Meas2 !END PartNo Meas1 Meas2 !END PartNo Meas1 Meas2 !END1 12 45 23 NaN 13 63 73 NaN 12 82 84 NaN我怎樣才能每第 4 個(gè)項(xiàng)目重置該行？我可以在原始數(shù)據(jù)框中創(chuàng)建一個(gè)新的索引列，然后遍歷行，使用 !END 為每一行遞增索引（然后使用索引對(duì)數(shù)據(jù)進(jìn)行分組），但似乎應(yīng)該有一個(gè)更優(yōu)雅的方法變形函數(shù)來(lái)處理這種情況，或者可能有一個(gè) Pivot 或 Transpose 的參數(shù)可以處理這種情況。我是 Python 初學(xué)者。這是完整的代碼：import pandas as pdfrom io import StringIOtdata = ('PartNo, 12\n''Meas1, 45\n''Meas2, 23\n''!END\n''PartNo, 13\n''Meas1, 63\n''Meas2, 73\n''!END\n''PartNo, 12\n''Meas1, 82\n''Meas2, 84\n''!END\n')tdf = pd.read_csv(StringIO(tdata), header=None)print(tdf)print(tdf.pivot(index=None, columns=0, values=1))print(tdf.T)
4 回答
當(dāng)年話下
TA貢獻(xiàn)1890條經(jīng)驗(yàn) 獲得超9個(gè)贊
#having dataframe x:
>>> x = pd.DataFrame([['PartNo',12],['Meas1',45],['Meas2',23],['!END',''],['PartNo',13],['Meas1',63],['Meas2',73],['!END',''],['PartNo',12],['Meas1',82],['Meas2',84],['!END','']])
>>> x
0 1
0 PartNo 12
1 Meas1 45
2 Meas2 23
3 !END
4 PartNo 13
5 Meas1 63
6 Meas2 73
7 !END
8 PartNo 12
9 Meas1 82
10 Meas2 84
11 !END
#grouping by first column, and aggregating values to list. First column then contains Series that you want. By converting each list in this series to series, dataframe is created, then you just need to transpose
>>> df = x.groupby(0).agg(lambda x: list(x))[1].apply(lambda x: pd.Series(x)).transpose()
>>> df[['PartNo','Meas1','Meas2']]
0 PartNo Meas1 Meas2
0 12 45 23
1 13 63 73
2 12 82 84
皈依舞
TA貢獻(xiàn)1851條經(jīng)驗(yàn) 獲得超3個(gè)贊
這是我會(huì)怎么做。我會(huì)將文件解析為任何文本文件，然后根據(jù)我需要的字段創(chuàng)建記錄。我會(huì)使用 '!END' 行作為行創(chuàng)建完成的指示器，將其寫入列表，然后最終將列表轉(zhuǎn)換為 DataFrame
import pandas as pd
filename='PartDetail.csv'
with open(filename,'r') as file:
LinesFromFile=file.readlines()
RowToWrite=[]
for EachLine in LinesFromFile:
ValuePosition=EachLine.find(" ")+1
CurrentAttrib=EachLine[0:ValuePosition-1]
if CurrentAttrib=='PartNo':
PartNo=EachLine[ValuePosition+1:len(EachLine)-1].strip()
if CurrentAttrib=='Meas1':
Meas1=EachLine[ValuePosition+1:len(EachLine)-1].strip()
if CurrentAttrib=='Meas2':
Meas2=EachLine[ValuePosition+1:len(EachLine)-1].strip()
if EachLine[0:4]=='!END':
RowToWrite.append([PartNo,Meas1,Meas2])
PartsDataDF=pd.DataFrame(RowToWrite,columns=['PartNo','Meas1','Meas2']) #Converting to DataFrame
這將為您提供一個(gè)更清晰的 DataFrame，如下所示：-
GCT1015
TA貢獻(xiàn)1827條經(jīng)驗(yàn) 獲得超4個(gè)贊
該文件不是 csv 文件，因此使用 csv 模塊解析它無(wú)法產(chǎn)生正確的輸出。它不是眾所周知的格式，所以我會(huì)使用自定義解析器：
with open(filename) as fd:
data = []
row = None
for line in fd:
line = line.strip()
if line == '!END':
row = None
else:
k,v = line.split(None, 1)
if row is None:
row = {k : v}
data.append(row)
else:
row[k] = v
header = set(i for row in data for i in row.keys())
df = pd.DataFrame(data, columns=header)
墨色風(fēng)雨
TA貢獻(xiàn)1853條經(jīng)驗(yàn) 獲得超6個(gè)贊
根據(jù)提供的信息，我認(rèn)為你應(yīng)該能夠使用這種方法實(shí)現(xiàn)你想要的：
df = df[df[0] != '!END']
out = df.groupby(0).agg(list).T.apply(lambda x: x.explode(), axis=0)
輸出：
0 Meas1 Meas2 PartNo
1 45 23 12
1 63 73 13
1 82 84 12
這基本上按 PartNo、Meas1 和 Meas2 鍵對(duì)原始 df 進(jìn)行分組，并為每個(gè)列表創(chuàng)建一個(gè)列表。然后將每個(gè)列表分解為一個(gè) pd.Series，從而為每個(gè)列表創(chuàng)建一個(gè)列，行數(shù)等于條目數(shù)每個(gè)鍵（都應(yīng)該相同）
添加回答
舉報(bào)
0/150
提交
取消