Split a Column - Create New Columns

Question

Hello All:

I have the following two data sets - world population by country and United States population by state, and I would like to combine them.

We have to keep United States in the Country column and create four new columns with the State information in one and Region in the other (with population totals for both), next to the Country column.  The US population comes from the POPESTIMATE2019 field in the us_state_region_population_2019 file.

I created an example of the output file structure for a visual.  The join part was easy, but splitting the data was a bit more challenging and would greatly appreciate your suggestions.

Thank you so much!!

Bruce.

us_state_region_population_2019.csv

world_population_2019_2020_live.csv

poplulation_structure_example.xlsx

grossal · Accepted Answer

Hi @bcampbell0621,

I think I got you! My result looks like this:

Workflow:

Steps (in the bottom path):

1) Select only needed columns

2) Split Nation / State Information from the rest

3) Skip Nation Information (nice to have)

4) Join both together

5) Union everything

6) Adjust column order and names to get the estimated outcome

Workflow is attached. Please mark it as a solution if it solves your problem or drop another question 😃

Best

Alex