Pandas hinged pivot table

Question

I have a data frame that is already rotated, which looks something like this.

             Cost      Transport     Currency        
Manufacturer  ABC  XYZ       ABC XYZ      ABC     XYZ
Date                                                 
2017-07-01    312  323        31  41   Pounds  Pounds
2017-07-02    423  335        21  32  Dollars  Pounds
2017-07-03    421  304        21  21  Dollars  Pounds

The above shows the costs and transportation costs associated with the purchase of goods from the manufacturer and in what currency the cost and expenses are indicated.

What I'm trying to do is fill in the numbers and put them under the currency. Desired outcome (I left the appendix unappreciated so that it was clear where it came from)

             Currency        
             Dollars     Pounds
Date                                                 
2017-07-01     0        312+323+31+41   
2017-07-02    423+21       335+32  
2017-07-03    421+21       304+21  

I tried

df.pivot_table(index='Date', columns='Currency', aggfunc=np.sum)

which Pandas doesn't like to give KeyError at all.

Here's the code to get the original data frame, df. In the actual use case, it is absolutely necessary to first turn the data over for analysis and aggregation, so please do not use the pivot table in my_list or df_raw.

my_list = ["2017-07-01", "ABC",312, 31, "Pounds",  "2017-07-01", "XYZ" ,323, 41, "Pounds",
           "2017-07-02", "ABC", 423, 21, "Dollars", "2017-07-02", "XYZ" ,335, 32, "Pounds",
           "2017-07-03", "ABC", 421, 21, "Dollars", "2017-07-03", "XYZ", 304, 21, "Pounds" ]
df_raw = pd.DataFrame(np.array(my_list).reshape(6,5),
                   columns = ["Date", "Manufacturer", "Cost", "Transport", "Currency"])
df = df_raw.pivot(index='Date', columns='Manufacturer')

Answer 1

stack, groupby, sum, unstack:

:

my_list = ["2017-07-01", "ABC",312, 31, "Pounds",  "2017-07-01", "XYZ" ,323, 41, "Pounds",
           "2017-07-02", "ABC", 423, 21, "Dollars", "2017-07-02", "XYZ" ,335, 32, "Pounds",
           "2017-07-03", "ABC", 421, 21, "Dollars", "2017-07-03", "XYZ", 304, 21, "Pounds" ]
df_raw = pd.DataFrame(np.array(my_list).reshape(6,5),
                   columns = ["Date", "Manufacturer", "Cost", "Transport", "Currency"])
df = df_raw.pivot(index='Date', columns='Manufacturer')

df = df.apply(pd.to_numeric,errors='ignore')

:

df.stack().groupby(['Date','Currency']).sum().sum(1).unstack(fill_value=0)

:

Currency    Dollars  Pounds
Date                       
2017-07-01        0     707
2017-07-02      444     367
2017-07-03      442     325

Answer 2

2:

df2 = df.stack()
df2['total'] = df2['Cost'] + df2['Transport'] 
df2.reset_index(inplace = True)
df2.pivot_table(index = 'Date', columns = 'Currency', values = 'total', aggfunc = np.sum, fill_value = 0)

: , . ...

...

df_raw['total_cost'] = df_raw['Cost'] + df_raw['Transport']
df_raw.pivot_table(index = 'Date', columns = 'Currency', values = ['total_cost'], aggfunc = 'sum', fill_value = 0)