Search code examples
rtidyverse

How to merge monthly payment data horizontally?

I'm having trouble merging large monthly data using Excel, so I wanted to learn to use R to consolidate my dataset. I do not know to consolidate my data on a customer level (unique), please see sample data.

df <- read.table(text="
Customer Payment Snapshot 
10001 20000 31-Jan-00
10001 19000 29-Feb-00
10001 18000 31-Mar-00
10001 17000 30-Apr-00
10001 16000 31-May-00
10001 15000 30-Jun-00
10001 14000 31-Jul-00
10001 13000 31-Aug-00
10001 12000 30-Sep-00
10001 11000 31-Oct-00
10001 10000 30-Nov-00
10001 9000 31-Dec-00
10002 50000 31-Jan-00
10002 48500 29-Feb-00
10002 47000 31-Mar-00
10002 45500 30-Apr-00
10002 44000 31-May-00
10002 42500 30-Jun-00
10002 41000 31-Jul-00
10002 39500 31-Aug-00
10002 38000 30-Sep-00
10002 36500 31-Oct-00
10002 35000 30-Nov-00
10002 33500 31-Dec-00", header=T)

In above data, we can see the monthly payments of the customer, however, I want it to be unique and the payments should be per column like this:

payments should be per column like this

Solution

  • Learn how to import Excel data into R through reading the excellent documentation for readxl. Then:

    library(tidyverse)

df %>%
  group_by(Customer) %>%
  pivot_wider(names_from = "Snapshot", values_from = "Payment")

    Here are some resources for learning R:

    https://swirlstats.com/students.html

    https://www.tidyverse.org/learn/