using .loc to assign values from another dataframe by index returns : ValueError: Must have equal len keys and value when setting with an iterable

Question

df_state_group:

Index	State	assigned_airport
4	Florida	NaN
5	Florida	NaN
6	Florida	NaN
7	Florida	NaN

df1:

Index	State	assigned_airport
0	Washington	NaN
1	New York	NaN
2	Illinois	NaN
3	Texas	NaN
4	Florida	NaN
5	Florida	NaN
6	Florida	NaN
7	Florida	NaN
8	Ohio	NaN
9	Colorado	NaN
10	Michigan	NaN
11	Indiana	NaN

df_airports:

Index	airports
0	a
1	b
2	c
3	d
4	e
5	f

index_nearest_airport:

[3]

desired outcome:

df1:

Index	State	assigned_airport
0	Washington	NaN
1	New York	NaN
2	Illinois	NaN
3	Texas	NaN
4	Florida	d
5	Florida	d
6	Florida	d
7	Florida	d
8	Ohio	NaN
9	Colorado	NaN
10	Michigan	NaN
11	Indiana	NaN

df_state_group is a subset of df1 for the state of Florida

I have tried:

df1.loc[df_state_group.index, 'assigned_airport'] = df_airports.loc[index_nearest_airport,'airports'].values

but I keep on geting the error: ValueError: Must have equal len keys and value when setting with an iterable

I think you forgot to add index_nearest_airport in your question — Anurag Dabas
– Anurag Dabas, Commented Jul 24, 2021 at 15:13
@Anurag Dabas It's right before the last df, with the value of [3] — Moe
– Moe, Commented Jul 24, 2021 at 17:13
Actually your values is having single element but where you are assigning will need 4 values. Hence length mismatch . — Pygirl
– Pygirl, Commented Jul 24, 2021 at 18:13
For Python 3.9 I can't comment. You are having single element in index_nearest_airport. But if there are more values I don't know the logic for assignment thing :/. — Pygirl
– Pygirl, Commented Jul 24, 2021 at 20:03

Pygirl · Accepted Answer · 2021-07-24 18:11:19Z

1

This will do:

(df1.assign(assigned_airport= 
     pd.DataFrame(np.repeat(df_airports.loc[index_nearest_airport,'airports'].values
     ,df_state_group.shape[0]), columns = ['assigned_airport'],index=df_state_group.Index)))

OR

(df1.loc[df_state_group.Index, 'assigned_airport'] = 
     np.repeat(df_airports.loc[index_nearest_airport,'airports'].values,
     df_state_group.shape[0]))

Index	State	assigned_airport
0	Washington	NaN
1	New York	NaN
2	Illinois	NaN
3	Texas	NaN
4	Florida	d
5	Florida	d
6	Florida	d
7	Florida	d
8	Ohio	NaN
9	Colorado	NaN
10	Michigan	NaN
11	Indiana	NaN

answered Jul 24, 2021 at 18:11

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

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

1 Comment

Moe Over a year ago

Thanks a lot that works perfectly, btw, does this has anything to do with python 3.9.6 update? since the previous code used to work fine until I have updated my python version, plus I noticed that it works if the array single value to be assigned is other than dtype = object

Collectives™ on Stack Overflow

using .loc to assign values from another dataframe by index returns : ValueError: Must have equal len keys and value when setting with an iterable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related