2020 & 2021 U.S. Population Growth Project

Features:

Software & Tools:

Excel, Power BI

Concepts:

Normalizing Concepts, Clustered Bar Charts, Pie charts, Cards, Shape Map, Multirow Cards

Documentation:

2020 & 2021 U.S. Population Growth Documentation

Introduction:

Information from this project have been summarized. For full content of this project, please see the documentation, here.

This report discusses an independent project that uses the software Excel and Power BI to analyze United States’ population growth, differences, and other characteristics from two imported tables from Wikipedia. The two tables used in this project have been imported from the Wikipedia sites List of U.S. states and territories by population and List of states and territories of the United States by population density. The data from the tables State and territory rankings and 2020 population density by rank and land area is normalized in Excel and used to create charts and graphs in Power BI for interpretation purposes.

The data within these tables have some information pertaining to the United States population size in 2020 and 2021, the state with the largest population size, and the state with the smallest population size. These attributes can be to identify how the population changed within a year’s time. The charts and graphs will help visually demonstrate these changes.

Normalization

This section will demonstrate using normalizing concepts to ensure only correct and necessary data is used for analysis. To do this, the data will be imported into Excel. Power Query will be used to standardize all important data needed.

Descriptions Of The Tables

Table 1 - State and territory rankings

Table 1 is imported from the website List of U.S. states and territories by population. This table contains information comparing 2021 to 2010 and comparing 2020 to 2010. This table contains the columns pertaining to rank, state or territory, census population, population change, percentage of total U.S. population, percentage of House of Representation, and population per electoral vote.

The data needed from this chart will include state or territory, 2021 census population, and 2020 percentage of the total U.S. population.

Table 2 - 2020 density by population rank and land

Table 2 is imported from the website List of states and territories of the United States by population density. This table contains information 2020 t. This table contains the columns pertaining to state or territory, population density, and land area.

The data needed from this chart will include state or territory and population.

Normalizing & Modifying The Data In Excel

This section shows the changes made to each table. Images will be shown to further show changes. This section has been summarized. Please see the documentation for more details.

Tables from two Wikipedia sites have been imported to Excel. The data from these tables needed to be normalized the corrected. The following normalizing concepts have been applied: removing unnecessary columns, removing unnecessary rows, ensuring compliant field names, removing errors, and standardizing data types. Additionally, the tables were combined into one tables and a calculated columns was added.

Table 1 : State and territory rankings

Before information was edited.

Table 2: 2020 density by population rank and land

Before information was edited.

Tables 3: 2020 & 2021 U.S. Population

Normalized and Correct Data.

Data Visualization & Analyzation

In this section, we will use Power BI to create charts and graphs to analyze observations of the 2020 and 2021 U.S. population. The charts, graphs, and features we will be using are the clustered bar chart, cards, multi-row card, shape map, and pie chart to help visualize our data. For example, our graphs and charts can help us answer some examples questions such as:

1. What state has the biggest population in 2020 and in 2021?

2. What state has the smallest population in 2020 and in 2021?

3. What percentages does each state or territory population makeup of the total U.S.A population?

4. What state had the largest/smallest population growth from 2020 to 2021?

5. What was the U.S. total population for 2020 and 2021? And what was the percentage change of those two numbers?

We will explain how each chart and graph is useful and how they might be used to analyze and answer questions in this section. An overview of all the graphs, cards, and charts that we will use in this report is shown below.

Clustered Bar Chart

Our clustered bar chart is used to illustrate the differences between the populations of the states and territories in the year 2020 and 2021. This chart helps us not only clearly visualize the numerical population difference between the two years but also allow us to compare the populations among the states and territories. Therefore, question regarding size of the population between years or between states and territories will be relevant here.

What state has the biggest population in 2020 and 2021?

For example, to answer this question, we can look at our clustered bar chart called 2020 and 2021 Population by State or Territory. We can see that in 2020 and in 2021 the state or territory with largest population is in California. This can be easily deciphered by using the descending order on this chart.

What state has the smallest population in 2020 and in 2021?

Likewise, we can use the clustered bar chart and sort by ascending order to see the state or territory with the smallest population. Then, we can see that Wyoming has the smallest state population.

Multi-row Card

The multi-row card is a useful way to organize and view data since it behaves as an interactive table. Therefore, for our information, we can not only have our population data for 2020 and 2021 side by side in a row format but also have our information grouped into states and territories. This makes it a great candidate to summarize our state/territory population percentage.

What percentages does each state or territory's population make up of the total U.S.A population?

For example, we can use this feature to find the percentages of each state or territory that make up of the total U.S. population for both 2020 and 2021. The multi-row card allowed us to put the data for each year’s population side by side and group by state or territory. This means to by changing the sorting order or simply scrolling, we can see all answers to our questions.

What percentage does the California population make up of the total U.S. population?

For instance, for this question, we can clearly see that in 2021 California made up 11.80% of the United States population and in 2020 California made up 11.71% of the population.

Pie Chart and Shape Map

Our project also uses a pie chart and a shape map. Pie charts are a useful tool to help analyze our categorized data. It helps make a clear comparison to show what state experienced the biggest population growth. On the other hand, our shape map gives a color gradient visualization to see what state decrease and increase in growth.

What state had the largest population growth from 2020 to 2021?

In both the pie chart and the shape map, we can observe that the state of Georgia had the greatest population growth. The part that represents Georgia in the pie chart has a large blue section. Moreover, the state of Georgia is the greenest state on that shape map. These features represent large population growth.

What state had the largest population growth from 2020 to 2021?

Another useful feature that the shape map includes is that if we click on a state, that state’s information will be shown on all the visualization tools. For example, we can see that New York is the reddest state on the map, and we can conclude that it had the least amount of population growth. By clicking on New York, we can see that the following information pertaining to this state is displayed.

Cards

The cards feature is a great tool to get a clear and concise number from our data. Therefore, we are looking for a precise number representing our data, we view the card to get the number.

What was the U.S. total population for 2020 and 2021? And what was the percentage change of those two numbers?

Hence, if a question like this was presented to us. We could quickly view our cards to get our answers. From the cards, we know that in 2020 the population was 334.577M and in 2020 the population was 335.325M. Therefore, the population increased by around 0.18%.

Conclusion

This report provides demonstrating of the use of Excel and Power BI software while using two tables from Wikipedia. The key elements are to normalizing and standardize the data collected from Wikipedia in the program Excel and to visualize and analyze the data in Power BI. Moreover, the purpose is to demonstrate fluidity in uses Power BI features such as clustered bar charts, multi-row cards, cards, pie charts, and shape maps. All these goals have been successful accomplished.

References

Wikimedia Foundation. (2022, April 3). List of states and territories of the United States by population density. Wikipedia. Retrieved May 31, 2022, from https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States_by_population_density

Wikimedia Foundation. (2022, May 24). List of U.S. states and territories by population. Wikipedia. Retrieved May 31, 2022, from https://en.wikipedia.org/wiki/List_of_U.S._states_and_territories_by_population

Documentation

United States Population Growth in 2020 to 2021.docx

Page updated

Google Sites

Report abuse