Combine and Pivot DataFrames with Julia

Question

I am trying to read in two csv files (Customer Purchase Data, Product Data) as data frames, then combine and pivot.

Example:

Customer Purchase Data:
CustomerID ProductId
1          39
1          6
2          8
3          39
3          40

Product Data:
ProductId Name
6         Car
8         House
39        Plane
40        Boat

Desired Pivot Table
ProductId Name  Cust_1 Cust_2 Cust_3
6         Car   1      0      0
8         House 0      1      0
39        Plane 1      0      1
40        Boat  0      0      1

My questions are: Can this be done?
Should it done? I could pivot this in Excel and save it out as a csv.

Answer 1

Here is another approach in two steps.

Step 1: Join the two tables

using DataFrames

### Create the DataFrame
customer = DataFrame(customerid = [1, 1, 2, 3, 3],
                     productid = [39, 6, 8, 39, 40])

product = DataFrame(productid = [6, 8, 39, 40],
                    name = ["Car", "House", "Plane", "Boat"])


res = join(customer, product, on = :productid)
# 5x3 DataFrames.DataFrame
# | Row | customerid | productid | name    |
# |-----|------------|-----------|---------|
# | 1   | 1          | 6         | "Car"   |
# | 2   | 2          | 8         | "House" |
# | 3   | 1          | 39        | "Plane" |
# | 4   | 3          | 39        | "Plane" |
# | 5   | 3          | 40        | "Boat"  |

Step2: : Add a dummy column with "1" and unstack the DataFrame (moving from long to wide format)

### Add dummy column
res[:tmp] = 1
res
# 5x4 DataFrames.DataFrame
# | Row | customerid | productid | name    | tmp |
# |-----|------------|-----------|---------|-----|
# | 1   | 1          | 6         | "Car"   | 1   |
# | 2   | 2          | 8         | "House" | 1   |
# | 3   | 1          | 39        | "Plane" | 1   |
# | 4   | 3          | 39        | "Plane" | 1   |
# | 5   | 3          | 40        | "Boat"  | 1   |


### Pivot from long to Wide
res = unstack(res, :customerid, :tmp)
# 4x5 DataFrames.DataFrame
# | Row | productid | name    | 1  | 2  | 3  |
# |-----|-----------|---------|----|----|----|
# | 1   | 6         | "Car"   | 1  | NA | NA |
# | 2   | 8         | "House" | NA | 1  | NA |
# | 3   | 39        | "Plane" | 1  | NA | 1  |
# | 4   | 40        | "Boat"  | NA | NA | 1  |


### Finally we can replace NA by 0
[res[isna(res[col]), col] = 0 for col in [symbol("1"), 
                                          symbol("2"), 
                                          symbol("3")]]
res
# 4x5 DataFrames.DataFrame
# | Row | productid | name    | 1 | 2 | 3 |
# |-----|-----------|---------|---|---|---|
# | 1   | 6         | "Car"   | 1 | 0 | 0 |
# | 2   | 8         | "House" | 0 | 1 | 0 |
# | 3   | 39        | "Plane" | 1 | 0 | 1 |
# | 4   | 40        | "Boat"  | 0 | 0 | 1 |

If you to change column name, you can do it manually

names!(res, [:productid, :name, :cust_1, :cust_2, :cust_3])