Group Abstract Group Abstract

Message Boards Message Boards

0
|
2.7K Views
|
7 Replies
|
4 Total Likes
View groups...
Share
Share this post:

How do I merge rows with same row name in a dataset?

Tobi Babatunde
Posted 3 years ago

I have a file with a similar structure to figure 1 below, how do I combine the data with the same row names (cylinder type) to look like figure 2 such that every data with the same row name are summed together? enter image description here
POSTED BY: Tobi Babatunde
7 Replies
Sort By:
Eric Rimbey
Eric Rimbey
Posted 3 years ago
POSTED BY: Eric Rimbey
Tobi Babatunde
Tobi Babatunde
Posted 3 years ago

Thanks a lot for the insight, this was very helpful.
POSTED BY: Tobi Babatunde
Eric Rimbey
Eric Rimbey
Posted 3 years ago

For simplicity, let's say your dataset looks like this:

origData =
 Dataset[
  {<|"Thing" -> "A", "Type" -> "4 cyl", "2019" -> 1, "2020" -> 4|>,
   <|"Type" -> "6 cyl", "2019" -> 1, "2020" -> 2|>,
   <|"Thing" -> "B", "Type" -> "4 cyl", "2019" -> 3, "2020" -> 4|>,
   <|"Type" -> "6 cyl", "2019" -> 0, "2020" -> 2|>,
   <|"Thing" -> "C", "Type" -> "4 cyl", "2019" -> 2, "2020" -> 3|>,
   <|"Type" -> "6 cyl", "2019" -> 6, "2020" -> 4|>}]

Then maybe you want something like this:

origData[GroupBy["Type"], Total][All, {"2019", "2020"}]

enter image description here
POSTED BY: Eric Rimbey
Eric Rimbey
Eric Rimbey
Posted 3 years ago

Remembered that the operations can be combined:

origData[GroupBy["Type"], Total, {"2019", "2020"}]
POSTED BY: Eric Rimbey
Tobi Babatunde
Tobi Babatunde
Posted 3 years ago

Thanks for the reply Eric, however, it's difficult to carry out this operation on the dataset I have because it's not an association list. I tried to convert the lists of list to an association but the repeated row names it isn't making it any easier.
POSTED BY: Tobi Babatunde
Eric Rimbey
Eric Rimbey
Posted 3 years ago

Show us the format you have. Your picture looks like a spreadsheet. That should be easy to export as csv. That in turn would be easy to import as Dataset.
POSTED BY: Eric Rimbey
Tobi Babatunde
Tobi Babatunde
Posted 3 years ago

Hi Eric, I've attached the excel file. Thanks.

Attachments:
Data.xlsx
POSTED BY: Tobi Babatunde
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard