How to replace value in a dataframe and convert it to a list of dictionary in python pandas-CodePudding

I have a data frame that is created after a query is executed. Call it df1.

 customerID flag
0        123   No

Now, I have another list of dictionary like below:

list2 = [{"customerID": "123", "response": "yes", "flag": ""}]

Now, I want to replace the value of the flag with the values of the flag from df1 for that specific customer. So, I converted this list to a data frame say df2 and tried doing it but it didn't give any errors but didn't give my expected output.

Attempt:

df2.loc[df2['customerID'].isin(df1.to_dict().keys()), 'flag'] = df2['customerID'].map(df1.to_dict())

Expected output:

[{"customerID": "123", "response": "yes", "flag": "No"}]

I am looking to replace it in df2 itself. Later on, I can do .to_dict(orient="records") to get the format I need.

CodePudding user response：

As you have a list of dictionaries, it might not be worth converting to DataFrame, performing a merge/map, etc.

Maybe just loop and mutate the object:

s = df1.set_index('customerID')['flag']

for d in list2:
    if d['customerID'] in {'123'} and d['customerID'] in s:
        d['flag'] = s[d['customerID']]

output:

[{'customerID': '123', 'response': 'yes', 'flag': 'No'}]

CodePudding user response：

You could set_index to "customerID", convert the DataFrame to a dictionary. Then iterate over list2 and modify flag value of the list where the "customerID"s match:

df_to_dict = df.astype(str).set_index('customerID').to_dict('index')
for d in list2:
    d['flag'] = df_to_dict.get(d['customerID'], {}).get('flag', d['flag'])

Output:

[{'customerID': '123', 'response': 'yes', 'flag': 'No'}]

CodePudding user response：

One liner solution.

df2 = df2.drop('flag',axis=1).merge(right=df1,on='customerID')

CodePudding user response：

I solved it using the below way:

df1 = df1.set_index('customerID')
df2 = df2.set_index('customerID')
df2.update(df1)
df2.reset_index(inplace=True)

CodePudding user response：

You can try achieving what you want with:

# creating dummy data
df1 = pd.DataFrame(
    data = [[123, 'No']],
    columns = ['customerID', 'flag']
)
list2 = [{'customerID': '123', 'response': 'yes', 'flag': ''}]

# creating a dataframe from list2
df2 = pd.DataFrame().from_records(list2) 

# getting the flag value for customer 123
flag = df1[df1['customerID'] == 123]['flag'].values[0]

# replacing the flag value in df2
df2.loc[df2['customerID'] == str(123), 'flag'] = flag

# printing results
print(df2.to_dict('records'))

[{'customerID': '123', 'response': 'yes', 'flag': 'No'}]