How to establish relationship in pandas or excel?

user_15996335 0
Viewed 93

I am working on a problem where i want to count the occurrences. I have an excel sheet containing customer data. I found out that people brought similar products. eg- lets assume someone brought a new mobile phone, most of the time they brought a Powerbank. The order id will be same for this two orders. How to i count the occurrences to deduce that if people buy one product they are likely to buy other.

Input:

----------------------------------------------|
order id  | Name        |    product          |
----------|-------------|---------------------|
123456    | Will Smith  |   mobile phone      |
123456    | Will Smith  |   power bank        |
123456    | Will Smith  |   charger           |
234567    | adam smith  |   mobile phone      |
234567    | adam smith  |   power bank        |
345678    | sam smith   |   mobile phone      |
345678    | sam smith   |   charger           |
345678    | sam smith   |   headphone         |
345672    | Ash smith   |   mobile phone      |
345672    | Ash smith   |   charger           |
345673    | kim smith   |   headphone         |   
----------------------------------------------|

expected Output

----------------------------------------------|
order id  | Name        |    product          |
----------|-------------|---------------------|
123456    | Will Smith  |   mobile phone      |
123456    | Will Smith  |   power bank        |
123456    | Will Smith  |   charger           |
234567    | adam smith  |   mobile phone      |
234567    | adam smith  |   power bank        |

output should contain both mobile phone and power bank.

pandas excel python
2 Answers

You can use groupby and agg(['count']) to count the number of occurrences of row value groups, e.g.:

#!/usr/bin/env python

import io
import pandas as pd

table_str = '''Order_id, Name, Product
123456, Will Smith, mobile phone
123456, Will Smith, power bank
123456, Will Smith, charger
234567, adam smith, mobile phone
234567, adam smith, power bank
345678, sam smith, mobile phone
345678, sam smith, charger
345678, sam smith, power bank
345678, sam smith, headphone
'''

def main():
    df = pd.read_csv(io.StringIO(table_str), header=0, skipinitialspace=True)
    df = df.groupby(['Order_id', 'Name']).agg(['count'])
    print(df)

if __name__ == '__main__':
    main()

Result:

$ ./so67644922.py
                    Product
                      count
Order_id Name              
123456   Will Smith       3
234567   adam smith       2
345678   sam smith        4

This shows you the relationship between unique orders and users and how many products they buy. You can adjust column names to view other relationships.

You can export a CSV file from Excel and read this file into a Pandas dataframe via the same read_csv() function.

Note: When asking questions in the future, please do not post images to represent your data. Post a text-formatted table which can be copied and pasted more easily. This will help your chances of getting help in the future.

user_194100

In Excel 365 it would look like this:

=FILTER(A2:B100,COUNTIFS(A2:A100,A2:A100,B2:B100,"mobile phone")*COUNTIFS(A2:A100,A2:A100,B2:B100,"power bank"))

enter image description here

user_38949170
Related