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

Question

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

我的意思是：

当前数据（以下为 CSV）

期望结果

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

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

当前数据的 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

Answer 1

您可以使用 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"