Data Wrangling with Python and Pandas LIVE

Key Takeaways

hello everyone it is Tuesday November 15 2022 I hope you're doing well we're going to do some live coding tonight and we are going to mute ourselves so there's not an infinite Loop there we go let me know if the audio sounds okay and um yeah so we're gonna pick up where we left off last time and check out some of the data that we're looking at so let's test a chat test test okay now it's going through there we go hola welcome make sure you join twitch uh where I am streaming right now to join along with the main chat and I'm going to put that link here you can also join our Discord uh not for now but for later where you can post stuff ask stuff and all that stuff there's our Discord I think I'm pacing everything multiple times but let's with that let's go ahead and get into it so um if you all recall if you we're not around for our last stream that's fine we'll catch you up here but on our last stream I worked on a kaggle notebook this SEC data set creation so we created this live on stream where we um we created a notebook that gathered a bunch of data from this SEC government website which had a bunch of information about companies they're required to file I think quarterly and maybe even yearly about some of their financial information and it's they're all in this on this website a bunch of different zip files and we wrote a notebook that pulls all this data down from the URL downloads the zip files and then this is the downloading part unzips these files and then concatenate some of the results now this uh this version of The Notebook didn't run but let's view the latest version so this version did run but I also I wanted to actually get into this what's up xcode welcome to the chat I want to actually get into this on my local machine so we're not restricted to the speed and limitations of the kaggle notebook so I mainly disk uh issues that we're having with not having enough size um not having enough um disk space to unzip all these files so I ran it locally here you can see is my local Jupiter lab and I have this SCC creation notebook that I just basically downloaded the same version that we worked on here in the kaggle notebook and I ran this locally before I started streaming today so what did that do I ran it um with no filters so it ran on all the different years and now if I show you the directory where this is in let's start a new tab by the way I also pushed this notebook to my GitHub repository which I can link here let's link this I'll put this in this chat stunner99 welcome to the chat how are you doing tonight let's go so yeah it's in this twitch stream projects view code 52 and we'll push the another notebook here once we do it but this is what I ran basically on my local machine um and now I have all these different parquet files so presentation and also numbers numbers combined so numbers I think has the main information we care about and I guess also the tags and the submissions submissions like um I think that this is like the master list which links all the companies to the numbers that they file so let's look we're going to dig into this data a little bit more and try to clean it up but just to give you an idea of all the data that we have this is it and I probably could combine this all into another like I I wrote by year here presentation combined but these are small enough I probably could have just combined them all together so the problem was on the kaggle notebook it was running out of space hello ralc welcome to the chat njrod howdy um so it's not really that big of data to actually just combine all these together so let's try that first let's actually do this um so this the files on here are getting unruly because I have one folder for each year and quarter of data that exists and just to show you all what an example quarter example zip file from the SEC website looks like it looks like and that's like if you clicked here and you downloaded this ZIP file and unzipped it you'd have this num pre-read me sub and tag so we learned this last time the number actual numbers pre is the presentation I'm not sure exactly what that means sub are all the submissions that were made so like the filings uh tag I'm not sure what that means either um I think that's just tags but let's make a directory called raw files and let's move all the 2021 into the raw files make sure you guys can hear this or see this so I moved all those in now they're no longer oh yeah now the 2021s are no longer in there but let's move the 20 choose and 2019 all these files all these directories now the only ones that didn't move were was this 2009 which is the first year of data and now it's all done there now let's make a directory called parquets parquettes parquettes startup parquettes will move into that folder um and it didn't like that because it doesn't want to actually move the folder which is called parquettes parquets so let's move the numbers cannot stat wait what is it saying there we go that's what I thought it was going to do okay now they're all in the parquet folder and uh we're good to go now if we go into our notebook here folder it's nice and clean not so much stuff here and let's go ahead and call this SEC data wrangling and we're going to try to actually get this into a workable format where we would be very easily like last time we were looking at uh some of the results of this or some of the actual values we took Twitter as an example when we pulled um some of their filing numbers like the revenue and what else do we look at yeah Revenue basically and it was kind of hard to manipulate and understand what was going on it also looked like there might be some duplication of submissions like if there was to submit twice in a row then we would only see we would see both if there was to submit the same thing twice we see both so we only would want to see the second version of that and we'll kind of get into that um let me make sure I have my stream up here social dist what does that mean xcode thanks for um spamming with your emoji can you give us an opinion about Mike West the data janitor on quora I have no idea who Mike West is should I know who my quest is maybe I'll form an opinion in between this stream and next stream probably not though I have enough people to worry about I don't need to bring Mike Weston to this all right does this look better zoomed in a little bit all right let's go ahead and do some data wrangling we need to find like a can someone please um no that's not what I wanted to look at like a a cowboy let me do this in another another tab I needed someone to take like a photo like this one and photo find a photo of like a cowboy a Wrangler cowboy and Photoshop my face on it then I can use it as the thumbnail for this video afterwards if someone wants to do that I will um pay you and in thanks all right so all right so we're gonna go ahead uh and import from uh actually from glob we're going to import glob right we always do this and then we're gonna go into this parquets folder which we just created numbers and star this so I don't think it's that big of a deal this other notebook is actually running let's shut this one down let's shut them all done shut it all down all right start it over that way we know we know the other notebook isn't running in the background and taking up a bunch of memory that we don't use so these are our number files let's see how much memory is being used right now 12 gigs of my 62 so I think we're good those are pretty small files right can we just read them all in I think we could uh thing is are they no this is wrong I want to do read parquet within the f uh yeah memories getting out of control here all right so let's restart this so we went from like 10 gigs of memory being used all the way up to 30 some yeah now we're back down to 12. so what did I do wrong there uh let's start off by reading num files zero let's see how big this is in memory this is only 106 megabytes so I think it was like I was doing something weird with the way I was did that list comprehension in there so we'll do list comprehension like this so we'll read in each file 23 gigs now we have in this a list of data frames right each one with a data frame if you do a length on this we'll have 14 data frames and we want to concatenate these data frames I think is this where it's going to explode up it's growing do you guys see this let's do b top here um actually b top doesn't show that over time memory use is still getting up to 30 gigs we're good we're good so we're trying to take each Year's number file and concatenate it together to get a single data frame with all of our data okay there we go there we go you have it so the size of this big one is 10 gigabytes but that's exactly what we're expecting if we do a parquet uh sorry let's reset this in next right and then write this out oh it's the reset index that's taking up all the memory look at that oh crazy why would resetting the index take so much memory it's just because of the size of this there's so many values so can I just save it without resetting the index let's restart here um Kish kishimit uh welcome I'm Kish and I'm an aspiring data scientist currently student and I like anime soccer and good food nice I love those things although I'm not that into anime maybe I'll get into it later I'm nothing against anime so this reset index is what's really doing Us in do we need to reset the index probably not let's see if we can save the file just as it is what's data wrangling data wrangling is like okay so you have a data set that's a great question um so typically you'll have data just handed to you in the format and either which it's collected or in the format that someone's organized it for whatever purposes they have um in mind so if they're running an application a web application they might have a bunch of logs or if you're talking about like a production system maybe it stores a value every minute or something to a database somewhere but data wrangling is the idea of taking that data and manipulating it into a way to solve whatever problem you're working on so let's say that you're working on a problem where your boss says I want you to find every company's uh Revenue year over year and compare it to these five companies to each other that might be a problem we could work on tonight and we have a data set that's not in that format we're gonna have to Wrangle it into the right format so that's what we're doing here first I'm trying to Wrangle it by getting all the data into single file that's what I'm doing right now or a single data frame rather and then we're going to start to merge some things together can you see the data yeah so actually this is what we worked on on our last stream this notebook we created and if you look at this data tab so I'm going to send that link there if you go to the data tab you'll see the actual files the raw files in these folders so they're like CSV or text files that are tab separated and we already wrote the code to combine them together see why is this data size just exploding so you can see this numbers.par K file is right there 2.1 gigs okay now it's not growing so it did write correctly okay now let's restart this notebook and instead of doing all this stuff we'll just read from our combined file which is called numbers and why am I doing all this reading and writing to disk um I'm doing all this reading and writing because I want to minimize the amount of of uh space that we're using in memory on the machine so when you were working with like medium-ish size data this is a big big deal to make sure that your machine doesn't get overloaded I mean you could paralyze all this stuff and there's a lot of other different ways you can approach this problem but at least for trying to use pandas on a data set that's 11 gig in size and not only is it 11 gigs in size it's like very um it's very I guess sparse data I don't know how else to say it oh there's a lot of there's a lot of rows of data so this is like 125 million rows all right so now we have our numbers data read in we're also going to read in from our parquets folder the filings no it's called submissions all right okay so we'll see we see here with our combined submissions what's the size of this there's about half a million submissions that means that to the SEC between 2009 and 2022 hey someone made it stunner let's see this picture that you made hey there we go man this is better than I would have expected too my ear looks kind of bizarre but I love it look at this guy he's ready to sell some Wrangler jeans thank you so much stunner that's the community if you're trying to combine robust optimization with machine learning to reach well-suited worst case scenarios dark tools what are you talking about are you just generating buzzwords can you make a video on how to create stock database use wi Finance for data and uh you make a postgres database maybe I mean it kind of already exists out there what are you trying to do with the data it's not that large compared to what we're working with here I mean the S P 500 if you have 500 stocks you just have one row per stock per day basically if you have like the opening closing high and low so that data is kind of small-ish I guess it it could get large your PhD topic is buzzwords you're doing a great job uh uh all right so all this many submissions I do the same thing every Eda notebook same Imports but you know some things stay the same so what do we have here we have some addresses the filer the Ein which I think is the employment identification number former I guess that's if they change their name period for year and FP and then the instance number and then I also appended this with the file name that we pulled from all right so let's do name all right Federal Home Loan Bank of Boston so here's the thing with this type of this type of data and this is part of data wrangling is the name is not gonna the name I'm guessing when people fill out these forms and let's look at one of these forms so let's look at the form types sorry for that pause that's that pregnant pause right there okay so these are the top types of forms that are submitted so most of the forms were submitted are 10 cues then eight K's in 10ks and we'll look at what those forms actually look like but uh part of data Ringling which is what I was trying to get to saying earlier is that in their form they're going to put down the name of their company right this is a probably a free text field which means that if the company went in there in Wells Fargo company let's say they changed the way they instead of the Ampersand they they write out and for some of their submissions then by the way Wells Fargo is no longer a company right then you can't just Group by that name column because it's not going to work someone seriously asked why parquet Carol come on Carol parquet because a parquet is the greatest I have some YouTube videos on this twizzers thanks for subscribing have I used streamlit at all no I haven't I I'm a little skeptical is is streamlit real extreme lit pay people to make YouTube videos about them because there's so many YouTubes about videos about streamlit I have no idea what it is oh no I don't want to do this one I'm sorry I'm spinning this wheel for you by the way twizzers thank you for yeah I maybe I need to learn what's um streamless I don't want to do this one either I guess that's the deal with the wheel that's the deal with the wheel I've written Pizza almost 2 000 times so while I'm writing Pizza a hundred times Streamlight is an easy way to create data science stuff yeah but why streamlit I'm just a little weirded out by the fact that the only time I've heard about streamlit is through YouTube videos which I I don't know if they're paid promotions maybe we need to give it a second chance and I haven't actually watched those YouTube videos I just see that they exist why not write it in a for Loop because that would be cheating this goes way back to when I first I used to spin the wheel every time I'd get a new follower that's how big we've come and now we have twizzers so I'm not trying to I'm not trying to hate on streamlit by the way I'm just being transparent with my ignorance oh mousy thank you for subscribing on Twitch subscribing with prime that's a way to do it making me spin this wheel more so what are we gonna do with this data there we go 2 000 pizzas have been typed on this stream live for you entertainment okay someone else thinks streamlit does seem scammy do 10 push-ups I'm gonna do those at the end so before I finish I need to do 10 push-ups let me finish this thought though submissions name um cannot okay we can't trust this to be unique so let's go ahead and look at Subs Ein yeah that's thing I most of the time I see people using Dash so I don't see why we would use it the challenge here is to um we're wrangling some SEC filing data all right so let's Group by Subs Group by Ein and then let's take name and then unique all right so what do we see here what did I do here the Ein is the employment identification number and let's actually open up this readme so we can remember Ein employee and identification number nine digit identification number assigned by the IRS so this is like a basically the equivalent of a social security number but for a company so this number should not change um and I group by the Ein and I'm finding the the unique names for that Ein now maybe I should do n unique and now let's sort values okay so now we have some weird things one being that this 999 is like that's clearly not a real Ein zero is not a real i n e i n but then this one looks legit so let's see what's going on here Subs query Ein equals this so with this Ein the name so a o t a o t s 30 Inc like all these different names are different ways that that gets their filing for this one company it's a primarily business of blank checks for financial reporting sketchy no um but that's something to keep in mind like we can assume that these names I was trying to find an example where maybe they they just typed it differently but actually surprisingly and we can do actually a value count on the value counts and see that most of them have one name some of them have two let's find an example where there are two names for uh Ein so this Ein has two different names and I want to find this example all right success Entertainment Group maybe they changed their name to Ultimo over the time I don't know what's going on with this company but that's something to keep in mind and let's actually look and see where this EIN number occurs I can put this plot up here explore the submission data set this lists all the submissions made to the SEC and the form company names that's this plot this is where we're seeing like the number eins we're trying to find an example one all right so now this submission query is Success entertainment name and let's sort by I'm assuming there's some sort of date column this is the accepted date so Subs and we're gonna sort by the accepted data frame has oh values so now we've sorted by the the accepted date so it should I guess be in the order in which it was submitted it looks like these were all submitted between 2014 and 2015. and let's see it looks like for their first two filings they were altamo group and then they changed their name to success entertainment so yet another reason why we would want to see I guess if there's also a merger hey Shelby welcome to the chat freeze oh yeah I just froze because I was reading chat sorry um so okay there we go the EIN number can be different than the name and that's about it let's look at one of the forms that we want to see so we did look at this the number of forms that there are it looks like the 10q scc10q is the main form that we're finding here and let's read about this form these are legal requirements for companies in the U.S form 10q shall be used quarterly I think that's what the Q stands for quarterly for reports under Section a bunch of this is all law this is all like required by law according law is pursuant so they have to submit these 40 days after the end of each fiscal year um and that's probably why we see the most of these so maybe we could focus in on these 10q form and let's see what sort of things they have to they have to give out all right so these are the different parts within the annual report I want to actually see the 10q all right so probably what's going to be the easiest is to take let's do this to get it to get an idea of what's going on with this data Let's Take Five companies five companies tell me in chat the names of five companies we're gonna explore this subset of this data to understand it from end to end at least somewhat we're going to pick five companies and just filter it down to those okay we got Microsoft number one K okay okay you guys are going too fast I love it uh what's KPMG okay it's it's a legit company uh we got Nvidia someone put Tesla Twitter Coke I know we're over five but b b w i Intel oh that's a consulting company GameStop Intel GameStop I Sid what's what's icid another Intel pltr I don't know what this one is it's not this International Commission of irrigation and drainage and pltr plantar Technologies okay so we actually need to get the full names of the company's um so we're going to take this name column and let's go ahead and make a list of this of this stuff that you guys gave me we're actually going to just pull the name part of it not my little dashes right and we're going to go like this we're gonna go like this we're gonna make these into oh didn't mean to do that we're going to make these into a long list of these foreign we're going to call this companies and we notice everything here is in uppercase right so we'll just make them all uppercase and then we're going to search um we're gonna just do a quick search 4C in companies so for each company we're gonna see where the names contain that company name and we'll print out here we'll print out what we're doing which company we're running for and then we'll print out the unique names that contain that company now we might run into some problems here with then company names that are generic I guess none of these are generic enough um why is it saying there's no names oh that's right so this Subs contains is just going to give us true and false right so then we actually want to slice based on this and then we'll take the names out of that and print the unique names okay you guys see what we're doing here you see what we're doing here how do you set the multi cursor so quickly so hold down Alt and then you can select like this which actually gives you a new cursor in each line and you can do some cool stuff with the lines that's one thing I actually like about Sublime Text too if you're in Sublime Text and let's do of course that I say I like Sublime Text and then it quiz quits on me um Okay so if we have this list in here in Sublime Text and we select here we do Ctrl shift L and now we have a cursor on each line and we can do stuff that's nice using those multi-cur and then you can do that in Vim too like with other keyboard commands but saves time so what do we do here we vs code does it too yeah I guess I just don't know how to do it in vs code um so IDE I'm using right now is Jupiter lab check out my video on my Channel about the ultimate data science setup AKA just my data science setup where I talk about everything that I'm using right now um so we could see that we matched Microsoft with its name only perfect Nvidia perfect Tesla perfect Twitter oh wait Tesla has two so Tesla has two Tesla Inc and Tesla Motors Coke this is where it starts getting a little bit complicated SCC filing for Coke Coca-Cola oh yeah that's right it's gonna be Coca-Cola okay we also need to do two to list because we want this to be a list and actually now I'm thinking I don't want to append it I want to take this names and I want to do it add which will just concatenate the two lists uh sorry plus equals there we go now we have a list of the company names that we want to focus in on got it uh pltr is plantar let's try to add that Palin oh palantir there we go what is palantir a big data company okay I'm saying that right right sintel Corp oh yeah that's not intended here's the thing instead of doing contains we could do starts with um that should get rid of that sintel right so these all actually start with Microsoft Nvidia Twitter yeah Coca-Cola now only thing we have to deal with is the Tesla being duplicate in multiple Coca-Cola but I think we're good sorry guys we're done taking names of companies we're we're past that okay so Subs uh so we're gonna say is in our names all right so here are here should be this should be just the submission files for these companies that we have subset to GameStop the world I wanted to get a what are we doing we're continuing etsw with our um SEC data so we're picking we picked this subset of companies we're filtering down all the SEC submissions to that subset of companies so we could get a better idea of this data what's going on with the data and then I think since we found here that these 10q form is required to be submitted we'll filter down just to the 10 cues now we have these adsh numbers which I think are like our unique key that we can match let's look at adsh we've looked at this last time this is the session number the 20 character string form for the 18 digit number signed to SEC during Edgar submission so I think this isn't unique per submission I mean we could double check this by doing a value counts on this right all of them are one now we want to do now we have us we made a list that's a subset of all these a d s h submissions let's look at the length of this 292 submissions um these are only 10 Q submissions um yes I will post this I'll push this code to GitHub which I've um also put in chat someone in chat was asking that that's the creation data set but I'll push the this one also and the cik what's the cik what are you asking out Mr beige cak unique company identifier okay is that in here okay so I guess I I assume that s that Ein was the best oh this by the way this is a great picture thank you for making it um cik this is going to be Central index key tangent number assigned to SEC for each registrant that submits file filings does that mean that if one Regis registrant is filing for multiple companies then you could have multiple ciks I guess we don't know yet let's do Subs Group by cik name unique it does look like this is at uh nope nope so this is one so Tesla Inc and Tesla Motors has maybe they changed their name so this is a great thing we can look at query cik equals this Tesla number and let's see if they're if their name just switched around a few years um there we go um sort values by the accepted date and let's look at the name let's just see a list okay so it started out as Tesla Motors and then changed the Tesla ink so I think this cik you guys are on to something the cik is a good thing no ciks like eim both signed by the SEC rather than the IRS it never changes even when a company changes name Ben Lynch thank you so much and we've found this out too with this data that Tesla Motors must have changed their name let's look at the Tesla Wikipedia page yeah look Tesla Motors redirects here's formerly Tesla Motors until February of 2017 then they changed their name look at that the data is correct well I guess I don't know if the data is correct I just know the data is supporting our assumptions which could be totally different than the data being correct Okay so what do we got here we got some data to Wrangle or the Wikipedia is wrong true true I trust Wikipedia though I donate to Wikipedia if you if you have the financial needs donate to Wikipedia it's worth it I don't know if it's worth it but it's it's a good thing to do so maybe what we could do here instead of trying to think yeah so probably taking the cik number no because we only want to pull the 10q filing numbers so I do think we want to do on adsh is what we want to actually pull from numbers so numbers if you remember has so this is like a pivoted the way I understand it at least it's like a unpivoted table with each form that was submitted and then it'll have a column with the tag the tag represents I believe the tag represents like the field in the form that they filled out and then we have a value for each so in each form like you know when you fill out your tax form your 1099 or whatever it is I haven't done taxes in a little bit but they're taking each form is going to have like a repetitive a new row for that data and then just have a different value but then there's also repeats we found so we're gonna have to figure this out and that's why exactly why we're we're doing this so we're doing we're gonna copy this over into like a subset data frame this is a large data frame so I'm keeping my eyes on the memory use as I do it but it looks like it was fine and let's see the shape of this so adsh subset so remember the length of the number of subsets or sorry the number of SEC filings we have from this list of companies which you all gave me um the number of actual filings going back to 2009 to the SEC every quarter I believe the these 10 Q filings there's 292. now in the numbers data that's represented by over a hundred thousand rows just let that sink in okay not it's not that crazy but oh you didn't know about the at symbol with query values you need to watch my YouTube video by the way a little bit of a plug here exclamation point YouTube If you're not subscribed yet on YouTube click that ink watch this video 25 newbie pandas mistakes I go over that using the at symbol um yeah check out that video I know you're subbed um Gabriel butt and then also exclamation point Discord join our Discord and also follow me on Twitter but I don't promote that enough I tweeted this today because I saw it on Reddit a path of 800 unmanned bicycles being pushed until they fall over this is beautiful data right there that's beautiful okay back to our numbers so what I want to see is this tag and see how many repeats net income loss operating income loss earnings per share diluted let's get like the first 10 fields and let's let's pick a subset pick a subset of fields to focus on so net income loss I think that's a big one gross profit cash being carried and we're going to compare these different companies assets I don't know what like the difference between assets and assets current means stockholders Equity that that might be interesting to look at any of these stand out to you guys these values would be very different between companies yeah yeah earnings per share is that oh what's the difference between the diluted and basic I'm feeling basic I'm a basic kind of guy I'm a basic data scientist there we go okay so we're gonna take this current assets are short-term assets that are typically used up in less than one year all right we're just going to take this subset and poof how are we gonna do it let's just pick one query tag equals net income loss so now we have all of the income loss we're gonna have to figure out which quarter filing this was for each company quarters three D date hmm are we gonna have to merge this back on to this Subs does Subs because Subs is gonna have maybe some more information about that submission right like what you're gonna find the accepted date FY should be four year right oh I think this period oops um all right so what am I looking at here I'm just trying to see the submissions do come in around the same time each year that's what we're seeing here and there are more of them recently I think because yeah so I think we can take this period and go off of that I think that's a good field that we can map onto here so we could do this a few different ways we could take numbers and we can merge on Subs because a period value does not like the period that the submission is for is not in our num data set so we could take this merge this on ads ad sh and the period of Subs underscore we can validate that it's a a many to one merge and it looks like it's the same sh size as before so now this just has numb just has a new column on it now another way we could have done this is taken sub since it was just one folder that we wanted to sorry one column that we wanted to add we could have taken adsh from our subs data fret data frame and then just mapped this on num this would do the same thing it basically like take our a dsh column and then use this other subset data frame where our index then would be that adsh and map on the period all that's to say we could do things multiple ways all right so now our net income loss let's oh we also wanna let's also hmm I'm thinking now I want to also map on the company name and some other stuff so let's take this I'm trying to think how to do this so what wrangling's all about this and this together also want some other stuff from Subs so let's do name because that'll make it a little bit easier to understand what's going on with the companies oh we want the cik because that's the unique thing right that's what I was thinking I think that's good enough okay uh because different things have different physical count physical calendars you might need to use a q1 q1 to compare them yeah let's see how it goes my mic isn't working is my mic not working someone in chat said that EPS earnings per share you can hear me okay it scared me there for a second someone could just screw up the whole stream the same ice mic doesn't work no it's okay it's cool yeah unmute to unmute the the volume bar on the screen all right so we have net income loss for all of these companies and now we have the name of the company and we have the period I want to take this period and convert it to a date in our format is we did this last time year month day there we go um let's Group by the name it's gonna screw up for Tesla but that's fine and then oh we're gonna have to set the index first set index as this period I don't know if this is gonna work guys taking a risk here ah what do we see here I think this is what someone was talking about before that they have different physical calendars but it actually looks pretty spaced out right this should be where that when they are um let's also add a legend [Music] all right we gotta pick just one of the Coca-Cola cos uh day month year as in the rest of the world yeah I I didn't choose it to be in this format I don't know why they did it and I think they're doing it because they're trying to keep track of like what period that it was submitted um so I can't the thing is as we've noted the name of companies might change and there it looks like there's multiple coca-colas all right I I know this might take a little longer but let's just filter down Coca-Cola down to yeah this makes more sense so now we only have Coca-Cola company I don't know I think that's the main one um rerun this let's just put all this together number of filings doing a little bit of cleanup here then we'll put here cik unique per company note that Tesla changed their name but the cik stays the same that's what we're trying to show here excuse me um subset that data to number data to these companies 10q filings explore the tag values for 10 Q that's what we did there we picked a subset of fields to look at we only look at the first one here we don't need this plot anymore we can do redo this merge while that's merging I'll make a figure here and what do we got here purple is Microsoft Microsoft and what is this that we're filtering down to um so this is plotting them all together but it's a little bit much so let's let's actually iterate over each one B box to Anchor I'm gonna put this outside the plot there we go and that's a little wide of the plot the three Coca-Cola companies are in fact different companies not just different names okay yeah that makes sense because they each have their unique cik and we've learned today that cik numbers should be uh yeah xcode that's exactly it so those were three different companies so they were showing up as three different cik numbers the only company we saw with different names with the same cik number was Tesla and Tesla Motors so we're gonna have to fix that that's what I want to do next um let's Group by cik and take the name last this will give us the last name that a company had um and then I'm gonna take now we're going to do the mapping now we're going to map that name into a new column called name latest what will this do this will allow us to now um actually if we had done this before it would have been better data wrangling and if we grouped on this name latest if you look here closely Tesla is this greenish and then Gray you can see that Tesla switched names here so it's like this green doesn't appear here to the left just because it had a different name all right so that's pretty cool I don't know why there's a big gap here at the new year do they not file in the new year I thought it's quarterly filings so shouldn't there be four per year I think I only see three lines per year something's weird here oh is it because they don't at the FI at the end of the year they do a different filing that could be it so maybe they have to do 10 Q each quarter Let's test that hypothesis if they did if they do 10 Q three quarters out of the year and then a 10K a Bentley Lynch in chat you're already on top of this and then they do 8 10K the fourth time then I also should include the 10K I think this is the great part about writing this in code and not doing spreadsheets now we can just do form in 10 Q or 10K when we do this subset now we have 352 filings so we've increased the number of filings but we can just run this code again Dash a are amended filings ooh I'm gonna have to keep an eye out for this we're gonna have to figure that out when we get it to each one where does the dash a appear maybe in the subs data column or data Source there we go now we see four lines see look we visualize the data saw something that didn't make sense then Ben in chat told me what the reason was but we had already kind of figured that out before he told us like adds a suspicion of what the heck is going on and we only see three lines per year now let's go buy company now interesting thing here is I see some purple dots that are negative is that Microsoft yeah so since this is net company can make and lose money I think so um so now we're going to actually iterate over each name make sure this at least works is there a change than pandas where this isn't going to work anymore doing iterating over a group by that'll be a bummer if that's the case all right so what are we doing We're looping over each company and now we're getting like a slice of the data frame just for that company so we first have this company we first have Bath and Body Works we're going to take D we're going to set index to the period we're going to take the value and plot this maybe as like a line plot now huh why does that not look right uh we have some new people here so just uh imposter engineer you're the person you're the basis for us doing this imposter engineer In the Flesh in the chat welcome imposter engineer is there anything you want to say about this pqp best channel on Twitch I want to be a data scientist and this Channel's motivating nice my goal of my channel is to to inspire people to want to do data science you just got here okay so we're going through the the 10q and 10K filings of each company and trying to print out their income slash loss per year in each filing so I'm confused about why okay the other ones look a little bit more legit plan Planters kind of all over the place I guess because it's a newer company um whoa what's going on with Tesla okay so I'm a little bit weirded out by Bath and Body Works why have they only filed back in 2021 they've been a company forever have I looked at one HTML form I was trying to but that's a good idea let's try to see we looked this up yesterday when we were looking at a different I think we're looking at Twitter so it looks like most companies will put these on their websites so this is the Tesla submission from what time period is this September 30th 2022 so just recently a little over a month ago so cash current assets Okay so oh is this where like the revenues is going to be different for each form because other companies aren't going to have Automotive Sales hey we got Henrik in chat welcome how are you doing I did not get fired from Twitter by the way some someone was asking that in the other chat but we are looking at Twitter data so what the heck is going on here one thing that I'm noticing here maybe we could fix uh fixate we could focus on a subset of these companies because Bath and Body Works is is confusing me but these other ones look like the trends are pretty interesting um it's like all of Bath and Body Works submissions happen on the same day and then the other ones look like it has I don't know what's going on here Microsoft like had some really negative numbers Tesla went negative and then went up and their income and loss could these be because of Amen amendments like why shouldn't they only have a single let's do this group uh let's query where the name latest is GameStop I think it was like this and then let's also look at this tag net income loss I think we're getting multiple fields for the same submission so in theory if we sort by the period and let's just subset these columns so we know that the name latest should be game stat stock tag period quarters D date that's the submission date I think and then the value let's just look at those columns so these are for the same period we have multiple Rose and the values are very different oh is it because it's showing prior quarters quarterly includes usually two net income one current one previous year same quarter oh so it's like built-in lag features here where it's showing the previous year it's showing current year and prior year and also cumulative year to date for current year and year to date for prior so how do we filter down this num data such that we only get the latest so these are now I'm going to just show all the tags so quarters should be equal one okay so now I'm learning and quarters equals one well wouldn't that only give the first quarter of the year when maybe we want the oh but that kind of explains why we see every year it going up and up and up yeah so it's increasing every year because it's a cumulative count throughout the year but you're seeing quarters equals one and let me also go into here the count of number of quarters represented by the data value rounded to the nearest whole number zero indicates it is a point in time got it so quarters equals one makes total sense so we're only looking at the last quarter of data totally makes sense now so let's rerun this these plots okay well I guess that made no data oh I put this all in the quote that was my comment in Discord you notice equals one oh you figured this out already Microsoft's got this nice little s shape but I'm still a little bit weirded out by why Microsoft has these very negative values So in theory I think we should only have one value now per submission but we don't so it looks like we still have two values for net income loss for GameStop over the years um do I have some recommendations for audio comparing audio I don't off the top my head Maybe librosa uh people talking about taking down the establishment I'm just trying to teach and learn data science current year and previous year oh so how do I know if this is giving just I want the current Year's version right there's no is there another field that'll tell me which one I'm getting oh so then there's a version so this is the same D date the same submission it's weird like they don't okay the D date is different what is the D date end of the date for the data value rounded oh so I need to be using D date so I need to be using D date not the period and again this weird format where they do year month day now if I do this query and I group by D date and let's look at the unique values oh just value okay so here look this is GameStop people this is GameStop look uh I just side side note yeah this is a weird format right now but we're gonna get it so this value like for for example let's take here 2010 1031 they must have filed four different times for this period their net income loss and it changed each time slightly or I guess not slightly this looks like a lot but here it changed just a little bit so this was like I guess an amendment so it's almost like we need to take the last value if it's sorted correctly the last value should give us their income for that quarter all right let's do some interactive plots there we go okay so now we have this is GameStop's net income loss in their SEC filings is this right am I missing something here in October 2012 what happened to GameStop big Larry welcome to the stream good to see you too I'm so happy to have you here can someone tell me what happened in October of 2012. by the way we're doing some data wrangling thank you chat for making this uh this photo of me as a data Wrangler giddy up people why did game stock go way down and then why oh it looks like they really lose money in the period right before am I reading this right or am I reading too much into this maybe they just had some up and downs I was trying to see if it looks like they kind of make their most profits right bef right after um New Year's it makes sense like the Christmas season is when people are um are buying games at darsh welcome to the chat how are you doing thanks for hanging out so I'm trying to figure out what happened in to GameStop 's reports third quarter results that's what this should be did they do some like shares diluting during the third quarter GameStop recorded Goodwill and other impairment charges of primarily as a result of fry okay so this is legit during the third quarter primarily as a result of finalizing a previously disclosed interim impairment test of the company's Goodwill and other I don't know what that means is due to a temporary decline in the company's stock price the charges primarily relate to GameStop's International reporting sediments I don't know if this is related to why we see this big drop here is this at GameStop let's see I can tell you the exact form foreign let's do this and then also query value equals less than negative 1 million um wait hold on negative 600 million just do 500 just to be sure so it should be this a d s h which is October 34 1st 2012 and this form that you gave me is October 27 2012. 27th 31st I don't know why they're different and then we want to see net income this parentheses around it means negative right negative six two four yeah I guess the people I think there was some weird thing with their stock that year or that filing because of something they had to dilute their stocks or something I don't know but we're learning about this we're also learning that they have animal they have this trend and then they um they just started hemorrhaging money about 2018 is when things caught up to them when they started to people started to realize hey we could just download games we don't need to go to GameStop and then January 2021 they actually made money that was the first year January 2021 they made 80 million dollars GameStop made a loss of 269 million in fiscal year 2012. yeah that checks out D minor as its progress in digital markets failed to offset the attendant costs and decline of physical retail but but that's been everything since 2012. this is an egg no negative 600. it's kind of crazy that they were above water all the way up to here and then it just kind of went off the rails for them all right so um let's do more companies um yeah let's do something like we were just doing where we plotted it out per company so we're going to do this plot multiple times we don't need to filter down to the company name anymore and we'll also put this company name here so something weird is going on here why is this this should give us this Group by D date value last and then we should plot it here maybe I need a show for plotly express no plotly Express cannot process wide form data with Columns of a different type okay yeah yeah I'm not giving it the right tip of data we're gonna set the index as D date wait hold on here that's right this is D2 let's get these plots out and then wrap it up here more important is cash flows if they have positive net cash flow okay so let's go look at cash flow I guess I can always do the this all right intel was going way up that why is until so far down now um let's let's look at cash flow instead foreign let's just do unique tags I'm guessing it's going to be like underscore cash flow in t all right cash flowed hedge gain loss oh there's so many different ones there's so many different cash flows what IDM using someone's asking about ID I'm using uh Jupiter lab if you check out my YouTube channel I have a um video on this on my setup I also have a video on Jupiter lab like an introduction if you're trying to get into coding data science stuff um wow nvidia's gone way up in their like they didn't take a hit at all during covid Tesla was losing losing money and then just this year boom I guess I'm looking at their net gain and loss and that could be net income loss that could be a strategy they're taking right um clipped welcome to chat we're working on SEC filing data we're doing some data wrangling I'm the Wrangler here and I'm trying to ring I'm the Wrangler now I'm the Wrangler now we're trying to just understand what's going on in this data and I think we're getting like I know like infinite amounts more about SEC filing data today than I did a week ago I could be on the show Yellowstone I want to watch that show is that good show so let's see what other stuff could be interesting uh oh we talked about them up here so I I don't know about the that tag you were talking about let's just look at cashing cash equivalents at carrying can we see something per something per share earnings per share yeah let's look at earnings per share earnings per share of these companies same plot ish I guessed earnings per share why is it saying the name is not defined oh yeah that'll be why it looks like these kind of follow the other trends more or less Twitter earnings per share so you telling me let's put a on each of these plots just to make it easier to see I just want to skip over this Bath and Body Works all right so for each of these let's actually put on this a line at zero so we can see when it's uh positive or negative oh it should be LS for line style and this should be a horizontal line and let's make the color Gray there okay so Coca-Cola always making money game stock was making money doesn't make money anymore look at cash and cash equivalents at the end of period okay let's look at that next Intel starting to make money or well always making money pretty much until recently what happened to Intel what happened to Intel in 2022 in August of 2022 Intel signed a 30 billion dollar partnership with Brookfield Asset Management to fund recent expansions all right so they're making some big Investments I think oh is this like the whole making chips locally and they're taking a hit purposefully for long-term gains I don't know I'm reading too much into this Microsoft is usually making money except for these parent dips it looks like Microsoft's due for a dip maybe M1 chips cut their profit I think it's more so that um yeah I don't know I don't know what it is I think is that they're trying to make chips in the US that is one thing I remember that came out recently or one in the U.S they came out with a new law about making chips overseas or actually uh selling certain types of chips overseas and Nvidia stock took a big hit because of that I remember hearing that in the news all right so Nvidia wasn't making money only early on but then really benefited from Bitcoin well or I don't know all the people all the stuff AI in in gaming and all that stuff and uh cryptocurrencies I think brought it way up in a cost earnings per share way up in 2018 and then it's kind of leveled off but still making money plan palantir not making money yet that's okay they're new Tesla started making money only recently Twitter golden years in the 2018s 2019s they were making money but this is like a strategy right oh Apple stopped buying Intel chips if this hit is just because of Apple that would be crazy for Intel they're like the most steady Growth Company out of the list that we chose all right so let's do a plot for um same plot for this last thing you guys were asking about what was chat asking about net income it is net income over the number of shares and earnings per share is similar to net income okay do you think not having a degree is an impediment for work as data science I think it's a lot easier to get a job if you have a degree but it's not it's not a requirement I mean except for the jobs where it's a requirement cash at end of period so cash and cash equivalent carrying value cash and cash equivalents increase period increase decrease that seems like good one first at carrying value this one you're saying I'm following you imposter engineer because this whole thing was your idea going into this data okay so let's what did I break here I broke something really bad oh it doesn't like the money money sign in here interestingly the tag why is it saying there is no query tag equals this there are values here there are values here too oh but then quarters is zero quarters should equal zero because we learned that we learned that this quarters will give you a one value if it's the and we have to learn this in the um uh here quarters is the count of the number of quarters represented in the data that's why we're filtering to one before but for this cash value it's like give me the amount of cash that you have right now and that value is going to be zero balance sheet is zero okay Coca-Cola so this is how much cash they're carrying why is why is GameStop have so much cash is that because they're pumping getting pumped money into their company and they weren't spending it on anything like they're not making new Game Stops so everyone's positive no one has negative money what is NVIDIA doing here everyone is loaded yeah this is one E10 so what is this is that 20 billion dollars am I cutting right I'm negative money nine is billion yeah so this 2 here is 20 billion that Tesla had Tesla almost bankrupt because of early low cash flows so Tesla kept their cash on hand really low is this kind of like what leads to bankruptcy similar to uh like we know FTX is in the news this week um like a company can vanish they go bankrupt because they don't have any money on hand to actually pay for the things they're doing and FDX was doing really sketchy stuff where they're basically like using their own money as collateral for Investments they're making if you can't pay your debt you end up closing shop okay FDX was a Ponzi scheme I don't know enough about it I don't know if anyone does yet except for the people running it I'm sure we'll find out that was a big one to hit um crypto world interesting to see these companies just increasing their cash there's something Elon likes about having cash on hand and Microsoft's just pretty solid all coins are a scheme yeah Oh you mean oh like Tech coins all right anything else about this you guys think is interesting hopefully we had fun here today now I did promise you guys thought I was gonna push this code so let's go up a few directories let's call this rank let's Commit This with a message of wrangling SCC data and push this push that to GitHub now if we go here to the twitch projects you can see here our SEC data and our data wrangling is right here I'm going to put this in the chat also try to put put it into the the notes in the Stream but we we just hit two hours of streaming let me know on Discord so again last plugs here Discord should definitely join if you have any questions or things that you want me to do on future streams let me know there the chat the live chat is not usually a good place to ask like a completely off-topic question because it's just I can't switch to answer one person's specific question but if you join the Discord that's a good place for you all to um to ask those types of questions and not that's not saying that I know all the answers but I can at least try I can at least try to look them up for UI you guys so um yes this is using restream so this is Discord um what else my Twitter you can follow me on there I'm gonna put that here YouTube I got the handle uh youtube.com so this is if you want to check that out if you're not already subscribed do that uh those of you watching on Twitch that's the best way to support me oh by the way we had the wheel spin earlier for a twitch subscription on on Twitch if you subscribe using prime it's free um I spin this wheel and this landed on 10 push-ups I'm I'm gonna be true to my word I'm gonna do my 10 push-ups right now all right look at that cool guy look at that cool guy doing push-ups and shorts I'm sorry you all had to witness that um so yeah I think I've promoted everything I want to promote and we're gonna raid some people on on Twitch please give this like a like share it on YouTube write a comment that helps get the algorithm algorithm to pick it up this is uh Apple watch I switched from I was like a Fitbit person for a long the longest time I switched that um switched into the Apple ecosystem that's not a proper push-up how dare you how dare you I had a stream once where I did like 70 push-ups I think and I I'll admit the end of that stream the push-ups were getting kind of uh a little bit lackluster but those right there were solid push-ups okay you guys are crazy let's go let's go over here to Twitch let's find someone to raid so and we're gonna send all the twitch viewers over somewhere let's browse software and game development people coding in Python time enjoyed is streaming Chrissy codes is doing Python programming do you guys have a preference hey from India hey what's up welcome this is live thanks for hanging out guys all right it looks like Chrissy has machine learning and NLP so we'll we'll give her a raid seems like up our alley and thank you all for hanging out I really appreciate it again I stream if I do stream they're on Tuesdays Thursdays and Sundays typically check out twitch where you can follow me and get the alerts you can also do the same thing on YouTube and Just support the community because we're just trying to hang out here learn and get better at what we do I hope you guys have fun and learn something new tonight I learned a lot of new stuff about SEC data that I didn't know I never cared to know before now I I do want to know more let me know on the Discord if you have any ideas for what more we could do with this data otherwise we might just move on I like starting projects thinking of interesting things we could potentially do and then hopefully letting you guys go with it from there right um tips and stuff go go to the Discord and ask me there that's a better place to ask I'm going to go ahead and set up this raid in about 10 seconds all of you in my twitch family are going to be going over to Chrissy hang around and don't just leave as soon as we join if you're going to leave go ahead and leave now and bring the energy bring the hype train and I'll see you guys next time appreciate it all right now for YouTube I'm out see you guys next