How to read, groupby and calculate over a large CSV file in Python
You can signup for Databricks Community Edition (for free), in which you can easily have a Spark-ready environment, also easy enough to upload your CSV file.
I have a large CSV file(300mb) with data about accidents based on pincodes/zipcodes. The file has basically header and comma separated values. Key fields are Month, Date, Year, Pincode, Count.
Count represents the accident count for that pincode, however each pincode can get several entries through the day say every few hours. So I want to be able to calculate the max accidents per pincode on a given date i.e I need to group by Month, Date, Year, Pincode and then sum over count after grouping?
I have an idea of how to do this if I loaded the large-ish file into a database or a cloud service such as GCP BigQuery but I want to be able to do this with Python/Pandas dataframes and then store the metrics I am calculating in a table. Is this approach possible with Pandas, if not then possibly PySpark is my last option but that involves the overhead of having to setup a Hadoop etc.
I am open to any other ideas as I am a PyNovice :)
Thank you