We’ve all heard about the many improvements and changes being made to help get women into Hi-Tech and the Sciences, but where are these improvements being seen? Is the entire nation reaching gender equality or are there pockets of improvement and pockets of stagnation. These are the questions I set out to answer using the Safe Graph US Census Bureau Census Block Group American Community Survey Data from Kaggle.

This census data is an extremely comprehensive dataset at the census block group level (on the order of 1500 households each) containing detailed information on a multitude of demographic aspects of the people living in the US. However, the data is quite sizable and comes in the form of a very large zip file that makes the data hard to explore. Moreover, the zip file contains many internal files containing different demographic topics, each with its own schema. I used Pivot Billions to preprocess my data for Tableau due to its data ETL and joining features.

Using it, I quickly extracted the key columns I wanted from the various internal files in the census data while the data was still zipped and in S3.

Then I joined those key columns together by census block group and enhanced the data with a few custom columns such as the percentage of women and the degree of gender inequality.

This second custom column is used to describe how far away from equal (50% men, 50% women) the computer, engineering, and science workers are in each census block group. The values range from 0 to 5 with 5 meaning highly biased towards one gender. Now that the main data I was interested in was ready and loaded into Pivot Billions, I then downloaded the Pivot Billions preprocessed data as a csv file and then simply imported the data into Tableau as a text file.

Now all I had to do to explore the distribution of gender equality and inequality in the United States was to visualize this data. I quickly opened a new worksheet and told Tableau to treat the Census Block, latitude/longitude, and DegreeOfGenderInqeuality fields as Dimensions (categories). Then I added some of the interesting fields to the displayed tooltip in Tableau and colored by DegreeOfGenderInequality. Focusing on the extremes of Equality (Green) and Inequality (Orange), I was left with a very telling interactive visualization.

It was immediately clear that the coastal regions of the US and the Southeastern US region consistently maintained a high level of gender equality. On the other hand, the North/NorthEastern US regions leaned heavily towards just one of the genders (typically towards men).

Though many areas of the US seem to be making strides towards gender equality in Hi-Tech and the Sciences, many still remain heavily biased and stagnant. It may be worthwhile to look at the regulations and companies operating in the coastal and southeastern regions of the US to see what policies (governmental or private) may be having an effect. With time hopefully these policies will be implemented across the US to equalize the representation of both genders in these fields and truly create a more equal society.

To interact with this visualization yourself, feel free to go through my Gender Equality and Inequality workbook on Tableau Public.

With a population of over 800,000 people within approximately 47 square miles, San Francisco is one of the most densely populated cities in the United States. Cities with this level of population density must contend with a high level of policing activity to ensure safety. I wanted to explore just how safe it actually is so I decided to analyze the SF Police Calls for Services and Incidents dataset from Kaggle which covers many years (2003-2017) of policing data and contains a variety of useful information about each record.

Starting with the police-department-incidents.csv file and loading it into Pivot Billions, I quickly renamed the x and y fields to what they truly were (longitude and latitude) and started to view the whole data. Getting a quick distribution of the Resolution column I was able to see the different ways each police incident was resolved and get an idea for how common each resolution was.

I was surprised to discover that a resolution of “Psychopathic Cases” occurred nearly 30,000 times across the data! What the heck?! Is San Francisco a city of psychopaths? Sure San Francisco has had its share of famous serial killers, but this hardly seemed likely.

To investigate this further, I simply applied a filter on the Resolution column in Pivot Billions to show only the data that ended up classified as a “Psychopathic Case” by the San Francisco PD. I then downloaded this filtered data to visualize in Tableau, choosing to also drop the Location column since Tableau has trouble interpreting it.

Now that my data was in a size and format manageable for Tableau, I loaded the data into Tableau as a Text file and graphed the data by the latitude and longitude columns. By adding the Address and Number of Rows data to the visualization and quickly integrating some interactive filters and highlighters, I ended up with a useful and informative visualization of my Policing data.

This interactive map shows where each “Psychopathic Case” police incident occured and how many incidents were recorded at that location. We can clearly see that there is a high concentration of these cases in Northeast San Francisco. By using the interactive filters in my visualization it was easy to determine that the Central, Northern, Southern, and Tenderloin districts are the main districts where such incidents were recorded the most.

So is San Francisco a city overrun by psychopaths? Hardly.

A little further digging reveals that the vast majority of these so called psychopathic resolutions were in fact “non-criminal - aided cases” where a mentally ill person was involved.

In fact if you look at the Psychopathic Case designation over time, you notice that it disappears almost entirely after 2014.

It looks like this is a case of poor classification of a very serious problem that police in all major cities have to deal with, and that is the rise of mental health related incident calls. There have been a slew of high profile incidents between law enforcement and the mentally ill in San Francisco in the past, so it is a promising sign that the SFPD is moving in the right direction and starting to limit the use of the “Psychopathic Case” designation.

If you want to explore more of this data please feel free to go through my workbook on Tableau Public.

The Wrap-up

Although we can't conclude anything definitive from the exploratory data analysis we conducted on either dataset, the results open up some intriguing paths to further investigate.  Perhaps by enriching with other datasets, we can get some clearer confirmation about what was the actual cause of the traffic spike in the LA data and why is there so much positive and negative sentiment in Game Apps with ratings of 4.4.

Both these datasets are available for anyone to play with through the free public demo of our cloud version of PivotBillions.  Feel free to analyze them for yourself, or upload your own datasets and see what sort of interesting insight you can find in five minutes.

 

Everyone wants to be healthy but there are many competing claims as to how you can achieve this. With so many contradictory diets, exercise routines that take enormous amounts of time and dedication, and many other perceived paths to a healthy body and mind; tying these claims to actual data becomes very necessary and useful. That is why we decided to explore the CDC’s Behavioral Risk Factor Surveillance System data from Kaggle.

This data contains over 400,000 individuals’ responses to survey questions from 2011 to 2015. It is an extremely comprehensive dataset containing answers to hundreds of questions regarding the individuals’ behaviors and their resulting health. However, all of this data becomes quite tedious and can be arduous to dive into. With Pivot Billions we are able to easily load and enhance the dataset and immediately start to understand the data and pivot it in various ways to see the trends buried within.

For our Behavioral Risk Factor data, we created additional columns categorizing each individuals’ intake of fruit, fruit juice, and green or orange vegetables from the data using the column-creation feature. We can then pivot the data by some of these columns and the amount of activity limitation due to health problems to immediately see the average activity limitation by green vegetable and fruit intake.

The over 400,000 surveys clearly show health improvements due to either daily intake of green vegetables or fruit. Moreover, we can easily see that daily eating of fruit is better than not eating fruit or green vegetables, but daily eating of green vegetables is preferred over eating fruit and that eating both green vegetables and fruit at least once daily is the best option. In fact, the data reveals that daily intake of green vegetables and fruit leads to an over 40% reduction in experienced health limitations!

By pivoting the data by orange vegetable and fruit juice intake, we can now quickly analyze their impact.

It appears that orange vegetables and fruit juice do reduce experienced health limitations by approximately 16%, but green vegetables and fruit are still much more effective.

These are just some of the underlying trends in the data that we found using Pivot Billions. There are many more to explore in this dataset. Stay tuned for our next analysis or feel free to try it out yourself.

 

 

Fantasy football can be a relaxing past time but for anyone who takes the competition seriously, data immediately becomes very necessary. While many people track their favorite players from their favorite teams, to truly put together a winning team you need to be able to explore and understand large amounts of data. Moreover, the data you need is typically spread across many files and needs to be put back together again to truly understand it. Pivot Billions dramatically improves this process and makes it easy to merge data and start to analyze it.

As an example of this usecase, we chose to work with the historical NFL Statistics data from Kaggle. For fun, we decided to try to determine who would be considered the greatest offensive players of all time to have on your fantasy team.  The period that the data covered was from 1924 to 2016.

For our analysis, we decided to use the fantasy scoring system found at FantasyData.com.  For offensive players, points are distributed as follows:

OFFENSIVE PLAYERS

• Passing Yards: 1 point per 25 yards
• Passing Touchdowns: 4 points
• Passing Interceptions: -2 points
• Rushing Yards: 1 point per 10 yards
• Rushing Touchdowns: 6 points
• Receptions: 1 points
• Receiving Yards: 1 point per 10 yards
• Receiving Touchdowns: 6 points
• 2-Point Conversions: 2 points
• Fumbles Lost: -2 points
• Fumble Recovered for a Touchdown: 6 points

We had to merge the 4 separate files in order to combine Receiving, Passing, Rushing, and Fumble stats for offensive players.  For some reason, this dataset did not have the 2 point conversion data, so we omitted it from the calculations. We also had to perform some ETL to convert some of the data from strings to integers.

Then we select what fields the files have in common and that we want to join by. This is made extremely simple through Pivot Billions’ easy Column Preview interface:

To make our analysis more efficient, we only loaded the data that directly correlated to the fantasy football point scoring system.  A copy of the cleansed and filtered dataset can be found here.

For our Fantasy Football usecase we created an additional Fantasy Points column from the data using our column-creation feature.   This is the combined offensive point score based on the scoring system.

With the new data column, we created a pivot table based on PlayerID and FantasyPts.  We can now easily compare the best offensive players based on their point scores.

Quarterbacks dominate the top of the list.  Considering that they are almost always involved in offensive plays it makes sense.  Interestingly, the only non-quarterback to have more than 5,000 points in career fantasy scoring is Jerry Rice.   Jerry Rice is consistently considered by players, fans, and analysts as the G.O.A.T.   But based on our analysis, Peyton Manning would be the greatest fantasy football offensive player.

Because of the versatility and speed of PivotBillions, we can also analyze based on position, decade, and team just as easily.  It's also fun to try different scoring methods.  Next time, we'll do a similar analysis for defensive players.

Data is rarely consistent. The most consistent attribute of data is that it is usually dispersed across many files and needs to be put back together again to truly understand it.

Pivot Billions dramatically improves this process and makes it easy to merge your data and start to analyze it. As an example of this use case, we chose to analyze the disjointed 2012- 2017 National Hockey League (NHL) data from Kaggle. This is very interesting and potentially valuable data that describes various aspects of past NHL hockey games. It provides many views into the data from team statistics to to play-by-play results. However these views are each in distinct files that need to be merged.

With Pivot Billions we are able to easily select the datasets that we want to merge and put the pieces back together. This simple process is completed in two steps.

First, we select all of the files we want to merge:

Then we select what fields the files have in common and that we want to join by. This is made extremely simple through Pivot Billions’ easy Column Preview interface:

The data is now quickly imported into Pivot Billions and you have full access to the merged files.

We can now quickly and effortlessly analyze the data. Pivot Billions provide quick-analysis features such as viewing the distribution of each column in the data as well as powerful analysis features including creating new columns from the data and pivoting all of the data by any of its columns to get a new view of the data. This is one of Pivot Billions’ unique features and it allows us to quickly draw insights from the data.

For our example usecase we need to create an additional column to combine the first and last name of players using our f(x) column-creation feature.

Then filter the event column for "Goal".  Finally we pivot the data by player name, player position and player type.

We can now immediately see in the pivot table that in a goal event, there are three types of players involved. The scorer, any assist player, and the goalie.  With regard to position, we see that the Center and the Goalie(obviously) are the most involved in goal events.  Interestingly, the Defenseman position contributes overall to more goals than either wing position.

By deselecting the goalie player type and adding the full name, we can find out which players contribute the most toward a goal in either scores or assists.

Sidney Crosby (Center) and Patrick Kane (Right Wing) lead the pack of players from 2012-2016 in overall contribution to their teams' goals.

This extremely powerful analysis allows us to start to put together the ideal NHL fantasy team as well as compare existing teams by their individual players.  Aside from the stats in the dataset, you can derive other common hockey metrics such as the Corsi and the Fenwick along with the provided plus minus stat.  It could also be used by NHL recruiters to determine which players might be the best fit for their team to increase their win percentage.

 

The Wrap-up

From the pivot table we can immediately see a variety of very popular datasets that have been downloaded thousands of times yet have very few or no kernels developed.

Many of these are likely underutilized datasets that may not be easily understood using existing tools and could benefit from additional exploration and analysis incorporating new tools such as PivotBillions. 

 

 

The Wrap-up

It is clear from this SymbolMap plot that the sales for Des Moines are highest near the city center. However, it is also clear that there are many high volume locations throughout the city. We can now visualize the data however we see fit in Tableau. We could also easily remove our city filter from PivotBillions and load the whole preprocessed data into Tableau, or change our filter(s) to select a different subset or arrangement of the data.

To view and interact with this visualization or download the workbook to Tableau, see my Iowa Liquor Sales Workbook on Tableau Public.

 

The Wrap-up

We are able to see that the CPI (consumer price index) and the IsHoliday dimensions affect total sales but have less of an effect on average sales.  The Store Type and Store Size, however, seem to have a stronger correlation to both the total and average weekly sales.

In five minutes, we were able to run these as well as similar pivot charts using Temperature and Unemployment dimensions.  These last two did not show a very strong correlation to total or average weekly sales.  The next steps if we wanted to dive deeper would be to try to use two or more dimensions and see how they might relate to sales behavior.  It would be a good idea to set some filters to better manage the size of the analysis.