Hi,
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 x Arrays remain zero. Other variations such as
@eval $(Symbol("x_$i[$j]")) =duration[j]
ERROR: UndefVarError: j not defined
or
@eval $(Symbol("x_$i[$j]")) = $(Symbol("duration[$j]"))
ERROR: UndefVarError: duration[1] 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 groupreduce.
Thank you very much in advance!