Pivot table (associative array) data

Question

I have data in the following format returned from my database:

user_id | video_id | liked
0         341       1
1         765       1
0         534       1
3         981       1

I need to pivot this table into the following form:

user_id | 341 | 765 | 534 | 981
0         1     0     1     0
1         0     1     0     0
3         0     0     0     1

What I'm doing is making a single row per user id and a single column per video id, 0 should be the default value unless the video is liked.

For my initial testing and experimentation I was using python's pandas library like so:

df = pd.read_csv('data.csv')
pivoted_df = df.pivot_table(
    index='user_id',
    columns='video_id',
    values='liked',
    fill_value=0,
)
pivoted_df.to_csv('pivoted_data.csv')

This is easy and works perfectly, but now I need to do the same in php and I couldn't find any pandas alternatives. The other solutions I've seen use key names for columns that don't work here since the columns can be any number.

To be clear about the actual data structure in php, the fetched data from the database looks as follows:

Array
(
    [0] => Array
        (
            [user_id] => 0
            [video_id] => 341
            [liked] => 1
        )

    [1] => Array
        (
            [user_id] => 1
            [video_id] => 765
            [liked] => 1
        )

    [2] => Array
        (
            [user_id] => 0
            [video_id] => 534
            [liked] => 1
        )

    [3] => Array
        (
            [user_id] => 3
            [video_id] => 981
            [liked] => 1
        )
)

And it needs to look like this:

Array
(
    [0] => Array
        (
            [user_id] => 0
            [341] => 1
            [765] => 0
            [534] => 1
            [981] => 0
        )

    [1] => Array
        (
            [user_id] => 1
            [341] => 0
            [765] => 1
            [534] => 0
            [981] => 0
        )

    [2] => Array
        (
            [user_id] => 3
            [341] => 0
            [765] => 0
            [534] => 0
            [981] => 1
        )
)

Answer 1

$input = [
  [ 'user_id' => 0, 'video_id' => 341, 'liked' => 1 ],
  [ 'user_id' => 1, 'video_id' => 765, 'liked' => 1 ],
  [ 'user_id' => 0, 'video_id' => 534, 'liked' => 1 ],
  [ 'user_id' => 3, 'video_id' => 981, 'liked' => 1 ]
];

$result = [];

$video_ids = array_unique(array_column($input, 'video_id'));
$video_ids_flipped = array_combine($video_ids, array_fill(0, count($video_ids), 0));

foreach ($input as $item) {
  $user_id = $item['user_id'];
  if (!array_key_exists($user_id, $result)) {
    $result[$user_id] = ['user_id' => $user_id] + $video_ids_flipped;
  }
  $result[$user_id][$item['video_id']] = $item['liked'];
}

$result = array_values($result);