{"id":513,"date":"2018-10-08T02:22:09","date_gmt":"2018-10-08T00:22:09","guid":{"rendered":"http:\/\/35.180.88.53\/?p=513"},"modified":"2018-10-14T21:41:00","modified_gmt":"2018-10-14T19:41:00","slug":"extracting-and-transforming-data-in-python","status":"publish","type":"post","link":"https:\/\/www.sergilehkyi.com\/uk\/2018\/10\/extracting-and-transforming-data-in-python\/","title":{"rendered":"Extracting and Transforming Data in Python"},"content":{"rendered":"\n

In order to get insights from data you have to play with it a little..<\/p><\/blockquote>\n\n\n\n

It is important to be able to extract, filter, and transform data from DataFrames in order to drill into the data that really matters. The pandas library has many techniques that make this process efficient and intuitive. And in today’s article I will list those techniques with code samples and some explanations. Let’s get started.<\/p>\n\n\n\n

For this article I’ve created a sample DataFrame with random numbers to play with it. We will use this data as an example during the explanations in the article.<\/p>\n\n\n\n

import pandas as pdimport numpy as npcols = ['col0', 'col1', 'col2', 'col3', 'col4']rows = ['row0', 'row1', 'row2', 'row3', 'row4']data = np.random.randint(0, 100, size=(5, 5))df = pd.DataFrame(data, columns=cols, index=rows)df.head()Out[2]: \n      col0  col1  col2  col3  col4\nrow0    24    78    42     7    96\nrow1    40     4    80    12    84\nrow2    83    17    80    26    15\nrow3    92    68    58    93    33\nrow4    78    63    35    70    95<\/strong><\/pre>\n\n\n\n
Indexing DataFrames<\/h3>\n\n\n\n
To extract data from pandas DataFrame we can use direct indexing or accessors. We can select necessary rows and columns using it’s labels:<\/p>\n\n\n\n
df['col1']['row1']\nOut[3]: 4<\/strong><\/pre>\n\n\n\n
Please, note the order in this type of indexing: first you specify column label and then row. But the truth is, datasets with is rare and small, while in real life we work with much heavier machinery. It is much better to select data using accessors – .loc<\/em> and .iloc<\/em>. The difference between them is that .loc<\/em> accepts labels and .iloc<\/em> – indexes. Also when we use accessors first we specify rows and then columns. I had some difficult time in the beginning to get used to it – SQL background, what else can you say..<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
So, to select a single value using accessors you’d do the following:<\/p>\n\n\n\n
df.loc['row4', 'col2']Out[4]: 35<\/strong>\ndf.iloc[rowindex, colindex] ==> df.iloc[4, 2]Out[5]: 35<\/strong><\/pre>\n\n\n\n
Using indexing we can select a single value, Series or DataFrame from a DataFrame (sorry for tautology). Above I have shown how to select a value.<\/p>\n\n\n\n
To subselect few columns, just pass nested list of it’s labels and DataFrame will be returned:<\/p>\n\n\n\n
df_new = df[['col1','col2']]df_new.head(3)Out[6]: \n      col1  col2\nrow0    78    42\nrow1     4    80\nrow2    17    80<\/strong><\/pre>\n\n\n\n
If you want to select also specific rows, add its indexes and you will get a DataFrame again. This technique is called slicing and more in detail about it – below.<\/p>\n\n\n\n
df_new = df[['col1','col2']][1:4]df_new.head(3)Out[7]: \n      col1  col2\nrow1     4    80\nrow2    17    80\nrow3    68    58<\/strong><\/pre>\n\n\n\n
To select a Series you have to select a single column with all or range of rows. Each line of code will produce the same output:<\/p>\n\n\n\n
df['col0']df.loc[:,'col0']df.iloc[:, 0]Out[8]: \nrow0    24\nrow1    40\nrow2    83\nrow3    92\nrow4    78\nName: col0, dtype: int32<\/strong><\/pre>\n\n\n\n
The colon means that we want to select all rows or all columns – df.loc[:,:]<\/em> or df.iloc[:,:]<\/em> will return all values. And slowly we’ve got to the slicing – selecting specific ranges from our data. To slice a Series you just add a range of rows you want to select using its indexes:<\/p>\n\n\n\n
df['col3'][2:5]Out[12]: \nrow2    26\nrow3    93\nrow4    70\nName: col3, dtype: int32<\/strong><\/pre>\n\n\n\n
And don’t forget about ranging in Python – first element included, second excluded. So the code above will return rows with indexes 5, 6, 7, 8 and 9. And indexes start from 0.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Slicing DataFrames works the same way. With just one nuance. When using .loc<\/em> (labels) both borders are included. For example, select rows from label ‘row1’ to label ‘row4’ or from row index 1 to index 4 and all columns:<\/p>\n\n\n\n
df.loc['row1':'row4', :]Out[20]: \n      col0  col1  col2  col3  col4\nrow1    40     4    80    12    84\nrow2    83    17    80    26    15\nrow3    92    68    58    93    33\nrow4    78    63    35    70    95<\/strong>df.iloc[1:4, :]Out[21]: \n      col0  col1  col2  col3  col4\nrow1    40     4    80    12    84\nrow2    83    17    80    26    15\nrow3    92    68    58    93    33<\/strong><\/pre>\n\n\n\n
The first line of code above selected row1, row2, row3 and row4. While the second – row1, row2 and row3 only. And few more examples below.<\/p>\n\n\n\n
Select columns from label ‘col1’ to label ‘col4’ or from column index 1 to index 4 and all rows:<\/p>\n\n\n\n
df.loc[:, 'col1':'col4']Out[22]: \n      col1  col2  col3  col4\nrow0    78    42     7    96\nrow1     4    80    12    84\nrow2    17    80    26    15\nrow3    68    58    93    33\nrow4    63    35    70    95<\/strong>df.iloc[:, 1:4]Out[23]: \n      col1  col2  col3\nrow0    78    42     7\nrow1     4    80    12\nrow2    17    80    26\nrow3    68    58    93\nrow4    63    35    70<\/strong><\/pre>\n\n\n\n
Select\u00a0 rows from label ‘row1’ to label ‘row4’ or from row index 1 to index 4 and\u00a0 columns from label ‘col1’ to label ‘col4’ or from column index 1 to index 4:<\/p>\n\n\n\n
df.loc['row1':'row4', 'col1':'col4']Out[24]: \n      col1  col2  col3  col4\nrow1     4    80    12    84\nrow2    17    80    26    15\nrow3    68    58    93    33\nrow4    63    35    70    95<\/strong>df.iloc[1:4,1:4]Out[25]: \n      col1  col2  col3\nrow1     4    80    12\nrow2    17    80    26\nrow3    68    58    93<\/strong><\/pre>\n\n\n\n
Use a list to select specific columns or rows that are not in a range.<\/p>\n\n\n\n
df.loc['row2':'row4', ['col1','col3']]Out[28]: \n      col1  col3\nrow2    17    26\nrow3    68    93\nrow4    63    70<\/strong>\ndf.iloc[[2,4], 0:4]Out[30]: \n      col0  col1  col2  col3\nrow2    83    17    80    26\nrow4    78    63    35    70<\/strong><\/pre>\n\n\n\n
Filtering DataFrames<\/h3>\n\n\n\n
Filtering is a more general tool to select parts of the data based on properties of interest of the data itself and not on indexes or labels. DataFrames have several methods for filtering. Underlying idea for all these methods is a Boolean Series. The df[‘col1’] > 20<\/em> (we assume col1 is of type integer) will return a Boolean Series where this condition is true. I will put here the output of .head() method, so you don’t need to scroll up to match the numbers.<\/p>\n\n\n\n
Out[2]: \n      col0  col1  col2  col3  col4\nrow0    24    78    42     7    96\nrow1    40     4    80    12    84\nrow2    83    17    80    26    15\nrow3    92    68    58    93    33\nrow4    78    63    35    70    95<\/strong><\/pre>\n\n\n\n
So to select part of a DataFrame where values of col1 are bigger than 20 we will use the following code:<\/p>\n\n\n\n
df[df['col1'] > 20]# assigning variable also works<\/em>condition = df['col1'] > 20df[condition]Out[31]: \n      col0  col1  col2  col3  col4\nrow0    24    78    42     7    96\nrow3    92    68    58    93    33\nrow4    78    63    35    70    95<\/strong><\/pre>\n\n\n\n
We can combine those filters using standard logical operators (and – &, or – |, not – ~). Notice usage of parenthesis for these operations.<\/p>\n\n\n\n
df[(df['col1'] > 25) & (df['col3'] < 30)] # logical andOut[33]: \n      col0  col1  col2  col3  col4\nrow0    24    78    42     7    96<\/strong>df[(df['col1'] > 25) | (df['col3'] < 30)] # logical orOut[34]: \n      col0  col1  col2  col3  col4\nrow0    24    78    42     7    96\nrow1    40     4    80    12    84\nrow2    83    17    80    26    15\nrow3    92    68    58    93    33\nrow4    78    63    35    70    95<\/strong>df[~(df['col1'] > 25)] # logical notOut[35]: \n      col0  col1  col2  col3  col4\nrow1    40     4    80    12    84\nrow2    83    17    80    26    15<\/strong><\/pre>\n\n\n\n
Dealing with 0 and NaN values<\/h4>\n\n\n\n
Almost always datasets have zero or NaN values and we definitely want to know where they are. Ours is particular, so we will modify it a little:<\/p>\n\n\n\n
df.iloc[3, 3] = 0df.iloc[1, 2] = np.nandf.iloc[4, 0] = np.nandf['col5'] = 0df['col6'] = np.NaNdf.head()Out[57]: \n      col0  col1  col2  col3  col4  col5  col6\nrow0  24.0    78  42.0     7    96     0   NaN\nrow1  40.0     4   NaN    12    84     0   NaN\nrow2  83.0    17  80.0    26    15     0   NaN\nrow3  92.0    68  58.0     0    33     0   NaN\nrow4   NaN    63  35.0    70    95     0   NaN<\/strong><\/pre>\n\n\n\n
To select columns that don’t have any zero value we can use .all()<\/em> method (all data is present):<\/p>\n\n\n\n
df.loc[:, df.all()]Out[43]: \n      col0  col1  col2  col4  col6\nrow0  24.0    78  42.0    96   NaN\nrow1  40.0     4   NaN    84   NaN\nrow2  83.0    17  80.0    15   NaN\nrow3  92.0    68  58.0    33   NaN\nrow4   NaN    63  35.0    95   NaN<\/strong><\/pre>\n\n\n\n
If we want to find a column that have at least one nonzero (any) value, this will help:<\/p>\n\n\n\n
df.loc[:, df.any()]Out[47]: \n      col0  col1  col2  col3  col4\nrow0  24.0    78  42.0     7    96\nrow1  40.0     4   NaN    12    84\nrow2  83.0    17  80.0    26    15\nrow3  92.0    68  58.0     0    33\nrow4   NaN    63  35.0    70    95<\/strong><\/pre>\n\n\n\n
To select columns with any NaN:<\/p>\n\n\n\n
df.loc[:, df.isnull().any()]Out[48]: \n      col0  col2  col6\nrow0  24.0  42.0   NaN\nrow1  40.0   NaN   NaN\nrow2  83.0  80.0   NaN\nrow3  92.0  58.0   NaN\nrow4   NaN  35.0   NaN<\/strong><\/pre>\n\n\n\n
Select columns without NaNs:<\/p>\n\n\n\n
df.loc[:, df.notnull().all()]Out[49]: \n      col1  col3  col4  col5\nrow0    78     7    96     0\nrow1     4    12    84     0\nrow2    17    26    15     0\nrow3    68     0    33     0\nrow4    63    70    95     0<\/strong><\/pre>\n\n\n\n
We can drop those rows containing NaNs, but it’s a dangerous game – dropping usually isn’t a solution. You have to understand your data and deal with such rows wisely. I warned you.<\/p>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
Are you sure you wanna know it? OK.. \ud83d\ude00<\/p>\n\n\n\n
df.dropna(how='all', axis=1) # if all values in a column are NaN it will be droppedOut[69]: \n      col0  col1  col2  col3  col4  col5\nrow0  24.0    78  42.0     7    96     0\nrow1  40.0     4   NaN    12    84     0\nrow2  83.0    17  80.0    26    15     0\nrow3  92.0    68  58.0     0    33     0\nrow4   NaN    63  35.0    70    95     0<\/strong>df.dropna(how='any', axis=1) # if any value in a row is NaN it will be droppedOut[71]: \n      col1  col3  col4  col5\nrow0    78     7    96     0\nrow1     4    12    84     0\nrow2    17    26    15     0\nrow3    68     0    33     0\nrow4    63    70    95     0<\/strong><\/strong><\/pre>\n\n\n\n
This methods do not modify original DataFrame, so to continue working with filtered data you have to assign it to new dataframe or reassign to the existing one<\/p>\n\n\n\n
df = df.dropna(how='any', axis=1)\n<\/pre>\n\n\n\n
The beauty of filtering is that we actually can select or modify values of one column based on another. For example, we can select values from col1 where col2 is grater than 35 and update those values by adding 5 to each:<\/p>\n\n\n\n
# Find a column based on another<\/em>\ndf['col1'][df['col2'] > 35]Out[74]: \nrow0    78\nrow2    17\nrow3    68\nName: col1, dtype: int32<\/strong>\n# Modify a column based on another<\/em>\ndf['col1'][df['col2'] > 35] += 5df['col1']Out[77]: \nrow0    83\nrow1     4\nrow2    22\nrow3    73\nrow4    63\nName: col1, dtype: int32<\/strong><\/pre>\n\n\n\n
And this brings us to the next part –<\/p>\n\n\n\n
Transforming DataFrames<\/h3>\n\n\n\n
Once we selected or filtered our data we want to transform it somehow. The best way to do this is with methods inherited to DataFrames or numpy universal funcs, that transform entire column of data element-wise. Examples would be pandas’\u00a0.floordiv()<\/em> function (from documentation: ‘Integer division of dataframe and other, element-wise’) or numpy’s .floor_divide()<\/em> (doc: ‘Return the largest integer smaller or equal to the division of the inputs.’).<\/p>\n\n\n\n
If those functions were not available we could write our own and use it with .apply() <\/em>method.<\/p>\n\n\n\n
def some_func(x):\n    return x * 2\ndf.apply(some_func) -- # update each entry of a DataFrame without any loops<\/em># lambda also works<\/em>\ndf.apply(lambda n: n*2) -- # the same<\/em><\/pre>\n\n\n\n
These functions do not return transformations, so we have to store it explicitly:<\/p>\n\n\n\n
df['new_col'] =\u00a0df['col4'].apply(lambda n: n*2)df.head()Out[82]: \n      col0  col1  col2  col3  col4  col5  col6  new_col\nrow0  24.0    83  42.0     7    96     0   NaN      192\nrow1  40.0     4   NaN    12    84     0   NaN      168\nrow2  83.0    22  80.0    26    15     0   NaN       30\nrow3  92.0    73  58.0     0    33     0   NaN       66\nrow4   NaN    63  35.0    70    95     0   NaN      190<\/strong><\/pre>\n\n\n\n
If index is a string it has a .str<\/em> accessor that allows us to modify entire index at once:<\/p>\n\n\n\n
df.index.str.upper()Out[83]: Index(['ROW0', 'ROW1', 'ROW2', 'ROW3', 'ROW4'], dtype='object')<\/strong><\/pre>\n\n\n\n
Also, we cannot use .apply()<\/em> method on index – the alternative for it is .map()<\/em><\/p>\n\n\n\n
df.index = df.index.map(str.lower)Out[85]: Index(['row0', 'row1', 'row2', 'row3', 'row4'], dtype='object')<\/strong><\/pre>\n\n\n\n
But .map()<\/em> can be used on columns as well. For example:<\/p>\n\n\n\n
# Create the dictionary: red_vs_blue<\/em>\nred_vs_blue = {0:'blue', 12:'red'}\n# Use the dictionary to map the 'col3' column to the new column df['color']<\/em>\ndf['color'] = df['col3'].map(red_vs_blue)df.head()Out[92]: \n      col0  col1  col2  col3  col4  col5  col6  new_col color\nrow0  24.0    83  42.0     7    96     0   NaN      192   NaN\nrow1  40.0     4   NaN    12    84     0   NaN      168   red\nrow2  83.0    22  80.0    26    15     0   NaN       30   NaN\nrow3  92.0    73  58.0     0    33     0   NaN       66  blue\nrow4   NaN    63  35.0    70    95     0   NaN      190   NaN<\/strong><\/pre>\n\n\n\n
Arithmetic operations on Series and DataFrames work directly. The expression below will create a new column where each value with index n<\/em> is a sum of values with index n<\/em> from ‘col3’<\/em> and ‘col7’.<\/em><\/p>\n\n\n\n
df['col7'] = df['col3'] + df['col4'] df.head()Out[94]: \n      col0  col1  col2  col3  col4  col5  col6  new_col color  col7\nrow0  24.0    83  42.0     7    96     0   NaN      192   NaN   103\nrow1  40.0     4   NaN    12    84     0   NaN      168   red    96\nrow2  83.0    22  80.0    26    15     0   NaN       30   NaN    41\nrow3  92.0    73  58.0     0    33     0   NaN       66  blue    33\nrow4   NaN    63  35.0    70    95     0   NaN      190   NaN   165<\/strong><\/pre>\n\n\n\n
This is the second version of the article, because the first one was a complete mess – errors in the code, no examples and few other things. Thanks to the feedback I went through the article one more time and I think it looks much better now. I have covered basics of transforming and extracting data in Python with code snippets and examples here and hopefully it will be useful for people who are just starting their path in this field.<\/p>\n\n\n\n
Meanwhile, love data science and smile more. We have to be positive as we have the sexiest job of 21st century \ud83d\ude00<\/p>\n","protected":false},"excerpt":{"rendered":"
In order to get insights from data you have to play with it a little.. It is important to be…<\/p>\n","protected":false},"author":1,"featured_media":519,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,5],"tags":[],"translation":{"provider":"WPGlobus","version":"3.0.0","language":"uk","enabled_languages":["gb","es","uk"],"languages":{"gb":{"title":true,"content":true,"excerpt":false},"es":{"title":false,"content":false,"excerpt":false},"uk":{"title":false,"content":false,"excerpt":false}}},"_links":{"self":[{"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/posts\/513"}],"collection":[{"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/comments?post=513"}],"version-history":[{"count":6,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/posts\/513\/revisions"}],"predecessor-version":[{"id":531,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/posts\/513\/revisions\/531"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/media\/519"}],"wp:attachment":[{"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/media?parent=513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/categories?post=513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sergilehkyi.com\/uk\/wp-json\/wp\/v2\/tags?post=513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}