Group value base on the key

Question

I have a tricky output based on the image above, I need to produce the CSV file like this

Date,Reservoir Level,P5,P6,P2,P3,P4,SP6,SP4,SP5,SP3,SP2
1574043963000,19.1,,,,,,,,,,
1574039698000,20.665,11.722,,,,,,,,,
1574039699000,8.735,8.879,8.835,,,,,,,,
1574039702000,15.106,,,,,,,,,,
1574039703000,undefined,12.09,,,,,,,,,
1574039704000,13.707,,,,,,,,,,
1574039705000,13.17,,,,,,,,,,
...

I used Laravel-Excel to produce it. Here what I have so far (based on the answer given below)

public function collection()
{
    $rows = collect();

    foreach ($this->data as $recordKey => $record) {
        $date = $record->date;
        $instrumentReading = json_decode($record->instrument_reading, true);

        if ($rows->has($date)) {
            $row = $rows->get($date);
        } else {
            $row = collect([
                'date' => $date,
            ]);
        }

        $i = 0;
        foreach ($instrumentReading as $key => $value) {
            $elementIndex = array_keys($this->header, $key);
            $price = $elementIndex[0];
            $row->put($key, $value);
            // info($price, [$i]);
            ++$i;
        }
        info($row);
        $rows->put($date, $row);
    }

    return $rows;
}

My problem starts on second entry P5: 20.665 goes into Reservoir level column. And this followed by the third entry P2: 8.735 also goes into Reservoir level column. How do I fix this?

EDITED

this is how I set the headings

public function headings(): array
{
    $header = collect(['Date']);

    foreach ($this->data as $rowNo => $row) {
        $reading = json_decode($row->instrument_reading);
        foreach ($reading as $ra1 => $val) {
            if (!$header->contains($ra1)) {
                $header->push($ra1);
            }
        }
    }
    $this->header = $header->toArray();

    return $header->toArray();
}

Appreciate your help

Can you describe in further detail what starts to "break"? Do you mean that not all records with the date "1985-06-21" are dispalyed? — akalucas, Apr 22 '20 at 14:03

score 0 · Answer 1 · answered Apr 22 '20 at 14:35

If I understand correctly you want to merge all rows based on date?

If so I think something like this is probably what you're after:

public function collection()
{
    $rows = collect();

    foreach ($this->data as $recordKey => $record) {
        $date = Carbon::createFromFormat('Y-m-d', $record->iv_date)->format('m/d/Y');
        $instrumentReading = json_decode($record->instrument_reading, true);

        if ($rows->has($date)) {
            $row = $rows->get($date);
        } else {
            $row = collect([
                'date' => $date
            ]);
        }

        foreach ($instrumentReading as $key => $value) {
            $row->put($key, $value);
        }

        $rows->put($date, $row);
    }

    return $rows;
}

yes you are right. This solution close to what I need but upon further checking on the output, if the column "reservoir level" is null, then the next column value will be placed inside it — Muhaimin CS, Apr 23 '20 at 08:46
If I inspect further `$rows` all values are placed correctly but it's different output in CSV file — Muhaimin CS, May 08 '20 at 07:36

score 0 · Answer 2 · answered May 07 '20 at 10:18

You can try below code.

function collection()
{
    $rows = [];

    $data = [
        ['date' => 123123, 'instrument_reading' => json_encode(['Reservoir Level' => '19.10'])],
        ['date' => 123123, 'instrument_reading' => json_encode(['P5' => '20.665', 'P6' => '11.722'])],
        ['date' => 123123, 'instrument_reading' => json_encode(['P2' => '8.735', 'P3' => '8.879', 'P4' => '8.835'])],
        ['date' => 123123, 'instrument_reading' => json_encode(['SP6' => '15.106'])],
        ['date' => 123123, 'instrument_reading' => json_encode(['SP4' => 'undefined', 'SP5' => '12.090'])],
        ['date' => 123123, 'instrument_reading' => json_encode(['SP3' => '13.707'])],
        ['date' => 123123, 'instrument_reading' => json_encode(['SP2' => '13.170'])],
    ];

    foreach ($data as $record) {

        $row = [];

        $row['date'] = $record['date'];

        foreach (json_decode($record['instrument_reading'], true) as $key => $value) {
            $row[$key] = $value;
        }

        $rows[] = $row;
    }

    $keys = array_unique(array_merge_recursive(... array_map(function ($row) {
        return array_values(array_keys($row));
    }, $rows)));

    foreach ($rows as &$row) {
        foreach ($keys as $key) {
            if (!array_key_exists($key, $row)) {
                $row[$key] = null;
            }
        }
    }

    $handle = fopen('result.txt', 'w');

    fputcsv($handle, $keys);

    foreach ($rows as $r) {
        fputcsv($handle, array_values($r));
    }

    fclose($handle);
}

collection();

Group value base on the key

2 Answers2