Hello and welcome to my first blog post on Quarto! As a part of my Google Data Analytics specialization course capstone project, I have created this blog. In this post, I will be exploring the changes in historical exports and imports of Lithuania over time, using the 6-step framework presented in the course. I hope you enjoy reading about my findings and analysis. Let’s begin!
Google Data Analytics framework
Ask
Before diving into data processing and visualization, it’s essential to take note of the following key events: the Covid-19 pandemic since spring 2020, the strained relationship between China and Lithuania due to the Taiwan question, and the Russian invasion of Ukraine in 2022.
The Covid-19 pandemic has brought unprecedented changes to the global economy, and Lithuania was no exception. The outbreak caused major disruptions in global trade, resulting in a decline in demand for Lithuanian goods and services. On the other hand, Lithuania’s relationship with China has been deteriorating due to the Taiwan question. This has led to a decrease in exports to China, one of Lithuania’s top trading partners.
The Russian invasion of Ukraine in 2022 has also had a significant impact on Lithuania’s trade patterns. The conflict has resulted in the imposition of economic sanctions on Russia, affecting Lithuania’s trade with its Eastern neighbor. The situation is still developing, and it will be interesting to see how Lithuania’s trade with Russia evolves in the coming years.
Prepare
The data was downloaded from the Lithuanian National website in an Excel file format. You can also find the same file in my blog’s Github repo. It was then loaded into the R environment using the tidyverse library and converted to data.table format.
Code
# load librarieslibrary(readxl)library(data.table)library(kableExtra)library(plotly)library(zoo)# read data from exceldt <-read_excel("lb_data.xlsx", sheet =1, range ="A13:AN201")# convert to data.table objectdt <-data.table(dt)# rename columns for conviniencesetnames(dt, c("...1"), "Type")# shift columns to get correct namesdt[, Country :=rep(dt[seq(1, nrow(dt), 4), Type], each =4)]dt <- dt[!(seq(1, nrow(dt), 4))]# generate table with first few rows and columnskbl(dt[1:6, c(41, 1, 38, 39, 40)]) %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))
Country
Type
Q1/2022
Q2/2022
Q3/2022
Total
Exports
3666.97
4230.51
4615.79
Total
Imports
2417.61
2585.59
2814.54
Total
Balance
1249.36
1644.92
1801.25
European Union (27 countries)
Exports
2712.50
3182.83
3343.46
European Union (27 countries)
Imports
1646.39
1887.25
2026.76
European Union (27 countries)
Balance
1066.11
1295.58
1316.70
In the table above, you can see the total exports and imports of all countries, as well as those of the European Union’s (27 countries) for the three most recent quarters. Even before data cleaning and preparation, a visual analysis shows that a majority of exports and imports go to EU countries. Data is available about 41 unique countries.
Process
For this project, we didn’t need to do much data cleaning or processing since there were no missing values or unknown formats. However, we did make some minor formatting adjustments in the Prepare section. To keep things simple, I decided not to split this section into different parts.
Analyze
After formatting and sorting the data, I investigated export changes of top 10 largest export destinations in 2003 Q3. Exports across European countries saw a substantial increase, with some countries experiencing growth rates of between 200-700%, exports to Belarus and Russia dropped significantly by 60% and 50%, respectively.
Code
# select exports data and exclude aggregated entiresagg <-c("Total", "European Union (27 countries)", "Euro Area (18 countries)", "Commonwealth of Independent States", "Offshore financial centers", "Other countries")dt.export <-copy(dt[Type =="Exports"&!Country %in% agg])dt.export[, `Change, %`:=round((`Q3/2022`/`Q3/2013`-1) *100, 1)]# get top 10 starting and last period countries by exportstart <-tail(dt.export[, c(41, 4, 40)][order(`Q3/2013`)], 10)# generate kable tablekbl(start, caption ="Top 10 largest export destinations in 2013 Q3") %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))
Top 10 largest export destinations in 2013 Q3
Country
Q3/2013
Q3/2022
United Kingdom
44.43
261.90
Netherlands
44.60
313.90
Norway
46.59
115.63
France
47.51
380.13
Denmark
61.80
214.14
Latvia
78.22
181.07
Poland
80.27
209.38
Belarus
120.38
45.89
Germany
148.49
637.61
Russia
323.90
154.09
I found it interesting to observe that out of the 41 countries with known data, there were export reductions in only four countries: Egypt, Russia, Belarus, and Japan. Furthermore, I noticed that the largest export changes were seen in countries with relatively small exports in 2003 Q3, such as Croatia and Malta, whose exports increased 40 times. It’s worth noting that Canada, a large economy, also saw a significant increase in exports, which grew almost 30 times.
Code
# get top 10 starting and last period countries by exporttop_change <-tail(dt.export[, c(41, 4, 40, 42)][order(`Change, %`)], 5)low_change <-head(dt.export[, c(41, 4, 40, 42)][order(`Change, %`)], 5)# get changesdt.changes <-rbind(low_change, top_change)# generate kable tablekbl(dt.changes, caption ="Top 5 largest positive and negative export changes") %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))
Top 5 largest positive and negative export changes
Country
Q3/2013
Q3/2022
Change, %
Egypt
4.65
0.94
-79.8
Belarus
120.38
45.89
-61.9
Russia
323.90
154.09
-52.4
Japan
1.64
1.21
-26.2
India
2.52
3.03
20.2
Bulgaria
0.76
13.92
1731.6
Canada
0.38
11.62
2957.9
Romania
0.70
27.03
3761.4
Malta
0.50
21.17
4134.0
Croatia
0.11
5.39
4800.0
Regarding imports, I observed that imports were reduced to only three countries: Finland, Belarus, and Russia. It is worth noting that imports from Japan increased by 80%, which is in contrast to the reduced exports by 26%. Lastly, I observed that imports from Portugal increased by 35 times.
Code
# select exports data and exclude aggregated entiresagg <-c("Total", "European Union (27 countries)", "Euro Area (18 countries)", "Commonwealth of Independent States", "Offshore financial centers", "Other countries")dt.import <-copy(dt[Type =="Imports"&!Country %in% agg])dt.import[, `Change, %`:=round((`Q3/2022`/`Q3/2013`-1) *100, 1)]# get top 10 starting and last period countries by exporttop_change <-tail(dt.import[, c(41, 4, 40, 42)][order(`Change, %`)], 5)low_change <-head(dt.import[, c(41, 4, 40, 42)][order(`Change, %`)], 5)# get changesdt.changes <-rbind(low_change, top_change)# generate kable tablekbl(dt.changes, caption ="Top 5 largest positive and negative import changes") %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))
Top 5 largest positive and negative import changes
Country
Q3/2013
Q3/2022
Change, %
Belarus
132.20
35.61
-73.1
Russia
121.83
41.64
-65.8
Finland
43.22
28.25
-34.6
Denmark
38.90
63.68
63.7
Japan
0.72
1.31
81.9
Germany
43.94
252.26
474.1
Luxembourg
1.58
21.70
1273.4
Hong Kong
0.67
9.97
1388.1
Malta
4.60
91.82
1896.1
Portugal
0.59
21.27
3505.1
Overall, this analysis highlights the importance of careful observation and analysis of data to uncover trends and patterns that can inform strategic decision-making for businesses and policymakers alike.
Share
Let’s start from visualizing total export and import changes over time.
Code
# select data for plottingdt.plot.1<-melt(dt, id.vars =c("Country", "Type"), variable.name ="Quarter")# convert to datetimedt.plot.1[, Quarter :=as.Date(as.yearqtr(Quarter, format ="Q%q/%Y"), frac =1)]# colors for plottingpal <-c("#6699ff", "#ff6666")# create figurefig <-plot_ly(data = dt.plot.1[Type !="Balance"& Country =='Total'],type ='scatter', mode ='lines+markers',x =~Quarter, y =~value, color =~Type, colors = pal)# update layoutfig <- fig %>%layout(title ="Total import and export volumes over time",xaxis =list(title ="Date"),yaxis =list (title ="Volumne, M EUR"))fig
There has been a significant increase in both total imports and exports, with a four-fold increase in imports and a three-fold increase in exports. However, during the first three quarters of 2020, there was a marked decline in both exports and imports, which can be attributed to the impact of Covid-19.
The European Union has seen a significant increase in the proportion of imports in their total volume, which has increased by 50%. This means that the EU is becoming more reliant on imports and is potentially exporting less to other countries.
When it comes to trade with Russia, both imports and exports have been declining steadily even before the start of the 2022 war. Prior to the war, Lithuania was a net importer with Russian trade. Surprisingly, tensions between China and Lithuania did not have a significant impact on their trade relationship, and the share of total imports from China increased almost 5 times.
Finally let’s analyze how imports and exports were affected with Countries close to Russia.
Code
# countries to selectcountry.list <-c("Kazakhstan", "Belarus", "Russia", "Turkey", "Offshore financial centers")# select data for plottingdt.plot.3<-copy(dt.plot.1[Country %in% country.list])# reshape tabledt.plot.3<-dcast(dt.plot.3, Quarter+Type~Country, value.var ="value")# reshape backdt.plot.3<-melt(dt.plot.3, id.vars =c("Quarter", "Type"), variable.name ="Country")# create new labeldt.plot.3[, label :=paste(Country, Type)]# color dictionarycolor.dict <-c("#C5AFA4", "#CC7E85", "#CF4D6F", "#A36D90", "#76818E")# left figurefig1 <-plot_ly(data = dt.plot.3[Type =="Imports"],x =~Quarter, y =~value, color=~label, colors = color.dict,type ='scatter', mode ='lines+markers',marker =list(line =list(width =3)))# right figurefig2 <-plot_ly(data = dt.plot.3[Type =="Exports"],x =~Quarter, y =~value, color=~label,colors = color.dict,type ='scatter', mode ='lines+markers',line =list(dash ='dot'),marker =list(line =list(width =3)))fig <-subplot(fig1, fig2, nrows =2) %>%layout(title ='Exports and imports with countries close to Russia')fig
Now, let’s turn our attention to how trade with countries close to Russia has been affected. Since the Russian invasion of Ukraine, imports and exports with Turkey and Kazakhstan have increased 3-5 times. This increase may be attributed to companies trying to circumvent sanctions to Russia through exports to Kazakhstan. It’s interesting to see how companies are adapting to changes in trade relationships and finding new opportunities to continue doing business.
Act
In conclusion, my analysis offers insights that could be utilized in evaluating the impact of historical events on Lithuania’s imports and exports. Based on the findings, several conclusions can be drawn:
Firstly, the declines in imports and exports with Russia preceded the 2022 invasion to Ukraine. This finding suggests that the declining trade relationship between the two countries started since 2014 Crimea annexation.
Secondly, the analysis revealed that political tensions over Taiwan did not have a substantial impact on exports to China.
Finally, the data indicates that imports and exports to Kazakhstan have increased, potentially due to sanctions against Russia. This finding underscores the importance of considering the geopolitical context when imposing economical sanctions.
Overall, the present study highlights the complexities of trade relationships between Lithuania and other countries.
Source Code
---title: "Exports in Lithuania"author: "Tomas Kristijonas Uždavinys"format: html: code-fold: true code-tools: truedate: "2023-03-11"categories: [R, Macro Economy, Coursera]---Hello and welcome to my first blog post on Quarto! As a part of my Google Data Analytics specialization course capstone project, I have created this blog. In this post, I will be exploring the changes in historical exports and imports of Lithuania over time, using the 6-step framework presented in the course. I hope you enjoy reading about my findings and analysis. Let's begin!{width="767"}## AskBefore diving into data processing and visualization, it's essential to take note of the following key events: the Covid-19 pandemic since spring 2020, the strained relationship between China and Lithuania due to the Taiwan question, and the Russian invasion of Ukraine in 2022.The Covid-19 pandemic has brought unprecedented changes to the global economy, and Lithuania was no exception. The outbreak caused major disruptions in global trade, resulting in a decline in demand for Lithuanian goods and services. On the other hand, Lithuania's relationship with China has been deteriorating due to the Taiwan question. This has led to a decrease in exports to China, one of Lithuania's top trading partners.The Russian invasion of Ukraine in 2022 has also had a significant impact on Lithuania's trade patterns. The conflict has resulted in the imposition of economic sanctions on Russia, affecting Lithuania's trade with its Eastern neighbor. The situation is still developing, and it will be interesting to see how Lithuania's trade with Russia evolves in the coming years.## PrepareThe data was downloaded from the Lithuanian National [website](https://www.lb.lt/en/export-and-import-of-services-by-country) in an Excel file format. You can also find the same file in my blog's Github [repo](https://github.com/TK-Problem/personal-website/tree/main/posts/google-analytics-capstone). It was then loaded into the R environment using the `tidyverse` library and converted to `data.table` format.```{r}#| warning: false# load librarieslibrary(readxl)library(data.table)library(kableExtra)library(plotly)library(zoo)# read data from exceldt <-read_excel("lb_data.xlsx", sheet =1, range ="A13:AN201")# convert to data.table objectdt <-data.table(dt)# rename columns for conviniencesetnames(dt, c("...1"), "Type")# shift columns to get correct namesdt[, Country :=rep(dt[seq(1, nrow(dt), 4), Type], each =4)]dt <- dt[!(seq(1, nrow(dt), 4))]# generate table with first few rows and columnskbl(dt[1:6, c(41, 1, 38, 39, 40)]) %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))```In the table above, you can see the total exports and imports of all countries, as well as those of the European Union's (27 countries) for the three most recent quarters. Even before data cleaning and preparation, a visual analysis shows that a majority of exports and imports go to EU countries. Data is available about 41 unique countries.## ProcessFor this project, we didn't need to do much data cleaning or processing since there were no missing values or unknown formats. However, we did make some minor formatting adjustments in the `Prepare` section. To keep things simple, I decided not to split this section into different parts.## AnalyzeAfter formatting and sorting the data, I investigated export changes of top 10 largest export destinations in 2003 Q3. Exports across European countries saw a substantial increase, with some countries experiencing growth rates of between 200-700%, exports to Belarus and Russia dropped significantly by 60% and 50%, respectively.```{r}# select exports data and exclude aggregated entiresagg <-c("Total", "European Union (27 countries)", "Euro Area (18 countries)", "Commonwealth of Independent States", "Offshore financial centers", "Other countries")dt.export <-copy(dt[Type =="Exports"&!Country %in% agg])dt.export[, `Change, %`:=round((`Q3/2022`/`Q3/2013`-1) *100, 1)]# get top 10 starting and last period countries by exportstart <-tail(dt.export[, c(41, 4, 40)][order(`Q3/2013`)], 10)# generate kable tablekbl(start, caption ="Top 10 largest export destinations in 2013 Q3") %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))```I found it interesting to observe that out of the 41 countries with known data, there were export reductions in only four countries: Egypt, Russia, Belarus, and Japan. Furthermore, I noticed that the largest export changes were seen in countries with relatively small exports in 2003 Q3, such as Croatia and Malta, whose exports increased 40 times. It's worth noting that Canada, a large economy, also saw a significant increase in exports, which grew almost 30 times.```{r}# get top 10 starting and last period countries by exporttop_change <-tail(dt.export[, c(41, 4, 40, 42)][order(`Change, %`)], 5)low_change <-head(dt.export[, c(41, 4, 40, 42)][order(`Change, %`)], 5)# get changesdt.changes <-rbind(low_change, top_change)# generate kable tablekbl(dt.changes, caption ="Top 5 largest positive and negative export changes") %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))```Regarding imports, I observed that imports were reduced to only three countries: Finland, Belarus, and Russia. It is worth noting that imports from Japan increased by 80%, which is in contrast to the reduced exports by 26%. Lastly, I observed that imports from Portugal increased by 35 times.```{r}# select exports data and exclude aggregated entiresagg <-c("Total", "European Union (27 countries)", "Euro Area (18 countries)", "Commonwealth of Independent States", "Offshore financial centers", "Other countries")dt.import <-copy(dt[Type =="Imports"&!Country %in% agg])dt.import[, `Change, %`:=round((`Q3/2022`/`Q3/2013`-1) *100, 1)]# get top 10 starting and last period countries by exporttop_change <-tail(dt.import[, c(41, 4, 40, 42)][order(`Change, %`)], 5)low_change <-head(dt.import[, c(41, 4, 40, 42)][order(`Change, %`)], 5)# get changesdt.changes <-rbind(low_change, top_change)# generate kable tablekbl(dt.changes, caption ="Top 5 largest positive and negative import changes") %>%kable_styling(bootstrap_options =c("striped", "hover", "condensed"))```Overall, this analysis highlights the importance of careful observation and analysis of data to uncover trends and patterns that can inform strategic decision-making for businesses and policymakers alike.## ShareLet's start from visualizing total export and import changes over time.```{r}# select data for plottingdt.plot.1<-melt(dt, id.vars =c("Country", "Type"), variable.name ="Quarter")# convert to datetimedt.plot.1[, Quarter :=as.Date(as.yearqtr(Quarter, format ="Q%q/%Y"), frac =1)]# colors for plottingpal <-c("#6699ff", "#ff6666")# create figurefig <-plot_ly(data = dt.plot.1[Type !="Balance"& Country =='Total'],type ='scatter', mode ='lines+markers',x =~Quarter, y =~value, color =~Type, colors = pal)# update layoutfig <- fig %>%layout(title ="Total import and export volumes over time",xaxis =list(title ="Date"),yaxis =list (title ="Volumne, M EUR"))fig```There has been a significant increase in both total imports and exports, with a four-fold increase in imports and a three-fold increase in exports. However, during the first three quarters of 2020, there was a marked decline in both exports and imports, which can be attributed to the impact of Covid-19.```{r}# select data for plottingdt.plot.2<-copy(dt.plot.1[Country %in%c("Total", "European Union (27 countries)", "Russia", "China")])# reshape tabledt.plot.2<-dcast(dt.plot.2, Quarter+Type~Country, value.var ="value")# calculate share to Total valuesdt.plot.2[, `EU (27 countries) share, %`:=round(`European Union (27 countries)`/ Total *100, 2)]dt.plot.2[, `Russia share, %`:=round(`Russia`/ Total *100, 2)]dt.plot.2[, `China share, %`:=round(`China`/ Total *100, 2)]# colorseu.color <-'rgb(22, 96, 167)'ru.color <-'rgb(205, 12, 24)'ch.color <-'rgb(0, 128, 0)'# create figure (imports)fig <-plot_ly(dt.plot.2[Type =='Imports'],x =~Quarter, y =~`EU (27 countries) share, %`,name ='EU (27 countries) imports', type ='scatter', mode ='lines',line =list(color = eu.color, width =4))fig <- fig %>%add_trace(y =~`Russia share, %`,name ='Russia imports',line =list(color = ru.color, width =4))fig <- fig %>%add_trace(y =~`China share, %`,name ='China imports',line =list(color = ch.color, width =4))# add exportsfig <- fig %>%add_trace(x = dt.plot.2[Type =='Exports']$Quarter,y = dt.plot.2[Type =='Exports']$`EU (27 countries) share, %`,name ='EU (27 countries) exports',line =list(color = eu.color, width =4, dash ='dash'))fig <- fig %>%add_trace(x = dt.plot.2[Type =='Exports']$Quarter,y = dt.plot.2[Type =='Exports']$`Russia share, %`,name ='Russia exports',line =list(color = ru.color, width =4, dash ='dash'))fig <- fig %>%add_trace(x = dt.plot.2[Type =='Exports']$Quarter,y = dt.plot.2[Type =='Exports']$`China share, %`,name ='China exports',line =list(color = ch.color, width =4, dash ='dash'))# update layoutfig <- fig %>%layout(title ="Total import and export volumes over time",xaxis =list(title ="Date"),yaxis =list (title ="Share to total, %"))fig```The European Union has seen a significant increase in the proportion of imports in their total volume, which has increased by 50%. This means that the EU is becoming more reliant on imports and is potentially exporting less to other countries.When it comes to trade with Russia, both imports and exports have been declining steadily even before the start of the 2022 war. Prior to the war, Lithuania was a net importer with Russian trade. Surprisingly, tensions between China and Lithuania did not have a significant impact on their trade relationship, and the share of total imports from China increased almost 5 times.Finally let's analyze how imports and exports were affected with Countries close to Russia.```{r}# countries to selectcountry.list <-c("Kazakhstan", "Belarus", "Russia", "Turkey", "Offshore financial centers")# select data for plottingdt.plot.3<-copy(dt.plot.1[Country %in% country.list])# reshape tabledt.plot.3<-dcast(dt.plot.3, Quarter+Type~Country, value.var ="value")# reshape backdt.plot.3<-melt(dt.plot.3, id.vars =c("Quarter", "Type"), variable.name ="Country")# create new labeldt.plot.3[, label :=paste(Country, Type)]# color dictionarycolor.dict <-c("#C5AFA4", "#CC7E85", "#CF4D6F", "#A36D90", "#76818E")# left figurefig1 <-plot_ly(data = dt.plot.3[Type =="Imports"],x =~Quarter, y =~value, color=~label, colors = color.dict,type ='scatter', mode ='lines+markers',marker =list(line =list(width =3)))# right figurefig2 <-plot_ly(data = dt.plot.3[Type =="Exports"],x =~Quarter, y =~value, color=~label,colors = color.dict,type ='scatter', mode ='lines+markers',line =list(dash ='dot'),marker =list(line =list(width =3)))fig <-subplot(fig1, fig2, nrows =2) %>%layout(title ='Exports and imports with countries close to Russia')fig```Now, let's turn our attention to how trade with countries close to Russia has been affected. Since the Russian invasion of Ukraine, imports and exports with Turkey and Kazakhstan have increased 3-5 times. This increase may be attributed to companies trying to circumvent sanctions to Russia through exports to Kazakhstan. It's interesting to see how companies are adapting to changes in trade relationships and finding new opportunities to continue doing business.## ActIn conclusion, my analysis offers insights that could be utilized in evaluating the impact of historical events on Lithuania's imports and exports. Based on the findings, several conclusions can be drawn:* Firstly, the declines in imports and exports with Russia preceded the 2022 invasion to Ukraine. This finding suggests that the declining trade relationship between the two countries started since 2014 Crimea annexation.* Secondly, the analysis revealed that political tensions over Taiwan did not have a substantial impact on exports to China.* Finally, the data indicates that imports and exports to Kazakhstan have increased, potentially due to sanctions against Russia. This finding underscores the importance of considering the geopolitical context when imposing economical sanctions.Overall, the present study highlights the complexities of trade relationships between Lithuania and other countries.
