CODEWITHSUNDEEP
pythonpandas
Dcook
Dcook

Reputation: 961

How to compare two dataframe based on column name of the dataframe

I have two pandas dataframes like this:

df1:

Product  pricing_type
prod1    I
prod2    p
prod3    E

df2:

id  internal_price  external price pilot_price
1    0.7             0.89             0.3

The output I want: df3

Product  pricing_type  price
prod1    I              0.7
prod2    P              0.3
prod3    E              0.89

How can I achieve this efficiently?

Upvotes: 3

Views: 53

Answers (2)

swagless_monk
swagless_monk

Reputation: 471

I would make use of the transpose method. For this problem, it seems like you don't really need to worry about joining on a particular field, but just to move one column from df2 to df1

#transpose the dataframe with the row values
df2 = df2.T.reset_index()

#column you want to join (since the 'id' value from df2 is 1)
prices = df2[1]

#add column to dataframe
df1 = df1.join(prices)

If you wanted to move multiple rows, you would just change

#column you want to join
prices = df2[1]

#add column to dataframe
df1 = df1.join(prices)

to

for col in df2:
    df1 = df1.join(df2[col])

and select the columns you need

Upvotes: 0

jezrael
jezrael

Reputation: 862921

Use for better performance first rename columns and then DataFrame.melt:

d = {'internal_price':'I','external price':'E','pilot_price':'p'}
df2 = df2.rename(columns=d).melt('id', var_name='pricing_type', value_name='price')
print (df2)
   id pricing_type  price
0   1            I   0.70
1   1            E   0.89
2   1            p   0.30

And last add to df1 like:

df = df1.merge(df2, on='pricing_type', how='left')

Upvotes: 4

Related Questions