Home > Enterprise >  Python Insert value when Column matches certain keywords
Python Insert value when Column matches certain keywords

Time:11-06

I want to insert an integer value in Column B if it matches a certain value from the list I create in Python.

For instance, data.xlsx will contain the following:

Column A: Today, Tomorrow

Column B: empty right now

Column C: abc, def

My condition will have something like if column A has a value of Today, then insert 123 in Column B, if Tomorrow, then insert 456, and so forth. Note Column C is irrelevant to this question but I still need to keep these and output them into Excel.

How would I be able to achieve that in Python code?

Would it be something like this?

Import pandas as pd Df=pd.read_excel(‘data.xlsx’) ??? Df.to_excel(‘data_final.xlsx’) - ensuring all Column A,B,C still are intact with the new values filled in Column B.

Thank you.

CodePudding user response:

df.loc[] is probably the simplest solution:

import pandas as pd

df = pd.read_excel('data.xlsx')

df.loc[df['A'] == 'Today', 'B'] = 123
df.loc[df['A'] == 'Tomorrow', 'B'] = 456

df.to_excel('data_final.xlsx')

But, np.select() can also be useful for this:

import pandas as pd
import numpy as np

df = pd.read_excel('data.xlsx')

conditions = [(df['A'] == 'Today'), (df['A'] == 'Tomorrow'), (df['C'] == True)]
choices = [123, 456, 789]
df['B'] = np.select(conditions, choices, default=np.nan)

df.to_excel('data_final.xlsx')

Or, you can accomplish things using a lambda expression:

import pandas as pd

df = pd.read_excel('data.xlsx')

df['B'] = df['A'].apply(lambda x: 123 if x == 'Today' else (456 if x == 'Tomorrow' else 789))

df.to_excel('data_final.xlsx')
  • Related