How to pivot a dataframe in R?

This recipe helps you pivot a dataframe in R

Recipe Objective

One of the major data manipulation operation is to compute cross tabulations of measuremens by categories. Excel has a new feature of pivot tables.

In this recipe, we will learn how to pivot a dataframe. There are two ways to pivot a table/dataframe using tidyverse package: ​

  1. pivot_wider() function (i.e to expand the columns to summarise the data well where each row corresponds to the unique factor levels from a column)
  2. pivot_longer() function (i.e to shrink the columns such that rows are increased to summarise the data well)

We will use the pivot_wider() function to explain this task ​

STEP 1: Loading the packages and dataset

# for data manipulation library(tidyverse) long_data = read.csv("R_329_wide_data.csv") attach(long_data) glimpse(long_data)

Rows: 16
Columns: 3
$ ï..Area  Mumbai, Mumbai, Mumbai, Mumbai, Kolkata, Kolkata, Kolkata, ...
$ Quarter  Q1_2012, Q2_2012, Q3_2012, Q4_2012, Q1_2012, Q2_2012, Q3_20...
$ Sales    150, 116, 150, 125, 116, 125, 116, 150, 125, 116, 125, 116,...

STEP 2: Pivoting the table

We use pivot_wider() function to carry out the task.

Syntax: pivot_wider(df, id_cols = , names_from = , values_from = )

where:

  1. df = dataframe
  2. id_cols = identifier columns not to pivot
  3. names_from = column to pivot into new column
  4. values_from = column with measurement values

pivot_wide_data = pivot_wider(long_data, id_cols = ï..Area, names_from = Quarter, values_from = Sales) pivot_wide_data

Area	Q1_2012	Q2_2012	Q3_2012	Q4_2012
Mumbai	150	116	150	125
Kolkata	116	125	116	150
Delhi	125	116	125	116
Chennai	116	150	116	116

What Users are saying..

profile image

Ray han

Tech Leader | Stanford / Yale University
linkedin profile url

I think that they are fantastic. I attended Yale and Stanford and have worked at Honeywell,Oracle, and Arthur Andersen(Accenture) in the US. I have taken Big Data and Hadoop,NoSQL, Spark, Hadoop... Read More

Relevant Projects

Build Regression Models in Python for House Price Prediction
In this Machine Learning Regression project, you will build and evaluate various regression models in Python for house price prediction.

Loan Eligibility Prediction Project using Machine learning on GCP
Loan Eligibility Prediction Project - Use SQL and Python to build a predictive model on GCP to determine whether an application requesting loan is eligible or not.

Build Piecewise and Spline Regression Models in Python
In this Regression Project, you will learn how to build a piecewise and spline regression model from scratch in Python to predict the points scored by a sports team.

Recommender System Machine Learning Project for Beginners-2
Recommender System Machine Learning Project for Beginners Part 2- Learn how to build a recommender system for market basket analysis using association rule mining.

OpenCV Project for Beginners to Learn Computer Vision Basics
In this OpenCV project, you will learn computer vision basics and the fundamentals of OpenCV library using Python.

Build a Multi Touch Attribution Machine Learning Model in Python
Identifying the ROI on marketing campaigns is an essential KPI for any business. In this ML project, you will learn to build a Multi Touch Attribution Model in Python to identify the ROI of various marketing efforts and their impact on conversions or sales..

MLOps Project to Deploy Resume Parser Model on Paperspace
In this MLOps project, you will learn how to deploy a Resume Parser Streamlit Application on Paperspace Private Cloud.

Build an AI Chatbot from Scratch using Keras Sequential Model
In this NLP Project, you will learn how to build an AI Chatbot from Scratch using Keras Sequential Model.

GCP MLOps Project to Deploy ARIMA Model using uWSGI Flask
Build an end-to-end MLOps Pipeline to deploy a Time Series ARIMA Model on GCP using uWSGI and Flask

Isolation Forest Model and LOF for Anomaly Detection in Python
Credit Card Fraud Detection Project - Build an Isolation Forest Model and Local Outlier Factor (LOF) in Python to identify fraudulent credit card transactions.