I have some survey data which are organized hierarchically and I would like to transform them into a rectangular dataset. For example using JuliaDB:
t = table([1,1,1,2,2,2], [1,2,2,2,4,5],[10,20,10,5,20,10], names=[:ID, :Activity,:Duration])
Table with 6 rows, 3 columns: ID Activity Duration ────────────────────── 1 1 10 1 2 20 1 2 10 2 2 5 2 4 20 2 5 10
Each activity can happen more than once per ID and some activities are not reported if the duration is zero. I would like to create a new variable for all possible Activities (which are 100) and sum the duration to something like this:
Table with 2 rows, 6 columns: ID x_1 x_2 x_3 x_4 x_5 ─────────────────────────── 1 10 30 0 0 0 2 0 5 0 20 10
I have tried the following:
duration = select(t,:Duration) activity = select(t,:Activity) for i in 1:5 @eval $(Symbol("x_$i")) = zeros(Int,size(activity,1)) end for j=1:size(activity,1) for i=1:5 if activity[j]==i @eval $(Symbol("x_$i[$j]")) =duration[$j] else @eval $(Symbol("x_$i[$j]"))=0 end end end
and then use
groupreduce using summation by
ID and push the arrays into a new table.
However, the loop doesn’t work and all the
Arrays remain zero. Other variations such as
@eval $(Symbol("x_$i[$j]")) =duration[j] ERROR: UndefVarError: j not defined
@eval $(Symbol("x_$i[$j]")) = $(Symbol("duration[$j]")) ERROR: UndefVarError: duration not defined
produce errors or return zeros.
Is there a way to make this work or a more efficient way of doing this without creating so many columns? For some activities of interest, I did it manually but since I would like to obtain the information for all the 100 activities it is not a feasible strategy.
The tricky part is that I need the information for the number of individuals that didn’t do some activities. The total duration per activity is straightforward and can be done without the above loops simply by
Thank you very much in advance!