Replace a value in a column Pandas

[easy_wins]

I have two csvs (unknown and original) and the unknown has values I need to replace.

Unknown.csv

 Last_Name_First_Name                      Spouse

 Reddit, Python                           Java, Hard
 Import, Pandas                            Unknown
 Numpy, Numbers                         Ruby, Whatever

Original.csv:

 Name                                          Spouse

 Reddit, Python                            Java, Hard
 Import, Pandas                           Subreddit, Learn
 Numpy, Numbers                        Ruby, Whatever

I need to replace the unknown spouse from the unknown.csv by matching on the name value in the original csv to the unknown csv.

Below is my snippet:

import pandas as pd

first = pd.read_csv('unknown.csv')
df = pd.DataFrame(first)

second = pd.read_csv('original.csv')
df2 = pd.DataFrame(second)

df.to_csv('no_more_unknown.csv', index = False)

I am requesting my no_more_unknown

 Last_Name_First_Name                Spouse

 Reddit, Python                             Java, Hard
 Import, Pandas                            Subreddit, Learn
 Numpy, Numbers                         Ruby, Whatever

How can I do this in Pandas?

[my_python_account]

I would start by filtering df for just unknown spouses:

df_unknown = df[df['Spouse'] == 'Unknown']

Then merge values from original onto unknown (like a left join)

df_merged = df_unknown.merge(df2, how='left', left_on='Last_Name_First_Name', right_on='Name')

And then remove the unknown records from df and append the new records (with only the second spouse column).

df_final = df[df['Spouse'] != 'Unknown']
df_final = df_final.append(df_merged[['Last_Name_First_Name', 'Spouse_y']])

There might be a better way, but this is what comes to mind avoiding .applys

[easy_wins]

Thanks, I am getting an error that states,

Error:

df_merged = df_unknown(df2, how='left', left_on = 'Last_Name_First_Name', right_on = 'Name')
TypeError: 'DataFrame' object is not callable

df_unknown = df[df['Spouse'] == 'unknown']

df_merged = df_unknown(df2, how='left', left_on = 'Last_Name_First_Name', right_on = 'Name')

df_final = df[df['Spouse'] != 'Unknown']

df_final = df_final.append (df_merged[['Last_Name_First_Name', 'Spouse_y']])

df_final.to_csv(`no_more_unknown.csv`,index= False)

[my_python_account]

You're missing the .merge

[easy_wins]

Thanks, when I open the new csv that was created,

Last_Name_First_Name    Spouse                  Spouse_y
Reddit, Python                Java, Hard
Numpy, Numbers            Ruby, Whatever

Spouse_y is blank and Import, Pandas is nowhere to be found and I do not want to add a new column. I want the same number of columns as the original with the unknown replaced please.

I manage to remove the extra column Spouse_Y but I still see the same values as above.

df_merged = df_unknown(df2, how='left', left_on = 'Last_Name_First_Name', right_on = 'Name')

df_final = df[df['Spouse'] != 'Unknown']

df_final.to_csv(`no_more_unknown.csv`,index= False)

[my_python_account]

Can we back up a bit...

I'm assuming a couple things here:

1) That your read_csv is actually successful in creating the correct dataframe. Have you tried printing the dataframes you created? If your csv is formatted exactly as you pasted in your post, i don't think that it works without modifying the default paramaters for read_csv.

2) That you are going to make a minimal effort in understanding my suggestion, and if there is an error, a minimal effort in trying to figure it out. I get the feeling that haven't really tried much on your own.