This post includes an interactive data visualization, which is best viewed on a computer or tablet. However, if you must use your phone, at least turn it landscape.

Observe

First, just observe the visualization.

This is one of the more complicated visualizations I have put together to date, so it's important to get a lay of the land before you dive into your analysis.

The purpose of this visualization is to show pay inequality across America. Typically when you see a Lorenz curve, it is static. There is an equality line (x=y) and a "bowed" line. The more bowed the line, the more inequality (and the higher the Gini coefficient). I'd urge you to familiarize yourself with both the Lorenz curve and the Gini coefficient to get the most value out of this tool.

This Lorenz curve is different. It is interactive! First, the curve is comprised of bubbles, rather than displayed just as a line. Each bubble is a different detailed Standard Occupational Code (SOC) as per the Occupation Employment and Wage Statistics (OEWS) Tables. The bubbles are ordered from lowest paying occupation to highest paying occupation.

Note the scales of the axes. The x-axis the is running total percent of employees, while the y-axis is the running total percent of total wages (where I calculate total wages by multiplying number of employees in each SOC by the average SOC wage).

The color scale shows what the SOC calls the "Major group." Basically, there is a hierarchy of occupations. All detailed occupations roll up to a "Major" occupation group. Example: "Home health and personal care aids" is a detailed occupation within the "Healthcare support occupations" Major group.

The size of each bubble corresponds to the number of employees in each detailed SOC. Larger bubble = more employees.

Also, note the filters to the left side. At the top, you can filter the location list by Area type (e.g., State, Metro area etc.). Below that, you can then select an area to update the Lorenz curve.

Note that you can also click on any of the Major group colors in the color legend to highlight only those groups within the Lorenz curve. This is quite helpful in exploring how different occupational groups are situated along the curve.

And lastly, at the top of the chart you will see a Gini coefficient for the selected location. If you have studied Gini coefficients before, these numbers may appear low. But they are not. You must realize that the OEWS database only includes incomes from W-2 workers as reported by companies running payrolls. So, it in effect is missing the entire self-employed workforce (and therefore, likely all of America's billionaires). It does not include income from investments, or any other income source that adds to wealth. Again, it is ONLY gross wages for W-2 employees. As such, a value of 0.3 should be considered to be highly imbalanced. A value closer to 0.2 should be considered to be more balanced.

Hover

Next, hover over any bubble (i.e., detailed occupation) to bring up a tooltip that will tell you:

1. The name of the detailed occupation
2. The occupation's Major group
3. The total employees in the occupation/location as of May 2024 (latest data)
4. The average annual wage for the occupation/location as of May 2024
5. The cumulative percent of employees at this point in the curve
6. The cumulative percent of wages at this point in the curve

These last two pieces of information are really important, as they quantify the wage dispersion at all points in the curve. For example, if I am looking at California's Lorenz curve, I can hover over a bubble right around the ~40% mark for cumulative employees and immediately see that the first 40.2% of employees only generated 21.7% of the wages.

Filter

There are two filters you can use.

First, the top filter on the left side of the visualization filters the 500+ areas down by "type." Using this filter you can switch from looking at the Metropolitan Statistical Area (MSA), to the Non-metro area, to the State level. There are also options to look at the U.S. as a whole and its' territories.

When you switch from one area type to another, the Lorenz chart will disappear. Don't be alarmed. Simply choose a new area from the second filter and the chart will update for the new area.

Highlight

As mentioned earlier, I have added functionality to highlight the Lorenz curve by Major group. Simply click on any Major group in the color legend and the detailed occupations in that group will highlight on the curve.

I highly recommend you use this feature to study the data. It will make it much easier to find occupations on the curve.

It will also make it quite clear what occupations are rewarded over others in each area.

Data joining and cleaning

1. Download the May 2024 Occupational Employment and Wage Statistics data table. I used the "All data" XLSX file. Conveniently, for this visualization, this file requires no clean up! You can just bring it directly into whatever data cleaning software you choose. I used Tableau Prep.
2. Add a clean step. Keep only NAICS code = "000000." Keep only non-null Tot_Emp.
3. Create a calculated field: Major code = left(OCC_Code,2)
4. Branch off a separate clean step. Remove all fields except Major code and OCC_Title. Feed that to an aggregate step. Add both Major code and OCC_Title to groups. You should have 22 different Major codes after this step.
5. Join the table from step 4 back in with the table from step 3 on Major code. Then create a new clean step.
6. Create a calculated field. Total Wages = A_MEAN * Tot_Emp
7. Keep only O_Group = "Detailed" and "Major"
8. Clean up unneeded / extraneous data fields and output final database

Create visualization

This visualization was very challenging to put together. In fact, I had to rely heavily on Gemini to instruct me how to do it. As such, rather than writing this methodology from my memory, I asked Gemini to do it. If you try to follow these instructions, you will get close. I still had to iterate back and forth with Gemini and Tableau to get this to work... especially the Equality line, which was the most challenging to get to appear properly. But hopefully these instructions will at least get you most of the way there!

________________________

1. Establishing the Cumulative Axes (The Lorenz Curve)

The Lorenz curve requires two running percentages. Both must be calculated using a specific sort order—from the lowest-paying occupation to the highest-paying.

• X-Axis (Cumulative % of Workforce): RUNNING_SUM(SUM([TOT_EMP])) / TOTAL(SUM([TOT_EMP]))
• Y-Axis (Cumulative % of Wages): RUNNING_SUM(SUM([Total Wages])) / TOTAL(SUM([Total Wages]))

2. Tableau Visualization Setup

1. Placement: Drag the X-Axis calculation to Columns and the Y-Axis calculation to Rows.
2. Detail: Drag OCC_TITLE (Occupation) to the Detail shelf. This forces Tableau to calculate a point for every occupation rather than a single aggregate dot.
3. Table Calculation Configuration: Right-click both pills and select Edit Table Calculation.
4. Set Compute Using to Specific Dimensions and check OCC_TITLE.
5. Set the Sort Order to Custom: A_MEAN, Ascending. This ensures the curve "bows" correctly below the equality line.

3. Adding the Line of Equality (X=Y)

To visualize the "gap," a 45-degree reference line is required.

• The Hack: Create a field [Equality Y] equal to your X-Axis calculation.
• Dual Axis: Drag [Equality Y] to the Rows shelf to create a dual-axis chart. Synchronize the axes.
• Mark Selection: Set the Equality Y mark type to Line and the Lorenz curve mark type to Circle or Line.

4. Calculating the Gini Coefficient

The Gini coefficient is calculated as the area between the Line of Equality and the Lorenz curve. In Tableau, we use the trapezoidal rule to approximate the area under the curve (AUC) and subtract it from 0.5 (or use the 1−Σ approach):

Trapezoidal Rule for Gini Coefficient:

1 - WINDOW_SUM(

( (RUNNING_SUM(SUM([Tot Emp])) / TOTAL(SUM([Tot Emp]))) -

ZN(LOOKUP(RUNNING_SUM(SUM([Tot Emp])) / TOTAL(SUM([Tot Emp])), -1)) ) *

( (RUNNING_SUM(SUM([Total Money])) / TOTAL(SUM([Total Money]))) +

ZN(LOOKUP(RUNNING_SUM(SUM([Total Money])) / TOTAL(SUM([Total Money])), -1)) ))

________________________

The only thing not included in these instructions are how to add color and size to the bubbles. Simply drag Tot_Emp to the size scale and Major Group to the color scale for the bubbles.

If you don't get this to work, just ask Gemini (or your AI chat bot of choice) for help. And don't give up! It took me a few hours of going back and forth between Gemini and Tableau to get it work. Figuring out how to take a cool idea and make it come to life is part of the fun of data visualization work.