Hello together, I am trying to create hourly means from an excel / csv data set.
Up to now, I have been splitting my date and hour column so I can sort by hourly values. I think I could group identical hourly values and then calculate the mean. However, I’m not getting anywhere with that approach. Does anyone have experience with this or know of a better way? The time intervals in the data vary (not always 5 minutes), and of course the hour values repeat each day (even if not in this example sheet). I would appreciate any hints! I’ve attached my script and a csv file.
in your eventually complete csv data set, you will have readings for many days, taken at different times
and I guess times will not necessarily be exactly the same for different days
I think you might want to organize data “by day” (using the date format at the beginning of your data) and then “by hour” (taking just the two digits before the “:” that separates hours and minutes)
I see that for each day and for each time, you have 7 different readings (A to H): do those readings need to be kept separate, meaning they correspond to different -for instance- areas, so As have to average only with other As? or they do average all together?
would you mind posting a csv that contains mixed readings for different dates at different times?
I had to do some rather ugly maths on the date/time data because Create Set won’t accept DateTimes, but I think this works. Please check with more data.
great thanks for your interest, time, and help! Tom, your solution works perfectly
Now, I tried to write everything back to an Excel or csv sheet. Unfortunately I dont get how to organize the list so that it can be written. At the moment, it doesnt work yet.
I think its a really simple problem, but I am a complete beginner in this part of grasshopper - sorry about that!
I’ve bolted this on to my existing definition. I might have made some different decisions if I’d known we were aiming for CSV output so there’d be less data mangling, but it works well enough
If your Excel uses the same locale as the OP’s then yes, but if you have dots as decimal points and commas as thousands separators you have to go through a lot of faff to work with their data. Whether it all still works if you share the spreadsheet with someone in a different locale I don’t know. It does manage to use commas and semi-colons correctly on export without further intervention, though.
Hi Tom, thats right. I also started working with Pivot tables in Excel. However, Excel always had a problem with the date/time format and grouped my hours as 1, 10, 11, etc., rather than in the correct chronological order. And since I now work much more with Grasshopper, I thought about solving the problem there. However, Im curious to see how it works with larger amounts of data.
