Data Structures in Pandas

Import pandas as pd

pd.__version__

pd.read_csv('xyz.csv')

df.to_csv('xyz.csv')

pd.ExcelFile('xyz.xls' )
pd.read_excel(file, 'Sheet1')

df.to_excel('xyz.xlsx', sheet_name='Sheet1')

pd.read_json('xyz.json')

pd.read_sql('xyz.sql')

pd.read_html('xyz.html')

pd.Series(data=Data)

Create a Pandas Series with 

Data like  {10: ‘DSA’, 20: ‘ML’, 30: ‘DS’}

pd.Series(data = ['Geeks','for','geeks'],
                 index = ['A','B','C'])

pd.DataFrame(data)

Create Pandas Data frame with 

Data like  

{‘Fruits’: [‘Mango’, ‘Apple’, ‘Banana’, ‘Orange’],       

 ‘Quantity’: [40, 20, 25, 10],       

 ‘Price’: [80, 100, 50, 70] }

df.dtypes

df.shape

df['Column_Name'].astype('int32')

df['Column_Name'].astype('str')

df['Column_Name'].astype('float')

df.info()

df.values

0

Mango

40

80

1

Apple

20

100

2

Banana

25

50

3

Orange

10

70

Sorting by values

df.sort_values('Price', ascending=True)

df.sort_values('Price', ascending=False)

Sorting by Index

df.sort_index(ascending=False)

Reindexing 

df.reset_index(drop=True, inplace=True)

Reset the indexes to default

• inplace = True # make changes to the original data frame
• drop = True # Drop the initial indexes, if False then the previous index is assigned in a column.

Renaming

df.rename(columns={'Fruits': 'FRUITS',
'Quantity': 'QUANTITY',
'Price': 'PRICE'},
inplace=True)

Rename the column name with its respective values:

In the given code ‘Fruits’ will be replaced by ‘FRUITS’, ‘Quantity’ will be replaced ‘QUANTITY’ and ‘Price’ will be replaced by ‘PRICE’

Reshaping

pd.melt(df)

pivot = df.pivot(columns='FRUITS',
values=['PRICE', 'QUANTITY'])

Create a Pivot Table

 Dropping

df1 = df.drop(columns=['QUANTITY'], axis=1)

Drop Column

• Drop the ‘QUANTITY’ from the data frame df, Here axis = 1 is for the column.

df2 = df.drop([1, 3], axis=0)

Drop Rows

• Drop 2nd and 4th rows of data frame df, Here axis = 0 is for row

df.head()

df.tail()

df.sample(n)

df.nlargest(2, 'QUANTITY')

df.nsmallest(2, 'QUANTITY')

df[df.PRICE > 50]

Selection Column data

df['FRUITS']

df[['FRUITS', 'PRICE']]

df.filter(regex='F|Q')

Select the column whose names match the patterns of the respective regular expression 

I.E ‘FRUITS’ & ‘QUANTITY’

Getting Subsets of rows or columns

df.loc[:, 'FRUITS':'PRICE']

df.loc[df['PRICE'] < 70, ['FRUITS', 'PRICE']]

df.iloc[2:5]

df.iloc[:, [0, 2]]

df.at[1, 'PRICE']

df.iat[1, 2]

Filter

df.filter(items=['FRUITS', 'PRICE'])

Filter by column name

•  Select the ‘FRUITS’ and ‘PRICE’ column of the data frame

df.filter(items=[3], axis=0)

Filter by row index

• Select the 3rd row of the data frame
• Here axis = 0 is for row

 df['PRICE'].where(df['PRICE'] > 50)

 Returns a new Series object with the same length as the original ‘PRICE’ column. 

But where() function will replace values where the condition is False with NaN (missing value) or another specified value.

 df.query('PRICE>70')

 Filter a DataFrame based on a specified condition

• Return the rows having PRICE > 70

Merge two data frame

pd.merge(df1, df2, how='left', on='Fruits')

Left Join

• Merge the two data frames df1 and df2 based on the ‘Fruits’ column of the left data frame i.e df1

pd.merge(df1, df2, how='right', on='Fruits')

Right Join

• Merge the two data frames df1 and df2 based on the ‘Fruits’ column of the right data frame i.e df2

pd.merge(df1, df2, how='inner', on='Fruits')

Inner Join

• Merge the two data frames df1 and df2 based on the common ‘Fruits’ name of both data frame 

pd.merge(df1, df2, how='outer', on='Fruits')

Outer Join

• Merge the two data frames df1 and df2 based on the common ‘Fruits’ name
• In this case ‘Fruits’ of both data frames will be arranged accordingly

Concatenation

concat_df = pd.concat([df, df1], axis=0, ignore_index=True)

Row-Wise Concatenation

• axis = 0 : denotes that the data frame df and df1 will join vertically 
• Ignore_index = True : ensures that the resulting DataFrame has a new index, starting from zero and incrementing sequentially
• concat_df has the rows of df followed by df1 

concat_df = pd.concat([df, df2], axis=1)

Row-Wise Concatenation

• axis = 1 : denotes that the data frame df and df1 will join horizontally 
• concat_df has the column of df followed by df2, 
• If the lengths of the DataFrames don’t match, NaN values will be assigned to the missing elements.

Describe dataset

df.describe()

Descriptive statistics of a data frame
Return

• count: Number of rows for each numerical column
• mean: Average values of each numerical column
• std: Standard deviation of each numerical column
• min: Minimum value of each numerical column
• 25%, 50%, 75%: 25, 50 & 75 percentile of each numerical column
•  max: Maximum values of each numerical column

df.describe(include=['O'])

Descriptive statistics of Object data types of the data frame

• include =[‘O’] : Signifies the Object data types column
• count: Number of rows for each object datatypes column
• unique: Count of unique values for each object datatypes column
• top: Top row value each object datatypes column
• freq: Frequecy of the unique value

df.FRUITS.unique()

• Check the unique values of ‘FRUITS’ column in the dataset

df.FRUITS.value_counts()

df['PRICE'].sum()

df['PRICE'].cumsum()

df['PRICE'].min()

df['PRICE'].max()

df['PRICE'].mean()

df['PRICE'].median()

df['PRICE'].var()

df['PRICE'].std()

df['PRICE'].quantile([0.25, 0.75])

df.apply(summation)

Apply any custom function with pandas

def summation(col):
    if col.dtypes != 'int64':
        return col.count()
    else:
        return col.sum()

df.cov(numeric_only=True)

df.corr(numeric_only=True)

Missing Values

df.isnull()

Check for null values

• Return True or False, Having True means data is missing

df.isnull().sum()

df['DISCOUNT'] = df['DISCOUNT'].fillna(value=VALUE)

df1 = df.dropna()

Add a new  column to the Data frame

df['COL_NAME'] = COL_DATA

Add a column to the Existing dataset

Note: The length of COL_DATA should be equal to the number of rows of existing dataset

df = df.assign(Paid_Price=lambda df:
              (df.QUANTITY * df.PRICE))

Group By

grouped = df.groupby(by='COL_NAME')

grouped.agg(['count','sum', 'mean'])

Graph with Pandas

grouped = df.groupby(['Origin'])
grouped.sum().plot.pie(y='Paid_Price', subplots=True)

Pie Chart

• Plot the Pie Chart showing group by sum of values in ‘Paid_Price’ as per group of  ‘Origin’

df.plot.scatter(x='PRICE', y='DISCOUNT')

Scatter Plot

• Scatter Plot between ‘PRICE’ and ‘DISCOUNT’

df.plot.bar(x='FRUITS', y=['QUANTITY', 'PRICE', 'DISCOUNT'])

Bar Chart

• Bar chart having horizontal axis with Fruit Names and the respective ‘QUANTITY,’PRICE’ and ‘DISCOUNT’ values.

df['QUANTITY'].plot.hist(bins=3)

Histogram Plot

• Histogram plot of  ‘QUANTITY’ column with specified bins value i.e 3 here.

df.boxplot(column='PRICE', grid=False)

Box Plot

• Box plot of ‘PRICE’ column 
• It is used for outlier detection