How to populate a column base on another dataframe column in Python?

Question

I asked a similar question the other day, but couldn't use the advice which I received there in my current issue.

How can I fill the "quanitity" column in df, with the same ticker position from "port_df" ?

dataA = {'ID': ['1407','1726','2910','1890'],
        'quanitity': ['nan','nan','nan','nan'],
        'NetRate': ['nan','nan','nan','nan'],
        'GrossRate': [150,0,238,0],
        'PositionClass': ['L','L','S','S'],
        }

df = pd.DataFrame(dataA, columns = ['ID', 'quanitity', 'NetRate', 'GrossRate','PositionClass' ])

df
     ID  quanitity  NetRate GrossRate   PositionClass
0   1407    nan      nan       150      L
1   1726    nan      nan       0        L
2   2910    nan      nan       238      S
3   1890    nan      nan       0        S

port = {'Symbol': ['1407','1726','3214','2910','1890','5614','4578'],
        'Position': ['56461','441','31316','546','105','0','nan'],
        'NetRate': ['nan','nan','nan','nan','nan','nan','nan'],
        'PositionClass': ['L','L','S','S','L','S','S'],
        }
port_df = pd.DataFrame(port, columns = ['Symbol', 'Position', 'NetRate', 'PositionClass' ])
port_df

   Symbol   Position    NetRate PositionClass
0   1407    56461       nan     L
1   1726    441         nan     L
2   3214    31316       nan     S
3   2910    546         nan     S
4   1890    105         nan     L
5   5614    0           nan     S
6   4578    nan         nan     S

I tried this two code.but couldn't get the results that I want

import numpy as np
import pandas as pd

df['quanitity'] = (np.where(df['ID'] == port_df['Symbol'], 
                            df['ID'].map(port_df.set_index('Symbol')['Position']),
                            np.nan))

or

df.loc[df['ID'].isin(port_df.Symbol),['quanitity']] = port_df['Position']

appreciate any help.

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Anurag Dabas, Jul 16 '21 at 02:39
for mapping use `df['quantity']=df['ID'].map(port_df.set_index('Symbol')['Position'])` — Anurag Dabas, Jul 16 '21 at 02:41
@K saman It is working fine on my side anyway then use `df.merge(port_df[['Symbol','Position']],left_on='ID',right_on='Symbol',how='left')` — Anurag Dabas, Jul 16 '21 at 02:50

Pranta Palit · Answer 1 · 2021-07-16T04:13:36.530

Add these lines after your code:

my_dict = dict(zip(port['Symbol'], port['Position']))
df['quanitity'] = [my_dict[id] for id in df['ID']]

Solution:

import pandas as pd
dataA = {'ID': ['1407','1726','2910','1890'],
        'quanitity': ['nan','nan','nan','nan'],
        'NetRate': ['nan','nan','nan','nan'],
        'GrossRate': [150,0,238,0],
        'PositionClass': ['L','L','S','S'],
        }

df = pd.DataFrame(dataA, columns = ['ID', 'quanitity', 'NetRate', 'GrossRate','PositionClass' ])

port = {'Symbol': ['1407','1726','3214','2910','1890','5614','4578'],
        'Position': ['56461','441','31316','546','105','0','nan'],
        'NetRate': ['nan','nan','nan','nan','nan','nan','nan'],
        'PositionClass': ['L','L','S','S','L','S','S'],
        }
port_df = pd.DataFrame(port, columns = ['Symbol', 'Position', 'NetRate', 'PositionClass' ])

# Add these two lines
my_dict = dict(zip(port['Symbol'], port['Position']))
df['quanitity'] = [my_dict[id] for id in df['ID']]

print(df)

Output

     ID quanitity NetRate  GrossRate PositionClass
0  1407     56461     nan        150             L
1  1726       441     nan          0             L
2  2910       546     nan        238             S
3  1890       105     nan          0             S

How to populate a column base on another dataframe column in Python?

1 Answers1