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')