如何使用键将分组列转置为行

Cliff DesPeaux 发表于 Dev

克里夫·德斯波

我希望将列中的数据组转换为行，同时保持唯一键。

这就是我的意思：

当前数据（以下为 CSV）

想要的结果

如果有帮助，tags每个filename.最多（但不一定）25个。

任何帮助，将不胜感激。谢谢！

当前数据的CSV：

filename,tag1,confidence1,tag2,confidence2,tag3,confidence3
filename1,water,0.760709643,green,0.733112454,nature,0.721782982
filename2,interior,0.787028313,room,0.752214372,architecture,0.72691071

罗森菲尔德

您可以使用 Excel 2010+ 中的 Power Query 执行此操作

开始于 Data => Get&Transform => from Text/CSV
选择文件后，Transform在打开的窗口中选择。
去 Home => Advanced Editor
记下第 3 行的正确文件名
- 用下面的 M 代码替换现有代码
- 将第 3 行中的文件名替换为您自己的文件名
检查Applied Steps窗口中的每个步骤和注释以了解其工作原理。

M码

let
    //read in the CSV file
    Source = Csv.Document(File.Contents("C:\Users\ron\Desktop\NewFile.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    //unpivot to reduce to three columns -- filename, attribute and Value
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"filename"}, "Attribute", "Value"),

    //Split off the number from the end of each tag/column
    //That number, in a column by itself, builds a relationship with the filename
  
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", 
            "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    //Group by Filename
    #"Grouped Rows" = Table.Group(#"Split Column by Character Transition", {"filename"}, {{"Grouped", each _, type table [filename=nullable text, Attribute.1=nullable text, Value=any, #"Integer-Division"=number]}}),
    
    //Pivot each grouped table
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Pivot([Grouped],
                        List.Distinct(Table.Column([Grouped],"Attribute.1")),
                        "Attribute.1", "Value")),

    //Remove unneeded columns
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"filename", "Grouped"}),

    //Expand the table, then set the correct data types
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"filename", "tag", "confidence"}, {"filename", "tag", "confidence"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"filename", type text}, {"tag", type text}, {"confidence", type number}})

in
    #"Changed Type"