(all these taken from Wikipedia)<\/em><\/li><\/ul>\n\n\n\nTo transform our data and make it tidy we can use melting.<\/p>\n\n\n\n
pd.melt() # transform columns to rows<\/pre>\n\n\n\nThere are two parameters you should be aware of: id_vars<\/em> and value_vars<\/em>. The id_vars<\/em> represent the columns of the data you do not want to melt (i.e., keep it in its current shape), while the value_vars<\/em> represent the columns you do wish to melt into rows. By default, if no value_vars<\/em> are provided, all columns not set in the id_vars<\/em> will be melted.<\/p>\n\n\n\nnew_df = pd.melt(df, id_vars = 'do not melt')<\/pre>\n\n\n\nThe opposite operation to melting is pivoting. Here we turn unique values into separate columns. We use it when we want to transform our data from analysis shape to reporting shape, from easy-machine-readable to easy-human-readable form.<\/p>\n\n\n\n
df.pivot(index='date', columns='element', values='value')<\/pre>\n\n\n\nAlthough, this method cannot handle duplicate values. When this is a case we should use .pivot_table()<\/em> that has an additional parameter – aggfunc<\/em>, which will handle those duplicates based on a function we provide (sum, count, mean, min, max or user defined function).\u00a0<\/p>\n\n\n\ndf.pivot_table(index='date', columns='element', values='value', aggfunc=np.mean)<\/pre>\n\n\n\nParsing data<\/h4>\n\n\n\n
Sometimes we might have data stored incorrectly. For example, values for the gender groups that stored as ‘m014’, ‘f014’, ‘m1528’, ‘f1528’. You cannot tell it’s completely wrong, but it would be better to split these values into ‘gender’ and ‘age’ columns. To do this we use Python slicing sintaxis by accessing .str attribute of column of object type.<\/p>\n\n\n\n
df['gender'] = df.variable.str[0]\ndf['age_group'] = df.variable.str[1:]<\/pre>\n\n\n\nConcatenating data<\/h4>\n\n\n\n
Also data might not come in one huge file and be separated into few different chunks, so we have to be able to concatenate all that data and clean it or clean the first sample and then apply the same process on remaining parts. To do this we can use pandas .concat<\/em> method, which provided with the list of dataframes will concatenate them all. By default it will store the original indexes what will result in duplicate index values. To prevent this we have to reset index of a new dataframe by passing an additional parameter ignore_index=True.<\/em><\/p>\n\n\n\nconcatenated = pd.concat([df1, df2], ignore_index=True)<\/pre>\n\n\n\nBut what if we have thousands of files? It’ll be dumb to import them one by one, clean them and repeat it again. And we are not stupid, we know loops in Python. The only missing part is to find all those files for import. We can do this with glob library. So the process will be the following: write a pattern, save all files into a list, iterate over csv files, import each file and concatenate the dataframes into one. Doesn’t seem that difficult, but with the code sample it’s much better:<\/p>\n\n\n\n
# Import necessary modulesimport globimport pandas as pd\n# Write the pattern: pattern\npattern = '*.csv'\n# Save all file matches: csv_filescsv_files = glob.glob(pattern)\n# Create an empty list: framesframes = []\n# Iterate over csv_files\nfor csv in csv_files: # Read csv into a DataFrame: df df = pd.read_csv(csv) # Append df to frames frames.append(df)# Concatenate frames into a single DataFrame: final_dffinal_df = pd.concat(frames)<\/pre>\n\n\n\nMerging data<\/h4>\n\n\n\n
Merging is the same as SQL join operation. You combine two or more tables into one by key which is present in every table. There are three types of joins: one-to-one, one-many, many-to-many. In SQL this process is advanced, with a lot of options, modifications, where you have to explicitly specify what and how you want to join. Here, all is done for you by one function and the type of join will only depend on a data in a dataframe. If the column name to be used as a key is the same in both dataframes ‘on’<\/em> parameter is used.<\/p>\n\n\n\nmerged = pd.merge(left=df1, right=df2, on=None, left_on='col1', right_on='col2')<\/pre>\n\n\n\nConverting data types<\/h4>\n\n\n\n
It is very important to have data with correct data types as later it may play a bad joke with you and your analysis. Remember how once I didn’t convert one column to the correct data type and spent 1 hour trying to subtract float from string :D. So be careful :D. That’s a typical error in data, which can be fixed with pd.to_numeric()<\/em> and with errors=’coerce’<\/em> it will convert all the err values into NaNs.<\/p>\n\n\n\nTo convert data we can use .astype()<\/em> method on a series. Also keep in mind the ‘category’ type – it reduces size of a dataframe and makes computations faster. We can convert to any value that can be used as category – days of the week, gender, continent abbreviations – depends on a context.<\/p>\n\n\n\ndf['column1'] = df['column1'].astype(str)\ndf['column1'] = df['column1'].astype('category')df['column1'] = pd.to_numeric(df['column1'], errors='coerce')<\/pre>\n\n\n\nDuplicates and missing values<\/h4>\n\n\n\n
Our favourite part, isn’t it? To drop duplicates we can use druuuuuuuuuums drop_duplicates() method.<\/p>\n\n\n\n
df = df.drop_duplicates()<\/pre>\n\n\n\nWith missing values it is little bit more complicated. In general there are three ways to deal with missing data:<\/p>\n\n\n\n