(Stata13): How to Reshape Wide to Longitudinal Data
HTML-код
- Опубликовано: 22 авг 2024
- This hands-on tutorial shows how to reshape data from wide to longitudinal format in Stata13.
Here is the link to the engee2.xlsx dataset used for this tutorial (endeavour to have a Google account for easy accessibility): drive.google.c...
Follow up with soft-notes and updates from CrunchEconometrix:
Website: cruncheconometr...
Blog: cruncheconomet...
Forum: cruncheconometr...
Facebook: / cruncheconometrix
RUclips Custom URL: / cruncheconometrix
Stata Videos Playlist: • (Stata13):Estimate and...
EViews Videos Playlist: • (EViews10):Interpret V...
RUclips recently changed the way my content will be monetised. My channel now needs 1,000 subscribers. So it would be amazing if you show your support by both watching my videos and subscribing to my channel if you haven’t done so already. Monetising my videos allows me to invest back into the channel with some new equipment so this small gesture from you will be extremely huge for me. Many thanks for your support….CrunchEconometrix loves to teach, help me stay online.
Hi Daniel, apologies for my very late response. You can employ the multiple imputation method which has its underlying assumptions. You may need to do a Google search on the procedure. Thanks.
Thank you so much! I spent 17 hours last week trying to organize all my data manually, this just saved me
Glad it helped Natalie!
First of all, thank you very much. Based on that, I followed the same approach directly in Stata using:
drop if missing(CountryName) | missing(CountryCode) | missing(SeriesName) | missing(SeriesCode)
egen id_C = group(CountryName)
egen id_S = group(SeriesName)
foreach var of varlist _all {
replace `var' = "" if `var' == ".."
}
reshape long YR, i(id_C id_S) j(year)
drop SeriesName SeriesCode
reshape wide YR, i(id_C year) j(id_S)
U're welcome 🙏
This is the first time I am stumbling on a Nigerian doing stata analysis on RUclips. Thank you so much for this.
You are welcome, Erinne. Please may I know from where (location) you are reaching me? Thanks.
I was struggling with this since 2 days, you just made it very simple and I did it in 10 minutes, thank you so much, lots of love and regards ❤️❤️❤️❤️
Thanks, Manoo for the encouraging feedback. Deeply appreciated! 💗🙏
I am highly grateful to you, Professor.
Your video helps me a lot to conduct both my undergraduate research and MSS thesis.
Huge respect for you ❣️
Thanks, Mahinur for your encouraging feedback. Deeply appreciated 🥰🙏
I love that you're from Africa! This is the by far the best videos I've seen on this, thank you so much for the great content!
Yes ooo proudly African, Victor😊. I'm humbled and encouraged by your positive feedback on my videos. Deeply appreciated! 🙏 Please may I know from where (location) you are reaching me?
@@CrunchEconometrix I'm in grad school at an Ivy league school in the US, but born and raised on the motherland. Believe me none of my professors can explain this!
This is a very useful video to reshape the dataset. Proud of you, professor.
Thanks so much for your encouraging feedback, Doc! 🥰🙏
This video has been a lifesaver 😭. LITERALLY. THANK YOU SO MUCH!
Glad you find it helpful, Matilda...thanks for your encouraging feedback! 🥰🙏
Subscribed! Thanks alot for your detailed and simple explanations! It also feels awesome hearing these things explained in a Nigerian accent! Thanks
Thanks Paul for your subscription. Deeply appreciated! I will really appreciate if you can help share the link to my RUclips Channel ruclips.net/user/CrunchEconometrix on your SM handles for awareness among my 9ja people and those in your network. Thanks again.
I'm so grateful for this video. I always refer back to it because it has simplified the process and made things so much easier for me. Thank you!
Thanks Crawford, for the encouraging feedback. May God bless you!!!
I was stuck with getting Series into the wide format for so long. Thank you a ton!
Glad to be of help, Simon!
this is life-saving. I was doing the manual mess and here I go enjoying the day because of this trick. thanks!
U're welcome, Meney! Kindly tell others too!!!
You are amazing! You have helped me out so much in both of my thesis. Thank you so much for your insights!!
Happy to help, Matteo! Please tell others too, thanks!
yoo is spent two days sorting my data thanks prof for this yooo am very grateful... from now on i will be using this for my panel data...
U're welcome, Msizi!🥰🙏
Thank you for this video and the GMM series. Saved me time and helped my thesis 💐💐
You are welcome, Zoey🥰
YA'LL DOIN THE LORD'S WORK. TYSM FOR THIS U ARE SAVING MY THESIS!!!
U're so welcome, Punch!
Very brilliant and detailed explanation, here is to hoping you carry on!
Thanks David, for the positive feedback and remarks on my video. Deeply appreciated! 😀
I used quarterly data to test sticky cost (weiss, 2010). I have an osiris database that uses quarter data.
Hi Junaid, that shouldn't be a problem. Follow the same steps to reshape the data and replace "year" with "quart" if "quart" is the time variable.
I have been working on this exact problrm for 2 days straight, THANK YOU
Happy to hear this, Gustii! 🥰
This video saved me!! Thank you so much for posting :)
Thanks for the encouraging feedback, Jovana. Deeply appreciated! Please may I know from where (location) you are reaching me?
CrunchEconometrix I’m from Canada!🇨🇦😊
Thank you so much for this tutorial. Helps me a lot. God bless you
You are welcome, Kevin. Glad you find this very helpful 🙏🥰
Thank you so much prof for your educative videos. You are doing a good job. very helpful indeed!
Thanks for the good feedback, Morris...deeply appreciated! 💕 Kindly share my videos with your friends and academic community😊
thank you so much professor for your kind and prompt suggestion and reply
My humble pleasure, Debasis. Please may I know from where (location) you are reaching me?
Thank you very much Prof. this is very important to me.
Thanks so much for your positive feedback, deeply appreciated! ☺
all of us who were doing this manually, lets gather here
Hahaha 🤣...now, you can use the Excel command to make things easier.
Thank you so much. Love and respect from Beijing China
U're welcome, Rain-Walker...kindly share my RUclips Channel link with your students, friends and academic community in China 🇨🇳. Thanks! 💕 😊
@@CrunchEconometrix Of course, i did. Thank you again.
@@Dr_Shiny Deeply grateful, R-W! 💕 😊
This is great! Thanks! Coming from a Jamaican!
Awesome and thanks, Alvin! Kindly share my RUclips Channel link with your students, friends and academic community in Jamaica 🇯🇲! 💕 😊
For all those who are facing issues with Quarterly Panel data.............
1- Create ID variables for each i
2- for quarters use 1,2,3,4,5...... instead of 2010q1, 2010q2, 2010q3..... Stata will give the error "so many missing values"
3- then run the above code
4- After reshape, just convert the 1,2,3,4,5..... Year variables into quarters, simple ...
Thanks
Thanks for this contribution. Grateful!
Hi, can you guide me to do step 3 and 4? Im just begin to use stata, thanks before🙏
@@ayunisanurkhaqiqi4119 I could send via email
My email ayunisanurkhaqiqi@gmail.com thanks before
Thank you so much for making this video, I do learn alot from this. :-)
Hi Hai, thanks for the encouraging feedback. Deeply appreciated! Please may I know from where (location) you are reaching me?
Thank you soo much Prof. i was exactly looking for this. stay blessed...
Hahahaha Hassaan, my Channel got you and every researcher out there covered. Good to know that the clip is helpful. Please share this with your cohorts, students and academic community...gracias! :)
You're literally amazing cheers crunch haha
Thanks for the positive feedback, Rowan...appreciated!
Thank you, this is really helpful!
Thanks for the encouraging feedback, Avesta!
Very helpful. Thank you so much
You're very welcome, Sir!
Thanks for this tutorial! It saved me lots of time.
I'm encouraged by your feedback, Armstrong...love ya!!! May I know from where (location) you are reaching me?
@@CrunchEconometrix Nairobi, Kenya
@@armstrongasenavi719 Awesome Armstrong...I will appreciate if you can spread the word about my videos to your students and academic community in Kenya 🇰🇪! 💕 😊
Thanks you for your help, it's very fantastic. That's just intelligent work.
Thanks, Pham for the encouraging feedback. Deeply appreciated!
In case you really download WDI/World Bank data, you can actually define how you wanna have the data - you find that under "Layout"
That's correct, Julian. Thanks
Thanks man.. you are a saviour..
U're welcome, Bhaloo! 🥰🙏
really very helpful
Thanks Muhd, for the encouraging feedback... deeply appreciated!
Nice explanation ,would you mind making video making aranging pannel data only using excell nto through Stata?
I have a video on BUILDING A PANEL DATA.
Thank you so much, this is incredibly helpful!!
U're welcome, Hanna...may I know from where (location) you are reaching me?
Currently I'm studying in the Netherlands :)
My love to all Netherlanders...please tell your students, colleagues and academic community about my YT Channel!😍
Thank you so much! Your video really help
You're welcome, Miriam!
Thanks! Very helpful :)
Glad it helped, Markus!
Great help. Thanks a lot
You are welcome, Sir.
Very helpful, thanx!
You are welcome, Diogo!
Brilliant! Thank you!
Hahahaha Heath, thanks for the positive feedback on my video. Deeply appreciated! 💕 Kindly share my Channel link with your students and academic networks! May I know where you are reaching me from?
The biggest LIKE
Appreciated, Ayman... thanks! 🙏
Thank you very much. You are doing an outstanding job.
would you please post a video on how to get data from IMF and World Bank
Thanks Thomas for the positive feedback...deeply appreciated! I will log this request and do a video on it. Thanks!
Thank You!
You are welcome, Esayas!🥰🙏
thank! . this is awesome!
U're welcome 😊...glad to be of help! May I know from where (location) you are reaching me?
U're welcome 😊...glad to be of help! May I know from where (location) you are reaching me?
God Bless you professor
You too, Big Boss!!! Please may I know from where (location) you are reaching me?
CrunchEconometrix I am an international masters student in economics currently studying in Norway. But I am from Pakistan. There’s a few things I need to ask you regarding stata. As I am currently in the process of writing my master thesis.
No problem. Simply post your queries on the comment section of the respective RUclips video for others to benefit from the discussion. I'll do my best to guide. Thanks
@@CrunchEconometrix Hi, I am also facing another issue regarding this video. I am doing an analysis on 20 countries across the world and I have a total of 12 variables and 10 years for each country. I followed all the steps you have listed in the videos but I eventually found a problem as I am missing 2 variables from my data set. Since I only have 10 years and 12 variables, when I go to rename my variables, 2 are missing. How can I fix this?
@@diamonddogsgaming7059 I believe that you were able to get this sorted. Well done! 👏🏽
You are a great teacher. And i have actually enjoyed most of your videos, especially the panel data subject. However, i have one question, How does one solve for the unbalanced panel data problem in stata for different variables involved?
Hi Scora, thanks for the encouraging feedback. Deeply appreciated! Stata works perfectly with unbalanced data. Used several in my publications. Please may I know from where (location) you are reaching me?
@@CrunchEconometrix i am reaching you from South Africa, Cape Town. Also i have followed your Rehape video from excel to stata importation. however, my data is giving me problem with the reshape command. It keeps on saying this
"reshape long YR i(idc INDICATORNAME ) j(YEAR)
option i() required
"
how do i rectify this please
It appears you did not generate id for the cross-sections. In my video, I created country id called c_id.
Good video and very helpful , please can you do a video on missen data estimation ? Notably by imputation. Thanks
I'll do my best to do that video, Asante. Thanks for watching my videos...keep sharing too for more awareness😀
Thank you
Glad you liked the video! 🙏🥰
I love you. Really.
Hahahhaha Paul, love you too for enjoying my video. Deeply grateful. May I know from where (location) you are reaching me?
CrunchEconometrix Burmese in Hong Kong. I had a hard time transferring data to Stata. Thx to you. I can move to other things.
@@paulluhan Good to know Paul...I will greatly appreciate if you share my YT Channel link with your students and academic community. Thanks!
CrunchEconometrix sure. I am still an undergraduate student.
Thank you Prof
U're welcome, Darcy😊
Thanks so much for the tutorial. Is there a continuation?
Thanks for the positive feedback, Cyril. Deeply appreciated! This is a standalone clip. Kind regards.
Thank u thank u
You are very welcome.
How to do monthly data? I tried to do analogically but my months are not in chronological order in stata
You should sort out your monthly data in Excel before importing to Stata to reshape.
Thanks! Very helpful
Thanks Lessa!!!..and please tell others about my YT Channel...
Great video and very helpful since I use stata for the first time. I have a similar dataset with countries and series and 3 years. I followe your video step by step but stata says there is an error "variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You
specified i(cid mid) and j(year). In the current wide form, variable cid
mid should uniquely identify the observations. Remember this picture:
...."
Can you please help me through it cause I dont understand what the problem is?
What is your "mid"?
@@CrunchEconometrix mid is your s_id! Specifically, it is some hr metrics like working hours, layoffs, etc
@@eleftheriachadouli7139 Does it uniquely identify the cs? Because my series id (s_id) does just that.
thanks a lot
You are welcome, Linara! 💕🙏
Hi prof Ngozi, i'm trying to import my data from excel to stata, but stata reads my annual time data as strings, please how Can i convert it to numeric data? Thanks in advance
Hi Asante, I have a dofile (DF005) that addresses that but it is available on my website for a token fee. Kindly visit the STORE
www.cruncheconometrix.com.ng/shop/ to make the purchase, thanks.
@@CrunchEconometrix thanks very much
It is great video. How if I use quarter data Sir? I look in stata quarter symbol is quart, but I can't combain quater and year. Please help me Sir
Hi Junaid, I'm wondering why you want to combine QUART and YEAR both defines the time variable in the data. Please watch the clip again and adapt to suit your data pattern.
Thank you very much for the video! I have a question: what should be the code in Stata if the date is not written in years , but in months and years, such as 1990/05 or may1990?
Hi Lligramm, please what code are you referring to?
Thank you very much. Is there a way to download this data in the Stata form? Have you uploaded it?
You are welcome, Anais... but the answer is no.
Hi, first of all, I want to thank you for this useful video. I would like to ask a question about the problem I am facing. When I write to command row "reshape long yr, i (c_id s_id) j (year)" , there is no problem. But, when I write to command row "reshape wide yr, i(c_id year) j(s_id)" command, I am getting error that "variable s_id is string; specify string option". How can I solve this problem? Can you help me about this problem?
I controlled my dataset and follow your this video's steps many times, but I couldn't solve this problem! If someone help me about this topic, I really will be glad.
Hi Cengiz, I honestly have not experienced this before. Perhaps, the issue is with the s_id column. Click on the Stata error on tips on how to fix it. Thanks.
whenever I try to export my excel data to stata it shows the "unexpected attribute" and can not load the excel sheet, please advise.
Nasir, I honestly have no idea what the issue could be. You may want to check the underlying excel sheet for errors before importing to Stata.
Thank you very very much!
You're very welcome😊🙏
Thanks for this amazing video. it's my first time using stata and also my first time dealing with panel data .
so, I have some question. why have the identifiers been generated for both country and series?
does matter or not if used space in the commands?
how to forecast a missing obs. using stata?
Hi Eman, thanks for the encouraging feedback. Deeply appreciated! Stata will not execute w/o identifiers. I explained that.
Thank you for this video! It is quite helpful. Could there be any problem if I decide to extract financial data from bank statements and simply prepare it in a long format/shape without necessarily having it wide and later reshaping to long? Now what happens with the series/variable names, do I have to reshape them to wide to make sense if at all whether I suggested makes sense?
Hi Nyakoe, reshaping from W to L is the format allowed by Stata for panel data analysis. So, if your extraction yields a L-format data, then no need to reshape.
Greetings crunch econometrics. I have an issue at the level of generating the s_id.
At the level of inserting the formula it is refusing to apply the formula.
What could be the problem. I need help
Hi Samuel, if there are errors within your data, the code won't work. You should sort your variables first, then use the EXCEL formula.
Can you please tell me how to select only six countries from the 46 countries in Stata? I usually use the filter tool in Excel to select the countries when I download data from the World Bank. But I want to do it in Stata but do not know the code to write. Kindly help. Thank you
Dino, do it in excel.
May thanks for the insightful videos. Please i would like to find out how to reshape string variables. Again, i would like to find out how to find averages in the long format.
Have you tried out this procedure to reshape your string data? I don't quite understand the 2nd query?
could u pls link the xlsx dataset again? Would be great! Great vid thx man.
Hi Hidan, dataset is available upon payment of a token fee on my website cruncheconometrix.com.ng/shop/. But you can always practice along with yours. Thanks!
Please make video transposing data without stata
Ok.
You’re a fucking legend
Appreciated!
A nice video, the URL for your dataset is not opening, will you kindly help.
Hi Atus, due to abuse and unethical conduct datasets used in my videos are no longer free (some are free though) but available on my website upon payment. Here's the link cruncheconometrix.com/view/datashop.php
I have too much dates on the first row for this as I have daily data instead of yearly, so State gives an error. Any suggestions?
Hi Patrick, click on the error code and follow the guide on how to fix the problem.
Greetings, please I have been trying to generate this command =if(B3=B2, A2, A2+1) on Excel but it does not work for me.
Please could assist me... I have been doing it manually 😪😪😪
Hi Micheal, the excel syntax works if your data is properly sorted and without errors.
sir what problem we face if number of countries is greater than number of years?
You will need to read about the technique you want to deploy to find out the econometric implications if you use the wrong panel data structure.
Thank you so much!!!!
U're welcome, Bayu🙏. I'm glad you find this video helpful 😊
Thanks please how can I upload my half year data to Stata.
Hi Fatima, same way you upload any data.
I get this error message:
"variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You specified i(s_id c_id) and j(year). In the
current wide form, variable s_id c_id should uniquely identify the observations."
do you know what to do?
Hi Stefanny, not an issue. Follow the Stata for guides on how to correct the problem.
I get the exact same error message and I have not been able to solve it. Could you please help me?
Please help me. What do i write in place of year if my data is like jul-2018 and its monthly???
First, ensure your data is in the right TIME format, then modify the code to relect MONTH.
@@CrunchEconometrix thanks a lot
Hello! What if it’s one country, is an id needed? If yes, how to go about it please?
No.
@@CrunchEconometrix thank you
Great video. please can someone help me on how to classify countries by low income and middle income in excel? Thank you in advance
Bobbie, you have to do it manually. Watch my video on "Building a Panel Data" on how to go about it.
Professor, I entered the following two commands:1. egen Banknum = group(Bank)
2. xtset Banknum Quarter, quarterly
Is it enough to make the data panel data?
Stata is saying that quarters are repeated and it's not doing stationary and other analysis by saying "repeated time values within panel" The quarters I entered normally belong to different banks so it should be normally accepted isn't it? Can you say what is wrong with my commands?
Please follow my elementary guide as shown in the video. Prep your data from Excel before importing into Stata.
Thank you so much for that video it helped me a lot, I have a question: If i have dates in the excel format dd/mm/yyyy and I would like to use the day as they are in the same year what would I add as prefix. In the video because your dates where years you added yr do I just need to add d? or day?
Hi Patick, thanks for the positive feedback. Deeply appreciated! Unfortunately I have not tried the code on a different date variant so may not be able to guide appropriately. My sincere apologies. Please may I know from where (location) you are reaching me?
@@CrunchEconometrix
Hey, okay thank you very much for your answer anyway, I am currently in China.
I'm strugling with getting my data right. Instead of one variable with longitudinal data I have 11, so I can't put yr in front of the specific year ,since I need to do it for 11 variables. e.g. ENVyr1, SOCyr1
Jari, ensure your excel format is the same as mine.
@@CrunchEconometrix yeah if i do that manually im formatting for 12hrs straight. Is there another way via stata, right now my data for 144 companies with a frame of 7 years is as follow:
horizontal axes coloms: company, country, year, ESG, ROA, (control variables)
vertical axes: year 2014-2020 (listed per company)
Jari, it is advisable to download the data in the appropriate excel format before reshaping in Stata.
@@CrunchEconometrix I used Refinitiv workspace, according the refinitiv support service of our university I can only extra it via excel in one format.
if i have monthly data what will be the code in stata?
Simplify YEAR to MONTH.
Thank You very much. It would be a great help to me if you do the same by taking data from prowess.
Subal, thanks for the positive feedback. What do you imply by "taking data from prowess"?
@@CrunchEconometrix A database on Indian listed and unlisted companies.
You can apply the knowledge to any data source.
You need not to create another column to get rid of the formula. Just copy and paste it in the same column and choose "values" as you did. Nice video though. Thanks..
Correct! I realized so very much later on. Thanks 😊. May I know from where (location) you are reaching me?
@@CrunchEconometrix I am in London. Cheers!!
@@sahilsambyal4968 Awesome! Please spread the word about my videos to your students, friends and academic community in London! 💕 😊
@@CrunchEconometrix sure will do and good luck with your work.
@@sahilsambyal4968 Awesome!!! Kindly share my videos with your, friends, students and academic community in London and on social media for awareness. They'll learn some useful tips and skills too! :)
Thanks Doctor, I am blocked at the level of creating identifiers for my series... When I fit in the formula, it doesn't execute, rather it takes it as a text.
Hi Wawah, perhaps the coding is wrong. Pls do it again.
Great video thank you! I am however stuck at the part where we reshape. Stata tells me: "option i incorrectly specified" could you help me with this?
Hi George, thanks for watching my videos, deeply appreciated! Regarding your query, not exactly sure what the error is, but I'll say that you start all over and use my coding structure. Just do as I did.
@@CrunchEconometrix are the country codes (the cc) important for stata?
@@CrunchEconometrix for some reason mine only worked with curved brackets and not square brackets (for the reshape command)
@@ECHTisaconcept Good to hear that it finally worked!
Hi, this is really a very useful video and gives what I need. I have similar data with 18 years and 44 institutions and and did exactly what you said, but it gives error as follows.
"reshape long yr, i(b_id s_id) j(year)
(note: j = 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018)
variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You specified i(b_id s_id) and j(year). In the current wide form, variable b_id s_id should uniquely identify the
observations"
can you help me? And how can we use it for quarterly data?
Follow the same procedure. But modify to indicate the quarterly data.
@@CrunchEconometrix thanks, using date instead of year and additional comments solved my problem.Errors always arise from the problems of your data.
Thanks Dr. Please the code to generate c_id is very tiny. I cannot see it. Can you assist with the code?
Hi Ifeanyi, aside typing the code, I also called out the cell names. Here is it: =if(B3=B2,A2,A2+1).
good job
...thanks Tizie, I'll appreciate if you tell your social media and academic community about by sharing the link to my RUclips Channel. My videos are really good...here's the link to myChannel cruncheconometrix.com.ng/youtube-videos/
Good day Prof,
I hope this comment finds you well. I am an Honours student (Post-grad) working on a project and being the first time am collecting raw data by myself, seems a bit difficult.
There's a particularly stubborn variable whose data displayed "abc" on the Eviews workfile and am sure it has meaning. Please kindly tell me what it means and what is likely to be the problem with the data?
Thank you
Beedee, I have no idea what your query is about.
How do i get the series data vertically in excel? I have it horizontal just like you have years, i can not assign a series id to them in this way
I suppose that is how you downloaded it from the source. Then, you can use the TRANSPOSE command in Excel.