CODEWITHSUNDEEP
pythonpandas
maroov
maroov

Reputation: 43

Relating column names with data from a different DataFrame

So, I have three DataFrames:

  1. One containing the amount of sales per item (row) per day (column).
  2. One containing a column with all days and a column with the weeks.
  3. One containing a column with all items, their weekly price and a column with the weeks.

What I would like to do is to modify the first table so that instead of displaying the sales it displays that number multiplied by the price that the item had that day. That is (pseudocode):

for all items (rows) and days (columns) in table1:

   table1(item, day) = table1(item, day) * table3.prices(item, table2.weeks(day))

And I would like to avoid loops and use Pandas operations.

EDIT:

Table 1 is something like this:

+-------+------+------+------+
|  item | day1 | day2 | day3 |
+-------+------+------+------+
| item1 |   0  |   2  |   3  |
+-------+------+------+------+
| item2 |   1  |   5  |   3  |
+-------+------+------+------+
| item3 |  12  |   7  |   8  |
+-------+------+------+------+

Table 2:

+------+-------+
|  day |  week |
+------+-------+
| day1 | week1 |
+------+-------+
| day2 | week2 |
+------+-------+
| day3 | week2 |
+------+-------+

Table 3:

+-------+-------+-------+
|  item |  week | price |
+-------+-------+-------+
| item1 | week1 |   3   |
+-------+-------+-------+
| item1 | week1 |   4   |
+-------+-------+-------+
| item2 | week2 |   7   |
+-------+-------+-------+
| item2 | week2 |   9   |
+-------+-------+-------+
| item3 | week1 |   2   |
+-------+-------+-------+
| item3 | week2 |   3   |
+-------+-------+-------+

Therefore, the expected output would be (if I calculated properly):

+-------+------+------+------+
|  item | day1 | day2 | day3 |
+-------+------+------+------+
| item1 |   0  |   8  | 12   |
+-------+------+------+------+
| item2 |   7  |  45  | 27   |
+-------+------+------+------+
| item3 |  24  |  21  | 24   |
+-------+------+------+------+

Upvotes: 1

Views: 55

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150735

I would do a double merge:

# stack the fist table so we can use `day` information
s=tbl1.melt('item', value_name='unit', var_name='day')

(s.merge(tbl3.merge(tbl2,on='week',how='inner'),
       on=['item','day'], how='outer')
  .assign(total=lambda x: x['unit']*x['price'])
   .pivot(index='item',columns='day',values='total')
)

Output:

day    day1  day2  day3
item                   
item1     0     8    12
item2     7    45    27
item3    24    21    24

Update:

Another way is to get get the daily price by day and multiply:

daily = (tbl3.merge(tbl2,on='week',how='inner')
     .set_index(['item','day']).price
     .unstack()
)

output = tbl1.set_index('item') * daily

Output:

       day1  day2  day3
item                   
item1     0     8    12
item2     7    45    27
item3    24    21    24

Upvotes: 2

Related Questions