Pandas pivot table acting on a pivoted dataframe

Question

I have a dataframe that is already pivoted that looks something like the following.

             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 cost and transport expenses associated with buying items from the manufacturer and in which currency the cost and expenses are denominated in.

What I am trying to do is aggregate the numbers and place them under the currency. The desired output is (I have left the addition un-evaluated so that it is clear where it comes 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 have tried

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

which Pandas doesn't like at all giving a KeyError.

Here's the code to get the starting dataframe, df. In the actual use case, the data absolutely needs to be pivoted first for analysis and aggregation so please do not recommend applying the pivot table on 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')

Pandas pivot table acting on a pivoted dataframe

2 Answers