r/pythonhelp • u/joseb • Jul 20 '23
Group data in CSV file
I have a CSV file with the following
| + | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Store Number | Device Name | Serial Number | Media Name | Life Counter_1 | Life Counter_2 | Life Counter Difference |
| 2 | 13 | DRS80 D | xxxxx | 8x10 | 30897 | 31460 | 563 |
| 3 | 13 | SSDS6801 | xxxxx | 4x6(a5) | 236995 | 240200 | 3205 |
| 4 | 13 | SDS6801 | xxxxx | 5x7 | 78833 | 79310 | 477 |
| 5 | 13 | SDS4801 | xxxxx | 4x8 | 71325 | 71683 | 358 |
| 6 | 14 | SDS6223 | xxxxx | 5x7 | 6763 | 6847 | 84 |
| 7 | 14 | SDS6223 | xxxxx | 6x8 | 68448 | 70513 | 2065 |
| 8 | 14 | SDS805 | xxxxx | 8x10 | 37013 | 37242 | 229 |
| 9 | 14 | SDS6223 | xxxxx | 6x8 | 54892 | 56155 | 1263 |
| 10 | 14 | SDS6223 | xxxxx | 6x8 | 61674 | 63655 | 1981 |
| 11 | 14 | SDS6223 | xxxxx | 6x8 | 66583 | 68155 | 1572 |
| 12 | 14 | SDS6223 | xxxxx | 5x7 | 6617 | 6741 | 124 |
| 13 | 23 | SDS6801 | xxxxx | 5x7 | 241573 | 241681 | 108 |
| 14 | 23 | SDS6801 | xxxxx | 5x7 | 41800 | 41891 | 91 |
| 15 | 23 | SDS6801 | xxxxx | 4x6(a5) | 232755 | 234102 | 1347 |
| 16 | 23 | SDS4801 | xxxxx | 4x8 | 29360 | 29455 | 95 |
| 17 | 23 | DRS80 D | xxxxx | 8x10 | 75013 | 75168 | 155 |
| 18 | 23 | SDS6801 | xxxxx | 4x6(a5) | 172003 | 172745 | 742 |
| 19 | 30 | SDS6223 | xxxxx | 6x8 | 30519 | 31800 | 1281 |
| 20 | 30 | SDS805 | xxxxx | 8x10 | 24840 | 24982 | 142 |
| 21 | 30 | SDS6223 | xxxxx | 6x8 | 25440 | 26920 | 1480 |
| 22 | 30 | SDS6223 | xxxxx | 6x8 | 35097 | 36715 | 1618 |
| 23 | 30 | SDS6223 | xxxxx | 5x7 | 21908 | 22208 | 300 |
What I would like to do is group the data by store number AND media name and then add up all the values for each combined media type using the initial values in the Life Counter Difference column
The desired output would be similar to below
| + | A | B | C |
|---|---|---|---|
| 1 | Store Number | Media Name | Totals |
| 2 | 13 | 8x10 | 563 |
| 3 | 13 | 4x6(a5) | 3205 |
| 4 | 13 | 5x7 | 477 |
| 5 | 13 | 4x8 | 358 |
| 6 | 14 | 5x7 | 208 |
| 7 | 14 | 6x8 | 6881 |
| 8 | 14 | 8x10 | 229 |
| 9 | 23 | 4x6(a5) | 2089 |
| 10 | 23 | 5x7 | 199 |
| 11 | 23 | 8x10 | 155 |
| 12 | 23 | 4x8 | 95 |
| 13 | 30 | 6x8 | 4379 |
| 14 | 30 | 5x7 | 300 |
| 15 | 30 | 8x10 | 142 |
Any idea how I could go about this?
2
Upvotes
3
u/joseb Jul 21 '23
Ahh I think I figured it out.
I ran the following
df1 = df1.groupby(["Store Number", 'Media Name'])['Life Counter Difference'].sum()And it output the desired result. I think it's OK but any thoughts on different methods would be appreciated.
•
u/AutoModerator Jul 20 '23
Note:
This sub went private for a few days recently in solidarity with other subs who are hoping to get Reddit to reconsider some changes that they have proposed. These changes will affect the Reddit API and many third-party apps that access Reddit. If you are not already aware of the proposed changes, please read up on the topic and the ongoing protest.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.