将CSV从宽格式转换为长格式 [英] Transforming a CSV from wide to long format

问题描述

我有这样的csv:

col1,col2,col2_val,col3,col3_val
A,1,3,5,6
B,2,3,4,5

并且我想像这样传输此csv:

and i want to transfer this csv like this :

col1,col6,col7,col8
A,Col2,1,3
A,col3,5,6

有col3和col3_val，所以我想在存储col3值的同一行中将col3保留在col6中，将col3的值保留在col7中，将col3_val的值保留在col8中.

there are col3 and col3_val so i want to keep col3 in col6 and values of col3 in col7 and col3_val's value in col8 in the same row where col3's value is stored.

推荐答案

我认为您正在寻找的是df.melt和df.groupby:

I think what you're looking for is df.melt and df.groupby:

In [63]: df.rename(columns=lambda x: x.strip('_val')).melt('col1')\
           .groupby(['col1', 'variable'], as_index=False)['value'].apply(lambda x: pd.Series(x.values))\
           .add_prefix('value')\
           .reset_index()
Out[63]: 
  col1 variable  value0  value1
0    A     col2       1       3
1    A     col3       5       6
2    B     col2       2       3
3    B     col3       4       5

_{向 John Galt 致谢，以获取第二部分的帮助.}

_{Credit to John Galt for help with the second part.}

如果要重命名列，请将上面的整个表达式分配给df_out，然后执行:

If you wish to rename columns, assign the whole expression above to df_out and then do:

df_out.columns = ['col1', 'col6', 'col7', 'col8']

使用df.to_csv保存这一点应该很简单.

Saving this should be straightforward with df.to_csv.

这篇关于将CSV从宽格式转换为长格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！