CODEWITHSUNDEEP
pythonpandasdataframe
JNams
JNams

Reputation: 15

Creating dataframe and mapping nested dictionaries to key:value pairs

I am a little stuck with the following problem:

I want to take the following dictionaries:

gasDict ={'Chair': {'CO2': 0.4, 'Methane': 0.5, 'Other': 0.6},'House': {'CO2': 0.4, 'Methane': 0.2, 'Other': 0.3}}

purchaseDict = {'Bob': ['Chair', 'House'], 'Jim': ['Chair']}

And create the following dataframe:

| Name | Purchase| CO2   | Methane |Other |
|:---- |:-------:|:-----:|:-------:|-----:|
| Bob  | Chair   | 0.4   |  0.5    | 0.6  |
| Bob  | House   | 0.4   |  0.2    | 0.3  | 
| Jim  | Chair   | 0.4   |  0.5    | 0.6  |

My two challenges are 1) Creating a name column with repeating names for the # of corresponding value list items; and 2) mapping the three gasDict nested key:value pairs with the corresponding 'purchase' key from the purchaseDict.

Thank you for any assistance!

Upvotes: 1

Views: 52

Answers (3)

Cameron Riddell
Cameron Riddell

Reputation: 13417

An alternative based off of @Henry Ecker's answer. Instead of using a comprehension, you can also construct a DataFrame and use .explode

gasDict = {
    "Chair": {"CO2": 0.4, "Methane": 0.5, "Other": 0.6},
    "House": {"CO2": 0.4, "Methane": 0.2, "Other": 0.3},
}

purchaseDict = {"Bob": ["Chair", "House"], "Jim": ["Chair"]}


df_gas = pd.DataFrame.from_dict(gasDict, orient="index")
df_purchase = (
    pd.DataFrame(purchaseDict.items(), columns=["Name", "Purchase"])
    .explode("Purchase")
)

out = df_purchase.join(df_gas, on='Purchase').reset_index()

print(out)
  Name Purchase  CO2  Methane  Other
0  Bob    Chair  0.4      0.5    0.6
1  Bob    House  0.4      0.2    0.3
2  Jim    Chair  0.4      0.5    0.6

Upvotes: 0

Andrej Kesely
Andrej Kesely

Reputation: 195468

Try:

gasDict = {
    "Chair": {"CO2": 0.4, "Methane": 0.5, "Other": 0.6},
    "House": {"CO2": 0.4, "Methane": 0.2, "Other": 0.3},
}

purchaseDict = {"Bob": ["Chair", "House"], "Jim": ["Chair"]}

df = pd.DataFrame(
    {"Name": k, "Purchase": vv, **gasDict[vv]}
    for k, v in purchaseDict.items()
    for vv in v
)
print(df)

Prints:

  Name Purchase  CO2  Methane  Other
0  Bob    Chair  0.4      0.5    0.6
1  Bob    House  0.4      0.2    0.3
2  Jim    Chair  0.4      0.5    0.6

Upvotes: 3

Henry Ecker
Henry Ecker

Reputation: 35646

We can use DataFrame.from_dict to convert gasDict and a list comprehension for purchaseDict. Then join them together:

gasDict = {'Chair': {'CO2': 0.4, 'Methane': 0.5, 'Other': 0.6},
           'House': {'CO2': 0.4, 'Methane': 0.2, 'Other': 0.3}}

purchaseDict = {'Bob': ['Chair', 'House'], 'Jim': ['Chair']}

df_gas = pd.DataFrame.from_dict(gasDict, orient='index')
df_purchase = pd.DataFrame([(k, v)
                            for k, lst in purchaseDict.items() for v in lst],
                           columns=['Name', 'Purchase'])
df = df_purchase.join(df_gas, on='Purchase')

df:

  Name Purchase  CO2  Methane  Other
0  Bob    Chair  0.4      0.5    0.6
1  Bob    House  0.4      0.2    0.3
2  Jim    Chair  0.4      0.5    0.6

Upvotes: 3

Related Questions