awesome so we are now recording um so hello everyone thank you so much
um so hello everyone thank you so much for coming out tonight um
for coming out tonight um this is a introductory journalism
this is a introductory journalism workshop my name is claire mallon and
workshop my name is claire mallon and i'm the president of de paul's chapter
i'm the president of de paul's chapter of the society of professional
of the society of professional journalists and the engagement editor
journalists and the engagement editor for 14 east magazine helping facilitate
for 14 east magazine helping facilitate this workshop tonight is grace del
this workshop tonight is grace del vecchio fort's niece magazine's
vecchio fort's niece magazine's editor-in-chief and cam rodriguez sports
editor-in-chief and cam rodriguez sports managing editor
managing editor and with us today and leading our
and with us today and leading our introductory data journalism workshop is
introductory data journalism workshop is ben welsh ben is a los angeles times
ben welsh ben is a los angeles times data journalist who currently serves as
data journalist who currently serves as the visiting la times journalist at
the visiting la times journalist at stanford university where he is
stanford university where he is expanding where he's leading an
expanding where he's leading an expansion of the big local news
expansion of the big local news initiative
initiative um ben stick with us while i just run
um ben stick with us while i just run through all your amazing credits he
through all your amazing credits he co-founded the times first digitally
co-founded the times first digitally focused projects team and went on to
focused projects team and went on to lead the modernization of the
lead the modernization of the newspaper's graphics department his work
newspaper's graphics department his work includes coverage of live election
includes coverage of live election results across four presidential cycles
results across four presidential cycles a mapping platform that's a new standard
a mapping platform that's a new standard for defining la neighborhoods custom
for defining la neighborhoods custom designs for dozens of flagship projects
designs for dozens of flagship projects and the most complete resource on the
and the most complete resource on the spread of cobit 19 in california
spread of cobit 19 in california his data-driven reporting has led to
his data-driven reporting has led to reforms in the la
reforms in the la fire department's 9-1-1 system a revamp
fire department's 9-1-1 system a revamp of the broken building inspection
of the broken building inspection program the replacement of the los
program the replacement of the los angeles police department's public crime
angeles police department's public crime map as well as an increased fines
map as well as an increased fines against exploitive landlords he has won
against exploitive landlords he has won numerous journalism awards including a
numerous journalism awards including a pulitzer prize for breaking news
pulitzer prize for breaking news reporting as part of the la times team
reporting as part of the la times team that covered the mass shooting in san
that covered the mass shooting in san bernardino he's a proud depaul alum and
bernardino he's a proud depaul alum and serves as a fellow for depaul's very own
serves as a fellow for depaul's very own center for journalism integrity and
center for journalism integrity and excellence he's a big believer in making
excellence he's a big believer in making data openly accessible and frequently
data openly accessible and frequently teaches data journalism and computer
teaches data journalism and computer programming skills to students and
programming skills to students and professionals like us tonight
professionals like us tonight ben we are so lucky to have you here
ben we are so lucky to have you here today
today well thank you so much for having me
well thank you so much for having me yeah of course we are very excited um
yeah of course we are very excited um i'm going to go ahead and spotlight you
i'm going to go ahead and spotlight you for everyone
for everyone you're now in the spotlight um so ben is
you're now in the spotlight um so ben is going to lead a introductory data
going to lead a introductory data journalism web workshop specifically on
journalism web workshop specifically on spreadsheets um
spreadsheets um ben i'll pass it off to you you can go
ben i'll pass it off to you you can go ahead and get us started okay great
ahead and get us started okay great thank you for the introduction claire
thank you for the introduction claire that was really generous um you know as
that was really generous um you know as she said i'm a depaul graduate i'm from
she said i'm a depaul graduate i'm from the class of 2004
the class of 2004 which is a frightening amount of time
which is a frightening amount of time has passed since then now that i'm
has passed since then now that i'm forced to reflect on it but it's really
forced to reflect on it but it's really great to be back at depaul and teaching
great to be back at depaul and teaching and helping again if even remotely
and helping again if even remotely because um
because um you know without being too corny about
you know without being too corny about it you know depaul really was a major
it you know depaul really was a major factor in my career and my life i grew
factor in my career and my life i grew up in eastern iowa
up in eastern iowa near cedar rapids and iowa city and
near cedar rapids and iowa city and moving to chicago at age 17 was it might
moving to chicago at age 17 was it might as it was like paris france to me and it
as it was like paris france to me and it was the biggest deal in my life and uh
was the biggest deal in my life and uh you know other than meeting my wife and
you know other than meeting my wife and getting married probably the best
getting married probably the best decision i ever made so being involved
decision i ever made so being involved with paul continuing involved with it is
with paul continuing involved with it is really important to me and i'd be happy
really important to me and i'd be happy to talk about that more but
to talk about that more but i won't get too sappy on you um it was
i won't get too sappy on you um it was after paul that i began my journalism
after paul that i began my journalism experience um working with carol moraine
experience um working with carol moraine and don mosley when they first took up
and don mosley when they first took up their residence and it was with them
their residence and it was with them that
that i built my first spreadsheet so there
i built my first spreadsheet so there was a story about a a suburb of chicago
was a story about a a suburb of chicago that people were telling us maybe
that people were telling us maybe had some crooked stuff going on with how
had some crooked stuff going on with how legal bills were being charged against
legal bills were being charged against the city and uh and don help me file my
the city and uh and don help me file my first public records request to the city
first public records request to the city just asking for all the legal fees
just asking for all the legal fees printed out
printed out it came back as one of those old school
it came back as one of those old school printouts which maybe you guys have
printouts which maybe you guys have never seen where the printer would have
never seen where the printer would have the little tear strips along the side
the little tear strips along the side and all the pages would be folded
and all the pages would be folded together like an accordion
together like an accordion and it was probably 20 or 30 feet long
and it was probably 20 or 30 feet long and from this like 1980s printer and it
and from this like 1980s printer and it had all the legal fees in there but they
had all the legal fees in there but they were you know hard to read and spread
were you know hard to read and spread out across this long scroll we kind of
out across this long scroll we kind of thought well how are we going to get
thought well how are we going to get from this to our to our story
from this to our to our story and the answer was my first spreadsheet
and the answer was my first spreadsheet which was to really just sit there and
which was to really just sit there and go through it page by page and type the
go through it page by page and type the numbers in
numbers in and then add it up so that we could say
and then add it up so that we could say this is how much money got spent over
this is how much money got spent over what time and and that became the basis
what time and and that became the basis for an investigative story that ran on
for an investigative story that ran on wmaq
wmaq and uh carol even climbed the ladder and
and uh carol even climbed the ladder and then let the paper drop like live on
then let the paper drop like live on camera to show how silly it was and and
camera to show how silly it was and and that for me was kind of my first hit of
that for me was kind of my first hit of it and what got me into data journalism
it and what got me into data journalism was really that by learning some nerdy
was really that by learning some nerdy stuff one i could kind of find my niche
stuff one i could kind of find my niche like how i could contribute as someone
like how i could contribute as someone who wasn't as experienced in other ways
who wasn't as experienced in other ways and two it was just kind of like a
and two it was just kind of like a shortcut to doing like really cool
shortcut to doing like really cool investigative work you know because if
investigative work you know because if you can learn these data skills
you can learn these data skills even if it's just like an extra kind of
even if it's just like an extra kind of power you have and that your main focus
power you have and that your main focus as a specialist
as a specialist it's something you can use to to just
it's something you can use to to just get stories nobody else is getting and
get stories nobody else is getting and to do stuff that really makes an impact
to do stuff that really makes an impact that people notice and so um you know i
that people notice and so um you know i ended up going down this whole road
ended up going down this whole road where i kind of became a specialist and
where i kind of became a specialist and so you know gradually becoming a
so you know gradually becoming a computer programmer and really just
computer programmer and really just focusing on data data data all day and
focusing on data data data all day and you could go down that road if you're
you could go down that road if you're interested and i'd be happy to talk to
interested and i'd be happy to talk to you about it but really a lot of the
you about it but really a lot of the most effective data journalists really
most effective data journalists really just have it as like an extra tool in
just have it as like an extra tool in their toolkit and they you know see
their toolkit and they you know see themselves more as traditional
themselves more as traditional journalists but they know enough about
journalists but they know enough about data to pull off a story now and then
data to pull off a story now and then and those are people often have some of
and those are people often have some of the biggest impact and so i think
the biggest impact and so i think you don't have to look at these skills
you don't have to look at these skills as something that have to consume your
as something that have to consume your whole career or life unless you really
whole career or life unless you really want them to
want them to so
so um so the training i'm going to give
um so the training i'm going to give tonight is is really just like
tonight is is really just like spreadsheets 101 it's a really basic
spreadsheets 101 it's a really basic introduction to like what is a
introduction to like what is a spreadsheet and how do you use it and
spreadsheet and how do you use it and then how to use it to kind of analyze
then how to use it to kind of analyze data to ask and answer questions to
data to ask and answer questions to interview it and so we're going to start
interview it and so we're going to start with sort of some phony data that we
with sort of some phony data that we make up just to like keep things simple
make up just to like keep things simple and we're going to cover some of the
and we're going to cover some of the fundamentals of working with a
fundamentals of working with a spreadsheet and then after we get some
spreadsheet and then after we get some practice we're going to introduce a real
practice we're going to introduce a real data set from the actual news in chicago
data set from the actual news in chicago and and try to ask it some questions try
and and try to ask it some questions try to interview it like we would another
to interview it like we would another source and that data set is the
source and that data set is the complaints against chicago police which
complaints against chicago police which is a database that was sort of
is a database that was sort of crowbarred out of the government a few
crowbarred out of the government a few years ago by the invisible institute i
years ago by the invisible institute i bet you guys have seen it in the news
bet you guys have seen it in the news from time to time
from time to time okay so that's kind of the plan
okay so that's kind of the plan all right that's that's the pre-spiel
all right that's that's the pre-spiel for the actual spiel
for the actual spiel um before we begin does anybody have any
um before we begin does anybody have any questions
questions or things they
or things they want to ask i'm happy to answer anything
and if no one has any questions now if you have questions throughout the
you have questions throughout the workshop uh cam grace and i will be
workshop uh cam grace and i will be answering questions in the chat so you
answering questions in the chat so you can just shoot them there and we'll try
can just shoot them there and we'll try and help you troubleshoot and if not
and help you troubleshoot and if not we'll bring them to ben's attention
we'll bring them to ben's attention looks like ava has a question oh what's
looks like ava has a question oh what's your question
your question yeah um i was just wondering i know
yeah um i was just wondering i know excel is like the most common um
excel is like the most common um platform for working with spreadsheets
platform for working with spreadsheets but are there any other any other
but are there any other any other types of programs or software that data
types of programs or software that data journalists typically use
journalists typically use yeah sure so when a you know spreadsheet
yeah sure so when a you know spreadsheet is really kind of a generic term you
is really kind of a generic term you know it's like soda
know it's like soda and excel is like coca-cola right you
and excel is like coca-cola right you know what i mean it's kind of the brand
know what i mean it's kind of the brand name of the biggest product but there's
name of the biggest product but there's other sodas on the market so to speak
other sodas on the market so to speak when it comes to spreadsheets in fact
when it comes to spreadsheets in fact the original spreadsheet was called
the original spreadsheet was called fizzy calc if you've heard of that from
fizzy calc if you've heard of that from the 1980s
the 1980s um
um and you know there's one that comes on
and you know there's one that comes on apple computers called numbers
apple computers called numbers um you know i'm a super nerd so i use
um you know i'm a super nerd so i use linux and there's like an open source
linux and there's like an open source one that's free
one that's free and then we're actually in this class
and then we're actually in this class going to use google sheets which is sort
going to use google sheets which is sort of like google's competitor to excel
of like google's competitor to excel it's available in a web browser and it's
it's available in a web browser and it's free which is why we're teaching it in
free which is why we're teaching it in this class
this class i think probably
i think probably if you were to compare them all excel
if you were to compare them all excel probably is the best because
probably is the best because it just can handle data sets that are a
it just can handle data sets that are a little larger
little larger and it has a lot of features and it's
and it has a lot of features and it's really well tested but when it comes to
really well tested but when it comes to like the basic features that we're going
like the basic features that we're going to use for this uh class and which most
to use for this uh class and which most journalists use they all have the basic
journalists use they all have the basic features and you can really use all of
features and you can really use all of them to do it and the stuff we cover in
them to do it and the stuff we cover in google sheets everything we cover is in
google sheets everything we cover is in excel it's just like the button is going
excel it's just like the button is going to be in a slightly different place or
to be in a slightly different place or they might have like a different nerdy
they might have like a different nerdy name for some feature or something right
name for some feature or something right so spreadsheets it's like you know we're
so spreadsheets it's like you know we're going to do use basketball as our
going to do use basketball as our example and so to me spreadsheets and
example and so to me spreadsheets and data journalism is like tripling you
data journalism is like tripling you know it's just like the fundamental
know it's just like the fundamental skill of what is data how do you
skill of what is data how do you understand it how do you how do you
understand it how do you how do you structure it manipulate it and analyze
structure it manipulate it and analyze it so that's where it all begins and so
it so that's where it all begins and so i really think that it's the fundamental
i really think that it's the fundamental tool however as you start working with
tool however as you start working with larger data sets that have more and more
larger data sets that have more and more records or you have a database that has
records or you have a database that has many tables that need to be joined
many tables that need to be joined together kind of in a complex system
together kind of in a complex system or if you're going to begin to like um
or if you're going to begin to like um automate things to routinely gather data
automate things to routinely gather data from the internet or other things like
from the internet or other things like that that's where you begin to introduce
that that's where you begin to introduce tools that go beyond the spreadsheet you
tools that go beyond the spreadsheet you know and so those include like big
know and so those include like big relational database software they
relational database software they include computer programming languages
include computer programming languages that are built for statistical analysis
that are built for statistical analysis you know maybe you've heard of r or
you know maybe you've heard of r or python right and you know a lot of
python right and you know a lot of people sometimes start learning those
people sometimes start learning those skills i think that that's fine it's
skills i think that that's fine it's fine to start with those but i really do
fine to start with those but i really do think that the spreadsheet is the is the
think that the spreadsheet is the is the basic building block and i think quite a
basic building block and i think quite a lot of data journalism can and should be
lot of data journalism can and should be done in the spreadsheet and doesn't
done in the spreadsheet and doesn't require the bigger tools
require the bigger tools did that answer your question
did that answer your question yes it did thank you
yes it did thank you [Music]
excuse me i have a little bit of a cough i have
i have flu like two weeks ago not covered but i
flu like two weeks ago not covered but i am still coughing and so that's going to
am still coughing and so that's going to happen tonight and i just apologize in
happen tonight and i just apologize in advance it's just a sign of what a
advance it's just a sign of what a weekly i am okay that's still carrying
weekly i am okay that's still carrying this around
this around all right so if there's no other
all right so if there's no other questions i'm just going to dive in
questions i'm just going to dive in which is going to start with sharing my
which is going to start with sharing my screen so bear with me here
all right can you guys see my screen yes
yes okay so this is a blank google sheet
okay so this is a blank google sheet this is like the very beginning of a
this is like the very beginning of a spreadsheet within google sheets but let
spreadsheet within google sheets but let me show you how to get there if you
me show you how to get there if you haven't been there before
haven't been there before the starting place i'm going to go back
the starting place i'm going to go back to a tab and it's just in google drive
to a tab and it's just in google drive so that's
so that's drive.google.com
drive.google.com right which is
right which is this is kind of like the microsoft
this is kind of like the microsoft office of google i mean you guys are
office of google i mean you guys are college students i bet you've all seen
college students i bet you've all seen this before right so this will require
this before right so this will require that you have a google account i think
that you have a google account i think and that you log into google so i would
and that you log into google so i would just ask everybody to start off
just ask everybody to start off by going to drive.google.com
by going to drive.google.com and kind of getting to this landing page
and kind of getting to this landing page which is all your like folders right
which is all your like folders right and you can see i've got some stuff in
and you can see i've got some stuff in my life and then some things i've been
my life and then some things i've been monkeying today
monkeying today okay
okay i'm going to give everybody you know a
i'm going to give everybody you know a couple seconds just to get there because
couple seconds just to get there because you got to log into your google account
you got to log into your google account etc
etc excuse me
all right if you're having trouble again just feel free to raise the hand button
just feel free to raise the hand button and one of the other hosts can help you
and one of the other hosts can help you along and if i'm going too fast or too
along and if i'm going too fast or too slow please speak up but i'm going to
slow please speak up but i'm going to begin
begin the first step is just to create a new
the first step is just to create a new blank spreadsheet you know a canvas for
blank spreadsheet you know a canvas for you to begin to create and you do that
you to begin to create and you do that by clicking the new button in the upper
by clicking the new button in the upper left hand corner
left hand corner and then there's this pull down which
and then there's this pull down which has the different types of things that
has the different types of things that google drive can do google docs that
google drive can do google docs that you've all seen they have a powerpoint
you've all seen they have a powerpoint thing
thing a little form doodly deal right but
a little form doodly deal right but right there in the middle is google
right there in the middle is google sheets sheets being short for
sheets sheets being short for spreadsheets so i click that
spreadsheets so i click that and boom
and boom i've got my new blank spreadsheet which
i've got my new blank spreadsheet which is oftentimes called a workbook in the
is oftentimes called a workbook in the language of spreadsheet nerds right
language of spreadsheet nerds right and it starts off blank we're just going
and it starts off blank we're just going to start from scratch so we can cover
to start from scratch so we can cover some of the fundamentals with some phony
some of the fundamentals with some phony data so the first thing i'm going to do
data so the first thing i'm going to do is i'm going to go and i'm going to name
is i'm going to go and i'm going to name my spreadsheet so i'm going to click in
my spreadsheet so i'm going to click in the upper left hand corner on that
the upper left hand corner on that untitled spreadsheet i'm going to give
untitled spreadsheet i'm going to give it a name
it a name and uh you know forgive me for the
and uh you know forgive me for the sports stuff but we're going to call
sports stuff but we're going to call this uh you know our dream team because
this uh you know our dream team because our what we're going to do in our first
our what we're going to do in our first spreadsheet is we're going to create our
spreadsheet is we're going to create our dream team of basketball players our
dream team of basketball players our favorite basketball players that we all
favorite basketball players that we all love
love and then you know start a starting
and then you know start a starting basketball team has five people in it
basketball team has five people in it right
right and so in a spreadsheet you know each
and so in a spreadsheet you know each row tends to be a record so one two
row tends to be a record so one two three four five so we're gonna fill in
three four five so we're gonna fill in five rows one for each player
five rows one for each player and each column or field
and each column or field tends to be
tends to be uh an attribute or a piece of metadata
uh an attribute or a piece of metadata about the person it's their name it's
about the person it's their name it's their birthday it's their salary these
their birthday it's their salary these are the things we'll fill in and that
are the things we'll fill in and that sort of grid or structure is what
sort of grid or structure is what makes it a spreadsheet
makes it a spreadsheet and so
and so the first thing that really comes with
the first thing that really comes with any spreadsheet is defining what your
any spreadsheet is defining what your columns are going to be what's the data
columns are going to be what's the data that you're going to track within each
that you're going to track within each row
row and so my goal is to make my basketball
and so my goal is to make my basketball dream team and so i'm going to the first
dream team and so i'm going to the first column is going to be the name of the
column is going to be the name of the player right so i'm just going to click
player right so i'm just going to click in right there
in right there a1 see it's a is the column one is the
a1 see it's a is the column one is the row it's just like battleship
row it's just like battleship right
right a
a one that's my first column in my first
one that's my first column in my first row and i'm gonna say name this is these
row and i'm gonna say name this is these are work this is what's called the
are work this is what's called the header
header right it's the kind of first row the
right it's the kind of first row the front of the sheet
front of the sheet and then each player has a position that
and then each player has a position that they play right like in basketball where
they play right like in basketball where do they play on the court so my next
do they play on the court so my next column on the type position you guys do
column on the type position you guys do this too play along
this too play along okay
okay name position
name position and then since we want to do a little
and then since we want to do a little math as part of this example we want to
math as part of this example we want to get into
get into how we can calculate stuff once we have
how we can calculate stuff once we have data put together we're going to have a
data put together we're going to have a column that is
column that is current salary is what i'm going to call
current salary is what i'm going to call so this is what they get paid right now
so this is what they get paid right now and then we're going to make a kind of
and then we're going to make a kind of fun fantasy column which is what we're
fun fantasy column which is what we're going to pay them from like our big
going to pay them from like our big budget right and so we're going to say
budget right and so we're going to say like new salary
like new salary right so just by clicking in
right so just by clicking in typing hitting enter i've been able to
typing hitting enter i've been able to put in these header fields
put in these header fields into the first row and then each each
into the first row and then each each row beneath it will have the values for
row beneath it will have the values for that record
i jumped in a little bit ahead of here there's some other stuff around that
there's some other stuff around that might be worth going over you see that
might be worth going over you see that just like in microsoft word or any uh
just like in microsoft word or any uh document editing thing you've used
document editing thing you've used there's kind of formatting ribbon of
there's kind of formatting ribbon of options up here which allow you to sort
options up here which allow you to sort of like style and muck around with the
of like style and muck around with the stuff that you put in so i would just
stuff that you put in so i would just say let's everybody just click on a1
say let's everybody just click on a1 hold down and drag to the right you see
hold down and drag to the right you see how i did that and i've now selected
how i did that and i've now selected four cells
four cells i'm gonna do that then i'm just gonna
i'm gonna do that then i'm just gonna hit that b
hit that b right there on the ribbon and just like
right there on the ribbon and just like in microsoft word or google docs it's
in microsoft word or google docs it's gonna bold it right and so now we've
gonna bold it right and so now we've bolded the first row of our data to make
bolded the first row of our data to make it clear these are the headers
it clear these are the headers right and there's lots of other options
right and there's lots of other options here which allow you to do background
here which allow you to do background colors italics and change the font
colors italics and change the font when it comes to numbers there's some
when it comes to numbers there's some important ways to format that we'll get
important ways to format that we'll get into and just a lot of stuff so if you
into and just a lot of stuff so if you look around up here there's a lot of
look around up here there's a lot of different options ultimately you
different options ultimately you probably only need four or five of them
probably only need four or five of them to kind of do what we need to do but
to kind of do what we need to do but there's a bunch of stuff
some of those things will of course be different in excel versus in google
different in excel versus in google sheets um on mac versus pc and so sort
sheets um on mac versus pc and so sort of if you switch from one spreadsheet
of if you switch from one spreadsheet program to another all the same buttons
program to another all the same buttons are kind of up there they're just going
are kind of up there they're just going to look a little different and be in
to look a little different and be in different places
different places and kind of just being patient about it
and kind of just being patient about it is and finding what you're after is kind
is and finding what you're after is kind of what's necessary
of what's necessary there's some so that's stuff that you
there's some so that's stuff that you see in documents but there's other
see in documents but there's other formatting things that are specific to
formatting things that are specific to spreadsheets so for instance our columns
spreadsheets so for instance our columns our columns and rows can be resized
our columns and rows can be resized right so you see here they each have the
right so you see here they each have the standard size but if i hover
standard size but if i hover over column d i can make it wider i can
over column d i can make it wider i can make it narrower
make it narrower you can do that for the rows make them
you can do that for the rows make them taller or shorter
taller or shorter right there's even a trick where if you
right there's even a trick where if you click in the upper left and that blank
click in the upper left and that blank box it selects the whole spreadsheet
box it selects the whole spreadsheet we'll use that later like that's a
we'll use that later like that's a little trick
little trick if you do that and then
if you do that and then double click on the headers it auto
double click on the headers it auto sizes them to fit the data did you see
sizes them to fit the data did you see how that happened
how that happened there's a million little tricks like
there's a million little tricks like that you don't need to know them all but
that you don't need to know them all but there's a lot underneath the hood here
there's a lot underneath the hood here that's really powerful that you'll start
that's really powerful that you'll start to learn more and more of as you get
to learn more and more of as you get more practice
more practice all right
um one little trick is that you can make the header row kind of special by
the header row kind of special by grabbing the bottom of that upper
grabbing the bottom of that upper upper left corner box which doesn't have
upper left corner box which doesn't have a name as far as i know the magic box
a name as far as i know the magic box i'll call it
i'll call it i'm going to grab that top one and pull
i'm going to grab that top one and pull it down and you can see that now it's
it down and you can see that now it's kind of put my header
kind of put my header right with a little line below it to
right with a little line below it to break it out and after i scroll now you
break it out and after i scroll now you can see how there's row two it sort of
can see how there's row two it sort of disappears and then comes back that's
disappears and then comes back that's gonna freeze that first row so if you
gonna freeze that first row so if you add more data and scroll it'll stay at
add more data and scroll it'll stay at the top that's just one nice little
the top that's just one nice little trick not essential but nice to know
trick not essential but nice to know okay everybody get that far
okay great so now we're going to fill this in we need five basketball players
this in we need five basketball players i need people to shout it out from the
i need people to shout it out from the audience who do you like who do you want
audience who do you like who do you want on your dream team
steph curry steph curry
steph curry he's healthy again not a bad pick
he's healthy again not a bad pick okay he plays
what he's a guard it's okay it's okay
it's okay it's okay he's a little fast guy
he's a little fast guy all right somebody else who's on your
all right somebody else who's on your dream team
dream team we can throw in lebron to make it
we can throw in lebron to make it interesting lebron james this is how
interesting lebron james this is how they do the nba all-star game you know
i'd call him a guard but that's debatable
debatable jason tatum
jason tatum who's that defense
who's that defense jason tatum is that's what the y
jason tatum is that's what the y yes okay we're going to say he's playing
yes okay we're going to say he's playing forward i don't know if that's true yes
forward i don't know if that's true yes he is a forward
he is a forward okay
okay celtic's defense last night was looking
celtic's defense last night was looking good in the second half
good in the second half matisse diable
matisse diable how do you spell that
how do you spell that t
t i
i s s e
s s e and then i think his last name is
and then i think his last name is t h y
t h y b
b u l l e he's a guard forward for the six
u l l e he's a guard forward for the six series okay and then i'm picking last
series okay and then i'm picking last because this is my spreadsheet my center
because this is my spreadsheet my center is britney grinder guys come on
is britney grinder guys come on [Laughter]
[Laughter] we're bringing her home
we're bringing her home okay
okay so we got our starting five
so we got our starting five and you can see here it's one row per
and you can see here it's one row per person right and whenever you get a
person right and whenever you get a spreadsheet from somebody else you make
spreadsheet from somebody else you make one it's always good to think about what
one it's always good to think about what is a row like is a row a person is it a
is a row like is a row a person is it a transaction from like your credit card
transaction from like your credit card statement
statement is it um an incident that's been
is it um an incident that's been recorded by a government agency is it a
recorded by a government agency is it a thing in the world just like what is the
thing in the world just like what is the row and we'll see this later with the
row and we'll see this later with the police data because there can be sort of
police data because there can be sort of interesting nuances to how you interpret
interesting nuances to how you interpret the data based on what the row kind of
the data based on what the row kind of is right and so in our case
is right and so in our case the row is a person or a player right
the row is a person or a player right okay and so we've got five rows but it's
okay and so we've got five rows but it's actually six rows right because it's a
actually six rows right because it's a header but there's five records
header but there's five records and then we've got two columns we filled
and then we've got two columns we filled out name
out name and position
and position right
right and then the one i'm going to get is
and then the one i'm going to get is their current salary so to do this i'm
their current salary so to do this i'm going to go to basketballreference.com
going to go to basketballreference.com which is just like a nerdy website that
which is just like a nerdy website that has data about players and i'm going to
has data about players and i'm going to get their salary so steph curry's most
get their salary so steph curry's most recent salary guys buckle up is 43
recent salary guys buckle up is 43 million dollars
million dollars geez
geez right so i'm gonna punch that in go
right so i'm gonna punch that in go ahead and punch it in yourself you don't
ahead and punch it in yourself you don't the numbers we put in here don't really
the numbers we put in here don't really matter you can kind of fudge them you
matter you can kind of fudge them you know let me make this a little bigger
know let me make this a little bigger too while we're doing it but i'm going
too while we're doing it but i'm going to punch it in there and you notice that
to punch it in there and you notice that i don't put in any commas or the dollar
i don't put in any commas or the dollar signs because that stuff's for humans
signs because that stuff's for humans that stuff's not for computers the
that stuff's not for computers the computer just wants the number right we
computer just wants the number right we can format the number to look however we
can format the number to look however we want later and i'll show you that in a
want later and i'll show you that in a minute but oftentimes when you're
minute but oftentimes when you're punching in numbers into the into the
punching in numbers into the into the computer especially when you're copying
computer especially when you're copying pasting like i did if you include things
pasting like i did if you include things like dollar signs or commas the computer
like dollar signs or commas the computer can get confused and think it's a word
can get confused and think it's a word and not a number right because the
and not a number right because the computer always has to guess is this a
computer always has to guess is this a number or a word or
number or a word or um
um or what and that affects how it gets
or what and that affects how it gets interpreted when you begin doing
interpreted when you begin doing mathematical formulas and trying to uh
mathematical formulas and trying to uh analyze it that's called the data type
analyze it that's called the data type and so what type of information is in
and so what type of information is in the cell and you know characters versus
the cell and you know characters versus numbers and then dates those three are
numbers and then dates those three are kind of the most common data types and
kind of the most common data types and making sure that the spreadsheet is kind
making sure that the spreadsheet is kind of kosher and happy with them is part of
of kosher and happy with them is part of you know preparing your data for
you know preparing your data for analysis so i put the number in there
analysis so i put the number in there with no commas or dollar signs for now
with no commas or dollar signs for now all right we're gonna go look up lebron
all right we're gonna go look up lebron next
salary a little less making a little less than
a little less making a little less than step
step 39 million
39 million i'm again just going to pull out those
i'm again just going to pull out those other things oops i put it in the see
other things oops i put it in the see what i did
what i did i made a mistake i put it over steps
i made a mistake i put it over steps number
number so what i can do is i'm going to copy it
so what i can do is i'm going to copy it to make sure i got it but then if i do
to make sure i got it but then if i do edit undo i get steps numbered back
edit undo i get steps numbered back right and the reason it went into that
right and the reason it went into that other one is because the blue selector
other one is because the blue selector box was around c2 i need to click and
box was around c2 i need to click and put it on c3 and then paste it in and
put it on c3 and then paste it in and hit enter which moves me down a row
hit enter which moves me down a row and that's how i get lebron's number
and that's how i get lebron's number incorrectly all right
incorrectly all right let's do jason tatum i like jason tatum
let's do jason tatum i like jason tatum my favorite celtic is marcus smart
my favorite celtic is marcus smart though
though he's wild
he's wild crazy
crazy all right we're going to go get the
all right we're going to go get the salary
salary is it in here
is it in here there it is only 9 million
i said that right i'm just going to search for him copy and paste it oh
search for him copy and paste it oh that bit my
that bit my my like mouse wheel went crazy
my like mouse wheel went crazy okay there we go
paste that in and then i checked this earlier spoiler
and then i checked this earlier spoiler they don't have women's basketball
they don't have women's basketball salaries on the site so i'm just going
salaries on the site so i'm just going to google it real quick
to google it real quick and you maybe want to vet this a little
and you maybe want to vet this a little more but i'm just going to take this
more but i'm just going to take this first site here and go with it and
first site here and go with it and brittany griner what a rip off only
brittany griner what a rip off only making two hundred thousand dollars
making two hundred thousand dollars so we're going to put her in there last
so we're going to put her in there last and we've got those numbers in so i'd
and we've got those numbers in so i'd encourage everybody to just punch in
encourage everybody to just punch in these numbers or really any numbers you
these numbers or really any numbers you want because we're just going to teach
want because we're just going to teach some principles of how to do math it
some principles of how to do math it doesn't really matter what the numbers
doesn't really matter what the numbers are for what we're about to learn
are for what we're about to learn you can see that kind of gathering that
you can see that kind of gathering that data was a key to begin creating
data was a key to begin creating something we can analyze and oftentimes
something we can analyze and oftentimes a lot of the best data journalism
a lot of the best data journalism stories come from data that you create
stories come from data that you create that there's kind of unstructured
that there's kind of unstructured information in the world or sprinkled
information in the world or sprinkled around the internet or hidden in the
around the internet or hidden in the government office and you plug it into a
government office and you plug it into a spreadsheet so you can get it to a place
spreadsheet so you can get it to a place where you can analyze it
where you can analyze it and that's where you're often doing work
and that's where you're often doing work that no one else has done before or done
that no one else has done before or done quite that way that's how i got that
quite that way that's how i got that story way back when
story way back when um everybody good any questions
i think we're good all right so there's one column we're missing which is the
one column we're missing which is the new salary so which is what we're gonna
new salary so which is what we're gonna pay him all right so i'm just gonna
pay him all right so i'm just gonna throw it out to the crowd what should we
throw it out to the crowd what should we pay steph curry somebody shout out a
pay steph curry somebody shout out a number
30 million 30 million he makes it though
okay lebron what's he getting
25 million 25 million okay
25 million okay all right steph's still top dog all
all right steph's still top dog all right jason tatum
100 million 100 million
20 million 20 million
you got to get britney at least that much yeah i don't know give her give her
much yeah i don't know give her give her 50 mil give her give her 50 billion give
50 mil give her give her 50 billion give her
her i mean when she gets home she deserves
i mean when she gets home she deserves something i'll tell you that yeah
okay so we've just got to punch some numbers
so we've just got to punch some numbers in you can punch in different ones you
in you can punch in different ones you want again it's not a big deal but now
want again it's not a big deal but now we've kind of got a complete data set
we've kind of got a complete data set that's ready for a little bit of
that's ready for a little bit of analysis and we can cover some fun
analysis and we can cover some fun spreadsheet tricks um one thing here's
spreadsheet tricks um one thing here's something i teased earlier is you know i
something i teased earlier is you know i told you to punch in these numbers
told you to punch in these numbers without commas or dollar signs so the
without commas or dollar signs so the computer would be happy but that makes
computer would be happy but that makes them kind of hard to read so what if you
them kind of hard to read so what if you want to make it easy for you to read but
want to make it easy for you to read but still be able to analyze it right the
still be able to analyze it right the way you do that is you say select the
way you do that is you say select the column so if i click on the c up at the
column so if i click on the c up at the top that selects all of column c so do
top that selects all of column c so do that
that and then there's a format menu here at
and then there's a format menu here at the top
the top you go and click on format and then
you go and click on format and then there's a
there's a option and you can see here a lot of
option and you can see here a lot of different preset ways that google sheets
different preset ways that google sheets is able to format a number depending on
is able to format a number depending on what kind of thing it is right should it
what kind of thing it is right should it have a decimal place is it a percent
have a decimal place is it a percent that should be taken by a hundred maybe
that should be taken by a hundred maybe you want scientific notation i sure as
you want scientific notation i sure as hell don't but maybe you do
hell don't but maybe you do um
um do you want it rounded or not you know
do you want it rounded or not you know is this a date at not a number like all
is this a date at not a number like all these different things can get sorted
these different things can get sorted out with the format menu so in our case
out with the format menu so in our case i think we want currency rounded right
i think we want currency rounded right we wanted to have a dollar we want the
we wanted to have a dollar we want the commas after every three
commas after every three values and we don't really need the
values and we don't really need the decimal places right
decimal places right so i'm going to select currency rounded
so i'm going to select currency rounded you can pick whatever you'd like it's up
you can pick whatever you'd like it's up to you but you see i that that magically
to you but you see i that that magically put in
put in the commas and the dollar sign on the
the commas and the dollar sign on the front then but if i click on one of the
front then but if i click on one of the values and i look into the
values and i look into the editor here at the top you see that
editor here at the top you see that there aren't actually any commas in it
there aren't actually any commas in it right it's because what's stored in the
right it's because what's stored in the data behind the scenes with sheets is
data behind the scenes with sheets is just the number but then the formatting
just the number but then the formatting for us to look at is sort of like icing
for us to look at is sort of like icing on the cake right
on the cake right and i'm going to do the same thing for
and i'm going to do the same thing for salary i'm going to click on d
salary i'm going to click on d and there's even a little shortcut do
and there's even a little shortcut do you see this little dollar sign here
you see this little dollar sign here in the uh ribbon
in the uh ribbon that will do it for you but you see that
that will do it for you but you see that actually puts those decimal places in
actually puts those decimal places in there which i don't want but there's
there which i don't want but there's another shortcut for that right here
another shortcut for that right here this button will decrease the decimal
this button will decrease the decimal places
places so i hit that twice and boom i've got
so i hit that twice and boom i've got some nice numbers
okay now if you were in a different country
now if you were in a different country sheets would be smart enough to maybe
sheets would be smart enough to maybe put a different currency value in front
put a different currency value in front or use the commas in a different way
or use the commas in a different way because these are
because these are as i suspect you guys know but just say
as i suspect you guys know but just say these are you know american or english
these are you know american or english conventions that aren't shared by
conventions that aren't shared by everyone in the world and so computer
everyone in the world and so computer software often has to internationalize
software often has to internationalize itself to the current locales
itself to the current locales okay
okay everybody get that in
okay so now we've got our starting data set it's what we're here but we want to
set it's what we're here but we want to analyze it we want to ask some questions
analyze it we want to ask some questions and answer them
and answer them numerically
numerically um using the spreadsheet it'll work for
um using the spreadsheet it'll work for our five records but also what's great
our five records but also what's great about spreadsheets is it would work for
about spreadsheets is it would work for 500 million records if you had enough
500 million records if you had enough these tricks that we learn on this
these tricks that we learn on this little small data set can be easily
little small data set can be easily applied to lots and lots of data once we
applied to lots and lots of data once we learn how to do them right so i'm going
learn how to do them right so i'm going to add a new column which is going to be
to add a new column which is going to be my new calculated thing
my new calculated thing that i'm going to pull out of thin air
that i'm going to pull out of thin air using excel and it's going to be change
using excel and it's going to be change because i want to know the change in
because i want to know the change in salary for each of the five players from
salary for each of the five players from their current salary to what we're going
their current salary to what we're going to pay them right so i just type in
to pay them right so i just type in change i hit b
change i hit b to control b to bold it you know hit
to control b to bold it you know hit that b right there
that b right there and then i click down to e2 so
and then i click down to e2 so here's a really tough math question guys
here's a really tough math question guys how would you calculate the difference
how would you calculate the difference between someone's new salary and their
between someone's new salary and their current salary
subtraction subtraction yeah right and so really you take the new
right and so really you take the new salary you then subtract from it the
salary you then subtract from it the current salary and the difference would
current salary and the difference would be the change right
be the change right and so that could be 30 million
and so that could be 30 million uh minus 43 million in the case of steph
uh minus 43 million in the case of steph curry right and in the case of lebron
curry right and in the case of lebron james it's 25 million minus 39
james it's 25 million minus 39 right so you could just type in these
right so you could just type in these numbers into a calculator and then type
numbers into a calculator and then type the results into the spreadsheet but
the results into the spreadsheet but that would be a lot of work we can have
that would be a lot of work we can have the spreadsheet automatically calculated
the spreadsheet automatically calculated by substituting the battleship positions
by substituting the battleship positions of the cells in the spreadsheet
of the cells in the spreadsheet into a mathematical formula and you do
into a mathematical formula and you do that
that by clicking into the cell where you want
by clicking into the cell where you want to work so i'm going to click on e2 and
to work so i'm going to click on e2 and then you start off your cell with an
then you start off your cell with an equal sign if you just type equal you
equal sign if you just type equal you can then write a mathematical equation
can then write a mathematical equation and the spreadsheet will magically do it
and the spreadsheet will magically do it for you you can see here that google's
for you you can see here that google's even suggesting one for me d2 minus c2
even suggesting one for me d2 minus c2 right it read my mind guys the ai is
right it read my mind guys the ai is here
here and like that is exactly what we want to
and like that is exactly what we want to do we want to take the cell
do we want to take the cell d2 which is steph curry's 30 million
d2 which is steph curry's 30 million dollar salary and we want to subtract
dollar salary and we want to subtract from it cell c2 which is steph curry's
from it cell c2 which is steph curry's current salary so you just do equal d 2
current salary so you just do equal d 2 minus c
minus c 2 right
2 right hit enter
hit enter boom
boom the result steph cam steph curry is
the result steph cam steph curry is getting his pay cut by 13 million bucks
getting his pay cut by 13 million bucks maybe he did it voluntarily he wants to
maybe he did it voluntarily he wants to play with these guys you know
play with these guys you know um
um and you can see that that's
and you can see that that's automatically been done and then whoa
automatically been done and then whoa check out this like pop-up on my screen
check out this like pop-up on my screen autofill
autofill right google sheets is already saying
right google sheets is already saying well you've done this uh formula for the
well you've done this uh formula for the first cell you want to roll it down
first cell you want to roll it down across the subsequent cells and i'm like
across the subsequent cells and i'm like oh hell yes and so i click that little
oh hell yes and so i click that little plus button there to auto fill and boom
plus button there to auto fill and boom you can see that the change in salaries
you can see that the change in salaries has been automatically calculated for
has been automatically calculated for each row in the spreadsheet here it did
each row in the spreadsheet here it did it for five but if we had everybody in
it for five but if we had everybody in the mba it would do it for ever for
the mba it would do it for ever for every row in the data set all the way to
every row in the data set all the way to the bottom right so
the bottom right so and you can see if i click into that
and you can see if i click into that second cell for lebron instead of d2 to
second cell for lebron instead of d2 to c2 it's now d3 to c3 if i click on the
c2 it's now d3 to c3 if i click on the next one it's d4 to c4 and you can see
next one it's d4 to c4 and you can see that the spreadsheet has just sort of
that the spreadsheet has just sort of upped the number right for each
upped the number right for each subsequent row as it applied the formula
subsequent row as it applied the formula down anybody have problems with that
no okay so then we're going to make another column we're going to get
another column we're going to get statistical i got to stretch for this
statistical i got to stretch for this we're going to do percent change
we're going to do percent change right so in f1 i'm going to type percent
right so in f1 i'm going to type percent change i'm going to bold it and so we
change i'm going to bold it and so we want to know on a percentage basis
want to know on a percentage basis what's the change for each person right
what's the change for each person right so who here is such a mathematical
so who here is such a mathematical wizard that they can share with the
wizard that they can share with the group
group the formula for percent change somebody
the formula for percent change somebody here knows it
math math math i think you got the wrong crowd i'm not
i think you got the wrong crowd i'm not gonna lie i don't know
all right so maybe not you can be you can get pretty far in
you can be you can get pretty far in journalism with just one or two
journalism with just one or two mathematical tricks but every report i
mathematical tricks but every report i was gonna say
was gonna say you know you should know how to do a
you know you should know how to do a percent change right which is you know
percent change right which is you know like if if there's 10 of something and
like if if there's 10 of something and it goes up to 20 it's doubled or that's
it goes up to 20 it's doubled or that's a hundred percent change right an
a hundred percent change right an increase or decrease and that's a way of
increase or decrease and that's a way of coming up with changes that you can
coming up with changes that you can compare for different groups obviously
compare for different groups obviously there's pitfalls to it really small
there's pitfalls to it really small numbers can have really large percent
numbers can have really large percent changes that can be misleading right
changes that can be misleading right there can be volatility in the data from
there can be volatility in the data from data point to data point that can lead
data point to data point that can lead to noise which is why everybody does
to noise which is why everybody does seven day averages with coven if you've
seven day averages with coven if you've noticed right
noticed right um but it's a really good formula to
um but it's a really good formula to learn and um the way i keep it memorized
learn and um the way i keep it memorized in my head because i'm not a
in my head because i'm not a mathematical
mathematical with either is with this little say
with either is with this little say it's new minus old divided by old right
it's new minus old divided by old right so you take the new value which is what
so you take the new value which is what we're going to pay somebody you subtract
we're going to pay somebody you subtract from it
from it the old value hey we already did that in
the old value hey we already did that in column e right that's new minus old
column e right that's new minus old right there and then you divide that
right there and then you divide that against the old value or the original
against the old value or the original value
value and that will return the percentage
and that will return the percentage change
change and so it's this minus this divided by
and so it's this minus this divided by this
this right is what's going to get us there
right is what's going to get us there and so you can
in the spreadsheet really write formulas of pretty great complexity following the
of pretty great complexity following the same principles that we just did before
same principles that we just did before so i want to do new minus old what do i
so i want to do new minus old what do i type just to do that right here guys
type just to do that right here guys what should i type in somebody
d3 or sorry
or sorry wait yeah d2 it's all right here we got
wait yeah d2 it's all right here we got it
it minus c2 minus c2 and then i want to
minus c2 minus c2 and then i want to divide it against c2 so that's divided
divide it against c2 so that's divided by c2 right all right so that's that's
by c2 right all right so that's that's my first hunch does anybody see what's
my first hunch does anybody see what's wrong with this anybody remember from
wrong with this anybody remember from high school or
high school or elementary school
the order of operations order of operations right so in math it'll do the
operations right so in math it'll do the division before it does the subtraction
division before it does the subtraction right which would screw up the math and
right which would screw up the math and so just like in like pre-algebra class
so just like in like pre-algebra class you can put a little percentage sign
you can put a little percentage sign around the subtraction d2 minus c2
around the subtraction d2 minus c2 inside
inside parentheses which will which will ensure
parentheses which will which will ensure that that runs first right and then the
that that runs first right and then the result of that is then divided into c2
result of that is then divided into c2 and so if i do that and hit enter
and so if i do that and hit enter right you can see that it's negative 0.3
right you can see that it's negative 0.3 well is that a percent what's wrong
you need to format it as a percent right because remember percentages are
right because remember percentages are often just you know fractions between 0
often just you know fractions between 0 and 1 or 1 and negative 1.
and 1 or 1 and negative 1. and when we read them we often take them
and when we read them we often take them times a hundred and like round them off
times a hundred and like round them off right
right and so you could you could put times 100
and so you could you could put times 100 in your formula and that would work and
in your formula and that would work and there'd be nothing wrong with it right
there'd be nothing wrong with it right but like another trick is just to run it
but like another trick is just to run it this way and then hit this little
this way and then hit this little percentage formatting button right there
percentage formatting button right there right and boom that'll do the math for
right and boom that'll do the math for you and that tells us steph curry's
you and that tells us steph curry's taking a 30
taking a 30 pay cut
pay cut right
right okay so there was that auto fill trick
okay so there was that auto fill trick before which is great but there's
before which is great but there's another way to roll down your formulas i
another way to roll down your formulas i want to make sure everybody knows
want to make sure everybody knows clicking here on f2 you see the blue box
clicking here on f2 you see the blue box around it and then in the lower right
around it and then in the lower right hand corner there's kind of this like
hand corner there's kind of this like little fatty box there
little fatty box there and that also doesn't have a name as far
and that also doesn't have a name as far as i know i think some people call it
as i know i think some people call it the magic corner
the magic corner or whatever but it's really just kind of
or whatever but it's really just kind of this little handlebar and you'll notice
this little handlebar and you'll notice when you hover over it the cursor
when you hover over it the cursor changes into this crosshairs again i
changes into this crosshairs again i don't know why a crosshairs just nerds
don't know why a crosshairs just nerds did it that way but what you're going to
did it that way but what you're going to do is you're going to hover till you get
do is you're going to hover till you get that crosshairs you're going to click
that crosshairs you're going to click and hold it down and then if you drag
and hold it down and then if you drag that
that down to f6 you can see that it applies
down to f6 you can see that it applies the formula across all those cells
the formula across all those cells that's another way to do the autofill
that's another way to do the autofill is to just grab that corner and drag it
is to just grab that corner and drag it down another way is just to double click
down another way is just to double click it
it and boom it'll fill all the way to the
and boom it'll fill all the way to the bottom for you
bottom for you and so we could see that we gave some
and so we could see that we gave some pretty big raises at the bottom of our
pretty big raises at the bottom of our sheet and a little at the top we took
sheet and a little at the top we took from the one percent guys
from the one percent guys we're we're the robin hood owners here
we're we're the robin hood owners here okay
okay that work for everybody any questions
there's actually a really bad joke about new minus old divided by old which is uh
new minus old divided by old which is uh you know it's there's like it's got it's
you know it's there's like it's got it's even like structured like a joke hey did
even like structured like a joke hey did anybody here get into journalism to make
anybody here get into journalism to make a lot of money
a lot of money no
no get it
i don't know if i like that but that is a joke that people tell
but that is a joke that people tell it is a joke that people tell that will
it is a joke that people tell that will be my halloween joke next year
be my halloween joke next year yeah
um okay so we've we've
okay so we've we've computed some new columns or calculated
computed some new columns or calculated some new columns by tacking them on and
some new columns by tacking them on and writing formulas right and so that's
writing formulas right and so that's called computing or calculating but like
called computing or calculating but like another common mathematical thing is to
another common mathematical thing is to aggregate it right is to add it all up i
aggregate it right is to add it all up i don't want to annotate a new column or a
don't want to annotate a new column or a new field on each row i want to look
new field on each row i want to look across all the fields and come up with
across all the fields and come up with some high level statistics right like
some high level statistics right like what is the total amount of money that
what is the total amount of money that we're spending as a team
we're spending as a team right what's the average salary on the
right what's the average salary on the team or the median right and those
team or the median right and those aggregated statistics can also be
aggregated statistics can also be calculated really easily
calculated really easily in a spreadsheet tool
in a spreadsheet tool using different formulas using functions
using different formulas using functions that are pre-written to do aggregations
that are pre-written to do aggregations that work a little differently okay and
that work a little differently okay and to do that we're going to go down um
to do that we're going to go down um beneath our data set here we want to
beneath our data set here we want to kind of keep this as its own thing we
kind of keep this as its own thing we don't want what we're about to do to get
don't want what we're about to do to get kind of confused with it as we work it
kind of confused with it as we work it later but um
later but um so we're going to go down below we're
so we're going to go down below we're going to create a little island a little
going to create a little island a little separate data set down below so just
separate data set down below so just click like three or four rows
click like three or four rows down below the total maybe click on like
down below the total maybe click on like c9
c9 and the question that i want to answer
and the question that i want to answer here
here is what was the total amount of money
is what was the total amount of money that all these players last year
that all these players last year right and the mathematical term for
right and the mathematical term for totaling something is summing it right
totaling something is summing it right the sum total and so if you hit equal
the sum total and so if you hit equal again and then just type the word sum
again and then just type the word sum see this like magic pull down whether
see this like magic pull down whether you know it or not available to you
you know it or not available to you within your spreadsheet is literally
within your spreadsheet is literally hundreds of magic functions that can
hundreds of magic functions that can take in cells or lists of cells and
take in cells or lists of cells and return some computed or aggregated
return some computed or aggregated result right and that includes all the
result right and that includes all the mathematical operations that the
mathematical operations that the calculator can do right like the sum and
calculator can do right like the sum and so by typing equal sum and then a
so by typing equal sum and then a parenthesis it's expecting you after the
parenthesis it's expecting you after the parenthesis to tell it what you want it
parenthesis to tell it what you want it to sum and i wanted to sum these five
to sum and i wanted to sum these five rows right in column c and so i'm gonna
rows right in column c and so i'm gonna while that little open parenthesis is
while that little open parenthesis is hanging there i'm just gonna go click
hanging there i'm just gonna go click and drag down across those five rows and
and drag down across those five rows and you can see it now says c2 colon c6 so
you can see it now says c2 colon c6 so that just means the series of rows that
that just means the series of rows that starts here and ends there right that
starts here and ends there right that list of them is going to be fed into the
list of them is going to be fed into the sum function
sum function i then close the parenthesis and we have
i then close the parenthesis and we have a complete function
a complete function and then hit enter
and then hit enter boom the sum total right and we can see
boom the sum total right and we can see that the current salaries of all of our
that the current salaries of all of our players together
players together is 95 million dollars and just here on
is 95 million dollars and just here on the left i'm going to type sum
the left i'm going to type sum just so that's kind of labeled right
just so that's kind of labeled right now
now if i want to
if i want to do that same thing for our new salary
do that same thing for our new salary what's our payroll this year on our
what's our payroll this year on our dream team
dream team using only tricks we've learned this far
using only tricks we've learned this far can anyone guess how i could with just a
can anyone guess how i could with just a flick of the wrist quickly calculate
flick of the wrist quickly calculate everything the same thing for column d
the magic corner the magic corner so if i click on c2
i click on c2 i go to the magic corner hold and drag
i go to the magic corner hold and drag to the right
to the right boom we can see when i click on it that
boom we can see when i click on it that c2 to c6 is now d2 to d6 right it's just
c2 to c6 is now d2 to d6 right it's just kind of magically carried over to the
kind of magically carried over to the next column and we can see that our
next column and we can see that our payroll has more than doubled
payroll has more than doubled right
right and you know i might see this as like
and you know i might see this as like the head of basketball operations on our
the head of basketball operations on our dream team and say we really gotta bring
dream team and say we really gotta bring down how much jason tatum's making like
down how much jason tatum's making like he's good but i'm gonna cut that in half
he's good but i'm gonna cut that in half and so if i just go up above and i
and so if i just go up above and i instead put in 50 million cut his pay in
instead put in 50 million cut his pay in half see how everything instantly
half see how everything instantly updated right so our sum total below
updated right so our sum total below went down 50 million and his his change
went down 50 million and his his change values instantly updated right if i put
values instantly updated right if i put one dollar in there you would see that
one dollar in there you would see that they changed to reflect that if i undo
they changed to reflect that if i undo and go back to where i was before it
and go back to where i was before it changes and this is part of the magic of
changes and this is part of the magic of the spreadsheet right is that once the
the spreadsheet right is that once the formula is in place if the numbers
formula is in place if the numbers change everything automatically updates
okay so sums one good statistic another one is the average right which is just
one is the average right which is just what's the sort of take all of them
what's the sort of take all of them together and divide it by the number of
together and divide it by the number of members
members what do we get so the average can be
what do we get so the average can be calculated by an equal
calculated by an equal uh mean
uh mean no average sometimes it's called the
no average sometimes it's called the mean sometimes it's called the average
mean sometimes it's called the average i often struggle is it average or is it
i often struggle is it average or is it mean guys
mean guys i think it's is it mean so let's just
i think it's is it mean so let's just try it
try it no so like the way i could do this like
no so like the way i could do this like let's say you're ben and you can't
let's say you're ben and you can't remember you just go to your buddy
remember you just go to your buddy google and you do like google
google and you do like google sheets
sheets average formula yeah what is that again
average formula yeah what is that again here's google has a website and they
here's google has a website and they have a page for every one of these
have a page for every one of these formulas that like tells you what the
formulas that like tells you what the name of it is defines it tell you how to
name of it is defines it tell you how to do it you can see here on the right
do it you can see here on the right there's literally hundreds of these like
there's literally hundreds of these like crazy math functions
crazy math functions and you can see that no band in this
and you can see that no band in this case it's not called the mean it's
case it's not called the mean it's called the average so i do equal average
called the average so i do equal average open parenthesis drag my c cells
open parenthesis drag my c cells hit enter
hit enter boom
boom right we can see that
right we can see that the average salary
the average salary is 19 000.
is 19 000. i then can just grab the corner drag to
i then can just grab the corner drag to the right we can see that's gone up to
the right we can see that's gone up to 35 million right
35 million right that's because we really raised up
that's because we really raised up people on the low end right because as
people on the low end right because as we know in averages things that are
we know in averages things that are really high or really low can kind of
really high or really low can kind of skew the numbers right so when it comes
skew the numbers right so when it comes to something like home sales or income
to something like home sales or income as is very well publicized there is a
as is very well publicized there is a small number of people that have really
small number of people that have really expensive homes and make lots and lots
expensive homes and make lots and lots of money and so they can kind of skew
of money and so they can kind of skew the results of an average in case it's
the results of an average in case it's not in a way that's saying wrong but
not in a way that's saying wrong but they can be a little misleading about
they can be a little misleading about what the typical sort of situation is
what the typical sort of situation is right does anybody know what the
right does anybody know what the statistical solution
statistical solution or alternative to an average is in a
or alternative to an average is in a case where you have skewed data
so the standard deviation that's part of it but that's not the
that's part of it but that's not the number that's not exactly it
number that's not exactly it the standard deviation is a little more
the standard deviation is a little more sophisticated
looking at the median the median who said that
too i don't know if i i don't know i feel like claire and i said at the same
feel like claire and i said at the same time
time okay i didn't say anything no you got it
okay i didn't say anything no you got it you didn't say it
you didn't say it no do you want what's the median explain
no do you want what's the median explain it the median is the one that's in the
it the median is the one that's in the dead middle and so it's not affected by
dead middle and so it's not affected by you know the really high
you know the really high the highest number or the lowest number
the highest number or the lowest number it's just kind of like what's right in
it's just kind of like what's right in the middle of the set of numbers yeah
the middle of the set of numbers yeah you just sort everybody from highest to
you just sort everybody from highest to lowest like say there's 100 people you
lowest like say there's 100 people you sort them and after you sort them by the
sort them and after you sort them by the value whoever is the 50th one is the
value whoever is the 50th one is the media right it's also you know it's also
media right it's also you know it's also the 50th percentile is like another way
the 50th percentile is like another way of thinking about it if you're familiar
of thinking about it if you're familiar with percentiles
with percentiles and you know you'll notice this in the
and you know you'll notice this in the news when people talk about home sales
news when people talk about home sales this is really common right it's always
this is really common right it's always the median sales price of homes in the
the median sales price of homes in the news right and that's the reason is
news right and that's the reason is because there's really a small number of
because there's really a small number of high ones that skew the number and there
high ones that skew the number and there could be a difference and so really
could be a difference and so really whenever you're doing a story if you
whenever you're doing a story if you calculate an average it's kind of good
calculate an average it's kind of good practice to look at the median just to
practice to look at the median just to kind of see if there's much difference
kind of see if there's much difference between the two the average while
between the two the average while statistically not as good in some ways
statistically not as good in some ways is is sometimes better to use because
is is sometimes better to use because it's just more commonplace you know when
it's just more commonplace you know when you're writing a news story people know
you're writing a news story people know what that is it's easy to get across
what that is it's easy to get across right
right and so um
and so um i'll sometimes use it if they're really
i'll sometimes use it if they're really close just because it's easier but the
close just because it's easier but the key thing to really contemplate is is my
key thing to really contemplate is is my data set skewed you know is there a few
data set skewed you know is there a few records that are really far out
records that are really far out different in the case of brittany griner
different in the case of brittany griner i think we kind of have a little bit of
i think we kind of have a little bit of skewing there right
skewing there right um so to calculate the median i'm going
um so to calculate the median i'm going to make another row to put median and
to make another row to put median and like you literally just do equal median
like you literally just do equal median right it's that easy
right it's that easy open parenthesis
open parenthesis drag
drag close parenthesis
close parenthesis boom and we can see there is quite a
boom and we can see there is quite a difference there right
difference there right uh now in the case of the median it's
uh now in the case of the median it's jason tatum who is the medium value
jason tatum who is the medium value right of the five he ranks in the middle
right of the five he ranks in the middle so he becomes the median if i drag it to
so he becomes the median if i drag it to the right and look at the median our
the right and look at the median our median becomes 30 million steph curry
any questions about the average the median
there's many other things minimum maximum
maximum standard deviation is one which helps
standard deviation is one which helps you understand the distribution
you understand the distribution the curve
okay then we're going to cover we're going to move on to our next basic
we're going to move on to our next basic skill which is sorting and filtering
skill which is sorting and filtering right so let's say you have a data set
right so let's say you have a data set but you want to resort it to say what
but you want to resort it to say what comes out on top what comes out on the
comes out on top what comes out on the bottom you want to focus in just on one
bottom you want to focus in just on one subset of the records this is where
subset of the records this is where sorting and filtering comes in handy and
sorting and filtering comes in handy and you can do that really easily in any
you can do that really easily in any spreadsheet tool by putting sort of a
spreadsheet tool by putting sort of a magic filter at the top
magic filter at the top you can do so what we want to do is want
you can do so what we want to do is want to select all of our data so i just
to select all of our data so i just clicked on f6 and i dragged and i
clicked on f6 and i dragged and i selected all the data that's outside of
selected all the data that's outside of my little island my statistical island
my little island my statistical island at the bottom there
at the bottom there and then
and then [Music]
[Music] i'm going to click on the data pull down
i'm going to click on the data pull down menu at the top and you see that there's
menu at the top and you see that there's this option called create a filter
this option called create a filter i want everybody to click on that
i want everybody to click on that and you'll see that not a lot of changes
and you'll see that not a lot of changes but there's these kind of magic green
but there's these kind of magic green arrows on each of our header rows right
arrows on each of our header rows right and excel has something just like this
and excel has something just like this it's just in a slightly different place
it's just in a slightly different place and these magic arrows now have like
and these magic arrows now have like options for us that allow us to
options for us that allow us to manipulate
manipulate our data set right so if i wanted to
our data set right so if i wanted to sort people by their current salary from
sort people by their current salary from highest to lowest so maybe i could see
highest to lowest so maybe i could see the median
the median i click the little green guy
i click the little green guy and you can see that there's two sorting
and you can see that there's two sorting options a to z sorting in ascending
options a to z sorting in ascending order or z to a sorting in descending
order or z to a sorting in descending order and so since i want the top salary
order and so since i want the top salary at the top
at the top i'm going to sort z to a
i'm going to sort z to a and we can see that that shifted things
and we can see that that shifted things with jason tatum at the bottom i think
with jason tatum at the bottom i think it was like that to start with i'm going
it was like that to start with i'm going to do it on d and do z to a and you can
to do it on d and do z to a and you can see that they resort right we now have
see that they resort right we now have tatum and grinder at the top and matisse
tatum and grinder at the top and matisse thigh bowl at the bottom if we wanted to
thigh bowl at the bottom if we wanted to sort by percentage change who had the
sort by percentage change who had the biggest percentage change i could do a
biggest percentage change i could do a sort there as well
and if you're looking at a column like name it'll just sort alphabetically
name it'll just sort alphabetically right as opposed to sorting numerically
right as opposed to sorting numerically and that's where the data type comes in
and that's where the data type comes in because depending on whether your column
because depending on whether your column is words or numbers or dates how the
is words or numbers or dates how the computer sorts it differs right and so
computer sorts it differs right and so that's why it's important to kind of get
that's why it's important to kind of get that right
that right the other thing is a filter let's say we
the other thing is a filter let's say we wanted to look just at our guards for
wanted to look just at our guards for instance i click on the position column
instance i click on the position column you can see that it gives you the three
you can see that it gives you the three unique values there's center forwards
unique values there's center forwards and guards if i just uncheck
and guards if i just uncheck center and forward see i just clicked on
center and forward see i just clicked on there to uncheck it and hit ok
there to uncheck it and hit ok boom the data set is filtered down to
boom the data set is filtered down to just the guards and you can see that
just the guards and you can see that there's now a little funnel icon
there's now a little funnel icon to indicate we've done a filter
to indicate we've done a filter if i click again and i switch it to
if i click again and i switch it to forward
forward it's filtered to our one or two forwards
it's filtered to our one or two forwards right if i click it again and i click on
right if i click it again and i click on select all
select all we have everybody
we have everybody that was a character filter based on a
that was a character filter based on a categorical value but you can also do
categorical value but you can also do filters that are numerical let's say we
filters that are numerical let's say we want to see everyone who makes more than
want to see everyone who makes more than 10 million dollars right you can do
10 million dollars right you can do what's called filtering by condition
what's called filtering by condition and then you can say greater than less
and then you can say greater than less than equal to et cetera and because this
than equal to et cetera and because this is a numerical field you can do your
is a numerical field you can do your filters with a numerical expression so
filters with a numerical expression so if i say i want to see everyone who made
if i say i want to see everyone who made more than 25 million
more than 25 million i just would select greater than the
i just would select greater than the condition i'd type in to great 25
condition i'd type in to great 25 million
million and you can see that there's only two
and you can see that there's only two players who then qualify right and this
players who then qualify right and this these sorting and filtering can be a
these sorting and filtering can be a good way to ask and answer some basic
good way to ask and answer some basic questions that you have about your data
questions that you have about your data set as you interview it as you try to
set as you interview it as you try to find a story in it right
okay any questions about sorting and
any questions about sorting and filtering
okay so i'm going to do one pop quiz question so if i wanted to get the grand
question so if i wanted to get the grand total of money spent across both years
total of money spent across both years of all salaries
of all salaries how would i do that what would i do in
how would i do that what would i do in the spreadsheet somebody pipe up and
the spreadsheet somebody pipe up and tell me what to type
i think you would do equal sum and then take your
take your magic little dragger and go across
magic little dragger and go across current salary and new salary
current salary and new salary just like that right selecting both
just like that right selecting both that's definitely a way to do it
that's definitely a way to do it we get 270 million another way would be
we get 270 million another way would be to sum the computed columns i could just
to sum the computed columns i could just click on the two totals for each year
click on the two totals for each year there
there and do that and that would work as well
and do that and that would work as well it comes out the same because you can do
it comes out the same because you can do formulas of formulas right and it all
formulas of formulas right and it all adds up together
adds up together i might even say this is current this is
i might even say this is current this is new and this is total okay if i wanted
new and this is total okay if i wanted to get the average salary across both
to get the average salary across both years how would i do that
it would just be equals average and then you would do
you would do parentheses current salary and then
parentheses current salary and then through the new salary right
through the new salary right yep equal average and drag them across
yep equal average and drag them across there so it's the same thing
there so it's the same thing that claire did for some it's just we
that claire did for some it's just we typed average instead of sum as our
typed average instead of sum as our formula right because it's the same
formula right because it's the same range of cells it's called a range the
range of cells it's called a range the sort of list of cells but it's just
sort of list of cells but it's just going into a different function
going into a different function right and i could do the same thing with
right and i could do the same thing with median as well
and if i wanted to do the change in my salary from year to year i could just
salary from year to year i could just simply do equals
simply do equals this
this minus this right and we've calculated
minus this right and we've calculated that i can autofill and we can see the
that i can autofill and we can see the change in each of those values from year
change in each of those values from year to year as a grand total too
to year as a grand total too and because we've kept this separately
and because we've kept this separately as an island the sort and filter doesn't
as an island the sort and filter doesn't really affect this that's why it's nice
really affect this that's why it's nice to leave that little gap in there
that's covered sorting and filtering i have one more basic skill thing we're
have one more basic skill thing we're going to learn which is sort of the
going to learn which is sort of the crowning skill of spreadsheets before we
crowning skill of spreadsheets before we get into our real data set but before i
get into our real data set but before i do that i just want to give a second in
do that i just want to give a second in case there's any questions
okay great so the next thing we're going to cover is how to group and aggregate
to cover is how to group and aggregate for groups within the data set right we
for groups within the data set right we were able to compute a value for each
were able to compute a value for each row we were able to aggregate values
row we were able to aggregate values across the entire data set but there's
across the entire data set but there's another thing that's a really common
another thing that's a really common kind of deal
kind of deal which is to look at to do aggregate
which is to look at to do aggregate values for groups of rows as opposed for
values for groups of rows as opposed for all the rows so for instance if you
all the rows so for instance if you wanted to ask the question what's the
wanted to ask the question what's the total amount of money we're paying our
total amount of money we're paying our guards versus our forwards right or if
guards versus our forwards right or if we added a column that was gender let's
we added a column that was gender let's do it let's add a column this gender
do it let's add a column this gender right oh see how i did that i clicked on
right oh see how i did that i clicked on b
b i right clicked it says insert one
i right clicked it says insert one column left boom i get a new column so
column left boom i get a new column so if i do gender and i just do you know f
if i do gender and i just do you know f m
m m right for the two the genders in this
m right for the two the genders in this case or the sexes i should say um
case or the sexes i should say um let's re-label that right
let's re-label that right because i think brittany griner is
because i think brittany griner is non-binary is that correct i can't be
non-binary is that correct i can't be corrected on this book yeah i think they
corrected on this book yeah i think they are
are yeah and um
yeah and um and so you know forgive the vulgarity of
and so you know forgive the vulgarity of this classification just to make the
this classification just to make the point
point um
um uh
uh you know by putting in these these
you know by putting in these these categorical values we've created uh
categorical values we've created uh records that can effectively be grouped
records that can effectively be grouped together for analysis and so you can use
together for analysis and so you can use this position or this sex column to look
this position or this sex column to look at what's the average salary for men
at what's the average salary for men versus women right to maybe do a gender
versus women right to maybe do a gender pay gap analysis or to look at the
pay gap analysis or to look at the different positions to see how the team
different positions to see how the team is investing in one thing versus another
is investing in one thing versus another or look at it by age or other kind of
or look at it by age or other kind of common attributes eye color if you
common attributes eye color if you wanted to right and to do those kind of
wanted to right and to do those kind of group end counts or group and sums which
group end counts or group and sums which really are pretty common
really are pretty common there's a technique that every
there's a technique that every spreadsheet can do and for some weird
spreadsheet can do and for some weird nerdy reason is almost always called a
nerdy reason is almost always called a pivot table have you guys heard this
pivot table have you guys heard this before
so a pivot table is just a really dumb piece of jargon from like the 1980s like
piece of jargon from like the 1980s like clippy the paperclip or whatever which
clippy the paperclip or whatever which really is just a sort of brand name in
really is just a sort of brand name in the spreadsheet software for grouping
the spreadsheet software for grouping and counting or grouping and summing or
and counting or grouping and summing or grouping and averaging using one of your
grouping and averaging using one of your columns and this is a really powerful
columns and this is a really powerful tool that can you know win you a
tool that can you know win you a pulitzer prize or whatever and
pulitzer prize or whatever and oftentimes result in good stories
oftentimes result in good stories because with just a couple clicks of the
because with just a couple clicks of the mouse right and so
mouse right and so let's do it let's make a pivot table so
let's do it let's make a pivot table so we want to select the data that we want
we want to select the data that we want to group and count so again i'm just
to group and count so again i'm just going to drag my mouse over kind of
going to drag my mouse over kind of everything from a1 to g6 right
and so that now is selected in blue kind of my data set and the and i want to ask
of my data set and the and i want to ask questions that have to do with position
questions that have to do with position and have to do with sex right and so i
and have to do with sex right and so i selected the data that i'd like to pivot
selected the data that i'd like to pivot and then again this button will be in a
and then again this button will be in a different place in depending on which
different place in depending on which program you're using but it's almost
program you're using but it's almost always called a pivot table
always called a pivot table and in google sheets you click on insert
and in google sheets you click on insert and then you see that right there pivot
and then you see that right there pivot table it can also put in charts that's a
table it can also put in charts that's a whole other world we won't get to
whole other world we won't get to but
but insert pivot table if you do that it's
insert pivot table if you do that it's going to say well here what data do you
going to say well here what data do you want me to use and this is the data
want me to use and this is the data range a1 to g6 that's good that's what i
range a1 to g6 that's good that's what i want
want and then it's like okay where do you
and then it's like okay where do you want to put the pivot table and the
want to put the pivot table and the right answer almost always is new sheet
right answer almost always is new sheet which is good so just assuming you see
which is good so just assuming you see something like that just hit create
something like that just hit create and that's going to take you to a new
and that's going to take you to a new spreadsheet
spreadsheet and you might be like holy cow where'd
and you might be like holy cow where'd my data go right and this is where it's
my data go right and this is where it's important to kind of stop and make sure
important to kind of stop and make sure you get one of the basic user interface
you get one of the basic user interface things about a spreadsheet that i
things about a spreadsheet that i haven't covered which is that your
haven't covered which is that your spreadsheet is a workbook that can have
spreadsheet is a workbook that can have multiple spreadsheets within it you can
multiple spreadsheets within it you can have kind of a bundle of spreadsheets in
have kind of a bundle of spreadsheets in your book so to speak and those are
your book so to speak and those are controlled in the lower left hand corner
controlled in the lower left hand corner do you see these little tabs down here
do you see these little tabs down here so sheet1 if i click there takes me back
so sheet1 if i click there takes me back to where i started i didn't lose my data
to where i started i didn't lose my data but then the pivot table was inserted
but then the pivot table was inserted into sheet two what's called pivot table
into sheet two what's called pivot table one by default right
one by default right which is
which is um where the new calculated values are
um where the new calculated values are going to be placed you can even name
going to be placed you can even name your tabs so if i click on the arrow on
your tabs so if i click on the arrow on sheet one and hit rename i can type
sheet one and hit rename i can type roster right because that's like my
roster right because that's like my roster of players
roster of players but we're going to go back to the pivot
but we're going to go back to the pivot table now we're going to use this to um
table now we're going to use this to um to group and count by position right
to group and count by position right and so you can see here on the left is
and so you can see here on the left is sort of a blank canvas on which we're
sort of a blank canvas on which we're going to group and count our data and
going to group and count our data and then on the right are some like little
then on the right are some like little knobs and like goofy things which is how
knobs and like goofy things which is how you manipulate the pivot table right you
you manipulate the pivot table right you can see it as rows columns and values
can see it as rows columns and values are the key ones so the row is really
are the key ones so the row is really the thing you want to group by right so
the thing you want to group by right so like which of the columns do you want to
like which of the columns do you want to roll up into like subtotals and for us
roll up into like subtotals and for us that's position and you can see here on
that's position and you can see here on the right hand side all of our columns
the right hand side all of our columns are like available so you just click on
are like available so you just click on position and you drag it into the rows
position and you drag it into the rows area of the pivot table and boom you see
area of the pivot table and boom you see automatically there on the left it's
automatically there on the left it's created a list of just the unique values
created a list of just the unique values by position
by position right center forward and guard and now i
right center forward and guard and now i want to calculate values for each of
want to calculate values for each of those so i want to get the total amount
those so i want to get the total amount of salary we're now spending so i'm
of salary we're now spending so i'm going to grab our new salary column
going to grab our new salary column i'm going to drag that to the values
i'm going to drag that to the values area i'm going to drop it and you can
area i'm going to drop it and you can see that here it says summarize by so
see that here it says summarize by so that's the that's the function or
that's the that's the function or formula to run on that column and if i
formula to run on that column and if i click on it you can see the sum and
click on it you can see the sum and count and average and max and min and
count and average and max and min and median and standard deviation and you
median and standard deviation and you know they can you could pick but some is
know they can you could pick but some is what we wanted to start with so i'm good
what we wanted to start with so i'm good with that we can see that we're spending
with that we can see that we're spending 50 million on center 70 on forward 55 of
50 million on center 70 on forward 55 of card so if i want to get the average
card so if i want to get the average it's as easy as taking new salary again
it's as easy as taking new salary again dragging and dropping it into values and
dragging and dropping it into values and you can see wow another column magically
you can see wow another column magically appears
appears i can select average
i can select average boom that's now got the averages
boom that's now got the averages automatically calculated right and i can
automatically calculated right and i can do this for counts too oftentimes the
do this for counts too oftentimes the most common way to use this is just to
most common way to use this is just to count how many people
count how many people are in each category that you're
are in each category that you're interested in right and so if i just
interested in right and so if i just take
take current salary
current salary drop it a third or you really can do it
drop it a third or you really can do it for any field but let's do new salary a
for any field but let's do new salary a third time
third time if i select count a count a is just like
if i select count a count a is just like the weird way of saying count the number
the weird way of saying count the number of records it's just like a weird
of records it's just like a weird tradition
tradition and if you do that it quickly tells us
and if you do that it quickly tells us there's two forwards two guards and one
there's two forwards two guards and one center right and you guys can see how
center right and you guys can see how this basic pivot table we just did if
this basic pivot table we just did if you were to put in like the city of
you were to put in like the city of chicago's government salaries database
chicago's government salaries database right you could compare how much the
right you could compare how much the average firefighter makes to the average
average firefighter makes to the average police officer you could look at
police officer you could look at different ranks within
different ranks within an agency and see how much the
an agency and see how much the executives get paid versus the lower
executives get paid versus the lower level workers and this these basic
level workers and this these basic tricks can be done to do all kinds of
tricks can be done to do all kinds of stories and analysis and the pivot is
stories and analysis and the pivot is often your way to get there
often your way to get there and there's a lot more to it and a lot
and there's a lot more to it and a lot more can do but that's really the basics
more can do but that's really the basics so we've grouped here by position just
so we've grouped here by position just to kind of show how easy it is i'm going
to kind of show how easy it is i'm going to click the little x there to get rid
to click the little x there to get rid of position and then i'm just going to
of position and then i'm just going to drop the sex column into rows now right
drop the sex column into rows now right and you can see that i've left the
and you can see that i've left the calculated values there and by switching
calculated values there and by switching what was in the rows it's now grouping
what was in the rows it's now grouping by sex as opposed to grouping by
by sex as opposed to grouping by position and the same analysis is just
position and the same analysis is just immediately run for all of them pretty
immediately run for all of them pretty nice right
nice right and a lot of times for data journals and
and a lot of times for data journals and stories the numbers that appear in your
stories the numbers that appear in your pivot table just go right into your
pivot table just go right into your story you know after you vet the data
story you know after you vet the data and do all that other stuff of course
and do all that other stuff of course but this is often where you come up with
but this is often where you come up with what is going to be reported
we'll do a more sophisticated pivot table in a minute but that's really the
table in a minute but that's really the basics of it you just learned
basics of it you just learned any questions about pivot tables
no okay well those are the basic skills that i wanted to cover that's kind of
that i wanted to cover that's kind of part one of this class and so now i
part one of this class and so now i think we're gonna advance to kind of
think we're gonna advance to kind of part two which is instead of creating
part two which is instead of creating sort of a funny data set on our own
sort of a funny data set on our own we're going to look at a serious data
we're going to look at a serious data set something that came from the real
set something that came from the real world and we're going to apply
world and we're going to apply some of those
some of those skills we just learned to ask and answer
skills we just learned to ask and answer questions of this real data set okay and
questions of this real data set okay and so the data set that we're going to use
so the data set that we're going to use comes from a group called the invisible
comes from a group called the invisible institute does anybody here know what
institute does anybody here know what that is
the invisible institute is a chicago-based sort of non-profit group
chicago-based sort of non-profit group that produces investigative journalism
that produces investigative journalism documentaries even an art exhibit that's
documentaries even an art exhibit that's currently on display at the ball is
currently on display at the ball is connected to the invisible institute and
connected to the invisible institute and it's sort of one
it's sort of one um permutation of kind of a shifting
um permutation of kind of a shifting group of chicago people who over the
group of chicago people who over the last couple decades have put a lot of
last couple decades have put a lot of effort
effort into
into exposing abuses by the chicago police
exposing abuses by the chicago police department through public records
department through public records requests lawsuits and other things um
requests lawsuits and other things um they are connected in a complicated way
they are connected in a complicated way to the john burge torture story if
to the john burge torture story if you're familiar with that if you're not
you're familiar with that if you're not google it um and one of their big data
google it um and one of their big data projects that they did a few years ago
projects that they did a few years ago and they were really one of the first
and they were really one of the first groups in the whole country to do this
groups in the whole country to do this is they were they um one through a
is they were they um one through a freedom of information act request not
freedom of information act request not an email not a memo not the sort of
an email not a memo not the sort of typical documents you might associate
typical documents you might associate with foia but a database you know
with foia but a database you know there's a literally a bureaucratic
there's a literally a bureaucratic process in the city of chicago where if
process in the city of chicago where if you believe a police officer has
you believe a police officer has mistreated you you can file a complaint
mistreated you you can file a complaint and that complaint depending on how it
and that complaint depending on how it plays off kicks off a kind of like
plays off kicks off a kind of like um i guess
um i guess adjudication process that results in um
adjudication process that results in um an investigation in some cases and then
an investigation in some cases and then a decision about whether your complaint
a decision about whether your complaint is upheld or not and then whether the
is upheld or not and then whether the officer should be disciplined
officer should be disciplined and that
and that government process generates data right
government process generates data right there is literally like a glorified
there is literally like a glorified spreadsheet or database system inside
spreadsheet or database system inside the city of chicago that's tracking all
the city of chicago that's tracking all those things and those databases are
those things and those databases are things that you can file public or
things that you can file public or records requests for and literally get
records requests for and literally get the raw data to analyze on your own and
the raw data to analyze on your own and so one whole sort of strain of data
so one whole sort of strain of data journalism one type of data story is not
journalism one type of data story is not building your own database but prying
building your own database but prying loose a database from the government and
loose a database from the government and then kind of figuring out how it works
then kind of figuring out how it works and what's really in it and then
and what's really in it and then analyzing it to do stories or in the
analyzing it to do stories or in the case of you know now on the web just
case of you know now on the web just republish the whole darn thing and
republish the whole darn thing and that's what the invisible institute did
that's what the invisible institute did i think in 2016 or so i can't remember
i think in 2016 or so i can't remember exactly that was a pretty controversial
exactly that was a pretty controversial time is they built the website where
time is they built the website where they just published um the complaints
they just published um the complaints against police officers you know one
against police officers you know one argument for this
argument for this is transparency just on its own but also
is transparency just on its own but also that um
that um that uh when people are defending
that uh when people are defending themselves in court after a police
themselves in court after a police officer is involved in testimony or
officer is involved in testimony or other parts of the investigation they
other parts of the investigation they you know the one argument is that they
you know the one argument is that they have a right to be able to you know find
have a right to be able to you know find out if the officer testifying against
out if the officer testifying against them has a track record that might not
them has a track record that might not be great and so um i suspect the defense
be great and so um i suspect the defense attorneys are probably the top user of
attorneys are probably the top user of this website you can see here that it
this website you can see here that it has an analysis of the whole system
has an analysis of the whole system overall how many people in aggregate
overall how many people in aggregate have had allegations against them
have had allegations against them discipline and then they even have a
discipline and then they even have a page for individual officers and this is
page for individual officers and this is part of what makes it controversial i
part of what makes it controversial i suppose you can see here they have a
suppose you can see here they have a readout on individual officers and so
readout on individual officers and so this database
this database fueled a whole lot of stories in chicago
fueled a whole lot of stories in chicago when it first came out
when it first came out many people in local news copied it in
many people in local news copied it in subsequent years and did similar stories
subsequent years and did similar stories there was a whole reform to the chicago
there was a whole reform to the chicago system for managing this to introduce
system for managing this to introduce the civilian review board
the civilian review board and a lot of it really stems back to the
and a lot of it really stems back to the work this group did and one thing they
work this group did and one thing they did that i think is quite admirable is
did that i think is quite admirable is they just publish the whole database in
they just publish the whole database in raw spreadsheet format right so they
raw spreadsheet format right so they made a pretty website they wrote stories
made a pretty website they wrote stories where they analyze the thing but then
where they analyze the thing but then they also just put up the data for other
they also just put up the data for other people to use which means other
people to use which means other journalists can analyze it academics
journalists can analyze it academics criminologists get their hands on it and
criminologists get their hands on it and maybe people who wanted to vet their
maybe people who wanted to vet their work right who might you know ultimately
work right who might you know ultimately try to poke a hole in it and i think
try to poke a hole in it and i think that that is just a really great kind of
that that is just a really great kind of you know scientific practice that is
you know scientific practice that is awesome and so kudos to them for that
awesome and so kudos to them for that and here on their website is this kind
and here on their website is this kind of odd page download the data
of odd page download the data and this it links off to this even
and this it links off to this even stranger nerdy website called github
stranger nerdy website called github where you can get all their computer
where you can get all their computer code but
code but if you were to click into this little
if you were to click into this little link
link you would find like literally a dropbox
you would find like literally a dropbox that has all the data they got and kind
that has all the data they got and kind of cleaned up from the government
of cleaned up from the government including the actual four-year request
including the actual four-year request themselves which is always kind of
themselves which is always kind of interesting to read you can see here
interesting to read you can see here that they literally wrote a letter dear
that they literally wrote a letter dear foia officer
foia officer under the freedom of information act
under the freedom of information act request i i request your database and
request i i request your database and you can see they even included the names
you can see they even included the names of the fields in the database they
of the fields in the database they wanted right i was trying to get rajiv
wanted right i was trying to get rajiv who was involved in this to join us but
who was involved in this to join us but he couldn't tonight but i suspect that
he couldn't tonight but i suspect that they did some reporting ahead of time to
they did some reporting ahead of time to know what was in there so that they
know what was in there so that they could make sure to request it so that it
could make sure to request it so that it didn't get left out in the response
didn't get left out in the response because one issue that could happen is
because one issue that could happen is is you might request the database but do
is you might request the database but do they really give you the whole thing
they really give you the whole thing a lot of time they don't want to right
a lot of time they don't want to right and so being specific in this case was
and so being specific in this case was probably part of their strategy to make
probably part of their strategy to make sure they got it at the end of the day
sure they got it at the end of the day um
um and writing that type of foia is a whole
and writing that type of foia is a whole sort of art that we can talk about if
sort of art that we can talk about if you want to
you want to um and but these files are really big
um and but these files are really big because they have hundreds of thousands
because they have hundreds of thousands of records and i don't want to like
of records and i don't want to like strain everybody's computer here in
strain everybody's computer here in class
class and they might require some heavier duty
and they might require some heavier duty programming tools to do like a super
programming tools to do like a super duper analysis so what i've done is i
duper analysis so what i've done is i talked with rajiv who's behind it i
talked with rajiv who's behind it i created like a little
created like a little extract
extract i went into the data i merged a couple
i went into the data i merged a couple tables into a single table and then i
tables into a single table and then i filtered it down to just one year worth
filtered it down to just one year worth of complaints this is from the year 2014
of complaints this is from the year 2014 and so what we have here is sort of a
and so what we have here is sort of a trimmed down simplified version of the
trimmed down simplified version of the data set that has i've removed a bunch
data set that has i've removed a bunch of columns and i've removed a bunch of
of columns and i've removed a bunch of rows to kind of get to something that is
rows to kind of get to something that is derived from the original original
derived from the original original analysis but is all real data
analysis but is all real data this process of like cleaning the data
this process of like cleaning the data of merging different tables filtering it
of merging different tables filtering it down getting rid of stuff you don't want
down getting rid of stuff you don't want is often like 80 90 of this type of data
is often like 80 90 of this type of data story because the stuff you get from the
story because the stuff you get from the government is often like cryptic or
government is often like cryptic or incomplete or like gnarly and like
incomplete or like gnarly and like understanding what's there just so you
understanding what's there just so you can trim it down to something nice and
can trim it down to something nice and clean that you can analyze is a whole
clean that you can analyze is a whole reporting effort and often requires
reporting effort and often requires another set of programming skills we're
another set of programming skills we're not going to cover that here in class
not going to cover that here in class okay sorry so what we're going to do is
okay sorry so what we're going to do is i'm going to share a link to the
i'm going to share a link to the spreadsheet
spreadsheet in the chat and i want everybody to open
in the chat and i want everybody to open it in google sheets and i'm going to
it in google sheets and i'm going to show you how to make your own copy of it
show you how to make your own copy of it really quickly okay
really quickly okay so in google sheets if you haven't done
so in google sheets if you haven't done it this is part of what makes it great
it this is part of what makes it great is you can click the share button in the
is you can click the share button in the upper right you can add people to
upper right you can add people to privately share it which is often useful
privately share it which is often useful but you also here can just get a link
but you also here can just get a link that you can give to the whole world or
that you can give to the whole world or your co-workers and this is how a lot of
your co-workers and this is how a lot of data journalists share data within their
data journalists share data within their newsroom it's how we make charts at the
newsroom it's how we make charts at the la times is people make a google sheet
la times is people make a google sheet and we plug it into the chart tool right
and we plug it into the chart tool right and the way you give you sort of or plug
and the way you give you sort of or plug it in somewhere give it to someone else
it in somewhere give it to someone else is you click that share button and then
is you click that share button and then here there's this get link section
here there's this get link section i'm going to click you know it's
i'm going to click you know it's restricted right now it starts as
restricted right now it starts as private i'm going to open it up so i
private i'm going to open it up so i click there and i'm going to say anybody
click there and i'm going to say anybody who has the link
who has the link can actually not just view but edit the
can actually not just view but edit the spreadsheet that's a little dangerous
spreadsheet that's a little dangerous you might not want to do that for stuff
you might not want to do that for stuff for your story but for this class this
for your story but for this class this is now like an open door to come into
is now like an open door to come into this spreadsheet and do whatever you
this spreadsheet and do whatever you want with it right
want with it right so i'm going to drop that into the chat
so i'm going to drop that into the chat here in class let me just find the chat
here in class let me just find the chat real quick
okay boom you guys see that link i want you
boom you guys see that link i want you to jump in
to jump in we should start seeing like people's
we should start seeing like people's little like google icons coming there
little like google icons coming there they are
they are we got a buffalo and a kiwi a mink a bat
we got a buffalo and a kiwi a mink a bat an elephant
an elephant i'm just gonna give folks a second to
i'm just gonna give folks a second to jump in
jump in [Music]
okay now i want you to duplicate it and make your own copy so if you go to the
make your own copy so if you go to the file menu you see this make a copy
file menu you see this make a copy option fourth one down once you just
option fourth one down once you just click that and that should like open up
click that and that should like open up a new tab on your computer
a new tab on your computer where you have to give it a name so i'm
where you have to give it a name so i'm gonna call mine copy
gonna call mine copy i'm gonna say make a copy and you can
i'm gonna say make a copy and you can see it's just gonna open a new tab
see it's just gonna open a new tab and make another copy of the data
and make another copy of the data excuse me
excuse me and now i'm going to go back to my
and now i'm going to go back to my original one because i don't need the
original one because i don't need the copy but i want everybody to do that so
copy but i want everybody to do that so just do file
just do file make a copy
okay so now we have our data set
so now we have our data set there's a couple like good practices
there's a couple like good practices anytime you get like a data set from a
anytime you get like a data set from a government or a source to help you just
government or a source to help you just like gradually begin to understand it
like gradually begin to understand it right and one of those things is what i
right and one of those things is what i talked about earlier which is like
talked about earlier which is like figuring out
figuring out what is a row right like in the
what is a row right like in the philosophical sense what is defined by
philosophical sense what is defined by each row in this data set right and if i
each row in this data set right and if i start looking at this i see okay i've
start looking at this i see okay i've got my columns there's a log number
got my columns there's a log number log no
log no i've seen a lot of data sets so i'm
i've seen a lot of data sets so i'm guessing that's a log number right
guessing that's a log number right there's a date a complaint date there's
there's a date a complaint date there's the officer's first name last name
the officer's first name last name their employee number maybe that's their
their employee number maybe that's their badge number i'm not sure i might have
badge number i'm not sure i might have to ask somebody to find that out
to ask somebody to find that out we've got the allegation against them
we've got the allegation against them which has some descriptions
which has some descriptions and then we've got the finding code
and then we've got the finding code which like finding is kind of weird
which like finding is kind of weird maybe that's the ultimate decision or
maybe that's the ultimate decision or disposition
disposition of the case those are all things i'm
of the case those are all things i'm like guessing right from just like what
like guessing right from just like what is this i'm smelling the data and like i
is this i'm smelling the data and like i think that's right right and you can
think that's right right and you can precede your like initial analysis of a
precede your like initial analysis of a data set just kind of
data set just kind of guessing what you know it is but
guessing what you know it is but ultimately you have to test those
ultimately you have to test those assumptions against the data itself
assumptions against the data itself and also just by maybe just asking the
and also just by maybe just asking the source you know like what is this column
source you know like what is this column is this is this defining or is that
is this is this defining or is that that's the result or is it not you know
that's the result or is it not you know and then one thing is i just like look
and then one thing is i just like look at the first few rows and i kind of
at the first few rows and i kind of sniff the data right i notice oh hey
sniff the data right i notice oh hey those log numbers repeat like we have
those log numbers repeat like we have look at this this log number is the same
look at this this log number is the same for like the first 23 records
for like the first 23 records right
right and hey roberto lopianco is in here
and hey roberto lopianco is in here twice with the same number that's that's
twice with the same number that's that's probably the same guy right
probably the same guy right so
so each log so each row isn't a single
each log so each row isn't a single complaint i don't think because it has
complaint i don't think because it has multiple people or maybe a complaint can
multiple people or maybe a complaint can have more than one officer
have more than one officer yeah that's it probably right so
yeah that's it probably right so the relationship between records or like
the relationship between records or like the data is often you have to think
the data is often you have to think about those relationships are often like
about those relationships are often like one to one or one to many and so we have
one to one or one to many and so we have here like one log number that has many
here like one log number that has many officers right and if you think about it
officers right and if you think about it that probably means when you file a
that probably means when you file a complaint you can accuse multiple people
complaint you can accuse multiple people which passes the common sense test and
which passes the common sense test and kind of smells okay but you might want
kind of smells okay but you might want to make sure that you might want to look
to make sure that you might want to look at the form
at the form right that's used to file a complaint
right that's used to file a complaint oftentimes if you don't understand your
oftentimes if you don't understand your data from a government database getting
data from a government database getting the form that it comes from can answer
the form that it comes from can answer those questions faster than asking
those questions faster than asking anybody right because if you just look
anybody right because if you just look at the blanks you fill in on any
at the blanks you fill in on any government form that almost always
government form that almost always corresponds to fields and tables in a
corresponds to fields and tables in a database right and so if i was to report
database right and so if i was to report this out that's what i would look for
this out that's what i would look for and i always look for that sometimes
and i always look for that sometimes i'll file a public records request for
i'll file a public records request for the form right because i can't get
the form right because i can't get anybody to answer my question and i know
anybody to answer my question and i know if i get the form i got something you
if i get the form i got something you know that i can go off that i can have
know that i can go off that i can have some
some confidence in right
confidence in right it'll i'll often get request the forms
it'll i'll often get request the forms before the database so that i know what
before the database so that i know what fields to ask for right and that's one
fields to ask for right and that's one way you can kind of know what they got
way you can kind of know what they got you know you can reverse engineer the
you know you can reverse engineer the database
database and then we see here robert lobianco is
and then we see here robert lobianco is in here twice what's that about
in here twice what's that about and if i look at this allegation
and if i look at this allegation category we see there's different
category we see there's different allegations so that suggests to me the
allegations so that suggests to me the complaint has a one-to-many relationship
complaint has a one-to-many relationship to the officers and then the officers
to the officers and then the officers have a one-to-many relationship to the
have a one-to-many relationship to the allegations which also makes sense right
allegations which also makes sense right so it's like i could
so it's like i could i could make an allegation against
i could make an allegation against multiple officers and i could say that
multiple officers and i could say that each one did multiple things wrong right
each one did multiple things wrong right and so you can see here that just within
and so you can see here that just within this one complaint there actually is
this one complaint there actually is like five or six officers and each one
like five or six officers and each one of them has multiple allegations against
of them has multiple allegations against them
them right
right okay so we're starting to get a sense of
okay so we're starting to get a sense of like i would say and this is the
like i would say and this is the question i was almost what's a row
question i was almost what's a row what's a row how would i define it i
what's a row how would i define it i would say the row is an allegation
would say the row is an allegation that's what i think i would call it
that's what i think i would call it right so each row is an allegation
right so each row is an allegation against an officer linked to a complaint
against an officer linked to a complaint i think
i think and like that definition of my head
and like that definition of my head often gets revised as i like better
often gets revised as i like better understand the data like i was working
understand the data like i was working with claire and her classmates on a
with claire and her classmates on a story last year and we thought we was
story last year and we thought we was about trees in the city of chicago and
about trees in the city of chicago and there was this one column that was just
there was this one column that was just like a number and it was just and we
like a number and it was just and we didn't know what it was and we thought
didn't know what it was and we thought each row is a tree each row is one tree
each row is a tree each row is one tree like one right but then we asked them
like one right but then we asked them hey what's this weird number column they
hey what's this weird number column they were like oh that's the number of trees
were like oh that's the number of trees we planted
we planted and so each row was in order to plant
and so each row was in order to plant trees
trees and that number told you how many trees
and that number told you how many trees there were and so the difference between
there were and so the difference between just counting each record is one and
just counting each record is one and summing that numerical thing is the
summing that numerical thing is the difference between your story being
difference between your story being right your story being wrong right and
right your story being wrong right and so
so doing this kind of data smelling
doing this kind of data smelling figuring it out what each row is
figuring it out what each row is really pushing yourself to kind of like
really pushing yourself to kind of like guess what each column is
guess what each column is and you can often just say these 10
and you can often just say these 10 columns i don't even think i need to
columns i don't even think i need to care about right but
care about right but you got to do that process
you got to do that process and then so we kind of got we came up
and then so we kind of got we came up with our working theory of what a row is
with our working theory of what a row is right and then another good thing is
right and then another good thing is just to look at like how much data you
just to look at like how much data you have and whether it's all filled in or
have and whether it's all filled in or not right because you have this data set
not right because you have this data set we don't know how many rows there are we
we don't know how many rows there are we don't know if there's empty values all
don't know if there's empty values all over the database it could be incomplete
over the database it could be incomplete and a really great way to do that is
and a really great way to do that is just to scroll around the four corners
just to scroll around the four corners of the database right just to like
of the database right just to like literally scroll to the bottom and see
literally scroll to the bottom and see what you've got and like see if stuff is
what you've got and like see if stuff is filled in so i'm just gonna page down
filled in so i'm just gonna page down really quick
really quick see i don't see a lot of empty cells
see i don't see a lot of empty cells there are some i've noticed look there's
there are some i've noticed look there's some empty cells that's interesting so
some empty cells that's interesting so sometimes there isn't a finding well
sometimes there isn't a finding well that's interesting why wouldn't there be
that's interesting why wouldn't there be a finding sometimes is it maybe still
a finding sometimes is it maybe still under investigation
under investigation maybe right is it um a flaw that they
maybe right is it um a flaw that they failed to put it in i gotta tuck that
failed to put it in i gotta tuck that that's that's a data smell right i smell
that's that's a data smell right i smell that and i'm like okay i'm gonna make a
that and i'm like okay i'm gonna make a little note for myself in my notepad
little note for myself in my notepad that might or might not be something
that might or might not be something that matters down the road but i wanna
that matters down the road but i wanna just like accrue my little list of like
just like accrue my little list of like you know status smells so i know
you know status smells so i know what's going on
what's going on and you got to get to know your data
and you got to get to know your data like this you don't want to run too fast
like this you don't want to run too fast and so you get to the bottom and we can
and so you get to the bottom and we can see that there's 4
see that there's 4 338 rows subtract our one header row we
338 rows subtract our one header row we know there's 4
know there's 4 337 complaints right
337 complaints right in this like set of data that ben gave
in this like set of data that ben gave there's other integrity checks you could
there's other integrity checks you could do like for instance i think this is
do like for instance i think this is everything in 2014 you might want to do
everything in 2014 you might want to do totals by month just to make sure we're
totals by month just to make sure we're not missing a couple months right you
not missing a couple months right you could do these little integrity checks
could do these little integrity checks on the data that help you like see kind
on the data that help you like see kind of what's up with it right
okay so we kind of smelled it we think it's for 2014 this is all the
it's for 2014 this is all the allegations made against officers in the
allegations made against officers in the city
city now i want to use some of the tricks
now i want to use some of the tricks that we've covered already to ask and
that we've covered already to ask and answer some questions right
answer some questions right so one thing i i want to look at is like
so one thing i i want to look at is like a story that's been done before so here
a story that's been done before so here was a story that ran in the chicago
was a story that ran in the chicago reporter where they've done lots and
reporter where they've done lots and lots of great data journalism over the
lots of great data journalism over the years they're having some management
years they're having some management problems right now like many journalism
problems right now like many journalism outlets are but the chicago reporter has
outlets are but the chicago reporter has a tremendous track record of great data
a tremendous track record of great data journalism especially around issues of
journalism especially around issues of race and policing and one story they did
race and policing and one story they did it looks like six years ago is
it looks like six years ago is they looked at a subsequent release they
they looked at a subsequent release they looked at this very same data set
looked at this very same data set right
right and they
and they looked at they analyzed something called
looked at they analyzed something called the affidavit requirement
the affidavit requirement and what they found
and what they found looking at like literally the same
looking at like literally the same spreadsheet that we have that's on the
spreadsheet that we have that's on the website is they found let me see if we
website is they found let me see if we can find the finding here
can find the finding here they found there were 17 000 complaints
they found there were 17 000 complaints in the three years they looked at which
in the three years they looked at which really would just be
really would just be scrolling to the bottom of the data set
scrolling to the bottom of the data set right in the same way we just did they
right in the same way we just did they found out investigators didn't open
found out investigators didn't open cases in 58
cases in 58 because they were marked no affidavit
because they were marked no affidavit well that looks familiar i see that
well that looks familiar i see that right here no affidavit right that's in
right here no affidavit right that's in my finding code
my finding code and if you read more into it it turns
and if you read more into it it turns out that
out that under the the rules of how police
under the the rules of how police complaints work which were hammered out
complaints work which were hammered out in a past labor contract negotiation
in a past labor contract negotiation with the police union um
with the police union um i'm not a lawyer so i might not get this
i'm not a lawyer so i might not get this exactly right but my understanding is is
exactly right but my understanding is is you can't just file a complaint you also
you can't just file a complaint you also have to file a legal affidavit where you
have to file a legal affidavit where you kind of swear that what you're saying is
kind of swear that what you're saying is what really happened which is a kind of
what really happened which is a kind of hoop a procedural hoop that each
hoop a procedural hoop that each complainant has to jump through for
complainant has to jump through for their complaint to actually be
their complaint to actually be investigated by the police and it turns
investigated by the police and it turns out that it's really really common
out that it's really really common for that to be done according to the
for that to be done according to the analysis of this group and then this
analysis of this group and then this piece kind of has a little bit of an
piece kind of has a little bit of an opinion angle to it where it's saying if
opinion angle to it where it's saying if they were to get rid of that requirement
they were to get rid of that requirement it would lead to a lot more
it would lead to a lot more investigations and a lot more discipline
investigations and a lot more discipline against police is kind of potential
against police is kind of potential right is what the story says
right is what the story says so
so if i wanted to do a similar analysis for
if i wanted to do a similar analysis for this data set i want to figure out
this data set i want to figure out of the what was the number again of the
ah here's a fun trick if you hit control
here's a fun trick if you hit control down control and hit up it jumps to the
down control and hit up it jumps to the last record of our 4
last record of our 4 37 complaints what percentage were no
37 complaints what percentage were no affidavit right like that's what i want
affidavit right like that's what i want to figure out and let's say you filed a
to figure out and let's say you filed a foia and got last year's complaints and
foia and got last year's complaints and you were the first reporter to get it
you were the first reporter to get it you could run this analysis to like
you could run this analysis to like update this old story right so if i
update this old story right so if i wanted to do that i want to know what's
wanted to do that i want to know what's the number
the number of
of allegations
allegations that that were
that that were um dropped because they were no
um dropped because they were no affidavit
affidavit no affidavit was filed and i want to
no affidavit was filed and i want to know that as a percentage of the total
know that as a percentage of the total what trick that we've used so far would
what trick that we've used so far would help us get there who can think of
help us get there who can think of something
it's the pivot table guys right because we've got our categorical data that we
we've got our categorical data that we want to group and count we want to know
want to group and count we want to know for each of the findings how many there
for each of the findings how many there are right
are right and so i'm going to click in the upper
and so i'm going to click in the upper left on the selector to select my whole
left on the selector to select my whole data set
data set i'm going to do insert
i'm going to do insert and then my buddy pivot table
and then my buddy pivot table oh yeah
selected new sheet
sheet okay so now i want to group by the
okay so now i want to group by the finding code column who can tell me how
finding code column who can tell me how to do that
finding code and then under row yes we got our columns here i grab drag that
got our columns here i grab drag that and i drop it into row and so that's one
and i drop it into row and so that's one per row and so we can now see
per row and so we can now see that these are all the potential values
that these are all the potential values there's some empty rows as we saw when
there's some empty rows as we saw when we were scrolling
we were scrolling there's some that are under
there's some that are under additional investigation required
additional investigation required some exonerated no affidavit there it is
some exonerated no affidavit there it is not sustained sustained which means they
not sustained sustained which means they said it was legit the complaint right
said it was legit the complaint right and then unfounded right and you might
and then unfounded right and you might need to report out what are all these
need to report out what are all these values right you know and that's part of
values right you know and that's part of the process of getting to know your data
the process of getting to know your data and so i just want to count
and so i just want to count how many allegations
how many allegations there are
there are for each one of these groups so how do i
for each one of these groups so how do i do that
do that pull finding code to values and do by
pull finding code to values and do by count a
count a yeah that would work so if i take
yeah that would work so if i take finding code the same column drop it
finding code the same column drop it there and leave it as count a
there and leave it as count a we can see that that instantly fills in
we can see that that instantly fills in right and we can see that there are two
right and we can see that there are two rows where additional investigation is
rows where additional investigation is required
required 297
297 right
right where they're exonerated and we see
where they're exonerated and we see there's
there's 1286 that are no affidavit out of our
1286 that are no affidavit out of our total of four two two three now wait
total of four two two three now wait hold on a second four two two three is
hold on a second four two two three is our total and this is part of the data
our total and this is part of the data smells it's like didn't we have more
smells it's like didn't we have more rows than that when i was looking
rows than that when i was looking right well if i go here and hit control
right well if i go here and hit control to the bottom
to the bottom there were
there were 4
4 38 so shouldn't there be a little more
38 so shouldn't there be a little more than that does anybody know why that why
than that does anybody know why that why we have that difference
we have that difference there's the ones that are potentially
there's the ones that are potentially still being investigated so the blank
still being investigated so the blank their left is empty and i think it's i
their left is empty and i think it's i think i didn't anticipate this but i
think i didn't anticipate this but i think that's because we use the finding
think that's because we use the finding code for our value
code for our value i said and those that's empty in some
i said and those that's empty in some cases and in the cases where it's empty
cases and in the cases where it's empty it's not counting it so i bet if we take
it's not counting it so i bet if we take log no which we know is filled in for
log no which we know is filled in for every row put that in and do count a
every row put that in and do count a look at that we get the total right and
look at that we get the total right and we see now that there's 114 that are
we see now that there's 114 that are blank you see that at the top there
blank you see that at the top there and so that was just like a little weird
and so that was just like a little weird wrinkle of how google sheets works right
wrinkle of how google sheets works right if we did our value in our account using
if we did our value in our account using a column that had null or empty cells
a column that had null or empty cells right it didn't count the null or empty
right it didn't count the null or empty ones right is that really the end of the
ones right is that really the end of the world no
world no in this case right and you might say
in this case right and you might say well for the purposes of this analysis i
well for the purposes of this analysis i might want to subtract those right like
might want to subtract those right like when i calculate the percentage if
when i calculate the percentage if they're still in progress cases so it
they're still in progress cases so it might actually be better to exclude them
might actually be better to exclude them methodologically but
methodologically but the lesson here i think to take home is
the lesson here i think to take home is that a little slip of the finger or the
that a little slip of the finger or the data the software not doing exactly what
data the software not doing exactly what you expected to do can sometimes change
you expected to do can sometimes change the analysis in a way that maybe you
the analysis in a way that maybe you don't even notice right and so that's
don't even notice right and so that's why when when you work with these pivot
why when when you work with these pivot tables
tables and other programming tools it's
and other programming tools it's important that every step to just stop
important that every step to just stop and look and be like did these numbers
and look and be like did these numbers add up
add up is this what i would expect it to do
is this what i would expect it to do because you'll often find it's not even
because you'll often find it's not even your fault like in this case it was just
your fault like in this case it was just kind of a weird thing and the way i was
kind of a weird thing and the way i was able to do that here just to recreate it
able to do that here just to recreate it is i just looked at the total
is i just looked at the total right
right and i said well wait wasn't it supposed
and i said well wait wasn't it supposed to be 4 300 or something and the reason
to be 4 300 or something and the reason i even had that thought is because i did
i even had that thought is because i did my four corners check right and i tucked
my four corners check right and i tucked away in my head
away in my head that's what i think it should be and
that's what i think it should be and that kind of back and forth of like
that kind of back and forth of like fumbling with it is really just part of
fumbling with it is really just part of the process and i've been doing this
the process and i've been doing this nearly 20 years and like i do this on
nearly 20 years and like i do this on everything i'm working on like on that
everything i'm working on like on that trees thing last year i probably screwed
trees thing last year i probably screwed up four or five things and we run around
up four or five things and we run around in circles like do i really know what
in circles like do i really know what this is and like that struggle is really
this is and like that struggle is really just part of the process right and the
just part of the process right and the fact that you're doing that is what's
fact that you're doing that is what's separating you from the competition
separating you from the competition right you're actually figuring it out
right you're actually figuring it out and you're going to get a story somebody
and you're going to get a story somebody else doesn't right and so when
else doesn't right and so when it was when i feel that frustration i'm
it was when i feel that frustration i'm as frustrated as anybody 20 years in i
as frustrated as anybody 20 years in i still feel the frustration every day but
still feel the frustration every day but i've got to the point where i realize i
i've got to the point where i realize i have the zen
have the zen that like oh that's good right it's
that like oh that's good right it's better that i had the frustration before
better that i had the frustration before the story came out than after the story
the story came out than after the story came out it's
came out it's right because i want to know it ahead of
right because i want to know it ahead of time of course so i'm right
time of course so i'm right and i don't have to write a correction
and i don't have to write a correction right but also it's usually a sign that
right but also it's usually a sign that you're kind of you're
you're kind of you're cutting some new territory right you're
cutting some new territory right you're there maybe beyond everybody else so
there maybe beyond everybody else so that's good
that's good okay so now we still haven't quite
okay so now we still haven't quite answered the question we know there's
answered the question we know there's 1286 no affidavits we know that's the
1286 no affidavits we know that's the total oh wait let me go back to the log
total oh wait let me go back to the log number so that uh
number so that uh we have the full count
we have the full count okay so now how using only tricks we've
okay so now how using only tricks we've learned before but using them a little
learned before but using them a little bit differently how can we calculate the
bit differently how can we calculate the percentage of cases that are no
percentage of cases that are no affidavit anybody
i just want to put it right there in c5 i want to calculate it what do i do
so how do you start a formula who remembers
rules equals so you type equals you can really just start riff and math so
can really just start riff and math so the percentage is just this
the percentage is just this right
right divided into this right
divided into this right boom
boom 0.29 hit the percentage sign
0.29 hit the percentage sign 29
29 we're no affidavit
we're no affidavit right
right and so that's 29
and so that's 29 of allegations right now if you look at
of allegations right now if you look at the story i think they actually don't do
the story i think they actually don't do percent of allegations they do percent
percent of allegations they do percent of cases
of cases right and this is where the relationship
right and this is where the relationship in your data and how you think about it
in your data and how you think about it can really vary and a case isn't defined
can really vary and a case isn't defined by a row in our data set right a case is
by a row in our data set right a case is defined by the log node or so we think
defined by the log node or so we think we have to report that out and so really
we have to report that out and so really we would want to get into like the
we would want to get into like the unique log nodes or something maybe a
unique log nodes or something maybe a little more sophisticated and if so if
little more sophisticated and if so if we say rather than counting all records
we say rather than counting all records only count the unique log numbers right
only count the unique log numbers right after you've grouped and then how many
after you've grouped and then how many unique log numbers are in each group we
unique log numbers are in each group we can see that while we may have 4 000
can see that while we may have 4 000 allegations we only have 1200 complaints
allegations we only have 1200 complaints right and this is again thinking
right and this is again thinking conceptually about what your data is
conceptually about what your data is right and you'll notice that in in these
right and you'll notice that in in these stories as you read them how that's
stories as you read them how that's managed is really part of kind of
managed is really part of kind of getting it right or being artful and
getting it right or being artful and precise with your data and so i might
precise with your data and so i might say if my initial hunches hold up that
say if my initial hunches hold up that 34
34 of complaints had no affidavit
of complaints had no affidavit but that
but that 29
29 of allegations
of allegations right uh had no affidavit and this kind
right uh had no affidavit and this kind of subtlety of like how you think about
of subtlety of like how you think about or work it really is important to be
or work it really is important to be thinking about it every step of your
thinking about it every step of your analysis a lot of journalists who do
analysis a lot of journalists who do this keep what they call a data diary
this keep what they call a data diary which is where they literally write down
which is where they literally write down kind of the decisions they're making the
kind of the decisions they're making the questions that are coming up as they go
questions that are coming up as they go along and then they go back to that
along and then they go back to that again and again and oftentimes you do a
again and again and oftentimes you do a lot of sort of doodles and curly cues as
lot of sort of doodles and curly cues as you mess around with the data and then
you mess around with the data and then you figure out well this is the number i
you figure out well this is the number i want to report or i want to build my
want to report or i want to build my story around and then you return to that
story around and then you return to that diary to the process all the steps you
diary to the process all the steps you took and you try to make sure is
took and you try to make sure is everything i'm doing on each step like
everything i'm doing on each step like exactly what i need it to be you know
exactly what i need it to be you know what i mean for this to be right are
what i mean for this to be right are there any shaky assumptions i'm making
there any shaky assumptions i'm making is there anything about the structure of
is there anything about the structure of the data that i'm unsure about or it
the data that i'm unsure about or it doesn't feel right or seem right
doesn't feel right or seem right is the language i'm using to describing
is the language i'm using to describing it correct that's really the kind of
it correct that's really the kind of bulletproofing process of data
bulletproofing process of data journalism that is similar to
journalism that is similar to bulletproofing other stories but just
bulletproofing other stories but just has a more technical nature and for me
has a more technical nature and for me personally having done this more and
personally having done this more and more this is where computer programming
more this is where computer programming comes into it for me because what i like
comes into it for me because what i like to do as because i've gotten nerdier is
to do as because i've gotten nerdier is literally write computer code that
literally write computer code that executes each step of the data
executes each step of the data transformation so that i can really
transformation so that i can really carefully regulate what i do with the
carefully regulate what i do with the data right
data right and know each step and then i can go
and know each step and then i can go back and go over it and go over and go
back and go over it and go over and go over it until i feel confident that i
over it until i feel confident that i have a strong and that's harder to do
have a strong and that's harder to do when you're working in a spreadsheet
when you're working in a spreadsheet because you really have like the
because you really have like the spreadsheet's like a machete right it's
spreadsheet's like a machete right it's not a scalpel you're sort of hacking at
not a scalpel you're sort of hacking at the data moving it around trying a lot
the data moving it around trying a lot of different things with pivots and you
of different things with pivots and you can sometimes get lost in it or make a
can sometimes get lost in it or make a mistake but there's ways to avoid that
mistake but there's ways to avoid that too like one really common thing
too like one really common thing stress is you know you should always
stress is you know you should always keep a pure and unedited copy of your
keep a pure and unedited copy of your data
data that you have not added any columns to
that you have not added any columns to or much around with so you're always
or much around with so you're always able to retrace your footsteps back to
able to retrace your footsteps back to where you began right it could live as
where you began right it could live as an email attachment from a government
an email attachment from a government official that's fine you might want to
official that's fine you might want to save it to your computer but you don't
save it to your computer but you don't want to end up in a situation where you
want to end up in a situation where you lose track of where you started because
lose track of where you started because then it can be really hard to find
then it can be really hard to find mistakes or make sure verify that what
mistakes or make sure verify that what you've done is correct
you've done is correct i think we're getting to the point where
i think we're getting to the point where i'm supposed to stop spilling and so i
i'm supposed to stop spilling and so i figured i would stop with like a really
figured i would stop with like a really um sanctimonious moral lesson like that
um sanctimonious moral lesson like that was
was uh which i i appreciate you guys sort of
uh which i i appreciate you guys sort of sitting you know
sitting you know enduring but that's kind of the spiel
enduring but that's kind of the spiel there's a lot of other questions and
there's a lot of other questions and stuff we could answer in the spreadsheet
stuff we could answer in the spreadsheet but i don't want to go on too long and i
but i don't want to go on too long and i want to give us time to just have kind
want to give us time to just have kind of a
of a discussion where we can answer
discussion where we can answer specific technical questions you have or
specific technical questions you have or just have a broader conversation about
just have a broader conversation about data journalism chicago depaul
data journalism chicago depaul the cubs chances this fall
the cubs chances this fall whatever you want to get into claire
whatever you want to get into claire what do you think
what do you think yeah that sounds good i think my video
yeah that sounds good i think my video might have gone out there for a second
might have gone out there for a second it was pretty funny but uh yeah i
it was pretty funny but uh yeah i i like to take these last 15 minutes for
i like to take these last 15 minutes for us to do a q a
us to do a q a um as ben said if you guys have any
um as ben said if you guys have any questions for him or about his work or
questions for him or about his work or about data driven reporting and data
about data driven reporting and data journalism so haley it looks like you
journalism so haley it looks like you have a question go ahead
have a question go ahead yeah so my first well my question is you
yeah so my first well my question is you know if you create so if you find this
know if you create so if you find this number by yourself say so we find like
number by yourself say so we find like that 29
that 29 um number
um number if you don't
if you don't if you s you know you put this in a
if you s you know you put this in a piece that you're working on and you
piece that you're working on and you publish it you say this is the number
publish it you say this is the number that i came up with
that i came up with are people going to be critical of that
are people going to be critical of that if you're not like you know what i mean
if you're not like you know what i mean if you're not citing somewhere else well
if you're not citing somewhere else well i mean like you're definitely you're
i mean like you're definitely you're climbing out on a limb every time you do
climbing out on a limb every time you do it right and i think a lot of in
it right and i think a lot of in journalism we're often taking numbers
journalism we're often taking numbers from other people and kind of passing
from other people and kind of passing them along but you know
them along but you know the most i think often impactful and
the most i think often impactful and original data journalism stories are
original data journalism stories are calculating their own findings right
calculating their own findings right yeah but i think but you know you have
yeah but i think but you know you have with that with that comes a higher
with that with that comes a higher degree of rigor and responsibility
degree of rigor and responsibility that's necessary yeah and so for me it's
that's necessary yeah and so for me it's really like i was saying understanding
really like i was saying understanding every step you're making in the process
every step you're making in the process uh making sure you fully grasp and
uh making sure you fully grasp and understand your data what is each row
understand your data what is each row what's in the data set what's left out
what's in the data set what's left out of the data set there's stuff we didn't
of the data set there's stuff we didn't cover like maybe we need to exclude some
cover like maybe we need to exclude some records right or maybe you need to like
records right or maybe you need to like really carefully frame your findings so
really carefully frame your findings so that you don't overstate it or say it
that you don't overstate it or say it kind of wrong you know what i mean yeah
kind of wrong you know what i mean yeah refinement process is hard especially
refinement process is hard especially when you're starting but once you get
when you're starting but once you get the hang of it it's not so bad i mean
the hang of it it's not so bad i mean obviously i think you if you're you know
obviously i think you if you're you know a lot of data journalism will get into
a lot of data journalism will get into the investigative space you know and
the investigative space you know and once you're there
once you're there um you know i think you really want to
um you know i think you really want to go above and beyond to give people an
go above and beyond to give people an opportunity to respond you know i've
opportunity to respond you know i've done you know not like i'm mr data
done you know not like i'm mr data journalism or whatever but like i've
journalism or whatever but like i've done stories that have gotten people
done stories that have gotten people fired
fired or
or put in the newspaper that uh uh
put in the newspaper that uh uh something is dangerous or you know like
something is dangerous or you know like i did a story like this is the most
i did a story like this is the most dangerous helicopter kind of thing
dangerous helicopter kind of thing and like and in those circumstances
and like and in those circumstances i always make sure that the subject the
i always make sure that the subject the people i'm writing about see everything
people i'm writing about see everything i'm writing about before it's published
i'm writing about before it's published and they have an opportunity to respond
and they have an opportunity to respond to
to um every basically the analysis itself
um every basically the analysis itself so in the case of the helicopter company
so in the case of the helicopter company where we're going to publish a story
where we're going to publish a story that is effectively
that is effectively ben says this is the most dangerous
ben says this is the most dangerous helicopter in the world
helicopter in the world the actual computer code that i wrote
the actual computer code that i wrote um i gave to the helicopter company
um i gave to the helicopter company ahead of time here's how i calculated
ahead of time here's how i calculated this i wrote out effectively a memo that
this i wrote out effectively a memo that was sent to them an email saying these
was sent to them an email saying these will be our key claims
will be our key claims and here's a bullet point summary of how
and here's a bullet point summary of how i arrived at those claims and you know
i arrived at those claims and you know in that case what we did is we modeled
in that case what we did is we modeled our study on one that had been done by
our study on one that had been done by the faa in the past so you know one way
the faa in the past so you know one way in investigations to deal with this
in investigations to deal with this issue is to look for outside standards
issue is to look for outside standards and outside methods that you're not just
and outside methods that you're not just like inventing you know so yeah that
like inventing you know so yeah that makes sense my method of calculating how
makes sense my method of calculating how the helicopters were dangerous was an
the helicopters were dangerous was an accident rate that was something that
accident rate that was something that the faa itself had done like 30 years
the faa itself had done like 30 years ago and so i recreated effectively
ago and so i recreated effectively something that had already been done by
something that had already been done by the agency and then in that process
the agency and then in that process i went to the agency i had an interview
i went to the agency i had an interview with them and i said i did this analysis
with them and i said i did this analysis i was on the phone with their data guy
i was on the phone with their data guy and i filtered this and i grouped that
and i filtered this and i grouped that and i joined this
and i joined this right is there anything i'm getting
right is there anything i'm getting wrong or missing and you give them an
wrong or missing and you give them an opportunity to tell you you're wrong so
opportunity to tell you you're wrong so you did all that stuff behind the scenes
you did all that stuff behind the scenes before the story came out
before the story came out and so recreating experiments or things
and so recreating experiments or things that other people have already done that
that other people have already done that have sort of like some justification is
have sort of like some justification is good also using standards that are sort
good also using standards that are sort of objective or defined by the
of objective or defined by the um
um defined by
defined by the subject themselves right so i've
the subject themselves right so i've done stories about the 911 system in la
done stories about the 911 system in la and like you can calculate the stats and
and like you can calculate the stats and say it's slow but it's like slow
say it's slow but it's like slow according to who
according to who yeah according to you yeah no well
yeah according to you yeah no well actually there's like a group of fire
actually there's like a group of fire chiefs who meet and set the standards
chiefs who meet and set the standards and i'm judging you against that
and i'm judging you against that right and so finding an outside standard
right and so finding an outside standard is often really key
is often really key to all investigative stories like did
to all investigative stories like did someone break the law or not would be a
someone break the law or not would be a classic example right
classic example right but can be really helpful when defining
but can be really helpful when defining your data methodology of kind of what
your data methodology of kind of what your target is right and i think that
your target is right and i think that these complaint stories kind of have an
these complaint stories kind of have an issue there because you can say well
issue there because you can say well only a small number of complaints
only a small number of complaints are um upheld right you can put that in
are um upheld right you can put that in the paper it's x percent but like is
the paper it's x percent but like is that how do we know that's good or bad
that how do we know that's good or bad you really don't right and that and that
you really don't right and that and that is i think
is i think one reason why the stories stories have
one reason why the stories stories have great impact they're great stories i'm
great impact they're great stories i'm jealous of them but they're missing that
jealous of them but they're missing that standard piece that really helps you hit
standard piece that really helps you hit in a lot of cases you know
yeah yeah context context context context so important
context so important um nadia i think you have a question
um nadia i think you have a question yeah hi thank you so much for um
yeah hi thank you so much for um speaking with us today um i did have a
speaking with us today um i did have a question so
question so typically i
typically i don't do math and i don't really do data
don't do math and i don't really do data um but i'm wondering like kind of what
um but i'm wondering like kind of what you were saying in that like last part
you were saying in that like last part of your answer to the question about how
of your answer to the question about how kind of data journalism and also but
kind of data journalism and also but like data can kind of like impact and
like data can kind of like impact and strengthen like a story so how would you
strengthen like a story so how would you say
say would be a good way to insert like a
would be a good way to insert like a little bit of data journalism in a story
little bit of data journalism in a story to kind of strengthen it without take
to kind of strengthen it without take without making it like the focus of a
without making it like the focus of a story yeah i mean i mean to me looking
story yeah i mean i mean to me looking to it for context and just like a little
to it for context and just like a little bit of background is where it's most
bit of background is where it's most commonly used like hey i'm writing the
commonly used like hey i'm writing the story about the chief of police saying
story about the chief of police saying homicides are up and it's a big problem
homicides are up and it's a big problem in chicago and it definitely is and i
in chicago and it definitely is and i wouldn't want to minimize that but that
wouldn't want to minimize that but that story would benefit from context of like
story would benefit from context of like well okay it's up over this year but how
well okay it's up over this year but how does it compare to 20 years ago or like
does it compare to 20 years ago or like what's the general trend it may be a
what's the general trend it may be a chart right and so i think like
chart right and so i think like background and context is like the most
background and context is like the most common thing you know so like often the
common thing you know so like often the thing that's in the news is a really
thing that's in the news is a really short time frame and just like
short time frame and just like stretching that out is good
stretching that out is good um i think you know understanding uh
um i think you know understanding uh something sense of proportion you know
something sense of proportion you know what i mean so like oh hospitalizations
what i mean so like oh hospitalizations are really up for coven among young
are really up for coven among young people but how many young people are
people but how many young people are actually being hospitalized like the
actually being hospitalized like the actual number right and so i think uh
actual number right and so i think uh context over time and kind of contest of
context over time and kind of contest of like in in the grand scheme of things
like in in the grand scheme of things you know what i mean are probably the
you know what i mean are probably the two most common data things if i had to
two most common data things if i had to i guess guess right um i also think
i guess guess right um i also think let's say you cover education or you
let's say you cover education or you cover the hospital system or you cover
cover the hospital system or you cover whatever i think you could put on a sort
whatever i think you could put on a sort of metaphorical set of database glasses
of metaphorical set of database glasses and kind of look at your beat and say
and kind of look at your beat and say well what's the data gathered on this
well what's the data gathered on this beat that i could maybe use to bring
beat that i could maybe use to bring some accountability or context and
some accountability or context and perspective to a story education's a
perspective to a story education's a classic example there's just so much
classic example there's just so much education data of all different shapes
education data of all different shapes and sizes that i think probably every
and sizes that i think probably every education reporter in america does some
education reporter in america does some data right it's like kind of whether
data right it's like kind of whether it's test scores or
it's test scores or dropped enrollment after covid or you
dropped enrollment after covid or you name it right you know there's just a
name it right you know there's just a lot of data inequity right is also a
lot of data inequity right is also a classic data frame
classic data frame and
and and i think i think looking at your beat
and i think i think looking at your beat and saying well what's the data gathered
and saying well what's the data gathered by this
by this this thing i'm covering or what are the
this thing i'm covering or what are the goals and standards that they claim that
goals and standards that they claim that they're reaching for or upholding and
they're reaching for or upholding and can i use data to measure whether that's
can i use data to measure whether that's really the case or not
anyone else have any questions you can either drop them in the chat or go ahead
either drop them in the chat or go ahead and
yeah i have one um so you've been doing this for a while you've been doing it
this for a while you've been doing it for a minute um
for a minute um and i mean i don't know what depaul's
and i mean i don't know what depaul's program journalism program was like 20
program journalism program was like 20 years ago but i'm guessing it didn't
years ago but i'm guessing it didn't have a super strong data journalism
have a super strong data journalism program so i guess how have you learned
program so i guess how have you learned and picked up all of these different
and picked up all of these different data skills over the past two decades
data skills over the past two decades have you like just gotten a thousand
have you like just gotten a thousand degrees
degrees from people in newsrooms like what's
from people in newsrooms like what's what's your secret sure
what's your secret sure um
um you're right yeah so when i went to
you're right yeah so when i went to depaul there was it was just the college
depaul there was it was just the college of communication i actually answered
of communication i actually answered phones at the front desk
phones at the front desk uh that was my gig
uh that was my gig um
um there was no data journalism program
there was no data journalism program no
no uh yes this was on the fifth floor of
uh yes this was on the fifth floor of sac i don't think it's still there right
sac i don't think it's still there right i don't know but that's where that's
i don't know but that's where that's where the department used to be up at
where the department used to be up at the top and
the top and use the whole loop thing now and it's
use the whole loop thing now and it's you know but um
you know but um no they're real very few places that any
no they're real very few places that any data journalism programs then data
data journalism programs then data journalism in the united states really
journalism in the united states really was kind of a grassroots movement that
was kind of a grassroots movement that grew out of the 60s and 70s and really
grew out of the 60s and 70s and really not unlike
not unlike hackers who made computers in silicon
hackers who made computers in silicon valley right there was sort of just like
valley right there was sort of just like a a niche of people who saw the
a a niche of people who saw the potential of the computer and began to
potential of the computer and began to work it into their profession and in
work it into their profession and in journalism that was really the it was
journalism that was really the it was really an investigative tradition to
really an investigative tradition to start with it's like we want to look at
start with it's like we want to look at the census data using a big crazy
the census data using a big crazy computer or we want to survey our
computer or we want to survey our readers about what they think about the
readers about what they think about the detroit riots in 1968 that was a really
detroit riots in 1968 that was a really big early data journalism story or we
big early data journalism story or we want to investigate the county
want to investigate the county courthouse for racial inequities right
courthouse for racial inequities right these and and a sort of small tribe of
these and and a sort of small tribe of investigative journalists began
investigative journalists began developing those skills in the 60s 70s
developing those skills in the 60s 70s 80s and then they formed actually a sort
80s and then they formed actually a sort of
of a non-profit group that's mission was to
a non-profit group that's mission was to train journalists in what was then
train journalists in what was then called computer assisted reporting
called computer assisted reporting and that became a group at the
and that became a group at the university of missouri which is the
university of missouri which is the national institute for computer assisted
national institute for computer assisted reporting which is kind of a ridiculous
reporting which is kind of a ridiculous name today you know it's like
name today you know it's like everyone is computer assisted in
everyone is computer assisted in everything they do we don't call it
everything they do we don't call it computer assisted photography right or
computer assisted photography right or whatever uh but um so that's data
whatever uh but um so that's data journalism has kind of replaced that as
journalism has kind of replaced that as a term but um after i finished it depaul
a term but um after i finished it depaul i went to the university of missouri to
i went to the university of missouri to graduate school and i was a graduate
graduate school and i was a graduate assistant at this place nikar and as a
assistant at this place nikar and as a graduate assistant working there i was
graduate assistant working there i was able to work on stories with newsrooms
able to work on stories with newsrooms that partnered with it and spend a year
that partnered with it and spend a year and a half at mizzou really focusing and
and a half at mizzou really focusing and that's really where i began to learn how
that's really where i began to learn how to code and i really began to learn and
to code and i really began to learn and i met a lot of people who did this field
i met a lot of people who did this field now since i graduated it's really become
now since i graduated it's really become much more institutionalized paul has a
much more institutionalized paul has a class there's many many graduate
class there's many many graduate programs that explicitly focus on data
programs that explicitly focus on data journalism right now that just didn't
journalism right now that just didn't exist then and i think that's great and
exist then and i think that's great and i think that people are getting a lot
i think that people are getting a lot more education on it but i really i was
more education on it but i really i was lucky to benefit from like the one
lucky to benefit from like the one program that existed at the time that
program that existed at the time that kind of got me into it
kind of got me into it and graduate school was great for me you
and graduate school was great for me you know um i
know um i mizzou was great for me um you know it's
mizzou was great for me um you know it's got a great program in it the tradition
got a great program in it the tradition is there um you know i would just say
is there um you know i would just say it's it's compared to other graduate
it's it's compared to other graduate schools very very inexpensive
schools very very inexpensive um you can see the chart in the wall
um you can see the chart in the wall street journal about this mizzou is
street journal about this mizzou is probably the least expensive journalism
probably the least expensive journalism graduate school
graduate school in its class you know what i mean by a
in its class you know what i mean by a long margin so i was able to go in an
long margin so i was able to go in an inexpensive way and it really worked out
inexpensive way and it really worked out for me and then i just kind of got jobs
for me and then i just kind of got jobs and learned from people i work with i
and learned from people i work with i was lucky to start off in non-profit
was lucky to start off in non-profit news at the center for public integrity
news at the center for public integrity which is something that's also changed
which is something that's also changed since then but i really do think
since then but i really do think nonprofit news is a great place to start
nonprofit news is a great place to start out because um the newsrooms tend to be
out because um the newsrooms tend to be smaller they tend to be more projects
smaller they tend to be more projects focused so you can like there's not
focused so you can like there's not quite so much of a hurry so they're not
quite so much of a hurry so they're not great places to learn how to like write
great places to learn how to like write right right you know what i mean because
right right you know what i mean because they oftentimes don't move as quickly
they oftentimes don't move as quickly but they are great places to learn how
but they are great places to learn how to do projects to learn how to do
to do projects to learn how to do investigations to learn how to like
investigations to learn how to like um put together something that goes
um put together something that goes beyond the average story so i would
beyond the average story so i would really recommend them as a place to look
really recommend them as a place to look for a first job
for a first job um or whatever because you can be kind
um or whatever because you can be kind of shielded from some of the
of shielded from some of the insanity of a larger newsroom oftentimes
insanity of a larger newsroom oftentimes but different people want different
but different people want different things in their career but for a data
things in their career but for a data person it's a great place to start it
person it's a great place to start it was for me
great thank you [Music]
well i have one um so ben what would you recommend for people today who
recommend for people today who maybe are just now getting introduced or
maybe are just now getting introduced or this might be their first introduction
this might be their first introduction into data journalism and spreadsheets
into data journalism and spreadsheets where do you think they should go to
where do you think they should go to continue refining the skills that they
continue refining the skills that they learned today what would be the next
learned today what would be the next step i think putting on if you're
step i think putting on if you're covering something like in 14 east or
covering something like in 14 east or the depaul or somewhere else i think
the depaul or somewhere else i think really putting on the database glasses
really putting on the database glasses and saying well i'm covering this topic
and saying well i'm covering this topic like can i come up with a story where is
like can i come up with a story where is there some data on this beat that i can
there some data on this beat that i can like try to do something with and that
like try to do something with and that could just be you can look at it as just
could just be you can look at it as just even a breaking news story like some new
even a breaking news story like some new data gets released what does it say i
data gets released what does it say i think covet data right now is a great
think covet data right now is a great opportunity to do this you know because
opportunity to do this you know because it's just
it's just you know obviously not the crazy story
you know obviously not the crazy story it was two years ago but still a really
it was two years ago but still a really big story and there's probably an
big story and there's probably an opportunity to find some angle on it if
opportunity to find some angle on it if you begin to
you begin to to look at it it's finding that data on
to look at it it's finding that data on your bead or something coming and then
your bead or something coming and then really treating it like a source and
really treating it like a source and like challenging yourself with to come
like challenging yourself with to come up with questions to answer that ask the
up with questions to answer that ask the data and then using some of these basic
data and then using some of these basic skills we covered to try to answer them
skills we covered to try to answer them and even if you don't have writing a
and even if you don't have writing a story just that practice of like finding
story just that practice of like finding the data on the beat
the data on the beat asking a question answering it is really
asking a question answering it is really just how you can start to strengthen
just how you can start to strengthen your muscles and kind of get more
your muscles and kind of get more comfortable
comfortable with this kind of approach
i actually have one more question and i hope it's not too personal
hope it's not too personal um
um where's pale wire from what what was the
where's pale wire from what what was the inspiration for that
inspiration for that this is my online handle so like
this is my online handle so like yeah so like i'm old enough you know i'm
yeah so like i'm old enough you know i'm gray-haired enough that like i i joined
gray-haired enough that like i i joined the internet in the early 1990s
the internet in the early 1990s when it was still
when it was still it was maybe even considered cool as
it was maybe even considered cool as weird as that it sound to have like a
weird as that it sound to have like a handle or a username that you kept
handle or a username that you kept and i think gradually over time it's
and i think gradually over time it's probably been a good development that
probably been a good development that people just use their names you know um
people just use their names you know um but i've kind of just always been
but i've kind of just always been attached to mine because i feel like
attached to mine because i feel like it's kind of a marker of where i came
it's kind of a marker of where i came from and my whatever and it's also a
from and my whatever and it's also a little bit of an internet brand but it
little bit of an internet brand but it actually it's pretty pretentious you
actually it's pretty pretentious you know it comes from william shakespeare
know it comes from william shakespeare um so uh timon of athens is a you know
um so uh timon of athens is a you know shakespeare play that has a soliloquy
shakespeare play that has a soliloquy about a
about a the moon and it's uh the moon has a pale
the moon and it's uh the moon has a pale fire that and um the silicone is called
fire that and um the silicone is called each thing's a thief you can find it and
each thing's a thief you can find it and it's sort of commonly played upon by
it's sort of commonly played upon by vladimir nabokov and others through the
vladimir nabokov and others through the years for different things they do and
years for different things they do and and um i always i've always i thought
and um i always i've always i thought that this locally has a certain
that this locally has a certain resonance or
resonance or you could see its connection to
you could see its connection to journalism um if you read it
journalism um if you read it and uh i just sounded cool when i was 21
and uh i just sounded cool when i was 21 or whatever and so you know
so that's awesome i love that
that's awesome i love that yeah it's pretty nerdy my my wife's not
yeah it's pretty nerdy my my wife's not a big fan
a big fan but i do think you know this and this
but i do think you know this and this has been this has been a big thing in
has been this has been a big thing in journalism twitter maybe you guys are
journalism twitter maybe you guys are getting pulled into that world i'm sorry
getting pulled into that world i'm sorry about you know personal branding as
about you know personal branding as people call it you know and whether
people call it you know and whether that's a good thing or a bad thing and i
that's a good thing or a bad thing and i don't think there's really a simple
don't think there's really a simple answer but i do think it's true that you
answer but i do think it's true that you know if you're not someone who gets
know if you're not someone who gets lucky to get a big job right away or who
lucky to get a big job right away or who is like you know rocketed to the top
is like you know rocketed to the top making it into journalism you kind of
making it into journalism you kind of have to carve out your niche of like who
have to carve out your niche of like who you are and i mean part of that is
you are and i mean part of that is branding you might not want to call it
branding you might not want to call it branding you might not want to think
branding you might not want to think about it that way but i i you know who
about it that way but i i you know who are you as a journalist what do you do
are you as a journalist what do you do and so for me like that decision kind of
and so for me like that decision kind of coming out of mizzou and depaul was like
coming out of mizzou and depaul was like i'm a data guy and i'm an internet guy
i'm a data guy and i'm an internet guy and that led me to do more web
and that led me to do more web development and stuff that some of my
development and stuff that some of my peers didn't want to do but i saw as
peers didn't want to do but i saw as like a path forward in my career as
like a path forward in my career as opportunity
opportunity and then part of that was like keeping
and then part of that was like keeping my dumb internet handle right because i
my dumb internet handle right because i wanted to kind of just communicate
wanted to kind of just communicate it sounds stupid to say today but like
it sounds stupid to say today but like in 2005 to say like i am an internet
in 2005 to say like i am an internet journalist
journalist was like you were a weirdo you know what
was like you were a weirdo you know what i mean and like
i mean and like and and so there's part of me that like
and and so there's part of me that like that worked for me and there's also part
that worked for me and there's also part of me that feels like that's kind of my
of me that feels like that's kind of my roots in a way and i don't want to give
roots in a way and i don't want to give it up even though at this point it's
it up even though at this point it's pretty uncool
pretty uncool but i think generally having your like
but i think generally having your like thing of like this is my thing and your
thing of like this is my thing and your thing doesn't have to be a dumb internet
thing doesn't have to be a dumb internet handle it doesn't have to be acting like
handle it doesn't have to be acting like a fool and showing your ass on
a fool and showing your ass on twitter you know what i mean
twitter you know what i mean but
but it you kind of want to figure out what
it you kind of want to figure out what you know what is it your thing and you
you know what is it your thing and you don't have to know right away
don't have to know right away and like part of that might be redefined
and like part of that might be redefined over time but kind of saying this is
over time but kind of saying this is like kind of what i do
like kind of what i do and making sure you have your little tag
and making sure you have your little tag line that clearly communicates that and
line that clearly communicates that and your website that like clearly
your website that like clearly communicates that
communicates that and then you kind of beat that drum and
and then you kind of beat that drum and sadly self-promote because you know
sorry you know like it's just it's it's part of kind of making it not like i'm
part of kind of making it not like i'm any huge success story but like
any huge success story but like it does help people know what i do and
it does help people know what i do and they're like what's pale wire i'm like
they're like what's pale wire i'm like oh that's me i'm the nerd you know
oh that's me i'm the nerd you know and oh yeah the nerd yeah
well thank you so much for that explanation um if we don't have any
explanation um if we don't have any other questions we can go ahead and wrap
other questions we can go ahead and wrap up um perfect right on the dot right at
up um perfect right on the dot right at nine o'clock uh ben thank you so much
nine o'clock uh ben thank you so much for being with us tonight i know spj
for being with us tonight i know spj depaul and fort denise are so grateful
depaul and fort denise are so grateful for your time
for your time and one more thing before we go we do
and one more thing before we go we do want to plug that this workshop was free
want to plug that this workshop was free and open to the public
and open to the public but um we are a student journalism
but um we are a student journalism newsroom and we need funding so
newsroom and we need funding so if you got anything out of this workshop
if you got anything out of this workshop tonight and you have the means we'd
tonight and you have the means we'd really encourage you to donate to our
really encourage you to donate to our student newsrooms fundraiser um so we
student newsrooms fundraiser um so we can fund more engagement events and
can fund more engagement events and workshops like this in the future and
workshops like this in the future and grace has dropped the link grace is on
grace has dropped the link grace is on it grace dropped the link to the
it grace dropped the link to the fundraiser in the chat so
fundraiser in the chat so anything helps if you want to donate um
anything helps if you want to donate um but other than that thank you so much
but other than that thank you so much ben for being with us tonight
ben for being with us tonight thank you for having me and thank you
thank you for having me and thank you for staying up late on a school night to
for staying up late on a school night to get nerdy i mean i really appreciate it
get nerdy i mean i really appreciate it also i would just add if anybody wants
also i would just add if anybody wants to talk about a story or has questions
to talk about a story or has questions or just wants whatever um i mean please
or just wants whatever um i mean please feel free to reach out i'm going to put
feel free to reach out i'm going to put my email my personal email into the chat
my email my personal email into the chat um you know you know i'm not uh
um you know you know i'm not uh you know i'm happy i've got time i'm
you know i'm happy i've got time i'm happy to talk just feel free to reach
happy to talk just feel free to reach out and google me probably find my phone
out and google me probably find my phone number too if you want it you know it's
number too if you want it you know it's like so
like so um
um thank you again
thank you again go demons