Create column from different column values replacing values-CodePudding

I would like to take the different values from my data frame and replace these values for the variables zip_code and id in the string query.

Example

This is my query

UPDATE hospitals SET zip_code = 96761 WHERE id = 'o5FOLOdM1UtOXDB5_WDbWA' AND zip_code = 9676

This is the input data frame

name      zip_code      new_zip_code    id
------------------------------------------------------------
Jeffrey   85698          75813        o5FOLOdM1UtOXDB5_WDbWA
Palmas   89723          36712        9567LEKDKLD95DDF_67_DD
 ...       ...           ...          ...

I would like to have a new column called query and replace the values for the variables zip_code and id with zip_code and new_zip_code in the string of the column query.

Expected output

name      zip_code      new_zip_code    id                          query
-------------------------------------------------------------------------------------
Jeffrey   85698          75813       o5FOLOdM1UtOXDB5_WDbWA      "UPDATE hospitals SET zip_code = 75813 WHERE id = 'o5FOLOdM1UtOXDB5_WDbWA' AND zip_code = 85698"
Palmas   89723          36712        9567LEKDKLD95DDF_67_DD   "UPDATE hospitals SET zip_code = 36712 WHERE id = '9567LEKDKLD95DDF_67_DD' AND zip_code = 89723"
 ...       ...           ...          ...

CodePudding user response：

import pandas as pd
   
# Initializing data
data = [{'name': 'Jeffrey', 'zip_code': 85698, 'new_zip_code': 75813, 'id': 'o5FOLOdM1UtOXDB5_WDbWA' },
        {'name': 'Palmas', 'zip_code': 89723, 'new_zip_code': 36712, 'id': '9567LEKDKLD95DDF_67_DD' }]

df = pd.DataFrame.from_records(data)

#printing dataframe before adding query column
print(df)

#adding query column to dataframe
df['query'] = df.apply(lambda row: "UPDATE hospitals SET zip_code = {} WHERE id = {} AND zip_code = {}".format(str(row.new_zip_code), row.id, row.zip_code), axis=1)

#printing dataframe after adding query column
print(df)

CodePudding user response：

assuming your original dataframe is named 'df', then this should work:

df['query'] = 'UPDATE hospitals SET zip_code = '   df['new_zip_code'].astype(str)   ' WHERE id = '   df['id'].astype(str)   ' AND zip_code = '   df['zip_code'].astype(str)