CODEWITHSUNDEEP
pythonpandasdataframe
kenshima
kenshima

Reputation: 583

check if one dataframe values match another dataframe column then set value in dataframe column

I have a dataframe dfScore

dfScore = pd.DataFrame([["ringo", 0,0,0]], columns=["Name","Sales total","Problem total","Finance total"])

    Name  Sales total  Problem total  Finance total
0  ringo            0              0              0

and a dataframe categories

data = [["Finance total", 14], ["Sales total", 4], ["Problem total", 5]] 
categories = pd.DataFrame(data, columns = ['Category', 'ScoreTruth'])

        Category  ScoreTruth
0  Finance total          14
1    Sales total           4
2  Problem total           5

What I would like to do is check whether a value from "Category" in categories is contained in the columns of dfScores. If yes then set the value in dfScores column to "ScoreTruth" adjacent value. I've tried using isin to get the indices in dfScores column but that doesn't actually tell me which Category is which index. i.e.

index = np.where(dfScore.columns.isin(categories["Category"]))
print(index[0])
>>>[1 2 3]

If I try to get the index from isin the other way around I get

index2 = np.where(categories["Category"].isin(dfScore.columns))
print(index2[0])
>>>[0 1 2]

So now I thought I could do something like this dfScore.iloc[:,index[0]] = categories.iloc[index2[0]].loc["ScoreTruth"] to set the values but I get KeyError: 'ScoreTruth' Obviously this would only work if I set every row in dfScores using the index[0] anyway which is not ideal.

I would like to output a dataframe that looks like this

    Name  Sales total  Problem total  Finance total
0  ringo            4              5             14

Upvotes: 2

Views: 110

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Let's try DataFrame.assign:

s = categories.set_index('Category')['ScoreTruth']
dfScore.assign(**s[s.index.intersection(dfScore.columns)])

    Name  Sales total  Problem total  Finance total
0  ringo            4              5             14

Upvotes: 1

Related Questions