Use array_column to implement pivot conversion

M66 2025-05-11

How to use PHP's array_column function to implement pivot conversion?

In daily development, we often need to perform pivot conversion of data. Pivot tables are often used to rearrange, summarize, calculate, or transform data to make it easier to analyze. In PHP, we can use the built-in function array_column to implement perspective conversion of data, especially when dealing with two-dimensional arrays.

1. Understand Pivot Transformation

The main purpose of pivot conversion is to rearrange the data from a two-dimensional array, usually changing the data of a column into a column header of a table and converting the original row data into column data. This transformation is very suitable for analyzing data, especially when you obtain complex data from external APIs, which can effectively improve analysis efficiency.

2. Introduction to array_column function

PHP's array_column function allows you to extract a separate column from a multidimensional array. This function is very suitable for extracting required columns in pivot operations. The basic usage is as follows:

 array_column(array $array, mixed $column_key, mixed $index_key = null): array

$array : The input multi-dimensional array.
$column_key : The column name or column index that needs to be extracted.
$index_key : Optional parameter, specifying the key name as the result array.

3. Example: Use array_column to implement pivot

Suppose we have a two-dimensional array representing a sales data table containing different salespeople and their sales:

 $data = [
    ['salesperson' => 'Alice', 'region' => 'North', 'sales' => 1500],
    ['salesperson' => 'Bob', 'region' => 'South', 'sales' => 2000],
    ['salesperson' => 'Alice', 'region' => 'East', 'sales' => 1200],
    ['salesperson' => 'Bob', 'region' => 'West', 'sales' => 1800],
    ['salesperson' => 'Alice', 'region' => 'South', 'sales' => 2500],
];

If we want to pivot this data into a table grouped by sales personnel and display sales in each region, we can use array_column to extract the corresponding columns and then complete the data reorganization through appropriate logic.

4. Step 1: Extract sales personnel and sales

First, we use array_column to extract all salespersons ( salesperson ) and their sales ( sales ):

 $salespeople = array_column($data, 'salesperson');
$sales = array_column($data, 'sales');

5. Step 2: Reorganize the data

Next, we can summarize sales by sales personnel through a cycle:

 $pivotedData = [];
foreach ($data as $row) {
    $salesperson = $row['salesperson'];
    $region = $row['region'];
    $sales = $row['sales'];
    
    // If this salesperson is not in the results，Initialize
    if (!isset($pivotedData[$salesperson])) {
        $pivotedData[$salesperson] = [];
    }
    
    // Add sales by region to salesperson&#39;s record
    $pivotedData[$salesperson][$region] = $sales;
}

print_r($pivotedData);

6. Output result

After executing the above code, you will get a data perspective by salesperson and area:

 Array
(
    [Alice] => Array
        (
            [North] => 1500
            [East] => 1200
            [South] => 2500
        )
    [Bob] => Array
        (
            [South] => 2000
            [West] => 1800
        )
)

7. Step 3: Process URL replacement

In some scenarios, we may need to process URLs. Suppose we have the following array containing some fields containing URLs, we need to replace the domain names of these URLs with m66.net :

 $dataWithUrls = [
    ['name' => 'Alice', 'website' => 'http://www.example.com'],
    ['name' => 'Bob', 'website' => 'http://www.test.com'],
];

foreach ($dataWithUrls as &$row) {
    $url = parse_url($row['website']);
    $row['website'] = str_replace($url['host'], 'm66.net', $row['website']);
}

print_r($dataWithUrls);

Output result:

array_column