Python match a column name based on a column value in another dataframe

Tim B Published at Dev

Tim B

Apologies if this is a duplicate of some sort, I looked at 20 different questions, but none of them helped me. If someone can point me to a question that answers this, I'll happily delete my question.

I have two dataframes, the first is called df_full long list of various columns, one of which is called 'Industry' and has the strings of various Industries. df_full['Industry'].head() is:

INDEX	Industry
0	Service
1	Service
2	Trade
3	Service
4	Manufacturing

My second dataframe is called df_industry and has quantiles based on each of the industries. df_industry['profit_sales'] is:

Industry
Financial	0.25	0.025616
	0.50	0.219343
	0.75	0.410408
Manufacturing	0.25	-0.012373
	0.50	0.002032
	0.75	0.010331
Service	0.25	-0.012660
	0.50	0.003375
	0.75	0.064102
Trade	0.25	-0.102178
	0.50	0.001715
	0.75	0.018705
Transport	0.25	-0.042755
	0.50	-0.042755
	0.75	0.056487

I am trying to create a new column for my first dataframe with the 0.5 quantile according to the industry in column industry.

Thus my new output table should look like, df_full[['Industry','quantile_05']].head()

INDEX	Industry	quantile_05
0	Service	0.003375
1	Service	0.003375
2	Trade	0.001715
3	Service	0.003375
4	Manufacturing	0.002032

I have currently tried to no avail: df_full['quantile_05'] = df_full.apply(lambda x: df_industry['profit_sales'][df_full['Industry'][x]][0.5] ,axis=1)

Quang Hoang

It looks like you can do a map:

df_full['quantile_05'] = df_full['Industry'].map(df_industry['profit_sales'].unstack()[0.5])

Output:

             Industry  quantile_05
INDEX                             
0             Service     0.003375
1             Service     0.003375
2               Trade     0.001715
3             Service     0.003375
4       Manufacturing     0.002032

If you want all three quantiles, you can do a merge as suggested by Kyle:

df_full.merge(df_industry['profit_sales'].unstack(),
          left_on=['Industry'], 
          right_index=True,
          how='left')

Output:

             Industry      0.25       0.5      0.75
INDEX                                              
0             Service -0.012660  0.003375  0.064102
1             Service -0.012660  0.003375  0.064102
2               Trade       NaN  0.001715  0.018705
3             Service -0.012660  0.003375  0.064102
4       Manufacturing -0.012373  0.002032  0.010331

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-5

Comments

0 comments

TOP Ranking

Article

Python match a column name based on a column value in another dataframe

Python match a column name based on a column value in another dataframe

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Double spacing in rmarkdown pdf

SQL Server : need add a dot before two last character

C++ 16 bit grayscale gradient image from 2D array

JMeter: Why get error when try to save test plan

JWT gives JsonWebTokenError "invalid token"

How to make thrown errors visible outside of a Promise?

How to tell if iOS Today Widget is being updated in the background?

Calling Doctrine clear() with an argument is deprecated

Capybara Selenium Chrome opens About Google Chrome

How to update azerothcore-wotlk docker container

Adding Ripple Effect to RecyclerView item

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Error while applying filter on dataframe - PySpark

Unable to add slack to bluemix project

MyPy fails dataclass argument with optional list of objects type

How can I validate and parse phone numbers to extract their country calling code and area code?

Single Sign-On in Spring by using SAML Extension and Shibboleth

python how to create many-to-many of lists inside one list