<jupyter_start><jupyter_text>Manipulating a dataset structureApplying some operations to change DataFrame strucutre and filtering content<jupyter_code>import pandas as pd
import numpy as np
tb_veic = pd.read_csv('data/dados_veiculos.csv')<jupyter_output><empty_output><jupyter_text>Como podemos ver abaixo, a tabela contém 15 colunas, sendo 9 numéricas e 6 de objetos. Além disso todas as linhas parecem ter todas as variáveis preenchidas.<jupyter_code>tb_veic.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 35952 entries, 0 to 35951
Data columns (total 15 columns):
 #   Column                   Non-Null Count  Dtype  
---  ------                   --------------  -----  
 0   Make                     35952 non-null  object 
 1   Model                    35952 non-null  object 
 2   Year                     35952 non-null  int64  
 3   Engine Displacement      35952 non-null  float64
 4   Cylinders                35952 non-null  float64
 5   Transmission             35952 non-null  object 
 6   Drivetrain               35952 non-null  object 
 7   Vehicle Class            35952 non-null  object 
 8   Fuel Type                35952 non-null  object 
 9   Fuel Barrels/Year        35952 non-null  float64
 10  City MPG                 35952 non-null  int64  
 11  Highway MPG              35952 non-null  int64  
 12  Combined MPG             35952 non-null  int64  
 13  CO2 Emission Grams/Mile  35952 non-null  float64
 14  Fuel Cost/Year        [...]<jupyter_text>Como podemos verificar o que as colunas com tipo 'object' contém?   Check DataFrame Column Names Rename all columns at once:- `data.columns` is an **attribute** of the DataFrame which results in a list-like of the column names    - You can substitute it by another list containing the names you want     - Note you have to substitute the whole set of column names at once    - `data.rename()` is a **method** of a DataFrame, in which you can rename one column at once    - You just need to pass a dictionary containing {'old_name':'new_name'}     - By default, it changes names of a **index** (`axis=0`), you can specify `axis=1` to change **column** names    - the `inplace` argument<jupyter_code>print(tb_veic.columns)
print(tb_veic.columns[2])
coluna_0 = tb_veic[tb_veic.columns[0]]
tipo_coluna_0 = coluna_0.dtypes.type
print(tipo_coluna_0)
tipo_coluna_0 == np.object_
tb_veic.columns
for column in tb_veic.columns:
    if tb_veic[column].dtypes.type == np.object_:
        print(tb_veic[column].describe())
tb_veic.describe()
tb_veic['id'] = tb_veic['Make'] + tb_veic['Model'] + tb_veic['Year'].astype('str')
tb_veic['id'].value_counts()<jupyter_output><empty_output><jupyter_text>Substituting `.columns` attribute<jupyter_code>print(tb_veic.columns)
old_column_names = list(tb_veic.columns)
print(old_column_names)
print([column.lower().replace(' ', '_') for column in list(tb_veic.columns)])
tb_veic.columns = [column.lower().replace(' ', '_') for column in tb_veic.columns]
tb_veic
tb_veic.columns = old_column_names
print(tb_veic.columns)<jupyter_output>Index(['Make', 'Model', 'Year', 'Engine Displacement', 'Cylinders',
       'Transmission', 'Drivetrain', 'Vehicle Class', 'Fuel Type',
       'Fuel Barrels/Year', 'City MPG', 'Highway MPG', 'Combined MPG',
       'CO2 Emission Grams/Mile', 'Fuel Cost/Year', 'id'],
      dtype='object')<jupyter_text>Using `.rename() method` `.rename({'old_column':'new_column'})`<jupyter_code>dict_nomes = dict()
dict_nomes['Year'] = 'model_year'
dict_nomes['year'] = 'model_year'
tb_veic = tb_veic.rename(dict_nomes, axis = 1)
print(tb_veic.columns)
tb_veic.columns = old_column_names
print(tb_veic.columns)
tb_veic = tb_veic.rename({'Year' : 'model_year'}, axis = 1)
print(tb_veic.columns)
tb_veic.rename({'Year' : 'model_year'}, axis = 1)
print(tb_veic.columns)
dict_rename = {column : column.lower().replace(' ', '_').replace('/', '_by_') for column in tb_veic.columns}

tb_veic = tb_veic.rename(dict_rename, axis = 1) #tb_veic.rename(dict_rename, axis = 1, inplace = True)
print(tb_veic.columns)<jupyter_output>Index(['make', 'model', 'model_year', 'engine_displacement', 'cylinders',
       'transmission', 'drivetrain', 'vehicle_class', 'fuel_type',
       'fuel_barrels_by_year', 'city_mpg', 'highway_mpg', 'combined_mpg',
       'co2_emission_grams_by_mile', 'fuel_cost_by_year', 'id'],
      dtype='object')<jupyter_text>So, we have two options:> 1. store it again on the variable `data`:     data = data.rename(columns={'Make':'Manufacturer', 'Year':'ANO'})> 2. Use the inplace argument `inplace =  True` to change the values within the dataframe automatically    data.rename(columns={'Make':'Manufacturer', 'Year':'ANO'}, inplace=True)    O parâmetro 'inplace' será deprecado e seu uso é considerado má prática.  Reorder columns in a dataframeRemember: You always pass a list of columns to access a dataframe<jupyter_code>print(tb_veic.columns)
tb_veic['model']
tb_veic[['make', 'model']]
lista_colunas = list(tb_veic.columns)
lista_colunas.sort()
print(lista_colunas)
tb_veic = tb_veic[lista_colunas]<jupyter_output><empty_output><jupyter_text>Remove column (or row)- The `.drop()` method- By default, `.drop()` drops a row given its index.<jupyter_code>tb_veic.drop('make', axis = 1)
tb_veic.drop(['make', 'model_year'])<jupyter_output><empty_output><jupyter_text>Deep vs Shallow copy on pandas<jupyter_code>copia_tb_veic = tb_veic
copy_tb_veic = tb_veic.copy(deep = False)
deepcopy_tb_veic = tb_veic.copy(deep = True) # comportamento padrão do método
a = [1, 2, 3]
b = [1, 2, 3]
a is b
a == b
c = b
print(c)
c is b
a = 1
b = 1
a is b
tb_veic_2 = tb_veic
tb_veic_2 is tb_veic
tb_veic_2 = tb_veic.copy()
tb_veic_2 is tb_veic<jupyter_output><empty_output><jupyter_text>Sort Values in a DatFrame<jupyter_code>tb_veic = tb_veic.sort_values('model_year')
tb_veic
tb_veic = tb_veic.sort_values('model_year', ascending = False)
tb_veic
tb_veic = tb_veic.sort_values(['model_year', 'engine_displacement'], ascending = [False, True])
tb_veic
tb_veic = tb_veic.sort_values(['model_year', 'engine_displacement', 'make'], ascending = False)
tb_veic<jupyter_output><empty_output><jupyter_text>Filter records>    - `mask` concept>    - `.query()` methodThis is really important for data wrangling.  Simple Example: Starting with a numpy array. How can I filter the values of a list?<jupyter_code>meu_array = np.array(range(1,10,2))
print(meu_array)<jupyter_output>[1 3 5 7 9]<jupyter_text>The results of `my_array > 5` is what is called **a mask**. A result containing the `True` and `False` results of an operation.<jupyter_code>print(meu_array > 5)<jupyter_output>[False False False  True  True]<jupyter_text>Masks can be used as an index to select data!<jupyter_code>print(meu_array[meu_array > 5])<jupyter_output>[7 9]<jupyter_text>After selecting, you can do anything with it, for example assigning it. This operation is called a `vectorial` operation. It is done all at once.<jupyter_code>meu_array_filtrado = meu_array[meu_array > 5]
print(meu_array_filtrado)<jupyter_output>[7 9]<jupyter_text>You can also save the condition<jupyter_code>num_gt5 = meu_array > 5
print(num_gt5)<jupyter_output>[False False False  True  True]<jupyter_text>Bitwise logical operators - Combining conditionsTo make more than one condition together, you can use - `&` - analogous to `and`- `|` - analogous to `or`  For example, get all numbers from my_array that are greater than 3 and smaller than 8 Let's do it in steps:- get values greater than 3<jupyter_code>num_gt3 = meu_array > 3
print(meu_array)
print(num_gt3)
print(meu_array[num_gt3])<jupyter_output>[1 3 5 7 9]
[False False  True  True  True]
[5 7 9]<jupyter_text>- get values smaller than 8<jupyter_code>num_st8 = meu_array < 8
print(meu_array)
print(num_st8)
print(meu_array[num_st8])<jupyter_output>[1 3 5 7 9]
[ True  True  True  True False]
[1 3 5 7]<jupyter_text>- get values greater than 3 AND smaller than 8<jupyter_code>num_3a8 = num_st8 & num_gt3
print(meu_array)
print(num_3a8)
print(meu_array[num_3a8])<jupyter_output>[1 3 5 7 9]
[False False  True  True False]
[5 7]<jupyter_text>- get values greater than 3 OR smaller than 8<jupyter_code>num_3a8 = num_st8 | num_gt3
print(meu_array)
print(num_3a8)<jupyter_output>[1 3 5 7 9]
[ True  True  True  True  True]<jupyter_text>Now in a DataFrame Let's find the rows in which the Cylinders values are exactly 6.<jupyter_code>cyl_6 = tb_veic['cylinders'] == 6
print(cyl_6)
sum(cyl_6)
#tb_veic_cyl6 = tb_veic[cyl_6] 
# mais abreviado seria:
tb_veic_cyl6 = tb_veic[tb_veic['cylinders'] == 6]
tb_veic_cyl6
tb_veic_cyl6.describe()<jupyter_output><empty_output><jupyter_text>You can combine conditions Cars from `Ford` and 6 `Cylinders`<jupyter_code>print(tb_veic.columns)
print(tb_veic['make'].unique())
True and False
print(tb_veic['make'] == 'Ford')
cond_ford_6 = ((tb_veic['make'] == 'Ford') & (tb_veic['cylinders'] == 6)) | ((tb_veic['make'] == 'Bentley') & (tb_veic['cylinders'] == 6))

print(cond_ford_6)
sum(cond_ford_6)
tb_ford_cyl6 = tb_veic[cond_ford_6]
tb_ford_cyl6<jupyter_output><empty_output><jupyter_text>Using conditions to create new columns<jupyter_code>tb_veic_2 = tb_veic.copy()
tb_veic_2.loc[tb_veic['cylinders'] == 6,'make']
tb_veic_2.loc[tb_veic['cylinders'] == 6, 'cyl_6'] = True
tb_veic_2
tb_veic_2[tb_veic['cylinders'] == 6]
tb_veic_2.loc[tb_veic['cylinders'] != 6, 'cyl_6'] = False
tb_veic_2
tb_veic_2.info()
tb_veic_2.describe()
tb_veic_2.loc[tb_veic_2['city_mpg']  < 15, 'eff_city'] = 'C'
tb_veic_2.loc[(tb_veic_2['city_mpg']  >= 15) & (tb_veic_2['city_mpg']  < 20), 'eff_city'] = 'B'
tb_veic_2.loc[tb_veic_2['city_mpg']  >= 20, 'eff_city'] = 'A'
tb_veic_2['eff_city'].value_counts()
p25_citympg = tb_veic_2['city_mpg']  < 15
p75_citympg = tb_veic_2['city_mpg'] < 20
print(p75_citympg)
tb_veic_2.loc[p25_citympg, 'eff_city'] = 'C'
tb_veic_2.loc[~p25_citympg & p75_citympg, 'eff_city'] = 'B'
tb_veic_2.loc[~p75_citympg, 'eff_city'] = 'A'
tb_veic_2['eff_city'].value_counts()
# np.where(CONDICAO, VALOR_SE_VERDADEIRO, VALOR_SE_FALSO)
tb_veic['cyl_6'] = np.where(tb_veic['cylinders'] == 6, True, False)
print(tb_veic['cyl_6'].describe())
sum(tb_veic['cyl_6'])
tb_veic['cyl_6_ford'] = np.where((tb_veic['make'] == 'Ford') & (tb_veic['cylinders'] == 6),
                                 True,
                                 False)
print(tb_veic['cyl_6_ford'].describe())
sum(tb_veic['cyl_6_ford'])
tb_veic['seila'] = tb_veic['cylinders'] * tb_veic['highway_mpg']
tb_veic.describe()
sum(tb_veic['city_mpg'] > tb_veic['highway_mpg'])
tb_veic['co2grams_by_gallon'] = np.where(tb_veic['city_mpg'] > tb_veic['highway_mpg'],
                               tb_veic['co2_emission_grams_by_mile']/tb_veic['highway_mpg'],
                               tb_veic['co2_emission_grams_by_mile']/tb_veic['city_mpg'],
                              )
print(tb_veic['co2grams_by_gallon'].describe())
tb_veic['eff_city'] = np.where(tb_veic['city_mpg'] < 15, 'C',
                               np.where(tb_veic['city_mpg'] < 20, 'B', 'A'))
tb_veic['eff_city'].value_counts()<jupyter_output><empty_output><jupyter_text>Another way to do the same thing * using the method `query`The method `query` receives a string in which you can say your condition. Important things:- `.query()` is a method of your dataframe- `.query()` method receives a string - Every word inside the string that is not `quoted` is considered a variable of your dataframe (so, for example `.query('Year == 1999')` will look for the variable `Year`. Another example: if you try to run `.query('Make == Ford')` will look both for the column name `Make` and the column named `Ford`. If you want the results of the column `Make` to match the **string** Ford, you have to run `.query('Make == "Ford"')`- If your column has spaces, you have to call it using backticks like in **.query('\`Engine Displacement\` < 4')**:<jupyter_code>tb_veic.query('model_year == 2016')
tb_veic.\
    query('make == "Ford"').\
    query('cylinders == 6').\
    query('model_year == 2017')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13199067909454062, 'fraction_numerical': 0.02821238348868176, 'mean_word_length': 4.777403846153846, 'pattern_counts': {'":': 0, '<': 86, '<?xml version=': 0, '>': 92, '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': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '355597', 'license': 'MIT', 'n_tokens_mistral': 4814, 'n_tokens_neox': 4515, 'n_words': 1074, 'path': 'aulas/64 DA PT NOV-2021 Aula 20211202.ipynb', 'repo_name': 'AndresCBF/64_PT_NOV202111', 'size': '12016'}