How to iterate through DataFrame rows and grab values from dicts in cols?

user_3571649 0
Viewed 220

Features_Frame

Each feature frame will be a batch of data. I would like to extract all values for the key 'coordinates' in the col geometry and iteratively insert into another df.

With that same df, I would also like to store data extracted from the properties col. Properties col has many keys.

Each source frame will have both 'geometry':'coordinates' and 'properties', which will consists of various keys.

Each col in this new DataFrame will be a key inside either 'geometry' or 'properties'.

For example:

      coordinates          name
0      [-108.600,39.09]    'Target'
1      [51.459,82.04]      'Costco'
2      [-35.459,82.04]     'BJ's Wholesale Club'
3      [98.459,12.07]      'Walgreens'
4      [105.404,96.04]     'Walmart

I can access both cols with the below:

coord_frame = features_frame['geometry'][:]
properties_frame = features_frame['properties'][:]

But that only splits the frame in two. Typically, if I did:

Feature_Frame['geometry'][:]['coordinates']

I'd get the values for the coordinates key in the geometry col for all rows, if I did:

Feature_Frame['properties'][:]['name']

I'd get the value for the name key in the properties col for all rows.

Instead I just get a key error saying name or coordinates dont exist.

pandas dataframe dictionary python
2 Answers

Feed list of dicts to pd.DataFrame constructor

pd.Series.apply is a Python-level loop, except that it usually underperforms a simple list comprehension. A much better idea is to use the optimised code used in the pd.DataFrame constructor. Here's an example:

df = pd.DataFrame({'geometry': [{'coordinates': [-108.600,39.09], 'name': 'Target'},
                                {'coordinates': [51.459,82.04], 'name': 'Costco'}]})

print(df)

                                            geometry
0  {'coordinates': [-108.6, 39.09], 'name': 'Targ...
1  {'coordinates': [51.459, 82.04], 'name': 'Cost...

res = pd.DataFrame(df['geometry'].values.tolist())

print(res)

       coordinates    name
0  [-108.6, 39.09]  Target
1  [51.459, 82.04]  Costco

Use concat for multiple series of dictionaries

The above can be extended to arbitrary series of dictionaries:

df = pd.DataFrame({'geometry': [{'coordinates': [-108.600,39.09], 'name': 'Target'},
                                {'coordinates': [51.459,82.04], 'name': 'Costco'}],
                   'properties': [{'osm_id': 288700723, 'osm_tye': 'W'},
                                  {'osm_id': 52734154, 'osm_tye': 'W'}]})

res = pd.concat((pd.DataFrame(df[col].values.tolist()) for col in df), axis=1)

print(res)

       coordinates    name     osm_id osm_tye
0  [-108.6, 39.09]  Target  288700723       W
1  [51.459, 82.04]  Costco   52734154       W
user_92095460

what about

df_new = pd.DataFrame()

and then e.g.

df_new['coordinates'] = features_frame['geometry'].apply(lambda x: x['coordinates'])

or

df_new['name'] = features_frame['properties'].apply(lambda x: x['name'])

And if you want to do that with all keys you can just loop over the keys of exemplary the dict in the first row:

for key in features_frame.geometry[0]:
    df_new[key] = features_frame.geometry.apply(lambda x: x[key])

for key in features_frame.properties[0]:
    df_new[key] = features_frame.properties.apply(lambda x: x[key])

supplemental:
...and just in case there are identical keys in the geometry- and the properties-dicts, you could easily decorate them when creating new columns to prevent overwriting:

for ...
    df_new['geom_' + key] = ...
for ...
    df_new['prop_' + key] = ...

EDIT:

In case that some dictionaries in a column don't have all keys, a default value, e.g. None should be returned.
To achieve that, simply use the get-method, which allow for defining a default value, in the lambda functions instead of indexing:

lambda x: x.get(key, None)

This is at least a proper solution against key errors.
However, if the code doesn't iterate through all keys because the dict in the first row is not representative for all dicts, at first a list of all keys has to be created.
And there are different possibilities to get to this list:

  1. Ideally you already know all the keys from elsewhere. Then you can put them in a list and iterate over it instead of the first dict.

  2. Perhaps you know that there is at least one dict with the most keys and that this longest dict has all keys and the keys of shorter dicts in the same column are always subsets. Then you can find the

    longest_dict = sorted(df.geometry, key=len)[-1]

  3. Perhaps you know nothing at all about the keys. So you have to collect all different keys which appear anywhere in a column:

    all_keys = []
for d in df.geometry:
    all_keys.extend(d)
all_keys = set(all_keys)
user_83001350
Related