How to work with DataFrame in Python?

Python language has vast application. Data analysis and manipulation is one of many application.

We can work on data using Pandas library in Python for generating data analytics and manipulating data to generate useful patterns.

What is Pandas?

Pandas is a Python library which is used to work with sequential and tabular data. It has functionality to manage, analyze and manipulate data in a simple and efficient way.

We can think of its data structures as relatives to database tables or spreadsheets.

Pandas includes NumPy library. Two primary data structures of pandas are series (1D data) and dataframe (2D data). It can work with Homogenous as well as Heterogenous data.

Features of Pandas

• Time-series manipulation tools

• Works with missing data (NaN)

• Works with different data files (xls,db,csv,psv,hdf5,etc.)

• ETL tools (Extraction, Transformation and Load tools)

What is DataFrame?

Pandas DataFrame is a heterogenous 2D object, i.e. data are of same type within each column but it could be a different data type for each column and can be labeled with an index (implicit or explicit).

In simple words, DataFrame is like table in database.

The index can be implicit, starting with 0 or we can have our own index. Index can even include dates and times.

Let us now work with DataFrame

Creating an empty DataFrame

import pandas as pd
df1 = pd.DataFrame()
print(df1)

Creating an empty structure DataFrame

import pandas as pd
df1 = pd.DataFrame(columns=['Sr. no','Item','Desc'])
print (df1)

df2 = pd.DataFrame(columns=['Sr. no','Item','Desc'],index=range(1,10))
print (df2)

Creating a DataFrame passing NumPy array

import pandas as pd
arr = {'Sr. no' : [1,2,3,4],
      'Items'  : ['A','B','C','D']}
df1 = pd.DataFrame(arr)
print(df1)

Creating a DataFrame passing a Dictionary

import pandas as pd
dict1 = {1:'A',2:'B',3:'C',4:'D'}
df1 = pd.DataFrame([dict1])
print(df1)

Creating a DataFrame with datetime index

import pandas as pd
arr = {'Sr. no' : [1,2,3,4],
      'Items'  : ['A','B','C','D']}
indx = pd.DatetimeIndex(['2021-12-30','2021-12-31','2022-01-01','2022-01-02'])
df1 = pd.DataFrame(arr,index=indx)
print(df1)

Viewing DataFrame

import pandas as pd
arr = {'Sr. no' : [1,2,3,4],
      'Items'  : ['A','B','C','D']}
df1 = pd.DataFrame(arr,index=indx)
print(df1)   # pd.DataFrame() also print DataFrame 

# get first two rows
df.head(2)

# get last two rows
df.tail(2)

# get DataFrame's index
df.index

# get DataFrame's columns
df.columns

#get DataFrame's values
df.values

Importing Data in Pandas

Pandas DataFrame can read data from several data formats, most common includes csv, psv, xls, json, sql, hdf5, etc.

We will look at few examples to import data from different data formats and customizations.

import pandas as pd
df1 = pd.read_csv('file1.csv',sep=' ') # to read dataframe csv file with blank space as separator
df1 = pd.read_csv('file1.csv',usecols=[0,1,2,3],nrows = 100) # to read data from columns 0 to 3
df1 = pd.read_excel('file1.xls',sheet_name='User') # to read data from excel file and sheet named 'User'

Once we data in DataFrame i.e. DataFrame is prepared, independent of source of data (csv,xls,db etc.) we can work with it, like it is a table in database, selecting element of our interest.

import pandas as pd
df1 = pd.read_csv('example.csv')
df1.head()
df1.shape

We have loaded data from csv file and created DataFrame. Now, we will see different select operation on this DataFrame.

Selecting a single column in DataFrame

column1 = df1['column_name']
column1.head()

Selecting multiple columns in DataFrame

cols = df1['column1','column2']
cols.head()

Selecting rows using indexing [] in DataFrame

rows = df1[10:20]
print (rows)

rows = df1[10:20]['column1','column2']
print (rows)

Selecting rows by lable (.loc[]) in DataFrame

df2 = df1.loc[10:20]
df3 = df1.loc[10:20,['column1','column2']]
print(df2)
print(df3)

Selecting rows by position (.iloc[]) in DataFrame

dfpos = df1.iloc[10:20,[3,4]] 

After looking at fetching required set of column we proceed to manipulation of DataFrame

How to manipulate a DataFrame

Transpose

import pandas as pd
df1 = pd.read_csv('example.csv')
df2 = df1[10:20]['cols1','cols2']
print("transpose : {}".format(df2.T))

sort_values

df2 = df1.sort_values(by='column_name') # sorting by single column
df3 = df1.sort_values(by=['col1','col2']) # sorting by multiple column
print(df2)
print(df3)

sort_index

df2 = df1.sort_index()
print(df2)

Re-indexing

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(3),index=['a','b','c'])
print(df1)
df2 = df1.reindex([1,2,3])
print(df2)

Adding a new column

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(10))
print(df1)
df1['col_new'] = 'a'
print(df1)

Remove existing column

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(10))
print(df1)
df1['col_new'] = 'a'
print(df1)

del df1['col_new']  # del df1[1] will work same

Data at particular location by label

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(3,5),index=[1,2,3],columns=['a','b','c','d','e'])
val1 = df1.at(1,'a')
print(val1)
df1.at(1,'a') = 0 # assign value at particular location

Data at particular location by position

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(3,5),index=[1,2,3],columns=['a','b','c','d','e'])
val1 = df1.iat[1,1]
print(val1)
df1.at[1,1] = 0 # assign value at particular location

df1[df1>0] = 2 # assign data at all location based on a condition

Applying a method or function in DataFrame

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(3,5),index=[1,2,3],columns=['a','b','c','d','e'])

def add_five(number):
    return number+5

df.apply(add_five,axis=2)

There are few more functions such as dropna(), fillna() etc. which is used in manipulation of data. DataFrame also have many statiscal methods like info(), describe(), value_counts(), mean(), std() etc.

Information and facts are major asset in digital world. Data is fuel that is running this world. However, everything is not required by everyone. Only specific part of data is relevant for certain use cases.

Thus filtering data becomes important. By filtering we can filter data as per requirement and generate pattern and knowledge to drive this digital world.

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(5,5),index=['a','b','c','d','e'],columns=['A','B','C','D','E'])
print (df1)

filtered_df = df1[df1['A']>0]
print(filtered_df)

Iterating Pandas DataFrame

for item in df.iterrows():
    print(item)

Merge, Append and Concat operation in Pandas DataFrame

DataFrame comes with feature to merge, concatenate. They are important and useful feature.

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(5,5),index=['a','b','c','d','e'],columns=['A','B','C','D','E'])
print (df1)


df2 = pd.DataFrame(np.random.randn(5,5),index=['a','b','c','d','e'],columns=['A','B','C','D','E'])
print (df2)

df3 = pd.merge(df1,df2)
print(df3)

append is function that allows us to add rows to existing DataFrame

import pandas as pd
import numpy as np
df1 = pd.DataFrame(np.random.randn(5,5),index=['a','b','c','d','e'],columns=['A','B','C','D','E'])
print (df1)


df2 = pd.DataFrame(np.random.randn(5,5),index=['a','b','c','d','e'],columns=['A','B','C','D','E'])
print (df2)

df3 = df1.append(df2)
print(df3)

df4 = pd.concat([df1,df2]) #concat by row
print (df4)

Hope it helps!

Happy Learning.