python - Replace a string in dataframe's column with values from other columns Pandas

Question

Need some help with pandas. I have a query stmnt-

query = "update table1 set Draft = firstvalue ,lastupdated=now() where StatsMonth = secondvalue and Code =thirdvalue"

I have a df with following columns

df[‘payments’], df[‘pub’] , df[‘query’] and a variable called t2 ='2020-12-24'

 pub  payments            query
  123  59495.17  update table1 set Draft ...
  786    887.50  update table1 set Draft ...
  456      4.58  update table1 set Draft ...
  789      0.00  update table1 set Draft ...
  221      0.00  update table1 set Draft ...

Goal is to update the strings - firstvalue, secondvalue, thirdvalue in the query column with values from df[‘payments’] , t2 . df[‘pub’ ]

End result should look like this.

  123  59495.17  update table1 set Draft =59495.17, lastupdated=now() where StatsMonth = '2020-12-24' and Code =123
  786    887.50  update table1 set Draft =887.50 , lastupdated=now() where StatsMonth = '2020-12-24' and Code =786

So we are using values from other two columns and replacing it with those strings in the query.

Here is the list of transformations I am applying -

df['query']=df.apply(lambda x: x['query'].replace('firstvalue', str(df['payments'])), axis=1)

df['query']=df.apply(lambda x: x['query'].replace('secondvalue', t2), axis=1)

df['query']=df.apply(lambda x: x['query'].replace('thirdvalue', str(df['pub'])), axis=1)

But the results has the same value for every row

This is how its looking -

pub  payments            query
  123  59495.17  update table1 set Draft = 0 59495.17
  786    887.50  update table1 set Draft = 0 59495.17 

Its truncating rest of the query and also prepending a zero to the query.

Also the same value is repeating for all rows.

Any idea what am I doing wrong here?

question from:https://stackoverflow.com/questions/65851347/replace-a-string-in-dataframes-column-with-values-from-other-columns-pandas

Answer 1

I think your answer is close. In the lambda instead of calling df try x.

df['query']=df.apply(lambda x: x['query'].replace('firstvalue', str(x['payments'])), axis=1)

df['query']=df.apply(lambda x: x['query'].replace('secondvalue', t2), axis=1)

df['query']=df.apply(lambda x: x['query'].replace('thirdvalue', str(x['pub'])), axis=1)

---

Edit 1

Since the t2 replacement is not referencing a column in the data frame you could use the Series str method.

df['query'] = df['query'].str.replace('secondvalue', t2)