Panel data from Excel transformation

Question

I have data from the WIND terminal with the data structured in rows (even time series information)

For example:

Ticker Company Name NetProfitGrowth2009 NetProfitGrowth2010

I believe in order to estimate a time series regression I need to transform the data into the panel data so that its structure is

Ticker Company Year Net Profit Growth (so that for example for 15 years 15 different rows correspond to the same company)

How do I do it quickly since doing it like transposing and pasting/copying would take hours given that I have 347 companies and 15 years? The data is in Excel. What would be your suggestions?

Welcome to StackOverflow! Are you familiar with R? A famous group of package ( grouped in tidyverse) include the package readxl and the function read_excel() . Also, pivot_longer() from the pacakge tidyr is meant to use the year (ex. in NetProfitGrowth as a value of a variable year. I don't know about WIND. — VinceGreg
– VinceGreg, Commented May 21, 2024 at 13:35

Nick Cox · Accepted Answer · 2024-05-21 13:41:06Z

1

In Stata, this calls for reshape long, perhaps

reshape long NetProfitGrowth, i(Ticker Company) j(Year)

answered May 21, 2024 at 13:41

Nick Cox

37.4k6 gold badges37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jilber Urbina · Accepted Answer · 2024-05-21 13:51:31Z

1

In R you can use this:

library(tidyr)
library(dplyr)
your_data_frame %>% 
  pivot_longer(NetProfitGrowth2009:NetProfitGrowth2010, names_to = "Year", values_to = "Profit_Growth") %>% 
  mutate(Year = sub("\\D+", "", Year) %>% as.integer)

answered May 21, 2024 at 13:51

Jilber Urbina

61.4k10 gold badges116 silver badges141 bronze badges

Collectives™ on Stack Overflow

Panel data from Excel transformation

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related