Help with dataframes please

New to Julia
,
1

Hi,

I’ve got a large CSV file I’m trying to analyse in Julia. I can load it into a data frame easily and I’ve got the column types identified correctly.

Format is basically

Location datetime value
area1, 2024-07-20 20:30:05, 20.0
…
area2, 2024-07-20 20:30:07, 60.0
…
area3, 2024-07-20 20:30:30, 20.0
…
area4, 2024-07-20 20:30:22, 660.0

There are 4 areas, and the file contains a whole year of data per area, then moves onto the next area. This does mean there’s a lot of duplication in the datetime column. I don’t actually need accuracy to the level of seconds. This is just how the data is dumped from the network.

What I’d like to do it for each location, produce stats (min, max, mean) for each (Year, month, and week)

I don’t know where to start for this last part - I’d appreciate any help.

1 Like
2
  1. transform the datetime column into multiple columns holding year, month and week of year columns (using functions from the Dates module).
  2. Then e.g. for yearly aggregates (assuming your data is in df data frame and you want yearly aggregates - for other this will be similar - and assuming you put year of observation in :Year column in step 1) do combine(groupby(df, [:Location, :Year]), :value .=> [minimum, maximum, mean). (you need to load Statistics package for this to work).
5 Likes
3

Here are some tips for your question.

using CSV, DataFrames, Dates, Statistics

# Load the CSV file
df = CSV.read("your_file.csv", DataFrame)

# Convert datetime column to DateTime type
df.datetime = DateTime.(df.datetime, dateformat"yyyy-mm-dd HH:MM:SS")

# Create year, month, and week columns
df.year = year.(df.datetime)
df.month = month.(df.datetime)
df.week = week.(df.datetime)

# Group by location, year, month, and week, and calculate
grouped_df = combine(groupby(df, [:Location, :year, :month, :week]),
                     :value => minimum => :min_value,
                     :value => maximum => :max_value,
                     :value => mean => :mean_value)

# View the first few rows
first(grouped_df, 5)
2 Likes
4

Thank you! That was just what I needed.

1 Like
5

Thank you very much.

1 Like
6

Very nice! You’re welcome!

1 Like