Question 251586 in PHP Question posted in
Official manual can be found here.

Php – Pivot table (associative array) data

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
        )
)
Tags:

3

Answers


  1. 0

    It’s just some relatively basic array manipulation. Here is a way to do it:

    $rows = [
    ['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' => 901, 'liked' => 1]
];

// Extract the user and video ids
$users = array_unique(array_column($rows, 'user_id'));
$videos = array_unique(array_column($rows, 'video_id'));

// Initialize the table
$table = [];
foreach($users as $user_id)
{
    foreach($videos as $video_id)
    {
        $table[$user_id]['user_id'] = $user_id;
        $table[$user_id][$video_id] = 0;
    }
}

// Add the likes
foreach($rows as $row)
    $table[$row['user_id']][$row['video_id']] += $row['liked'];

$table = array_values($table);

print_r($table);

    Output:

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

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

    [2] => Array
        (
            [user_id] => 3
            [341] => 0
            [765] => 0
            [534] => 0
            [901] => 1
        )
)
    Login or Signup to reply.
  2. 0 
    $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);
    Login or Signup to reply.
  3. 0

    To achieve the desired pivot transformation in PHP, you can create a custom script that processes the data fetched from the database. Here’s a PHP script to pivot the data:

    $data = [
['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 = [];
 if (!isset($result[$user_id])) {
    $result[$user_id] = ['user_id' => $user_id];
}`$result[$user_id][$video_id] = $liked;

    }

    $result = array_values($result);
print_r($result);

    This script processes the data fetched from the database and creates the desired pivoted format. It initializes an empty result array, iterates through the database rows, and populates the result array accordingly.

    Now, addressing your questions:

    Why not change the database query? It’s often preferable to perform such transformations in code when the database schema or query is not under your control or when you want to keep the raw data intact for other purposes.

    Is ‘liked’ always 1? Based on your description, it seems that ‘liked’ is always 1. If it can be 0 or false, you can modify the script to handle those cases by setting the value accordingly in the result array.

    What if more than one user_id has the same video_id? This script overwrites the value for a specific user_id and video_id combination. If you want to aggregate likes from multiple users for the same video_id, you can modify the script to add the likes instead of overwriting.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search