Multiply and append python

Question

I have a pandas dataframe

   Colour   Type    Cost    Price   Type
0  Red      Car     3        5       Standard
1  Blue     Bike    6        7       Standard
2  Blue     Car     4        8       Standard
3  Green    Bike    6        9       Standard
4   Yellow  Bike    3        3       Standard

I then have an adjustment series, which I want to multiply the costs by and add to the bottom

Red   2
Blue  1
Green 3

So the output is:

   Colour   Type    Cost    Price   Type
0  Red      Car     3        5       Standard
1  Blue     Bike    6        7       Standard
2  Blue     Car     4        8       Standard
3  Green    Bike    6        9       Standard
4   Yellow  Bike    3        3       Standard
0  Red      Car     6        10       Adjusted
1  Blue     Bike    6        7       Adjusted
2  Blue     Car     4        8       Adjusted
3  Green    Bike    18        27       Adjusted

Is there an easy way of doing this as I'm a bit lost?

Answer 1

You can use merge and concat.

Adjustment dataframe:

   Colour   value
0   Red     2
1   Blue    1
2   Green   3

Then:

temp = df.merge(adj)

temp["Cost"] = temp["Cost"]*temp["value"]
temp["Price"] = temp["Price"]*temp["value"]
temp["Type.1"] = ["Adjusted"]*temp.shape[0]


pd.concat([df, temp.iloc[:,0:5]], axis=0)

Output:

    Colour  Type    Cost    Price   Type.1
0   Red     Car     3   5   Standard
1   Blue    Bike    6   7   Standard
2   Blue    Car     4   8   Standard
3   Green   Bike    6   9   Standard
4   Yellow  Bike    3   3   Standard
0   Red     Car     6   10  Adjusted
1   Blue    Bike    6   7   Adjusted
2   Blue    Car     4   8   Adjusted
3   Green   Bike    18  27  Adjusted