Python Pandas can only compare identically-labelled series objects

Question

I have three data frame. Here's my code and I am getting value error.
raise ValueError("Can only compare identically-labeled Series objects")

ValueError: Can only compare identically-labeled Series objects.

Here is my code:

df1
df2
df3
for i in df1:                 # for i from df1
     x = df2.loc[df2['col_1'] == i, 'col_2']   #looking for i in col_1 of df2 and getting coresponding value of col_2 as x.
     y = df3.loc[df3['col_1'] == x, 'col_2']   #looking for x in col_1 of df3 and getting coresponding value of col_2 as y

The first statement in for loop runs correctly but getting value error in second statement.

when you run `for i in df1` , `i` only takes the column name of `df1`. I don't think that's what you want. are you iterating over rows of `df1`? — Reza, Aug 29 '20 at 07:36
df1 has only one column. it runs fine when i commented the 2nd line of 'for' loop. — Sourabh Mittal, Aug 29 '20 at 07:42
so you might have multiple matches in `x` and `x` is not a single value. You can try this `x = df2.loc[df2['col_1'] == i, 'col_2'].iloc[0]` . — Reza, Aug 29 '20 at 07:44
Have you tried [`merge`](https://stackoverflow.com/q/53645882/4985099) ? — sushanth, Aug 29 '20 at 07:46
Please provide also subsets of all your DataFrames with which your error occurs. It is impossible to check how your code works without data is operates on. — Valdi_Bo, Aug 29 '20 at 07:48
here are subsets of my df's `#df1` `500` `459 ` `722` `273 ` `#df2` `500 a` `459 d` `722 b` `273 c` `#df3` `a 100` `b 200` `c 300` `d 400` — Sourabh Mittal, Aug 29 '20 at 07:57
if m wiritng like `y = df3.loc[df3['col_1'] == 'x', 'col_2']` then the value error is gone. but still it not working — Sourabh Mittal, Aug 29 '20 at 08:29

score 2 · Accepted Answer · answered Aug 29 '20 at 09:01

Assume that your 3 DataFrames have the following content:

df1:                df2:              df3:
       Aa    Bb       col_1 col_2       col_1 col_2
0  123.15  12.6     0    Aa    Cc     0    Cc    Gg
1  137.53  28.3     1    Bb    Dd     1    Dd    Hh
                    2    Bb    Ee     2    Ee    Jj
                                      3    Ff    Kk

In the first turn of your loop i contains the name of the first column in df1, i.e. 'Aa'.

When you execute x = df2.loc[df2['col_1'] == i, 'col_2'], the result is a Series:

0    Cc
Name: col_2, dtype: object

And now, even if you attempted to execute df3['col_1'] == x, your error occurs.

Note that in this case both df3['col_1'] and x are of Series type. In this case:

the first thing Pandas does is to align both Series (on the index),
and then it would compare each pair of (aligned) elements.

But in this case:

df3['col_1'] contains indices 0 thru 3,
the index in x contains only one position - 0.

So there is alignment failure, which causes this exception.

To cope with this issue, change the offending line to:

y = df3.loc[df3['col_1'].isin(x), 'col_2']

Now Pandas operates just as you intended:

iterates over df3['col_1'],
for the current element checks whether its value is among values present in x,
if it is, value from col_2 in the current row is added to the result.

To demonstrate how this code works, complete it with some printouts:

for i in df1:
    print(f'\ni: {i}')
    x = df2.loc[df2['col_1'] == i, 'col_2']
    print(f'\nx:\n{x}')
    y = df3.loc[df3['col_1'].isin(x), 'col_2']
    print(f'\ny:\n{y}')

When you run the above code, on my data, the result is:

i: Aa

x:
0    Cc
Name: col_2, dtype: object

y:
0    Gg
Name: col_2, dtype: object

i: Bb

x:
1    Dd
2    Ee
Name: col_2, dtype: object

y:
1    Hh
2    Jj
Name: col_2, dtype: object

Python Pandas can only compare identically-labelled series objects

1 Answers1