1

How can I achieve this in DoT Product? I am not able to append one Pandas DataFrame to another. I don't want lengthy solutions, but a simple code with Pandas and Numpy only.

Product Prices: 

Apple Price = 3
Cherry Price = 4
Blueberry Price = 2

Expected Output: 

                    Apple        Cherry     Blueberry       
Mon                 13           8             6
Tues                9            7             4
Wed                 7            4             0
Thurs               15           6             3
Total Rev.($)       83           63            37

My code: 

prod_prices = np.array([3,4,2])
prod_prices

prod_days = np.array([[13,9,7,15],
                    [8,7,4,6],
                    [6,4,0,3]]).T
prod_days

df_week_sales = pd.DataFrame(prod_days,
                 index=["Mon","Tues","Wed","Thurs"],
                columns=["Apple","Cherry","Blueberry"])
df_week_sales

weekly_total = df_week_sales.dot(prod_prices)
weekly_total

type(weekly_total)

weekly_total_nparray = np.array(weekly_total)

type(weekly_total_nparray)

weekly_total_nparray

df_weekly_total_nparray = pd.DataFrame(weekly_total_nparray.reshape(1,4))

df_weekly_total_nparray
weekly_revenue_data = df_week_sales.append(df_weekly_total_nparray)

weekly_revenue_data

And this gives me the following output:

enter image description here

Improve this question

3 Answers 3

Reset to default
2

You may try something like this:

prod_prices = np.array([3,4,2])

prod_days = np.array([[13,9,7,15],
                    [8,7,4,6],
                    [6,4,0,3]]).T
df_week_sales = pd.DataFrame(prod_days,
                 index=["Mon","Tues","Wed","Thurs"],
                columns=["Apple","Cherry","Blueberry"])

# obtain the dot product
weekly_total = prod_days.dot(prod_prices)

# obtain the last row index of the data frame
jj = df_week_sales.shape[0]

# get the list to append to the data frame (need to be of the same column length)
df_week_sales.loc[jj] = weekly_total[:-1]

# rename the last index as desired
df_week_sales.rename(index={jj: 'Total Rev.($)'}, inplace=True)
df_week_sales
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

just WOW. Could you please explain what is your code doing exactly starting from "weekly_total = df_week_sales.dot(prod_prices).to_list()"
13+9+7+15 = 44 apples, each apple is $3, total is 44*3 = 132.
@lrh09 The aim of the code is to show how to append rows to the existing pandas' data frame and obtain the desired result using the same workflow.
2 
prices = {'Apple': 3, 'Cherry': 4, "Blueberry": 2}
df_week_sales = pd.DataFrame(prod_days,
                 index=["Mon","Tues","Wed","Thurs"],
                columns=["Apple","Cherry","Blueberry"])

df_week_sales

       Apple  Cherry  Blueberry
Mon       13       8          6
Tues       9       7          4
Wed        7       4          0
Thurs     15       6          3

The transpose:

df = df_week_sales.transpose()

           Mon  Tues  Wed  Thurs
Apple       13     9    7     15
Cherry       8     7    4      6
Blueberry    6     4    0      3

df['Total Revenue'] = [np.sum(df_week_sales[x]*prices[x]) for x in df_week_sales.columns]


df

           Mon  Tues  Wed  Thurs  Total Revenue
Apple       13     9    7     15            132
Cherry       8     7    4      6            100
Blueberry    6     4    0      3             26

results = df.transpose()

               Apple  Cherry  Blueberry
Mon               13       8          6
Tues               9       7          4
Wed                7       4          0
Thurs             15       6          3
Total Revenue    132     100         26

prod_prices = np.array([3,4,2])
results['Revenue'] = df_week_sales.dot(prod_prices)

               Apple  Cherry  Blueberry  Revenue
Mon               13       8          6     83.0
Tues               9       7          4     63.0
Wed                7       4          0     37.0
Thurs             15       6          3     75.0
Total Revenue    132     100         26      NaN

Understand that your expected output is having 83, 63, 37 at the bottom, but it makes no sense at all. 83 = 13*3 + 8*4 + 6*2, 63 = 9*3 + 4*2, similarly for 37. Then where is your 75. Data are not meant to be displayed in that direction. So I will stick to this way, which is more meaningful in representing the data.

Improve this answer

1 Comment

But the resulting values are not correct here. Please recheck the "expected output" section.
1

You can achieve this with two lines:

total_revenues = [df_week_sales[x].sum() for x in df_week_sales.columns]* prod_prices
output = pd.concat([df_week_sales, pd.DataFrame(total_revenues, index= df_week_sales.columns, columns = ['Total Revenue']).T])

Output

|               |   Apple |   Cherry |   Blueberry |
|:--------------|--------:|---------:|------------:|
| Mon           |      13 |        8 |           6 |
| Tues          |       9 |        7 |           4 |
| Wed           |       7 |        4 |           0 |
| Thurs         |      15 |        6 |           3 |
| Total Revenue |     132 |      100 |          26 |
Improve this answer

1 Comment

But the resulting values are not correct here. Please recheck the "expected output" section

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.