Pandas - Create New Attribute and Value for Groups by Dividing EAV Format Data

user_12352743 0
Viewed 100

Suppose I have a dataframe like such:

import pandas as pd
import numpy as np

data = [[5123, '2021-01-01 00:00:00', 'cash','sales$', 105],
        [5123, '2021-01-01 00:00:00', 'cash','items', 20],
        [5123, '2021-01-01 00:00:00', 'card','sales$', 355],
        [5123, '2021-01-01 00:00:00', 'card','items', 50],
        [5123, '2021-01-02 00:00:00', 'cash','sales$', np.nan],
        [5123, '2021-01-02 00:00:00', 'cash','items', np.nan],
        [5123, '2021-01-02 00:00:00', 'card','sales$', 170],
        [5123, '2021-01-02 00:00:00', 'card','items', 35]]

columns = ['Store', 'Date', 'Payment Method', 'Attribute', 'Value']

df = pd.DataFrame(data = data, columns = columns)
Store Date Payment Method Attribute Value
5123 2021-01-01 00:00:00 cash sales$ 105
5123 2021-01-01 00:00:00 cash items 20
5123 2021-01-01 00:00:00 card sales$ 355
5123 2021-01-01 00:00:00 card items 50
5123 2021-01-02 00:00:00 cash sales$ NaN
5123 2021-01-02 00:00:00 cash items NaN
5123 2021-01-02 00:00:00 card sales$ 170
5123 2021-01-02 00:00:00 card items 35

I would like to create a new attribute, called "average item price", which is generated by, for each Store/Date/Payment Method, dividing the sales$ by the items (e.g. for store 5123, 2021-01-01, cash, I would like to create a new row with an attribute called "average item price", with a value equal to 5.25).

I realize that I could pivot this data out, and have one column for sales, one column for items, and divide the two columns, then restack, but is there a better way to do this without having to pivot?

Store Date Payment Method Attribute Value
5123 2021-01-01 00:00:00 cash sales$ 105
5123 2021-01-01 00:00:00 cash items 20
5123 2021-01-01 00:00:00 cash average item price 5.25
5123 2021-01-01 00:00:00 card sales$ 355
5123 2021-01-01 00:00:00 card items 50
5123 2021-01-01 00:00:00 card average item price 7.10
5123 2021-01-02 00:00:00 cash sales$ NaN
5123 2021-01-02 00:00:00 cash items NaN
5123 2021-01-02 00:00:00 cash average item price NaN
5123 2021-01-02 00:00:00 card sales$ 170
5123 2021-01-02 00:00:00 card items 35
5123 2021-01-02 00:00:00 card average item price 4.86
pandas python
3 Answers

You can use pivot_table to get the sum of sales/items per group, then compute the average value and merge with the original data:

s = (df.pivot_table(index=['Store', 'Date', 'Payment Method'],
                    columns='Attribute', values='Value', aggfunc='sum')
       .assign(avg=lambda d: d['sales$']/d['items'])
       ['avg']
     )

df.merge(s, left_on=['Store', 'Date', 'Payment Method'], right_index=True)

output:

   Store                 Date Payment Method Attribute  Value       avg
0   5123  2021-01-01 00:00:00           cash    sales$  105.0  5.250000
1   5123  2021-01-01 00:00:00           cash     items   20.0  5.250000
2   5123  2021-01-01 00:00:00           card    sales$  355.0  7.100000
3   5123  2021-01-01 00:00:00           card     items   50.0  7.100000
4   5123  2021-01-02 00:00:00           cash    sales$    NaN       NaN
5   5123  2021-01-02 00:00:00           cash     items    NaN       NaN
6   5123  2021-01-02 00:00:00           card    sales$  170.0  4.857143
7   5123  2021-01-02 00:00:00           card     items   35.0  4.857143

concat

df2 = (df.pivot_table(index=['Store', 'Date', 'Payment Method'],
                      columns='Attribute', values='Value', aggfunc='sum')
         .assign(Attribute='average item price',
                 Value=lambda d: d['sales$']/d['items'],
                 )
         .reset_index()
       )

(pd.concat([df,df2])
   .sort_values(by=columns)
   [columns]
 )

Output:

   Store                 Date Payment Method           Attribute       Value
0   5123  2021-01-01 00:00:00           card  average item price    7.100000
3   5123  2021-01-01 00:00:00           card               items   50.000000
2   5123  2021-01-01 00:00:00           card              sales$  355.000000
1   5123  2021-01-01 00:00:00           cash  average item price    5.250000
1   5123  2021-01-01 00:00:00           cash               items   20.000000
0   5123  2021-01-01 00:00:00           cash              sales$
user_163434640

pivot and then append after assigning the "Attribute" as needed:

pivoted = df.pivot(["Store", "Date", "Payment Method"], "Attribute", "Value")
output = (df.append(pivoted["sales$"].div(pivoted["items"])
                    .rename("Value").reset_index()
                    .assign(Attribute="average item price"), ignore_index=True)
          .sort_values(["Store", "Date", "Payment Method"])
          .reset_index(drop=True)
          )

>>> output
    Store                 Date Payment Method           Attribute       Value
0    5123  2021-01-01 00:00:00           card              sales$  355.000000
1    5123  2021-01-01 00:00:00           card               items   50.000000
2    5123  2021-01-01 00:00:00           card  average item price    7.100000
3    5123  2021-01-01 00:00:00           cash              sales$  105.000000
4    5123  2021-01-01 00:00:00           cash               items   20.000000
5    5123  2021-01-01 00:00:00           cash  average item price    5.250000
6    5123  2021-01-02 00:00:00           card              sales$  170.000000
7    5123  2021-01-02 00:00:00           card               items   35.000000
8    5123  2021-01-02 00:00:00           card  average item price    4.857143
9    5123  2021-01-02 00:00:00           cash              sales$         NaN
10   5123  2021-01-02 00:00:00           cash               items         NaN
11   5123  2021-01-02 00:00:00           cash  average item price         NaN
user_98576310

One option is to set the index, do the computation, and use categoricals to get a sorted output that matches yours:

cols = df.columns[:-1].tolist()
temp = df.set_index(cols)
# computation
summary = temp.xs('sales$', level='Attribute').div(temp.xs('items', level='Attribute'))
# add attribute to index, with a name:
summary = summary.set_index([['average item price'] * len(summary)], 
                            append = True)
summary.index = summary.index.set_names('Attribute', level = -1)
output = pd.concat([temp, summary]).reset_index()
# create categoricals and sort:
dtype = pd.CategoricalDtype(['sales$', 'items', 'average item price'], ordered = True)
output.Attribute = output.Attribute.astype(dtype)
dtype = pd.CategoricalDtype(['cash', 'card'], ordered = True)
output['Payment Method'] = output['Payment Method'].astype(dtype)
output.sort_values(cols)

    Store                 Date Payment Method           Attribute       Value
0    5123  2021-01-01 00:00:00           cash              sales$  105.000000
1    5123  2021-01-01 00:00:00           cash               items   20.000000
8    5123  2021-01-01 00:00:00           cash  average item price    5.250000
2    5123  2021-01-01 00:00:00           card              sales$  355.000000
3    5123  2021-01-01 00:00:00           card               items   50.000000
9    5123  2021-01-01 00:00:00           card  average item price    7.100000
4    5123  2021-01-02 00:00:00           cash              sales$         NaN
5    5123  2021-01-02 00:00:00           cash               items         NaN
10   5123  2021-01-02 00:00:00           cash  average item price         NaN
6    5123  2021-01-02 00:00:00           card              sales$  170.000000
7    5123  2021-01-02 00:00:00           card               items   35.000000
11   5123  2021-01-02 00:00:00           card  average item price    4.857143
user_71757130
Related