<jupyter_start><jupyter_text>We've seen a preview of how Pandas handles missing values using the None Type and NumPy NaN keywords. Missing values are pretty common in data cleaning activities, and missing values can be there for any number of reasons, and I just want to touch on a few of those here.For instance, if you're running a survey and a respondent didn't answer a question, the missing value is actually an omission. This kind of missing data is called **Missing at Random** if there are other variables that.. might be used to predict the variable which is missing. In my work when I deliver surveys I often find that missing data say interest in being involved in a follow-up study, often has some correlation with other data like gender or ethnicity. If there's no relationship to other variables, then we call this data **Missing Completely at Random (MCAR)**.So these are just two examples of missing data and there's many more. For instance, data might be missing because it wasn't collected. Either because the process responsible for collecting the data such as the researcher, or because it wouldn't make sense if it were to be collected. This last example is extremely common when you start joining DataFrames together from multiple sources such as joining a list of people at a university with a list of offices in the university (Students don't generally have offices but they're still people at the university). So let's take a look at some ways of handling missing data in Pandas.<jupyter_code>#So let's import Pandas
import pandas as pd
#Pandas is pretty good at detecting missing values directly from underlying data formats like CSV files. Although most missing
#values are often formatted as NaN, NULL, None, or N/A, sometimes missing values are not labeled so clearly. For example, I've
#worked with social scientists who regularly use the value of 99 in binary categories to indicate that it's a missing value.
#The Pandas read_csv() function has a parameter called na_values that allows us to specify the format of missing values. It
#allows scalar, string, lists, or dictionaries to be used.

#So let's load a piece of data from a file called log.csv:
df = pd.read_csv('datasets/class-grades.csv')
df.head(10)
#We can actually use the functions .isnull() to create a Boolean mask of the whole DataFrame. This effectively broadcasts the
#isnull() function to every cell of data:
mask = df.isnull()
mask.head(10)
#So this can be useful for processing rows based on certain columns of data. Another useful operation is to be able to drop all
#of those rows which have any missing data, which can be done with the dropna() function:
df.dropna().head(10)

#So note how the rows index with 2, 3, 7, 11 are all gone now. One of the handy functions that Pandas has for working with missing values is the filling function called fillna(). This function takes a number or parameters. You can pass in a single value which is called a scalar value to change all of the missing data to one value. This isn't really applicable in this case, but it's a pretty common use case.

#So if we wanted to fill in all missing values with zero we could use fillna:
#So we just say df.fillna, we pass in a zero, here I'll just change the DataFrame in place. Remember most DataFrame operations returned copies of DataFrames. So if you want to do it in place you often have to use the in-place parameter
df.fillna(0, inplace=True)
df.head(10)<jupyter_output><empty_output><jupyter_text>So note that the in-place attribute causes Pandas to fill those values in line and it does not return a copy of the DataFrame, but instead modifies the DataFrame that you have. We can also use the na_filter option to turn off whitespace filtering. If whitespace is an actual value of interest, but in practice this is pretty rare. In data without any NAs passing na_filter=False, can improve the performance of reading a large file. In addition to rules controlling how missing values might be loaded, it's sometimes useful to consider missing values as actually having information. I'll give an example from my own research. I often deal with logs from online learning systems. I've looked at video use in lecture capture systems. In these systems, it's common for the player to have a heartbeat functionality, where playbacks statistics are sent to the server every so often, maybe every 30 seconds. These heartbeats can get big as they carry the whole state of the playback system. Such as where the video play head is at, to where the video sizes at. Where the video is being rendered to the screen. How loud the volume is and so on.<jupyter_code>#So if we load the data file log.csv we can see an example of this: 
df = pd.read_csv('datasets/log.csv')
df.head(20)<jupyter_output><empty_output><jupyter_text>So in this data the first column is a timestamp in the Unix epoch format. The next column is the username followed by a webpage they're visiting and the video that they're playing. Each row of the DataFrame has a playback position. We can see that as the playback position increases by one, the timestamp increases by about 30 seconds.Except for user Bob. It turns out that Bob has paused his playback, so as time increases the playback position doesn't change. Note too how difficult it is for us to try and derive this knowledge from the data, because it's not sorted by timestamp as one might expect. This is actually not uncommon on systems which have a high degree of parallelism. There are a lot of missing values in the paused and volume columns. It's not efficient to send this information across the network if it hasn't change, so implementations rarely do. So this particular system just inserts null values into the database if there's no changes.<jupyter_code>#Next up is the method parameter(). The two common fill values are ffill and bfill. ffill is for forward filling and it updates an na value for a particular cell with the value from the previous row. bfills for backward filling which is the opposite of that fill. It fills the missing values with the next valid value. It's important to note that your data needs to be sorted in order for this to have the effect you might want. Data which comes from traditional database management systems usually has no order guarantee just like this data, so you have to be careful. 

#So in Pandas we can sort by index or by value. Here will just promote the timestamp to an index and then sort on the index:
df = df.set_index('time')
df = df.sort_index()
df.head(20)
#If we look closely at the output though we'll notice that the index isn't really unique. Two users seemed to be able to use the system at the same time, and again this is actually a common case. So let's reset the index and use some multi-level indexing on time AND user together, and promote the username to a second level index to deal with the issue.
df = df.reset_index()
df = df.set_index(['time','user'])
df
#Now that we have the Data indexed and sorted appropriately we can fill the missing datas using ffill. It's good to remember when dealing with missing values so that you can deal with individual columns or sets of columns by projecting them. So you don't have to fix all missing values in one command:
df = df.fillna(method='ffill')
df.head()

#We can also do customize fill-in to replace values with the replace() function. It allows replacement from several approaches. Value-to-value, list, dictionary, regex. So let's generate a simple example:
df = pd.DataFrame({'A': [1,1,3,4,5],
                   'B': [3,5,4,6,7],
                  'C': ['a','b','c','d','e']})
df

#We can replace one's with 100, so let's try the value-to-value approach:
df.replace(1,100)

#How about changing two values, so let's try the list approach. For example, we want to change 1's to 100 and 3's to 300:
df.replace([1,3],[100,300])
#So what's really cool about Pandas replacement is that it supports regex too. So let's look at our dataset again from the logs.csv:
df = pd.read_csv('datasets/log.csv')
df.head(20)<jupyter_output><empty_output><jupyter_text>To replace using regex, we make the first parameter to replace the regex pattern that we want to replace. The second parameter the value that we want to emit upon a match and then we pass in a third parameter "regex=True". So take a moment to pause this video and think about this problem: imagine that we want to detect all HTML pages in the "video" column. Let's say that this just means that they ended with ".HTML", and we want to overwrite that with the keyword "webpage". How could we accomplish that?<jupyter_code>#Here's my solution. first, matching any number of characters then ending in .html:
df.replace(to_replace='.*html$', value='webpage', regex=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.05202048947068867, 'fraction_numerical': 0.006715993170176437, 'mean_word_length': 4.477556109725686, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '343302', 'license': 'MIT', 'n_tokens_mistral': 2249, 'n_tokens_neox': 2121, 'n_words': 1426, 'path': '9. Missing Values.ipynb', 'repo_name': 'souvik-97/2._Basic_Data_Processing_with_Pandas', 'size': '8785'}