Скачать или смотреть How to Update Column Values in a DataFrame from Another DataFrame in Python

Learn how to update values in a DataFrame by matching rows from another DataFrame using `Pandas` in Python, complete with code examples and explanations.
---
This video is based on the question https://stackoverflow.com/q/72454832/ asked by the user 'its.kcl' ( https://stackoverflow.com/u/17390230/ ) and on the answer https://stackoverflow.com/a/72454954/ provided by the user 'constantstranger' ( https://stackoverflow.com/u/18135454/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to update column value of a data frame from another data frame matching 2 columns?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Update Column Values in a DataFrame from Another DataFrame

If you are working with Pandas in Python, you may find yourself needing to update values in one DataFrame based on values in another. This commonly occurs when you're dealing with datasets that have similar attributes. For instance, you might have two datasets containing scores, and you want to update the scores in the main dataset based on updated values from a subset.

The Problem

Imagine you have two DataFrames in Python:

DataFrame 1:

DEP IDTeam IDGroupScore001002A50001004A70002002A50002007A90DataFrame 2 (a subset):

DEP IDTeam IDGroupResult001002A80001003A60001004A70You want to update the scores in DataFrame 1 where Team ID and Group match the corresponding values in DataFrame 2. The expected output would look like this:

DEP IDTeam IDGroupScore001002A80001004A70002002A80002007A90However, you might find that using a simple merge or join isn’t delivering the desired result. Fear not! Let’s walk through how to accomplish this correctly.

The Solution

To update the Score in DataFrame 1 based on values in DataFrame 2, you can follow these straightforward steps:

Step 1: Modify DataFrame 2

Firstly, you need to set the Team ID and Group as the index of DataFrame 2 and drop the irrelevant DEP ID column, keeping only the Result column.

Step 2: Join the DataFrames

Use the join method to bring in the Result values into DataFrame 1. This is done by linking the two DataFrames on the Team ID and Group columns.

Step 3: Update Missing Scores

Then, using the loc method, update the values in the Score column for rows that have a valid Result value (i.e., where Result is not null).

Step 4: Clean Up

Finally, drop the Result column, as it is no longer needed and only clutters your DataFrame.

Example Code

Here’s the complete code encompassing all of the above steps:

[[See Video to Reveal this Text or Code Snippet]]

Expected Output

This will yield the following output:

DEP IDTeam IDGroupScore001002A80001004A70002002A80002007A90Conclusion

Updating values in a DataFrame based on another DataFrame can initially seem challenging. However, using the combination of joining DataFrames and conditional updates makes this a manageable task. With these steps, you’ll be able to keep your data up-to-date and accurate efficiently!

Remember, if you decide to rename the Result column to Score in DataFrame 2 instead, just adjust the reference in your code slightly, and you’ll still achieve the desired outcome!