CODEWITHSUNDEEP
pythonpandas
Sayantan Ghosh
Sayantan Ghosh

Reputation: 338

How to get the value of a particular column from dataframe based on matching data from another field?

I have a csv file which I have imported into pandas DataFrame. The file is as follows:

Col1     Col2     Col3     Col4
AB       12       AB12     1,11,12,45
CD       34       CD34     4,6,98,78,12
EF       56       EF56     9,41,52,63,47

So, I have created Col3 with the code:

df['Col3'] = df['Col1'].str.cat(df['Col2'],sep='')

Now, I have a list of values

values_list = [['AB','12'],['CD','34'],['EF','56']]

So, I want to match the values of the list to the dataframe values and consequently import the values from 'Col4'. I am trying to do this in the following way

def extract_values(key):
col4_value = df['Col4'].np.where(df['Col3']==key)
return col4_value

for entry in values_list:
key = ''.join(entry)
extract_values(key)

However, this is not working. Any suggestions regarding how to tackle this? The output expected is:

1,11,12,45
4,6,98,78,12
9,41,52,63,47

Upvotes: 0

Views: 29

Answers (1)

Aryerez
Aryerez

Reputation: 3495

It is not clear how exactally the values are stored in Col4, so I treated them as if they are a string of numbers and commas. You can do:

output_values = [list(df[(df['Col1'] == item[0]) & (df['Col2'].astype(str) == item[1])]['Col4']) for item in values_list] #Get the values from the corresponding Col4 where it fits Col1 and Col2
output_values = [list(eval(item[0])) for item in output_values] #Transform the string to a list of values

Output:

[[1, 11, 12, 45], [4, 6, 98, 78, 12], [9, 41, 52, 63, 47]]

Upvotes: 1

Related Questions