Creating a key value pair, mapping a string to another key value pair-CodePudding

Suppose I have this

d = {'State': ['California', 'California', 'New York', 'New York', 'New York'],
    'County': ['Los Angeles County', 'Orange County', 'Mason County', 'Willams County', 'Stephan County'],
    'FIPS': ['34027', '23421', '12345', '54321', '12456']}
df_test = pd.DataFrame(d)

I want to have a mapping of the state to the county and its associated unique code. Example,

California -> {Los Angeles county : 34027, Orange County : 23421}

I tried this

d = {}
for state, df_state in df_test.groupby('State'):
    d[state] = df_state[['County', 'FIPS']].to_json()

but I get this for say California

d['California']

'{"County":{"0":"Los Angeles County","1":"Orange County"},"FIPS":{"0":"34027","1":"23421"}}'

This is almost right but I am not sure how to get it in the format I exactly want.

CodePudding user response：

d = {key: g.pivot(index='State', columns='County', values='FIPS').to_dict('records')[0] for key, g in df_test.groupby('State')}
d

###
{'California': {'Los Angeles County': '34027',
                'Orange County': '23421'},
 'New York': {'Mason County': '12345',
              'Stephan County': '12456',
              'Willams County': '54321'}}

d['California']

###
{'Los Angeles County': '34027', 'Orange County': '23421'}

CodePudding user response：

Personally I would do something like this:

import pandas as pd
d = {'State': ['California', 'California', 'New York', 'New York', 'New York'],
    'County': ['Los Angeles County', 'Orange County', 'Mason County', 'Willams County', 'Stephan County'],
    'FIPS': ['34027', '23421', '12345', '54321', '12456']}
df_test = pd.DataFrame(d)

# Get list of unique States
states = list(set(df_test['State'].to_list()))
for state in states:
    # Filter df for rows with this State
    state_df = df_test[df_test['State'] == state]
    # Generate dictionary of County, FIPS pairs
    print({t.County: t.FIPS for t in state_df.itertuples()})

Output:

{'Los Angeles County': '34027', 'Orange County': '23421'}
{'Mason County': '12345', 'Willams County': '54321', 'Stephan County': '12456'}