A csv file has a state, county, and data on each line. How can I use PHP associative arrays to convert to states=>counties=>county=>data

Question

My data looks like:

   countyFIPS,County Name,State,stateFIPS,1/22/20,1/23/20,1/24/20,1/25/20,....
   1001,Autauga County,AL,1,0,0,0,0,0,0,0,0,....
   ...

I've been able to retrieve it using an Ajax call and collect it into a simple PHP array, then convert it to json to use in my javascript application. While it appears that the data is all counties of a state, followed by the same configuration for the next state, there is no guarantee that it won't be mixed up in some later set of data.

I'm an old Fortran programmer, and would tend to build a hash table for the "states", then check if the state exists in the hash table. If not create a new hash table and add this empty hash table as the value for the key with the name of the state to the "state" hash table. Then check the state hash table to see if it has a key for the county. Again, if it doesn't, then add an empty array as the value for the key with the county name and add that to the state hash table, then proceed to put the values for that row into the county array. I know this will work, but thought maybe there was some clever way to use associative arrays in PHP to accomplish the same thing.

I look at array_filter, but can't seem to figure out how to adapt it to this case. Are there other functions that might work here?

Then, once I have this structure of

 $nested_object = { state1=>{county1,county2,county3...},state2=>{counties}},
 and those counties have:
 county=>[values],

how can I easily convert this to a json structure? Should it have other keys like "states", and within a state "counties". From looking at Haroldo's question "Convert a PHP object to an associative array" of Dec 3, 2010, it appears like I would use:

$array = json_decode(json_encode($nested_object), true);

Will this give me the structure I am looking for?

I want to end up with a structure that I can ask for the states as a set of keys, then for a selected state ask for the counties in that state as a set of keys, and upon selecting one, get the array of values for that state/county. This has to run on a server with potentially a large amount of data and a moderate amount of hits per unit time so I wanted as reasonably efficient way as possible.

LSerni · Answer 1 · 2020-06-25T23:04:11.097

I want to end up with a structure that I can ask for the states as a set of keys, then for a selected state ask for the counties in that state as a set of keys, and upon selecting one, get the array of values for that state/county

Okay, so you need something like:

    $structure = [
       'AL' => [
           'counties' => [
               'FIPS1' => 'County1',
               'FIPS2' => 'County2',
           ],
           'data' => [
               'FIPS1' => [
                 [ 'date1' => value1, 'date2' => value2, 'date3' => value3... ]
               ],
           ],
       ],
       'AK' => [ ... ]
    ];

You can do that using array_map() and a lambda function writing to $structure, but... in my experience, it is not worth it.

Best to do like you said:

    while ($row = get_another_row()) {
        $countyFIPS = array_unshift($row);
        $countyName = array_unshift($row);
        $stateName  = array_unshift($row);
        $stateFIPS  = array_unshift($row);
        if (!array_key_exists($stateName, $structure)) {
            $structure[$stateName] = [
                'counties' => [ ],
                'data'     => [ ],
            ];
        }
        if (!array_key_exists($countyFIPS, $structure[$stateName]['counties'])) {
            $structure[$stateName]['counties'][$countyFIPS] = $countyName;
            $structure[$stateName]['data'][$countyFIPS] = [ ];
        }
        // Now here you will have $headers, obtained from the header row unshifting
        // the first four fields.
        foreach ($headers as $i => $key) {
            $structure[$stateName]['data'][$countyFIPS][$key] = $row[$i];
        }
    }

This way if you add two CSVs with different dates, the code will still work properly. Dates will not be sorted though, but you can do that with a nested array_map and the aksort function.

To output this in JSON, just use json_encode on $structure.

A csv file has a state, county, and data on each line. How can I use PHP associative arrays to convert to states=>counties=>county=>data

1 Answers1