Create dictionary and append certain fields and columns to existing dataframe (based on condition in Python)

user_5942100 0
Viewed 69

I have an existing dataframe, df, where I would like to append several columns and values to if values in the existing columns meet a certain criteria

Data

location    type    count   year
ny          marvel  1       2021
ca          dc      1       2021

Desired

location    type    count   year    strength    points  cost
ny          marvel  1       2021    13          1000    100,000
ca          dc      1       2021    10          500     200,000

IF the string in the type column is 'marvel' then strength = 13, points = 1000 and cost = 100,000 IF the string in the type column is 'dc' then strength = 10, points = 500 and cost = 200,000 essentially, I would like to create 3 new columns and add values in these columns based on certain criteria

         strength   points  cost    
 marvel  13         1000    100,000 
 dc      10         500     200,000

Doing

  #empty dictionary
  marvel = {}
  dc = {}

  
  marvel_a = {strength:13,  points: 1000, cost: 100,000}
  dc_a     = {strength:10,  points: 500,  cost: 200,000}


  df.assign({'strength': '', 'points': '', 'cost': ''})

I am creating a dictionary that will hold the key and the value and then I am thinking that I need to append this to the existing dataframe, however, the dictionary is working fine, but I am not able to add these 3 new columns.

Any suggestion or advice is appreciated.

pandas numpy python
3 Answers

If you have dataframe df:

  location    type  count  year
0       ny  marvel      1  2021
1       ca      dc      1  2021

And dataframe df_criteria:

        strength  points     cost
marvel        13    1000  100,000
dc            10     500  200,000

Note the index of this dataframe.

Then:

print(df.merge(df_criteria, how="left", left_on="type", right_index=True))

Prints:

  location    type  count  year  strength  points     cost
0       ny  marvel      1  2021        13    1000  100,000
1       ca      dc      1  2021        10     500  200,000
user_100359850

assume that your DataFrame is df

import numpy as np

df['strength'] = np.where(df['type']=='marvel', 13, 
                    np.where(df['type']=='dc', 10, None))

df['points'] = np.where(df['type']=='marvel', 1000, 
                    np.where(df['type']=='dc', 500, None))

df['cost'] = np.where(df['type']=='marvel', 100000, 
                    np.where(df['type']=='dc', 200000, None))

let me explain

numpy where function is np.where(condition, value if condition is True, value if condition is False)

I use overlapped np.where because there are two conditios, marvel and dc.

user_110843380

Also an alternative where you have dict of each attribute:

import pandas as pd
df = pd.DataFrame({'location': ['ny', 'ca'], 'type': ['marvel','dc'], 'count':[1, 1], 'year': [2021, 2021]})

strength = {'marvel': 13, 'dc': 10}
points = {'marvel': 1000, 'dc': 500}
cost = {'marvel': 100000, 'dc': 200000}

df['strength'] = df['type'].map(strength)
df['points'] = df['type'].map(points)
df['cost'] = df['type'].map(cost)
user_85581980
Related