Reshaping to Panel Data in R
What does the code do?
The code converts a data of the format
|
panelname |
variable |
2000 |
2001 |
2002 |
2003 |
|
SBI |
Total Assets |
34 |
36 |
37 |
42 |
|
SBI |
ROA |
2 |
4 |
3 |
2 |
|
HDFC |
Total Assets |
23 |
25 |
24 |
27 |
|
HDFC |
ROA |
4 |
5 |
3 |
2 |
|
Citi |
Total Assets |
16 |
17 |
19 |
20 |
converts to the following shape
|
panelname |
Time |
Total Assets |
ROA |
|
SBI |
2000 |
34 |
2 |
|
SBI |
2001 |
36 |
4 |
|
SBI |
2002 |
37 |
3 |
|
SBI |
2003 |
42 |
2 |
|
HDFC |
2000 |
23 |
4 |
|
HDFC |
2001 |
25 |
5 |
Installation of Packages
if (!require(pacman)) install.packages("pacman")## Loading required package: pacman## Loading required package: pacman
pacman::p_load(utils,openxlsx,tidyverse, survival, ggfortify, survminer, plotly, gridExtra, Epi, KMsurv, texreg,stargazer,gnm, cmprsk, mstate, flexsurv, splines, epitools,eha, shiny,xtable, ctqr, scales,tigerstats, data.table, readxl,gplots, reshape2, foreign,plm)Code
Following codes converts and reshapes the data
make_panel=function(dataname=data, panelname="Panel Name", variable="Variable Name"){
data=dataname
colnames(data)[which(colnames(data)==panelname)]=c("Panel")
colnames(data)[which(colnames(data)==variable)]=c("Variable")
molted=melt(data,id.vars=c("Panel","Variable"))
View(molted)
pdata=dcast(molted,Panel+variable~Variable, fun.aggregate =mean) #id is column
colnames(pdata)[which(colnames(pdata)=="Panel")]=panelname
colnames(pdata)[which(colnames(pdata)=="variable")]=c("Time")
return(pdata)
}An Example
df<- read.xlsx("https://mudulisilu.files.wordpress.com/2021/07/ease-of-doing-business-score.xlsx")
head(df)## Country Variable 2016
## 1 India Starting a business - Score 71.74225
## 2 India Enforcing contracts (DB17-20 methodology) - Score 36.56210
## 3 India Registering property (DB17-20 methodology) - Score 47.67867
## 4 India Global: Ease of doing business score (DB17-20 methodology) 54.52099
## 5 Bhutan Starting a business - Score 85.56595
## 6 Bhutan Enforcing contracts (DB17-20 methodology) - Score 67.21093
## 2017 2018 2019
## 1 72.18791 73.90121 80.95635
## 2 39.33987 41.19173 41.19173
## 3 45.61477 46.11753 47.92572
## 4 55.94046 60.90760 67.49639
## 5 85.58816 86.32791 86.38179
## 6 67.21093 69.98871 69.98871panel_data=make_panel(df,"Country","Variable")
head(panel_data)## Country Time Enforcing contracts (DB17-20 methodology) - Score
## 1 Afghanistan 2016 31.75645
## 2 Afghanistan 2017 31.75645
## 3 Afghanistan 2018 31.75645
## 4 Afghanistan 2019 31.75645
## 5 Bangladesh 2016 22.21285
## 6 Bangladesh 2017 22.21285
## Foreign direct investment, net inflows (% of GDP) GDP growth (annual %)
## 1 0.5194396 2.260314
## 2 0.2731004 2.647003
## 3 0.6507349 1.189228
## 4 0.1213230 3.911603
## 5 1.0535523 7.113478
## 6 0.7249966 7.284174
## GDP per capita growth (annual %)
## 1 -0.5414162
## 2 0.0647642
## 3 -1.1949004
## 4 1.5356367
## 5 5.9466783
## 6 6.1364783
## Global: Ease of doing business score (DB17-20 methodology)
## 1 39.25519
## 2 38.93563
## 3 37.13062
## 4 44.20343
## 5 40.90786
## 6 41.01625
## Registering property (DB17-20 methodology) - Score
## 1 27.50000
## 2 27.50000
## 3 27.50000
## 4 27.50000
## 5 27.15354
## 6 27.18706
## Starting a business - Score
## 1 90.47103
## 2 90.35477
## 3 82.55461
## 4 92.04130
## 5 81.71167
## 6 81.50748Saving Data in MS Excel Format
write.table(panel_data,"Panel Data.csv",na = "",row.names =FALSE,col.names = TRUE,append = FALSE,sep = ",")