我的目標(biāo)是按“患者”列分組，并將每個(gè)患者輸出為一行 =，然后依次從我的輸入文件中輸出多列。理想情況下，我更喜歡在列標(biāo)題中有一個(gè)計(jì)數(shù)器。這是我的輸入文件示例：Patient Test panel gene alteration1 A 54 APC E1345*1 B 54 TP53 Y205H1 C 54 APC V2278V2 A 54 KRAS G12D2 B 54 PTEN L25L3 A 54 KRAS G13D3 C 54 TP53 C141W3 C 54 APC R876* 3 A 54 ERBB2 L663P 按“患者”列分組的預(yù)期輸出，然后循環(huán)“測(cè)試”、“基因”和“改變”列以創(chuàng)建以下內(nèi)容：Patient Test gene alteration Test gene alteration Test gene alteration Test gene alteration Test gene alteration1 A APC E1345* B TP53 Y205H C TP53 Y205H 2 A KRAS G12D B PTEN L25L 3 A KRAS G13D C TP53 C141W C APC R876* A ERBB2 L663P A ERBB2 L663P理想情況下，#Tests/gene/alteration ie Test_1gene_1alteration_1 等會(huì)很好。但是，我意識(shí)到這讓事情變得復(fù)雜。這是我嘗試過(guò)的，但我無(wú)法獲得感興趣的輸出df = pd.read_table(args.md, sep="\t")df=pd.DataFrame(df) #I used an input file values=grouped['gene'].apply('\t'.join).reset_index()這個(gè)函數(shù)的輸出 1) 沒(méi)有讓我組合超過(guò) 'gene' 列，所以如果我使用 ['gene', 'Test'] 它不會(huì)給出所需的輸出 2) 加入 '\t' 會(huì)從字面上看輸出為 '\t' 而不是制表符然后我嘗試了grouped=df.groupby('Patient')print groupedvalues=grouped['gene'].apply('\t'.join).reset_index()print valuesid_df = grouped['Test'].apply(lambda x: pd.Series(x.values)).unstack()id_df = id_df.rename(columns={i: 'Test{}'.format(i + 1) for i in range(id_df.shape[1])})result = pd.concat([id_df, values], axis=1)print(result)第二次嘗試的結(jié)構(gòu)不符合我的需要，但它確實(shí)為我提供了一個(gè)計(jì)數(shù)器我想知道是否有人可以提供一些見(jiàn)解來(lái)獲得所需的輸出。我通過(guò)查看 SO 使用了上述命令，但無(wú)法進(jìn)行故障排除。
2 回答
慕田峪7331174
TA貢獻(xiàn)1828條經(jīng)驗(yàn) 獲得超13個(gè)贊
下面是可能的解決方案之一。也許不是超級(jí)優(yōu)雅，但有效。
grouped = df.groupby('Patient')
col = ['Patient']
data = []
for p, g in grouped:
d = {'Patient': p}
g.reset_index(inplace=True)
for i, row in g.iterrows():
for c in range(2, len(g.columns)):
col_name = g.columns[c] + '_' + str(i + 1)
d[col_name] = row[g.columns[c]]
if col_name not in col:
col.append(col_name)
data.append(d)
df = pd.DataFrame(data, columns=col)
慕姐4208626
TA貢獻(xiàn)1852條經(jīng)驗(yàn) 獲得超7個(gè)贊
一種使用melt,groupby和的方法unstack：
數(shù)據(jù)
原來(lái)的
In []: df
Out[]:
Patient Test panel gene alteration
0 1 A 54 APC E1345*
1 1 B 54 TP53 Y205H
2 1 C 54 APC V2278V
3 2 A 54 KRAS G12D
4 2 B 54 PTEN L25L
5 3 A 54 KRAS G13D
6 3 C 54 TP53 C141W
7 3 C 54 APC R876*
8 3 A 54 ERBB2 L663P
整齊的數(shù)據(jù)
pd.DataFrame.melt 允許整理這張表：
In []: tidy = df.melt(id_vars=['Patient', 'Test'], value_vars=['panel', 'gene', 'alteration'])
In []: tidy
Out[]:
Patient Test variable value
0 1 A panel 54
1 1 B panel 54
2 1 C panel 54
3 2 A panel 54
4 2 B panel 54
5 3 A panel 54
6 3 C panel 54
7 3 C panel 54
8 3 A panel 54
9 1 A gene APC
10 1 B gene TP53
11 1 C gene APC
12 2 A gene KRAS
13 2 B gene PTEN
14 3 A gene KRAS
15 3 C gene TP53
16 3 C gene APC
17 3 A gene ERBB2
18 1 A alteration E1345*
19 1 B alteration Y205H
20 1 C alteration V2278V
21 2 A alteration G12D
22 2 B alteration L25L
23 3 A alteration G13D
24 3 C alteration C141W
25 3 C alteration R876*
26 3 A alteration L663P
重塑
使用 goupby 和 unstack
In []: (tidy.groupby(['Patient', 'Test', 'variable']) # group by three levels of interest
...: .first() # access values as a dataframe
...: .unstack(level=[1,2])) # pivot on levels [1, 2] of multiindex
Out[]:
value
Test A B C
variable alteration gene panel alteration gene panel alteration gene panel
Patient
1 E1345* APC 54 Y205H TP53 54 V2278V APC 54
2 G12D KRAS 54 L25L PTEN 54 NaN NaN NaN
3 G13D KRAS 54 NaN NaN NaN C141W TP53 54
使用交叉表
這給出了等效的結(jié)果：
In []: pd.crosstab(tidy.Patient, # index
[tidy.Test, tidy.variable], # columns
values=tidy.value,
aggfunc='first') # get first value
Out[]:
Test A B C
variable alteration gene panel alteration gene panel alteration gene panel
Patient
1 E1345* APC 54 Y205H TP53 54 V2278V APC 54
2 G12D KRAS 54 L25L PTEN 54 NaN NaN NaN
3 G13D KRAS 54 NaN NaN NaN C141W TP53 54
添加回答
舉報(bào)
0/150
提交
取消