Create new column in dataframe with match values from other dataframe

2024/4/15 0:58:55

Have two dataframes, one has few information (df1) and other has all data (df2). What I am trying to create in a new column in df1 that finds the Total2 values and populates the new column accordingly based on the Names. Note that the Names visible in df1 will always find a match in Names of df2. I am wondering if there is some function in Pandas that already does this? My end goal is to create a bar chart.

alldatapath = "all_data.csv"
filteredpath = "filtered.csv"import pandas as pddf1 = pd.read_csv(filteredpath,     # file namesep=',',                    # column separatorquotechar='"',              # quoting characterna_values="NA",                # fill missing values with 0usecols=[0,1],     # columns to usedecimal='.')                # symbol for decimalsdf2 = pd.read_csv(alldatapath,     # file namesep=',',                    # column separatorquotechar='"',              # quoting characterna_values="NA",                # fill missing values with 0usecols=[0,1],     # columns to usedecimal='.')                # symbol for decimalsdf1 = df1.head(5) #trim to top 5print(df1)
print(df2)

output (df1):

         Name  Total
0  Accounting      3
1   Reporting      1
2     Finance      1
3       Audit      1
4    Template      2

output (df2):

          Name   Total2
0    Reporting    100
1   Accounting    120
2      Finance    400
3        Audit    500
4  Information     50
5     Template   1200
6      KnowHow   2000

Final Output (df1) should be something like:

         Name  Total  Total2(new column)
0  Accounting      3    120
1   Reporting      1    100
2     Finance      1    400
3       Audit      1    500
4    Template      2   1200
Answer

Need map by Series first for new column:

df1['Total2'] = df1['Name'].map(df2.set_index('Name')['Total2'])
print (df1)Name  Total  Total2
0  Accounting      3     120
1   Reporting      1     100
2     Finance      1     400
3       Audit      1     500
4    Template      2    1200

And then set_index with DataFrame.plot.bar:

df1.set_index('Name').plot.bar()
https://en.xdnf.cn/q/73073.html

Related Q&A

MYSQL- python pip install error

I tried to get build an app on Django and I wanted to use MySQL as the database. After setting up the settings.py right, I tried to migrate. Then I got the obvious error saying that MySQL is not instal…

How to do a boxplot with individual data points using seaborn

I have a box plot that I create using the following command: sns.boxplot(y=points_per_block, x=block, data=data, hue=habit_trial)So the different colors represent whether the trial was a habit trial or…

Load QDialog directly from UI-File?

I work with QT Designer and create my GUIs with it. To launch the main program, I use this code:import sys from PyQt4 import uic, QtGui, QtCore from PyQt4.QtGui import * from PyQt4.QtCore import *try:_…

Is there a way to detect if running code is being executed inside a context manager?

As the title states, is there a way to do something like this:def call_back():if called inside context:print("running in context")else:print("called outside context")And this would …

Adding title to the column of subplot below suptitle

Is there a simple way to add in to my original code so that I can add another title to both column of my subplot? for example like somewhere in the pink region shown in the picture below.Someone refer…

Conditional Inheritance based on arguments in Python

Being new to OOP, I wanted to know if there is any way of inheriting one of multiple classes based on how the child class is called in Python. The reason I am trying to do this is because I have multip…

Slice endpoints invisibly truncated

>>> class Potato(object): ... def __getslice__(self, start, stop): ... print start, stop ... >>> sys.maxint 9223372036854775807 >>> x = sys.maxint + 69 >…

Selenium Webdriver with Java vs. Python

Im wondering what the pros and cons are of using Selenium Webdriver with the python bindings versus Java. So far, it seems like going the java route has much better documentation. Other than that, it s…

asyncio - how many coroutines?

I have been struggling for a few days now with a python application where I am expecting to look for a file or files in a folder and iterate through the each file and each record in it and create objec…

Calculating a 3D gradient with unevenly spaced points

I currently have a volume spanned by a few million every unevenly spaced particles and each particle has an attribute (potential, for those who are curious) that I want to calculate the local force (ac…