I do I create one row for each unique timestamp in this dataframe?

I have the following data frame in R that looks like this. Each depth and timestamp have a unique value. I can't figure out how to collapse the data frame to get rid of the NAs and have 1 row for each timestamp.

time	depth1	depth2	depth3
t1	value	NA	NA
t1	NA	value	NA
t1	NA	NA	value
t2	value	NA	NA
t2	NA	value	NA
t2	NA	NA	value

I worked out a code that gave me unique depths for each row and unique timestamps for each column, resulting in a very wide data frame.

How can I reduce the rows to a single time stamp? I need a dataframe that looks like this:

time	d1	d2	d3
t1	v1	v2	v3
t2	v1	v2	v3
t3	v1	v2	v3

I have tried a bunch of different things including spread(depth_ft, value) but have not had any luck.

The original data is like this:

time	depth	value
t1	d1	v1
t1	d2	v2
t1	d3	v3
t2	d1	v1
t2	d2	v2
t2	d3	v3

Solution

Please try

tribble(
~time,  ~depth, ~value,
't1',   'd1',   'v1',
't1',   'd2',   'v2',
't1',   'd3',   'v3',
't2',   'd1',   'v1',
't2',   'd2',   'v2',
't2',   'd3',   'v3'
) |> pivot_wider(id_cols = time, names_from = depth, values_from = value)

^{Created on 2023-07-07 with reprex v2.0.2}

# A tibble: 2 × 4
  time  d1    d2    d3   
  <chr> <chr> <chr> <chr>
1 t1    v1    v2    v3   
2 t2    v1    v2    v3