How to get the first row of each group of a DataFrame and subtract that value from each subsequent row in that group?

New to Julia
1

Hi!

Hope someone can help me figure out probably a pretty simple problem! I have a data set where I have a column called ‘Time’ and ‘Trial’. The time is recorded from the first trial in 4ms increments. I want to transform the ‘Time’ column so each trial begins with time 0. For that, I am trying to get the first-row value in column ‘Time’ for every trial in 144 trials and subtract that first value from each subsequent time value separately for each trial. I hope I make sense, I am more than happy to elaborate, but it should look something like this:

Screen Shot 2022-08-29 at 10.08.12 PM

I tried: groupby(loaddata, :Trial), :Time =>loaddata[:, :Time] .- loaddata[:1, :Time] but it is probably very far from the right answer!

Thank you to anyone who can help me with this problem!!

2

try

grouped = groupby(loaddata, :Trial)
newdf = transform(grouped, :Time => (t -> t .- first(t)), renamecols=false)

If you don’t want to have that extra variable hanging around you could do it using Chain.jl like

newdf = @chain loaddata begin
    groupby(:Trial)
    transform(:Time => first)
    select(:Time_first)
    .-(loaddata.:Time, _)
end

Note that if your data is not sorted on :Time you may want to use minimum instead of first

1 Like
3

Thank you so much for your reply! I’ll try this! I’m very new to Julia and was looking for this solution for a while, I’ll look up all functions too! I assume I can also make a new variable in my data frame that will nest the transformed time?

4

if you want the adjusted time to be a new variable, then you can just omit the renamecols=false part, as by default that line will create a new column called :Time_first. you can also use the Pair syntax to customize the name, like so

transform(grouped, :Time => (t -> t .- first(t)) => :AdjTime). Just be very sure you put those parentheses around the lambda! in Julia, => has higher precedence than ->.

For the second way, that actually just returns the new column itself, so you could modify loaddata in place like

loaddata[!, :AdjTime] = @chain loaddata begin
    groupby(:Trial)
    transform(:Time => first)
    select(:Time_first)
    .-(loaddata.:Time, _)
end