Как автоматически суммировать данные столбцов сгруппированного подмножества строк с общими атрибутами (таблица excel csv)

Как автоматически суммировать данные столбцов сгруппированного подмножества строк с общими атрибутами (таблица excel csv)
Как автоматически суммировать данные столбцов сгруппированного подмножества строк с общими атрибутами (таблица excel csv) - stevelieman @ Unsplash

У меня есть большой файл csv (один миллион строк), который я импортировал в excel. Мне нужно сделать простую операцию, которую легко выполнить вручную, но я должен сделать это для тысяч группировок, поэтому я ищу автоматизированную систему для этого.

попробую объяснить: Я сгруппировал строки, которые имеют первые 2 общих столбца данных, те, которые выделены на картинке примера (с атрибутами 6 и 0101). Я называю их исходными строками.

Мне нужно создать новую строку, которая имеет те же 2 значения в первых 2 столбцах (6 и 0101); остальные значения в этой новой строке должны быть суммой каждого столбца исходных строк (должно получиться 1,1,2,0,0,2,0,1.... в примере).

Затем исходные строки, выделенные на рисунке, должны быть УДАЛЕНЫ.

Затем процедуру следует продолжить для следующих групп (7 и 0101), (8 и 0101) и так далее... до конца.

Я не настолько хорошо владею excel (или другим программным обеспечением), чтобы сделать эту операцию автоматически. Я хотел бы знать, есть ли простой метод в excel или это может быть сделано с помощью bash скрипта o загрузке csv файла в программное обеспечение базы данных, его обработке и извлечении нового csv файла. Любое простое решение было бы интересно.

Надеюсь, мой английский понятен, большое спасибо за помощь!

пожалуйста, помните, что я новичок... проще - лучше! :)

пример фото

пример использования Power Query, доступного в Windows Excel 2010+ и Office 365

Чтобы использовать Power Query

  • Data => Get&Transform => from Text/CSV и выберите файл => Преобразование
  • Когда откроется редактор PQ Editor: Home => Advanced Editor.
  • Обратите внимание на путь во второй строке кода
  • Вставьте приведенный ниже M-код на место того, что вы видите
  • Измените имя пути во второй строке на то, которое было сгенерировано изначально.
  • Читайте комментарии и изучайте Applied Steps, чтобы понять алгоритм.
  • Способ кодирования должен адаптироваться к любому количеству столбцов

M Код

let

//Read in CSV document and convert to table
    csvSource = Csv.Document(File.Contents("C:\users\ron\desktop\test.csv"),[Delimiter = ",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Source = Table.PromoteHeaders(csvSource,[PromoteAllScalars = true]),

//type first two columns as Text and
//type all columns after first two as integers (but could change to floats if need be
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(List.FirstN(Table.ColumnNames(Source),2), each {_, Text.Type}) &
        List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),2), each {_, Int64.Type})),

//create dynamic list of Sum functions for the Table.Group command
//we will sum all columns except the first two
//so need to create a list of a sum function for each column in the table
colsToSum= List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),2),
sumFunction = List.Transform(colsToSum, 
        (C) => {C, each List.Sum(Table.Column(_, C)), type number}),

//Group on first two columns and aggregate with Sum
groupSum = Table.Group(#"Changed Type",
                List.FirstN(Table.ColumnNames(#"Changed Type"),2), 
                sumFunction)

in
    groupSum

Источник в виде документа csv

col1,col2,col3,col4,col5
1,0101,0,1,0
1,0101,0,2,1
1,0101,0,3,0
1,0101,1,4,1
2,0101,0,5,0
2,0101,0,6,1
2,0102,1,7,0
2,0102,0,8,1
3,0101,0,1,0
3,0101,0,2,1
3,0101,0,3,0
3,0101,1,4,1
4,0101,0,5,0
4,0101,0,6,1
4,0101,0,7,0

Результаты


NevaDev, 1 февраля 2023 г., 13:36