0

Here is the dataset that I have. The items below are recorded on a daily basis.

Cigarettes, Tobacco, Snack/Grocery, Beverages, Milk, Coffee, Solaray, Prepared Foods, International Foods, Automotive/NewsPaper, Lottery - Scratch, Lottery - Machine, Whl-Sales/Gift-Card are repeated per date.

I want to transform this frame to one that covers the same data, with the repeated departments as columns, Date as index and Sales as the values. I tried using pivot_table, but I realized it changes the values, the combination. This is how I thought about it, but it returned unexpected results... 

dept = dept.pivot_table(values='Sales', index = dept.index, columns='Dept', aggfunc='first')

and here is the original dataframe that I want to change. 

Date                   Dept        Sales
2018-12-01             Cigarettes  426.889
2018-12-01                Tobacco    43.84
2018-12-01          Snack/Grocery   198.57
2018-12-01              Beverages   160.97
2018-12-01                   Milk    11.56
2018-12-01                 Coffee    29.72
2018-12-01                Solaray     9.99
2018-12-01         Prepared Foods     3.99
2018-12-01     International Food       65
2018-12-01                 Sweets        0
2018-12-01  Automotive/News Paper    10.47
2018-12-01      Lottery - Scratch     1397
2018-12-01      Lottery - Machine      191
2018-12-01    Whl-Sales/Gift-Card        0
2018-12-01                  Total     2549
2018-12-02             Cigarettes   374.01
2018-12-02                Tobacco    89.29
2018-12-02          Snack/Grocery   178.01
2018-12-02              Beverages   135.28
2018-12-02                   Milk     9.57
2018-12-02                 Coffee    33.76
2018-12-02                Solaray    17.99
2018-12-02         Prepared Foods    20.98
2018-12-02     International Food     3.98
2018-12-02                 Sweets        0
2018-12-02  Automotive/News Paper    13.16
2018-12-02      Lottery - Scratch      651
2018-12-02      Lottery - Machine      211
2018-12-02    Whl-Sales/Gift-Card        0
2018-12-02                  Total  1738.03
2018-12-03             Cigarettes   463.54
2018-12-03                Tobacco    35.26
2018-12-03          Snack/Grocery   164.19
2018-12-03              Beverages   126.01
2018-12-03                   Milk     8.57
2018-12-03                 Coffee    30.47
2018-12-03                Solaray    17.99
2018-12-03         Prepared Foods        0
2018-12-03     International Food    21.98
2018-12-03                 Sweets        0
2018-12-03  Automotive/News Paper    70.17
2018-12-03      Lottery - Scratch     1046
2018-12-03      Lottery - Machine      461
2018-12-03    Whl-Sales/Gift-Card        0
2018-12-03                  Total  2445.18
2018-12-03             Cigarettes   463.54
2018-12-03                Tobacco    35.26
2018-12-03          Snack/Grocery   164.19
2018-12-03              Beverages   126.01
2018-12-03                   Milk     8.57
2018-12-03                 Coffee    30.47
2018-12-03                Solaray    17.99
2018-12-03         Prepared Foods        0
2018-12-03     International Food    21.98
2018-12-03                 Sweets        0
2018-12-03  Automotive/News Paper    70.17
2018-12-03      Lottery - Scratch     1046
2018-12-03      Lottery - Machine      461
2018-12-03    Whl-Sales/Gift-Card        0
2018-12-03                  Total  2445.18
2018-12-04             Cigarettes   291.91
2018-12-04                Tobacco    42.93
2018-12-04          Snack/Grocery   207.87
2018-12-04              Beverages   163.11
2018-12-04                   Milk     3.99
2018-12-04                 Coffee    32.17
2018-12-04                Solaray    40.98
2018-12-04         Prepared Foods        5
2018-12-04     International Food     6.98
2018-12-04                 Sweets        0
2018-12-04  Automotive/News Paper       47
2018-12-04      Lottery - Scratch      762
2018-12-04      Lottery - Machine   112.75
2018-12-04    Whl-Sales/Gift-Card      NaN
2018-12-04                  Total  1716.69
2018-12-05             Cigarettes   255.72
2018-12-05                Tobacco    81.52
2018-12-05          Snack/Grocery   212.94
2018-12-05              Beverages    87.94
2018-12-05                   Milk     9.77
2018-12-05                 Coffee    15.95
2018-12-05                Solaray    11.98
2018-12-05         Prepared Foods     8.98
2018-12-05     International Food    17.73
2018-12-05                 Sweets        0
2018-12-05  Automotive/News Paper    46.24
2018-12-05      Lottery - Scratch      540
2018-12-05      Lottery - Machine      151
2018-12-05    Whl-Sales/Gift-Card      NaN
2018-12-05                  Total  1439.77
2018-12-06             Cigarettes   377.96
2018-12-06                Tobacco   129.07
2018-12-06          Snack/Grocery   281.83
2018-12-06              Beverages   235.73
2018-12-06                   Milk        0
2018-12-06                 Coffee    29.32
2018-12-06                Solaray    12.99
2018-12-06         Prepared Foods    27.37
2018-12-06     International Food     9.99
2018-12-06                 Sweets        5
2018-12-06  Automotive/News Paper    32.92
2018-12-06      Lottery - Scratch      509
2018-12-06      Lottery - Machine      194
2018-12-06    Whl-Sales/Gift-Card      NaN
2018-12-06                  Total  1845.18
2018-12-07             Cigarettes   526.91
2018-12-07                Tobacco    65.71
2018-12-07          Snack/Grocery   202.27
2018-12-07              Beverages   183.59
2018-12-07                   Milk     2.79
2018-12-07                 Coffee    16.22
2018-12-07                Solaray     5.99
2018-12-07         Prepared Foods    24.98
2018-12-07     International Food     1.99
2018-12-07                 Sweets        0
2018-12-07  Automotive/News Paper    31.06
2018-12-07      Lottery - Scratch      300
2018-12-07      Lottery - Machine     61.5
2018-12-07    Whl-Sales/Gift-Card        0
2018-12-07                  Total  1423.01
Improve this question
3
  • What is your expected output? Do you want to sum the results? Try df.pivot_table(index='Date', columns='Dept', values='Sales', aggfunc=sum) Commented Aug 17, 2018 at 1:57
  • Is there any reason you have duplicate Dept and values for 2018-12-03 - is that to be expected? What were your unexpected results? Commented Aug 17, 2018 at 2:05
  • Yeah the duplication is a good point, it is only done to this section... will check it out and let you know thanks Commented Aug 17, 2018 at 21:44

1 Answer 1

Reset to default
1

One way to do this would be to set the index to ['Date', 'Dept'] and unstack() but you have multiple values for each Dept for the date 2018-12-03.

Note sure if that is expected but one way to resolve that issues is to groupby().first() to take the first value and then unstack(), e.g.:

In []:
df.set_index(['Date', 'Dept']).groupby(level=[0, 1]).first().unstack()

Out []:
                           Sales                                                                                                                                                                           
Dept       Automotive/News Paper Beverages Cigarettes Coffee International Food Lottery - Machine Lottery - Scratch   Milk Prepared Foods Snack/Grocery Solaray Sweets Tobacco    Total Whl-Sales/Gift-Card
Date                                                                                                                                                                                                       
2018-12-01                 10.47    160.97    426.889  29.72              65.00            191.00            1397.0  11.56           3.99        198.57    9.99    0.0   43.84  2549.00                 0.0
2018-12-02                 13.16    135.28    374.010  33.76               3.98            211.00             651.0   9.57          20.98        178.01   17.99    0.0   89.29  1738.03                 0.0
2018-12-03                 70.17    126.01    463.540  30.47              21.98            461.00            1046.0   8.57           0.00        164.19   17.99    0.0   35.26  2445.18                 0.0
2018-12-04                 47.00    163.11    291.910  32.17               6.98            112.75             762.0   3.99           5.00        207.87   40.98    0.0   42.93  1716.69                 NaN
2018-12-05                 46.24     87.94    255.720  15.95              17.73            151.00             540.0   9.77           8.98        212.94   11.98    0.0   81.52  1439.77                 NaN
2018-12-06                 32.92    235.73    377.960  29.32               9.99            194.00             509.0   0.00          27.37        281.83   12.99    5.0  129.07  1845.18                 NaN
2018-12-07                 31.06    183.59    526.910  16.22               1.99             61.50             300.0   2.79          24.98        202.27    5.99    0.0   65.71  1423.01                 0.0

But this is almost identical to df.pivot_table(index='Date', columns='Dept', values='Sales'):

Dept        Automotive/News Paper  Beverages  Cigarettes  Coffee  International Food  Lottery - Machine  Lottery - Scratch   Milk  Prepared Foods  Snack/Grocery  Solaray  Sweets  Tobacco    Total  Whl-Sales/Gift-Card
Date                                                                                                                                                                                                                    
2018-12-01                  10.47     160.97     426.889   29.72               65.00             191.00             1397.0  11.56            3.99         198.57     9.99     0.0    43.84  2549.00                  0.0
2018-12-02                  13.16     135.28     374.010   33.76                3.98             211.00              651.0   9.57           20.98         178.01    17.99     0.0    89.29  1738.03                  0.0
2018-12-03                  70.17     126.01     463.540   30.47               21.98             461.00             1046.0   8.57            0.00         164.19    17.99     0.0    35.26  2445.18                  0.0
2018-12-04                  47.00     163.11     291.910   32.17                6.98             112.75              762.0   3.99            5.00         207.87    40.98     0.0    42.93  1716.69                  NaN
2018-12-05                  46.24      87.94     255.720   15.95               17.73             151.00              540.0   9.77            8.98         212.94    11.98     0.0    81.52  1439.77                  NaN
2018-12-06                  32.92     235.73     377.960   29.32                9.99             194.00              509.0   0.00           27.37         281.83    12.99     5.0   129.07  1845.18                  NaN
2018-12-07                  31.06     183.59     526.910   16.22                1.99              61.50              300.0   2.79           24.98         202.27     5.99     0.0    65.71  1423.01                  0.0
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.