06-17-2019 04:12 PM
I need to consolidate rows in a .csv file so that multiple rows from the same user are consolidated into a single row.
Input data:
Key,Person,Amount
P-1,Bill,3.00
B-3,Joe,7.50
G-2,Jane,4.55
P-1,Bill,7.00
P-1,Bill,10.00
G-2,Jane,5.45
I need the output to add the 3rd column together and make it into a single row
Expected result:
Key,Person,Amount
P-1,Bill,20.00
B-3,Joe,7.50
G-2,Jane,10.00
06-17-2019 04:48 PM
If you have LabVIEW 2019, a map (brand new datatype!) with key=user or key, value=array of DBL.
(Since you did not mention your version yet, we need to wait. Maybe something based on Variant attributes could be done for earlier LabVIEW versions. How big is the typical dataset? Is the key and person always matched?)
06-17-2019 05:32 PM
My bad, I'm using LV2016
Key is indeed always tied to Name
Typical number of entries to sort is less than 100 rows
06-18-2019 06:41 AM
Make an empty array of names and one for the other items.
Make a for loop, with a shift register for each array.
In the for loop, search each entry against the name array.
If it's there (use an in place element structure to) get the current value, add the new value and replace the element.
If it's not there, add the name to the array, and the values to the other arrays.
06-18-2019 11:02 AM
I just installed LV2019
Now, you mentioned this MAP data type?
06-18-2019 11:51 AM
@Murph wrote:
I just installed LV2019
Now, you mentioned this MAP data type?
Yes!
(If you want the keys in original order, some changes would need to be made.)
06-18-2019 01:56 PM - edited 06-18-2019 02:00 PM
Awesome!
I took a more painful but successful route.
06-18-2019 01:59 PM
Well, now you know why maps are so great 😄
(even though I see quite a bit of room for improvements in your code ;))
06-18-2019 02:01 PM - edited 06-18-2019 02:02 PM
I am of the many who are not programmers, just guys trying to get something done 🙂
Improvements to my code are most welcome!
06-18-2019 02:33 PM - edited 06-18-2019 02:38 PM
The MAP version is definitely preferred for large dataset, because the lookup is much more efficient. Just for kicks, here's a "noMap" version. This one keeps the element in order of first occurrence. There are many ways to do this, of course. 😉