<jupyter_start><jupyter_text>Common Messy DatasetsThe previous notebooks focused on one particular type of messy dataset. A dataset where the column names are actually variable values and not variable names. This was illustrated with the dataset on arrival delay. The `melt` method will quickly tidy these basic datasets. But, often is the case that datasets take more manipulation to make them tidy. This notebook covers several more common messy datasets. Most common messy data problems1. Column names are variable values, not variable names.1. Multiple variables are stored in one column.1. Variables are stored in both rows and columns.1. Multiple types of observational units are stored in the same table.1. A single observational unit is stored in multiple tablesThe first type of messy data was covered in the previous notebook. This notebook will cover the next three examples.  Multiple variables are stored in one columnA tidy data set requires that values of a single variable are stored in one column. Column names appear as values in a columnTake a look at the dataset below. Notice how the `Value` column has both numeric and string data types and the `Info` column contains variable names.<jupyter_code>import pandas as pd
df = pd.DataFrame(data={'State': ['Texas', 'Arizona', 'Florida'] * 3,
                        'Info': ['Age'] * 3 + ['Salary'] * 3 + ['Hair Color'] * 3, 
                        'Value': [10, 15, 20, 3, 4, 5, 'Brown', 'Pink','Red']},
                 columns=['State', 'Info', 'Value'])
df<jupyter_output><empty_output><jupyter_text>The fixThis dataset has three variables in a single column. You can think of it as 'overly melted'. Pivoting it with the **`pivot`** method will make it tidy.<jupyter_code>df.pivot(index='State', columns='Info', values='Value')
df_tidy = df.pivot(index='State', columns='Info', values='Value').reset_index()
df_tidy = df_tidy.rename_axis(None, axis='columns')
df_tidy<jupyter_output><empty_output><jupyter_text>Checking data typesWhenever we have a mix of variables in a single column, you might also have a mix of data types. It's important to check the data types after reshaping the data.<jupyter_code>df_tidy.dtypes<jupyter_output><empty_output><jupyter_text>Changing data typesBoth `Age` and `Salary` should be integers but instead are objects. We need to change their data types. We have seen this previously with the function `pd.to_numeric`, but you can also see use the `astype` method. They both do nearly the same thing, except `pd.numeric` gives you more options (which were needed in a previous notebook). We will use each here.<jupyter_code>df_tidy['Age'] = df_tidy['Age'].astype('int')
df_tidy['Salary'] = pd.to_numeric(df_tidy['Salary'])
df_tidy.dtypes<jupyter_output><empty_output><jupyter_text>Two or more values are stored in the same cellTwo or more values of the same variable or different variable can be stored in the same cell in a DataFrame. You will need to extract the desired quantities which might necessitate regular expressions. Let's take a look at a dataset with multiple variables stored in a single cell.<jupyter_code>geo = pd.DataFrame({'City':['Houston', 'Dallas', 'Austin'], 
                   'Geolocation':['(29.7604° N, 95.3698° W)', 
                                  '32.7767° N, 96.7970° W', 
                                  '30.2672° N, 97.7431° W']})
geo<jupyter_output><empty_output><jupyter_text>Identify the VariablesThe first step in tidying data is identifying the variables. The `Geolocation` column has quite a lot of information packed into it. We will parse it into 4 separate variables.* latitude * latitude direction* longitude* longitude direction  Extracting information with regular expressions using the `str` accessorThe `extract` string method takes a regular expression with **capture groups** and returns each captured group as a new column.Our regular expression has 4 capture groups. One for each variable. ```([0-9.]+).*?([NS]).*?([0-9.]+).*?([EW])``` My explanation* `([0-9.]+)` - This is a capture group that matches one or more of the digits 0-9 and the literal `.`* `.*?([NS])` - Matches any number of characters in a non-greedy fashion before capturing N or S.* The pattern then repeats to capture the longitude and direction<jupyter_code>geo_extract = geo['Geolocation'].str.extract(r'([0-9.]+).*?([NS]).*?([0-9.]+).*?([EW])')
geo_extract<jupyter_output><empty_output><jupyter_text>Column namespandas defaults the column names of the resulting DataFrame to integers. We would like these new columns appended to our original DataFrame. Creating multiple new column namesIt is possible to create several new columns in our original DataFrame by simply assigning the above resulting DataFrame to a selection of new column names as a list.<jupyter_code>geo[['latitude', 'latitude direction', 'longitude', 'longitude direction']] = geo_extract
geo<jupyter_output><empty_output><jupyter_text>Dropping the original columnWe can remove the **`Geolocation`** column as we have finished processing it.<jupyter_code>geo = geo.drop(columns='Geolocation')
geo<jupyter_output><empty_output><jupyter_text>Check and change Data TypesBoth latitude and longitude are clearly supposed to be numeric (floats) but since they were extracted from a string, remain as strings. Let's change them to float.<jupyter_code>geo.dtypes
geo['latitude'] = geo['latitude'].astype('float')
geo['longitude'] = geo['longitude'].astype('float')
geo.dtypes<jupyter_output><empty_output><jupyter_text>We now have tidied this dataset which had multiple values stored in a single cell.<jupyter_code>geo<jupyter_output><empty_output><jupyter_text>Variables are stored in both rows and columnsA more difficult situation occurs when variables are stored down a column and across the column names. Pivoting and melting may have to be used together to make it tidy. Let's take a look at the example below.<jupyter_code>tfp = pd.read_csv('../data/tidy/temp_flow_pressure.csv')
tfp<jupyter_output><empty_output><jupyter_text>Identifying the VariablesIdentifying variables in this dataset is not as straightforward as it is in others. There are variable values stored across multiple rows and variable values stored as column names.We can use the following as variables:* Group* Pressure* Temperature* Flow* YearsThe years are column names and the pressure, temperature, and flow are values in the property column. The Group column is the only one in the correct place.  Melt the yearsTidying this particular dataset must happen in multiple stages. We won't be able to tidy each variable at the same time. We will begin by melting the year column names into a single column.<jupyter_code>tfp_melt = tfp.melt(id_vars=['Group', 'Property'], 
                    value_vars=['2012', '2013', '2014', '2015', '2016'],
                    var_name='Year')
tfp_melt.head(10)<jupyter_output><empty_output><jupyter_text>Need to pivot PropertyWe now need to pivot the **Property** column so that the values become column names, and keep Group and Year as columns. The values will come from the `value` column. Problem! `pivot` only works with a single column as the indexIf we try and pivot by passing a list of values to the `index` parameter, we get an error. Pandas actually thinks we are using the `['Group', 'Year']` not as column names but as values to pivot.<jupyter_code>tfp_melt.pivot(index=['Group', 'Year'], columns='Property', values='value')<jupyter_output><empty_output><jupyter_text>Must use `pivot_table`The `pivot_table` method does allow us to keep multiple columns in the index. Multiple aggregation functions produce the same result as there is only one value to aggregate per group.<jupyter_code>tfp_tidy = tfp_melt.pivot_table(index=['Group', 'Year'], columns='Property', 
                                values='value', aggfunc='max')
tfp_tidy<jupyter_output><empty_output><jupyter_text>Verify that there is one value per intersectionLet's verify that there is one value per intersection.<jupyter_code>tfp_melt.pivot_table(index=['Group', 'Year'], columns='Property', 
                     values='value', aggfunc='size')<jupyter_output><empty_output><jupyter_text>Clean-up<jupyter_code>tfp_tidy = tfp_tidy.reset_index()
tfp_tidy = tfp_tidy.rename_axis(None, axis='columns')
tfp_tidy
tfp_tidy.dtypes<jupyter_output><empty_output><jupyter_text>Convert Year to integer<jupyter_code>tfp_tidy['Year'] = tfp_tidy['Year'].astype('int')
tfp_tidy.dtypes
tfp_tidy<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0934557438794727, 'fraction_numerical': 0.01059322033898305, 'mean_word_length': 5.082319255547602, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '166803', 'license': 'MIT', 'n_tokens_mistral': 2510, 'n_tokens_neox': 2360, 'n_words': 986, 'path': 'jupyter_notebooks/pandas/mastering_data_analysis/09. Tidy Data/03. Common Messy Datasets.ipynb', 'repo_name': 'manual123/Nacho-Jupyter-Notebooks', 'size': '8496'}