excel 使用Python(或Power Query)将数据从不同的行转置到同一列,但间隔一定

x7rlezfr  于 2023-11-20  发布在  Python
关注(0)|答案(2)|浏览(105)

所以,我有一个.xlsx数据集,我称之为“源”数据集。“源”数据集有数百行关于每个物种昆虫数量的信息。数据集每列的标题都有每个物种的名称。我需要将此数据转置到另一个数据集的同一列,也是一个.xlsx文件,我称之为“命运”数据集。
我需要创建一个循环来执行以下操作:
1.从单元格“A2”开始,迭代“源”数据集每行的列“A”,并验证是否有数据。
1.如果单元格“A2”中有数据,则将整个信息从单元格“B2”复制到单元格“E2”。
1.将复制的数据转置到“destiny”数据集的“C”列,从单元格“C2”开始
1.然后,在下一次迭代中,验证源数据集的单元格“A3”中是否有数据,并将整个信息从单元格“B3”复制到单元格“E3”。
1.然后将复制的数据转置到“destiny”数据集的列“C”,但这次从单元格“C2”下面的7个单元格开始,也就是说,将其转置到单元格“C8”。
1.并且在下一次迭代中,总是将来自“源”数据集的数据转置到最后转置的数据之下7个单元。
我知道这有点令人困惑,但下面的例子应该澄清:
“源”数据集看起来像这样:

Point|Spongillidae|Olindiidae|Hydridae|Oceaniidae|
:------|---------:|---------:|-------:|---------:|
 MK1   |3         |          |1       |5         |
 RT2   |2         |7         |        |          |
 GT3   |10        |1         |6       |15        |

字符串
当前的“命运”数据集目前看起来像这样:

Point|  Species   |Value |
:----|:----------:|-----:|
 MK1 |Spongillidae|      |
 MK1 |Olindiidae  |      |
 MK1 |Hydridae    |      |
 MK1 |Oceaniidae  |      |
 MK1 |Colletor    |TG    |
 MK1 |Habitat     |Edge  |
 RT2 |Spongillidae|      |
 RT2 |Olindiidae  |      |
 RT2 |Hydridae    |      |
 RT2 |Oceaniidae  |      |
 RT2 |Colletor    |DG    |
 RT2 |Habitat     |Riffle|
 GT3 |Spongillidae|      |
 GT3 |Olindiidae  |      |
 GT3 |Hydridae    |      |
 GT3 |Oceaniidae  |      |
 GT3 |Colletor    |JB    |
 GT3 |Habitat     |Riffle|


最后,“命运”数据集应该是这样的:

Point|  Species   |Value |
:----|:----------:|-----:|
 MK1 |Spongillidae|3     |
 MK1 |Olindiidae  |      |
 MK1 |Hydridae    |1     |
 MK1 |Oceaniidae  |5     |
 MK1 |Colletor    |TG    |
 MK1 |Habitat     |Edge  |
 RT2 |Spongillidae|2     |
 RT2 |Olindiidae  |7     |
 RT2 |Hydridae    |      |
 RT2 |Oceaniidae  |      |
 RT2 |Colletor    |DG    |
 RT2 |Habitat     |Riffle|
 GT3 |Spongillidae|10    |
 GT3 |Olindiidae  |1     |
 GT3 |Hydridae    |6     |
 GT3 |Oceaniidae  |15    |
 GT3 |Colletor    |JB    |
 GT3 |Habitat     |Riffle|


我希望有人能帮助我找到解决这个问题的方法。

f87krz0w

f87krz0w1#

这也可以使用Windows Excel 2010+和Microsoft 365(Windows或Mac)中提供的Power Query来完成
使用Power Query

  • 选择数据表中的某个单元格
  • 第一个月
  • PQ编辑器打开时:Home => Advanced Editor
  • 记下第2行中的表名称
  • 将下面的M代码粘贴到您所看到的位置
  • 将第2行中的Table名称更改回最初生成的名称。
  • 阅读评论并探索Applied Steps以了解算法
  • 来源与命运 *


的数据

  • M代码 *
let

//Read in Source Table
//  Change depending on your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Point", type text}} & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1), each {_, Int64.Type})), 

//Unpivot to produce a three column table
    #"Unpivot Source" = Table.UnpivotOtherColumns(#"Changed Type",{"Point"},"Species","Value"),

//Read in Destiny Table
//  Change depending on your actual data source
    Source2 = Excel.CurrentWorkbook(){[Name="Destiny"]}[Content],

//Add Index Column for Sorting
    #"Added Index" = Table.AddIndexColumn(Source2, "Index", 0, 1, Int64.Type),
    #"Destiny Typed" = Table.TransformColumnTypes(#"Added Index",{
        {"Point", type text}, {"Species", type text},{"Value", type any}}),

//Join the two tables
    joined = Table.NestedJoin(#"Destiny Typed",
        {"Point","Species"},#"Unpivot Source",{"Point","Species"},"Join",JoinKind.FullOuter),

//Replace the null Values
    #"Replace null Values" = Table.ReplaceValue(
        joined,
        each [Value],
        each if [Value] = null then [Join][Value]{0} else [Value],
        Replacer.ReplaceValue,
        {"Value"}
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Replace null Values",{"Join"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

字符串

  • 结果 *


  • 注意:* 实际上,您可以从一个包含ColletorHabitat的源表开始,然后生成整个Destiny表。

例如,如果您的Source表看起来像:



然后一个简单的UnPivot将产生Destiny表:

et

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Source14"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Point", type text}, {"Colletor", type text}, {"Habitat", type text}}
        & List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),3), each {_, Int64.Type})),

//Unpivot all except the Point Column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Point"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"


生产=>



如果这是可行的,代码可以很容易地修改,以包括“空白”条目以及所需的排序顺序。

qvtsj1bj

qvtsj1bj2#

请确保您的初始数据从Sheet 1的左上角开始(A1=“点”)。
在Excel中创建一个宏,并将其命名为Sub change_format(),然后将以下代码粘贴在“Sub change_format()”和“End sub”之间。然后运行它,您将在Sheet 2中获得结果。确保保存您的文件为.xlsm格式!!

i = 1 'First row of Sheet1 for reading
r = 2 'First row of Sheet2 for writing
   
'Find number of Points

While Sheet1.Cells(i, 1) <> ""
    
    i = i + 1

Wend

pnt = i - 2

For p = 1 To pnt 'Points
    For s = 2 To 7 'Species
                 
    cur_type = Sheet1.Cells(p + 1, 1) 'Current type
    cur_spc = Sheet1.Cells(1, s)      'Current species
    cur_val = Sheet1.Cells(p + 1, s)  'Current value
    
    Sheet2.Cells(1, 1) = "Point"
    Sheet2.Cells(1, 2) = "Species"
    Sheet2.Cells(1, 3) = "Value"

    Sheet2.Cells(r, 1) = cur_type
    Sheet2.Cells(r, 2) = cur_spc
    Sheet2.Cells(r, 3) = cur_val
    r = r + 1
    
    Next s
    
Next p

字符串

相关问题