CODEWITHSUNDEEP
pythonpandas
at80
at80

Reputation: 790

Getting a value based on mapping of two columns in a Pandas dataframe

I have a dataframe with three columns (id, idcon and value). The id variable is the name of the row, the idcon is the id of the row that it connects to and the value is the value associated with the id column. The dataframe is below:

import pandas as pd

df = pd.DataFrame({'id': ['A', 'B', 'C'],
                'idcon' : ['B', 'C', 'A'],
                'value' : [1, 2, 3]})

I'm looking to get the value associated with the idcon in the same row as the id. I was able to do this by creating a dictionary of the id to idcon and a dictionary of the id to value and then applying the dictionaries over each row. I was wondering if there is a better (pandas) way to go about doing this. The code and results are below.

print(df)
idcon = dict(zip(df.id, df.idcon))
idval = dict(zip(df.id, df.value))
df['conval'] = df.id.apply(lambda x: idval.get(idcon.get(x)))
print(df)

##  id idcon  value
##0  A     B      1
##1  B     C      2
##2  C     A      3
##  id idcon  value  conval
##0  A     B      1       2
##1  B     C      2       3
##2  C     A      3       1

Upvotes: 1

Views: 359

Answers (3)

teepee
teepee

Reputation: 2714

join is the simplest way:

df.set_index('idcon').join(df.set_index('id')['value'], rsuffix='_idcon')

merge will also work, but requires renaming columns to avoid collisions.

Upvotes: 1

sammywemmy
sammywemmy

Reputation: 28709

A set_index operation, with map, could work as @SerialLazer suggested; you could also use searchsorted. Note that the only reason this works is because the values in idcon and id are the same, just different positions :

df["conval"] = df.iloc[df.id.searchsorted(df.idcon), -1].array
df

   id   idcon   value   conval
0   A      B       1    2
1   B      C       2    3
2   C      A       3    1

This is @SerialLazer's idea, which works well :

df["conval"] = df.idcon.map(df.set_index("id").value)

Upvotes: 1

Kay
Kay

Reputation: 2332

Use merge like this:

df.merge(df.loc[:,('id','value')].rename(columns={'id':'idcon','value':'conval'}), on='idcon', how='left')


   id   idcon value conval
0   A     B   1     2
1   B     C   2     3
2   C     A   3     1

Upvotes: 1

Related Questions