Combine an index from one Pandas DataFrame with a different index in a different Pandas DataFrame

Question

Pandas newbie here so this may be a fairly basic and easy question.

I want to combine the data from one index in one DataFrame into a different index in a different DataFrame.

In the below example, I've split the data into a DataFrame of verified indexes (MainTable) and a DataFrame of erroneous indexes (ErrorTable).

After manually reviewing the erroneous entries in ErrorTable, I want to be able merge the data from ErrorTable's 'Cta' and 'Mice' entries into MainTable's 'Cat' and 'Mouse' entries.

import pandas as pd
RawData = pd.DataFrame({'Number sighted today':[4, 3, 1, 2, 8, 3],
                        'Number sighted yesterday':[5, 1, 0, 2, 1, 0]},
                       index = ['Dog', 'Cat', 'Cta', 'Mouse', 'Ant', 'Mice'])

AllowedIndexes = ['Dog','Cat','Mouse','Ant']

MainTable = RawData[RawData.index.isin(AllowedIndexes) == True]
ErrorTable = RawData[RawData.index.isin(AllowedIndexes) == False]

I have tried:

MainTable.loc['Cat'] = MainTable.loc['Cat'] + ErrorTable.loc['Cta']
MainTable.loc['Mouse'] = MainTable.loc['Mouse'] + ErrorTable.loc['Mice']

And while it technically works, it keeps throwing up a warning message that I'm doing something wrong which I haven't been unable to understand, so I figure there must be a more stable way to do this.

Thanks!

mosc9575 · Accepted Answer · 2021-02-01 19:12:08Z

1

If you mean an elementwise addition, this could look like this:

MainTable.loc['Mouse', :] += ErrorTable.loc['Mice', :]
MainTable.loc['Cat', :] += ErrorTable.loc['Cta', :]

>>> MainTable
       Number sighted today  Number sighted yesterday
Dog                       4                         5
Cat                       4                         1
Mouse                     5                         2
Ant                       8                         1

edited Feb 1, 2021 at 19:12

answered Feb 1, 2021 at 19:03

mosc9575

6,3922 gold badges12 silver badges36 bronze badges

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

4 Comments

mosc9575 Over a year ago

What do you mean with 'combine'?

Rockall Over a year ago

Thanks, this seems to do the job too. I'm still getting the big red scary SettingWithCopy warning that I don't understand. Would be grateful if anybody could explain it to me in layman's terms.

mosc9575 Over a year ago

The warning comes from the different type of slicing. It seems to be that MainTable.loc['Cat'] creates a copy and this copy is stored in a dummy variable. If your work with this dummy, the warning appears. To avoid this, use the : which means all values.

Rockall Over a year ago

I added .copy() to the end of the original MainTable and ErrorTable declaration statements and that seems to have sorted out the warning! dataquest.io/blog/settingwithcopywarning

Collectives™ on Stack Overflow

Combine an index from one Pandas DataFrame with a different index in a different Pandas DataFrame

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