9
$\begingroup$

Newbie to analytics with Python so please be gentle :-) I couldn't find the answer to this question - apologies if it is already answered elsewhere in a different format.

I have a dataset of transaction data for a retail outlet. Variables along with explanation are:

  • section: the section of the store, a str;
  • prod_name: name of the product, a str;
  • receipt: the number of the invoice, an int;
  • cashier, the number of the cashier, an int;
  • cost: the cost of the item, a float;
  • date, in format MM/DD/YY, a str;
  • time, in format HH:MM:SS, a str;

Receipt has the same value for all the products purchased in a single transaction, thus it can be used to determine the average number of purchases made in a single transaction.

What is the best way to go about this? I essentially want to use groupby() to group the receipt variable by its own identical occurrences so that I can create a histogram.

Working with the data in a pandas DataFrame.

EDIT:

Here is some sample data with header (prod_name is actually a hex number):

 section,prod_name,receipt,cashier,cost,date,time 
 electronics,b46f23e7,102856,5,70.50,05/20/15,9:08:20 
 womenswear,74558d0d,102857,8,20.00,05/20/15,9:12:46 
 womenswear,031f36b7,102857,8,30.00,05/20/15,9:12:47 
 menswear,1d52cd9d,102858,3,65.00,05/20/15,9:08:20 

From this sample set I would expect a histogram of receipt that shows two occurrences of receipt 102857 (since that person bought two items in one transaction) and one occurrence respectively of receipt 102856 and of receipt 102858. Note: my dataset is not huge, about 1 million rows.

$\endgroup$
  • $\begingroup$ Done, added some sample data. $\endgroup$ – new_analyst Apr 22 '16 at 4:05
15
$\begingroup$

From this sample set I would expect a histogram of receipt that shows two occurrences of receipt 102857 (since that person bought two items in one transaction) and one occurrence respectively of receipt 102856 and of receipt 102858.

Then you want:

df.groupby('receipt').receipt.count()

receipt
102856    1
102857    2
102858    1
Name: receipt, dtype: int64
$\endgroup$
  • $\begingroup$ since the result is no longer a dataframe, how do we filter this to show only the values that have a count of more than 1? $\endgroup$ – Nikhil VJ Jul 18 '18 at 15:51
  • 1
    $\begingroup$ You can still do things like s[s>1], where s=df.groupby('receipt').receipt.count() $\endgroup$ – Emre Jul 18 '18 at 18:24
2
$\begingroup$

I'm putting together some tutorials around data wrangling. Maybe my jupyter notebook on github will help. I think that it is the key is modifying the line:

df.groupby('male')['age'].mean()

to be:

df.groupby('reciept')['prod_name'].count()

To group by multiple variables this should work:

df.groupby(['reciept','date'])['reciept'].count()
$\endgroup$
  • $\begingroup$ Thanks for that. However, sometimes receipt is repeated (when the date is different too). Therefore, we are aggregating all the different occurrences of receipt even on different dates, when we really want to know the number of receipt by transaction - however there is not a unique transaction ID variable. I don't think receipt repeats on the same day - can we use date as a way to group? df.groupby('reciept')['date'].count() gives the same result as df.groupby('reciept')['prod_name'].count() $\endgroup$ – new_analyst Apr 22 '16 at 4:27
  • $\begingroup$ Add date as a parameter inside the groupby call. Edited my answer above to group by multiple vars. $\endgroup$ – Ryan Apr 22 '16 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.