python - calculate a new column in pandas using 2 numpy arrays

Question

I have a pandas dataframe : column header is called "Location" example contents: "London Arndale Centre" "Manchester Arndale" "Birmingham Central Station" "Newcastle Metro Centre"

2 numpy arrays :

originalLocation = np.array(["London Arndale Centre","Manchester Arndale","Birmingham Central Station","Newcastle Metro Centre")

newLocation = np.array(["London","Manchester","Birmingham","Newcastle"]

i want to create a new column in the pandas : newLocation

the result needs to be the matching column in newLocation, where the location field matches the original location numpy.

example : "London Arndale Centre" needs to be "London" "Manchester Arndale" needs to be "Manchester"

i have tried this , but it throw back errors

df['newLocation'] = newLocation[int(np.where(originalLocation == df['Location'])[0])]

errors : ValueError: ('Lengths must match to compare', (159,), (12,))

what am i doing wrong here ?

JANO · Accepted Answer · 2022-01-18 14:34:28Z

1

It seems like you forgot the commas in your originalLocation array. Also, the int() is not necessary. Updated code:

df_data = ["London Arndale Centre", "Manchester Arndale", "Birmingham Central Station", "Newcastle Metro Centre"]
df = pd.DataFrame(df_data, columns=['Location'])

originalLocation = np.array(["London Arndale Centre", "Manchester Arndale", "Birmingham Central Station", "Newcastle Metro Centre"])

newLocation = np.array(["London","Manchester","Birmingham","Newcastle"])      

df['newLocation'] = newLocation[np.where(originalLocation == df['Location'])[0]]

df

Output:

    Location    newLocation
0   London Arndale Centre   London
1   Manchester Arndale  Manchester
2   Birmingham Central Station  Birmingham
3   Newcastle Metro Centre  Newcastle

EDIT: As you mentioned merge works even if not all values are included in the new locations. I create a small example using merge:

df_data = ["London Arndale Centre", "Manchester Arndale", "Birmingham Central Station", "Newcastle Metro Centre"]
df = pd.DataFrame(df_data, columns=['Location'])

originalLocation = ["London Arndale Centre", "Birmingham Central Station", "Newcastle Metro Centre"]
newLocation = ["London", "Birmingham", "Newcastle"]   

df_new = pd.DataFrame({'Location': originalLocation,
                       'newLocation': newLocation})

df.merge(df_new, on='Location', how='left')

Output with Manchester entry missing:

Location    newLocation
0   London Arndale Centre   London
1   Manchester Arndale  NaN
2   Birmingham Central Station  Birmingham
3   Newcastle Metro Centre

edited Jan 18, 2022 at 14:34

answered Jan 17, 2022 at 18:15

JANO

3,0962 gold badges17 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mr Paul Over a year ago

thanks for your response. this does work. however, if i take out one of the items in df_data (eg "London Arndale Centre" and try the code i get the error message again "'Lengths must match to compare'" how can i get the code to match even if one of the items in the match list, does not exist at source?

Mr Paul Over a year ago

merge works better for me with this.

JANO Over a year ago

You are right! merge works also with missing values. I added an example to the answer for completeness.

Mr Paul Over a year ago

thank you for you help.

Collectives™ on Stack Overflow

python - calculate a new column in pandas using 2 numpy arrays

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related