pivot()
and pivot_table()
methodsmelt()
methodcrosstab()
method
df.pivot()
and df.pivot_table()
Methods¶df.pivot(index=None, columns=None, values=None)
pandas.pivot(data, index=None, columns=None, values=None)
Where,
index
: Column to use as new dataframe's index. If None, uses existing index.columns
: Column to use to make new dataframe columns.values
: Column(s) to use for populating new frame's values.Read more about pd.pivot()
: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html
Read more about df.pivot()
: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot.html
df.pivot_table(index=None, columns=None, values=None, aggfunc= 'mean', fill_valus=None)
pandas.pivot_table(data, index=None, columns=None, values=None, aggfunc= 'mean', fill_valus=None)
Where,
index
: Column to use as new dataframe's index. If None, uses existing index.columns
: Column to use to make new dataframe columns.values
: Column(s) to use for populating new frame's values.aggfunc
: default is numpy.meanfill_value
: Value to replace missing values withRead more about pd.pivot_table()
: https://pandas.pydata.org/docs/reference/api/pandas.pivot_table.html
Read more about df.pivot_table()
: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.pivot_table.html?highlight=dataframe%20pivot_table#pandas.DataFrame.pivot_table
import pandas as pd
df = pd.read_csv('datasets/pivot_weather1.csv')
df
date | city | temperature | humidity | |
---|---|---|---|---|
0 | 20/06/2021 | Lahore | 38 | 76 |
1 | 21/06/2021 | Lahore | 41 | 80 |
2 | 22/06/2021 | Lahore | 39 | 85 |
3 | 20/06/2021 | Muree | 17 | 71 |
4 | 21/06/2021 | Muree | 15 | 70 |
5 | 22/06/2021 | Muree | 18 | 74 |
6 | 20/06/2021 | Karachi | 33 | 93 |
7 | 21/06/2021 | Karachi | 30 | 91 |
8 | 22/06/2021 | Karachi | 35 | 90 |
df.pivot_table(index='city', columns='date', values='humidity')
date | 20/06/2021 | 21/06/2021 | 22/06/2021 |
---|---|---|---|
city | |||
Karachi | 93 | 91 | 90 |
Lahore | 76 | 80 | 85 |
Muree | 71 | 70 | 74 |
Suppose we want to have one record for each city, containing temperature and humidity for each date
# using pivot()
df1 = df.pivot(index='city', columns='date')
df1
temperature | humidity | |||||
---|---|---|---|---|---|---|
date | 20/06/2021 | 21/06/2021 | 22/06/2021 | 20/06/2021 | 21/06/2021 | 22/06/2021 |
city | ||||||
Karachi | 33 | 30 | 35 | 93 | 91 | 90 |
Lahore | 38 | 41 | 39 | 76 | 80 | 85 |
Muree | 17 | 15 | 18 | 71 | 70 | 74 |
Let us repeat the same using
pivot_table()
# using pivot_table()
df1 = df.pivot_table(index='city', columns='date')
df1
humidity | temperature | |||||
---|---|---|---|---|---|---|
date | 20/06/2021 | 21/06/2021 | 22/06/2021 | 20/06/2021 | 21/06/2021 | 22/06/2021 |
city | ||||||
Karachi | 93 | 91 | 90 | 33 | 30 | 35 |
Lahore | 76 | 80 | 85 | 38 | 41 | 39 |
Muree | 71 | 70 | 74 | 17 | 15 | 18 |
index='city'
, the city column is the left most column now having unique values.columns='date'
, the values from the date column have become the column headers now.
Suppose we want to see only the temperature or humidity column in the output dataframe. This can be achieved by setting the values
argument to the name of the column
df1 = df.pivot(index='city', columns='date', values='temperature')
df1
date | 20/06/2021 | 21/06/2021 | 22/06/2021 |
---|---|---|---|
city | |||
Karachi | 33 | 30 | 35 |
Lahore | 38 | 41 | 39 |
Muree | 17 | 15 | 18 |
df1 = df.pivot(index='city', columns='date', values='humidity')
df1
date | 20/06/2021 | 21/06/2021 | 22/06/2021 |
---|---|---|---|
city | |||
Karachi | 93 | 91 | 90 |
Lahore | 76 | 80 | 85 |
Muree | 71 | 70 | 74 |
Let us keep the date along index and city at the column, so that the output dataframe should have one record for each date, containing temperature and humidity for each city
# using pivot()
df1 = df.pivot(index='date', columns='city')
df1
temperature | humidity | |||||
---|---|---|---|---|---|---|
city | Karachi | Lahore | Muree | Karachi | Lahore | Muree |
date | ||||||
20/06/2021 | 33 | 38 | 17 | 93 | 76 | 71 |
21/06/2021 | 30 | 41 | 15 | 91 | 80 | 70 |
22/06/2021 | 35 | 39 | 18 | 90 | 85 | 74 |
# using pivot_table()
df1 = df.pivot_table(index='date', columns='city')
df1
humidity | temperature | |||||
---|---|---|---|---|---|---|
city | Karachi | Lahore | Muree | Karachi | Lahore | Muree |
date | ||||||
20/06/2021 | 93 | 76 | 71 | 33 | 38 | 17 |
21/06/2021 | 91 | 80 | 70 | 30 | 41 | 15 |
22/06/2021 | 90 | 85 | 74 | 35 | 39 | 18 |
You cannot use pivot()
due to multiple values, however, pivot_table()
will work, as it will take the mean()
of those values
import pandas as pd
df = pd.read_csv('datasets/pivot_weather2.csv')
df
date | city | temperature | humidity | |
---|---|---|---|---|
0 | 20/06/2021 | Lahore | 38 | 76 |
1 | 20/06/2021 | Lahore | 40 | 75 |
2 | 21/06/2021 | Lahore | 39 | 79 |
3 | 21/06/2021 | Lahore | 37 | 74 |
4 | 20/06/2021 | Muree | 15 | 88 |
5 | 20/06/2021 | Muree | 17 | 90 |
6 | 21/06/2021 | Muree | 10 | 93 |
7 | 21/06/2021 | Muree | 8 | 91 |
Note in this dataset we donot have unique values for date and city combined
#df1 = df.pivot(index='date', columns='city')
#df1
date
and columns to city
, the pivot()
tries to set the left key to 20/06/2021
and then match the column name of the differing city (Lahore) values.20/06/2021
and columns of Lahore
. The function doesn't know what value to put into cell values.pivot_table()
methodpivot_table
function, there is another argument aggfunc=’mean’
that decides this.df1 = df.pivot_table(index='date', columns='city')
df1
humidity | temperature | |||
---|---|---|---|---|
city | Lahore | Muree | Lahore | Muree |
date | ||||
20/06/2021 | 75.5 | 89.0 | 39 | 16 |
21/06/2021 | 76.5 | 92.0 | 38 | 9 |
The default value to the
aggfunc
argument ismean
, and you can explicitly pass any other aggregate function name.
df1 = df.pivot_table(index='date', columns='city', aggfunc='sum')
df1
humidity | temperature | |||
---|---|---|---|---|
city | Lahore | Muree | Lahore | Muree |
date | ||||
20/06/2021 | 151 | 178 | 78 | 32 |
21/06/2021 | 153 | 184 | 76 | 18 |
import pandas as pd
df = pd.read_csv('datasets/pivot_std1.csv')
df
gender | sport | age | height | weight | |
---|---|---|---|---|---|
0 | male | cricket | 22 | 72 | 200 |
1 | female | cricket | 21 | 72 | 130 |
2 | female | basketball | 23 | 73 | 150 |
3 | male | basketball | 21 | 75 | 175 |
4 | female | cricket | 20 | 68 | 170 |
Suppose we want to have one record for each gender, containing age, height and weight for each sport
df1 = df.pivot_table(index='gender', columns='sport')
df1
age | height | weight | ||||
---|---|---|---|---|---|---|
sport | basketball | cricket | basketball | cricket | basketball | cricket |
gender | ||||||
female | 23.0 | 20.5 | 73 | 70 | 150 | 150 |
male | 21.0 | 22.0 | 75 | 72 | 175 | 200 |
When we try to repeat the same using pivot()
, we get a ValueError: Index contains duplicate entries, cannot reshape
#df1 = df.pivot(index='gender', columns='sport')
#df1
gender
and columns to sport
, the pivot()
female
and play basketball
.pivot()
function doesn't know what value to put into cell values.pivot_table()
method use the default aggfunc=’mean’
argument to decide this.Use of margins argument to pivot_table()
method
df.pivot_table(index='gender', columns='sport', margins=True)
age | height | weight | |||||||
---|---|---|---|---|---|---|---|---|---|
sport | basketball | cricket | All | basketball | cricket | All | basketball | cricket | All |
gender | |||||||||
female | 23.0 | 20.5 | 21.333333 | 73 | 70.000000 | 71.0 | 150.0 | 150.000000 | 150.0 |
male | 21.0 | 22.0 | 21.500000 | 75 | 72.000000 | 73.5 | 175.0 | 200.000000 | 187.5 |
All | 22.0 | 21.0 | 21.400000 | 74 | 70.666667 | 72.0 | 162.5 | 166.666667 | 165.0 |
In this dataset, since we have ...., so the pivot()
method will flag an error as it donot know what out of the three values to place in the dataframe. However, the pivot_table()
method will use some aggregation function to compute the value to be placed and will work fine....
import pandas as pd
df = pd.read_csv('datasets/waterneed.csv')
df
uniq_id | animal | water_need | speed | |
---|---|---|---|---|
0 | 1001 | elephant | 500 | 20 |
1 | 1002 | elephant | 600 | 25 |
2 | 1003 | elephant | 350 | 29 |
3 | 1004 | tiger | 300 | 60 |
4 | 1005 | tiger | 320 | 65 |
5 | 1006 | tiger | 330 | 70 |
6 | 1007 | tiger | 290 | 69 |
7 | 1008 | tiger | 310 | 72 |
8 | 1009 | zebra | 200 | 75 |
9 | 1010 | zebra | 220 | 77 |
10 | 1011 | zebra | 240 | 72 |
11 | 1012 | zebra | 230 | 80 |
12 | 1013 | zebra | 220 | 82 |
13 | 1014 | zebra | 100 | 79 |
14 | 1015 | zebra | 80 | 76 |
15 | 1016 | lion | 420 | 66 |
16 | 1017 | lion | 600 | 67 |
17 | 1018 | lion | 500 | 68 |
18 | 1019 | lion | 390 | 64 |
19 | 1020 | kangaroo | 410 | 19 |
20 | 1021 | kangaroo | 430 | 22 |
21 | 1022 | kangaroo | 410 | 17 |
The pivot()
method requires atleast two arguments index and columns
The pivot_table()
on the contrary can work on index argument only, the values place are using the mean aggregate function.
df1 = df.pivot_table(index='animal')
df1
speed | uniq_id | water_need | |
---|---|---|---|
animal | |||
elephant | 24.666667 | 1002.0 | 483.333333 |
kangaroo | 19.333333 | 1021.0 | 416.666667 |
lion | 66.250000 | 1017.5 | 477.500000 |
tiger | 67.200000 | 1006.0 | 310.000000 |
zebra | 77.285714 | 1012.0 | 184.285714 |
You can apply aggregation function on the new dataframe as well, such as compute the average speed
df1['speed'].agg('mean')
50.94714285714285
# You can also perfrom aggragtion to summarize data
df1[['speed','water_need']].agg('mean')
speed 50.947143 water_need 374.357143 dtype: float64
Multilevel indexing You can perfrom multi-level indexing by passing the columns as a list to index argument to pivottable()
df.pivot_table(index=['animal','uniq_id'])
speed | water_need | ||
---|---|---|---|
animal | uniq_id | ||
elephant | 1001 | 20 | 500 |
1002 | 25 | 600 | |
1003 | 29 | 350 | |
kangaroo | 1020 | 19 | 410 |
1021 | 22 | 430 | |
1022 | 17 | 410 | |
lion | 1016 | 66 | 420 |
1017 | 67 | 600 | |
1018 | 68 | 500 | |
1019 | 64 | 390 | |
tiger | 1004 | 60 | 300 |
1005 | 65 | 320 | |
1006 | 70 | 330 | |
1007 | 69 | 290 | |
1008 | 72 | 310 | |
zebra | 1009 | 75 | 200 |
1010 | 77 | 220 | |
1011 | 72 | 240 | |
1012 | 80 | 230 | |
1013 | 82 | 220 | |
1014 | 79 | 100 | |
1015 | 76 | 80 |
df.melt()
Method¶pivot()
and pivot_table()
, Pandas melt()
method is also used to transform or reshape data.pd.melt()
method is used to change the DataFrame format from wide to longpd.melt()
method is useful to reshape a DataFrame into a format where one or more columns are identifier variables (id_vars), while all other columns, considered measured variables (value_vars). Its signature is:pandas.melt(Dataframe, id_vars=None, value_vars=None, var_name=None, value_name='value',ignore_index=True)
Where,
id_vars
: tuple, list, or ndarray, optional (Column(s) to use as identifier variables)value_var
: tuple, list, or ndarray, optional (If not specified, uses all columns that are not set as id_vars)var_name
: Name to use for the ‘variable’ column. If None it uses frame.columns.name or ‘variable’.value_name
: Name to use for the ‘value’ column.ignore_index
: bool, default True (If True, original index is ignored. If False, the original index is retained.)
df = pd.read_csv('datasets/weather.csv')
df
day | lahore | karachi | murree | |
---|---|---|---|---|
0 | Monday | 32 | 75 | 41 |
1 | Tuesday | 30 | 77 | 43 |
2 | Wednesday | 28 | 75 | 45 |
3 | Thursday | 22 | 82 | 38 |
4 | Friday | 30 | 83 | 30 |
5 | Saturday | 20 | 81 | 45 |
6 | Sunday | 25 | 77 | 47 |
df1 = pd.melt(df, id_vars =['day'])
df1
day | variable | value | |
---|---|---|---|
0 | Monday | lahore | 32 |
1 | Tuesday | lahore | 30 |
2 | Wednesday | lahore | 28 |
3 | Thursday | lahore | 22 |
4 | Friday | lahore | 30 |
5 | Saturday | lahore | 20 |
6 | Sunday | lahore | 25 |
7 | Monday | karachi | 75 |
8 | Tuesday | karachi | 77 |
9 | Wednesday | karachi | 75 |
10 | Thursday | karachi | 82 |
11 | Friday | karachi | 83 |
12 | Saturday | karachi | 81 |
13 | Sunday | karachi | 77 |
14 | Monday | murree | 41 |
15 | Tuesday | murree | 43 |
16 | Wednesday | murree | 45 |
17 | Thursday | murree | 38 |
18 | Friday | murree | 30 |
19 | Saturday | murree | 45 |
20 | Sunday | murree | 47 |
You can change the name of columns for example, replace the column
variable
andvalue
with some meaningful names. likecity
andtemperature
using thevar_name
andvalue_name
arguments ofmelt()
method
df1 = pd.melt(df, id_vars =['day'], var_name='city', value_name='temperature')
df1
day | city | temperature | |
---|---|---|---|
0 | Monday | lahore | 32 |
1 | Tuesday | lahore | 30 |
2 | Wednesday | lahore | 28 |
3 | Thursday | lahore | 22 |
4 | Friday | lahore | 30 |
5 | Saturday | lahore | 20 |
6 | Sunday | lahore | 25 |
7 | Monday | karachi | 75 |
8 | Tuesday | karachi | 77 |
9 | Wednesday | karachi | 75 |
10 | Thursday | karachi | 82 |
11 | Friday | karachi | 83 |
12 | Saturday | karachi | 81 |
13 | Sunday | karachi | 77 |
14 | Monday | murree | 41 |
15 | Tuesday | murree | 43 |
16 | Wednesday | murree | 45 |
17 | Thursday | murree | 38 |
18 | Friday | murree | 30 |
19 | Saturday | murree | 45 |
20 | Sunday | murree | 47 |
You can filter the rows of your choice using the
value_vars
argument ofmelt()
method
df2 = pd.melt(df, id_vars = ['day'], value_vars =['lahore'], var_name='city', value_name='temperature')
df2
day | city | temperature | |
---|---|---|---|
0 | Monday | lahore | 32 |
1 | Tuesday | lahore | 30 |
2 | Wednesday | lahore | 28 |
3 | Thursday | lahore | 22 |
4 | Friday | lahore | 30 |
5 | Saturday | lahore | 20 |
6 | Sunday | lahore | 25 |
df3 = pd.melt(df, id_vars =['day'], value_vars=['karachi'],var_name='city', value_name='temperature')
df3
day | city | temperature | |
---|---|---|---|
0 | Monday | karachi | 75 |
1 | Tuesday | karachi | 77 |
2 | Wednesday | karachi | 75 |
3 | Thursday | karachi | 82 |
4 | Friday | karachi | 83 |
5 | Saturday | karachi | 81 |
6 | Sunday | karachi | 77 |
# You can achieve the similar result by using Boolean indexing
df1[df1['city'] == 'karachi']
day | city | temperature | |
---|---|---|---|
7 | Monday | karachi | 75 |
8 | Tuesday | karachi | 77 |
9 | Wednesday | karachi | 75 |
10 | Thursday | karachi | 82 |
11 | Friday | karachi | 83 |
12 | Saturday | karachi | 81 |
13 | Sunday | karachi | 77 |
You can apply aggregation function on the new dataframe as well, such as compute the average temperatures
df1
day | city | temperature | |
---|---|---|---|
0 | Monday | lahore | 32 |
1 | Tuesday | lahore | 30 |
2 | Wednesday | lahore | 28 |
3 | Thursday | lahore | 22 |
4 | Friday | lahore | 30 |
5 | Saturday | lahore | 20 |
6 | Sunday | lahore | 25 |
7 | Monday | karachi | 75 |
8 | Tuesday | karachi | 77 |
9 | Wednesday | karachi | 75 |
10 | Thursday | karachi | 82 |
11 | Friday | karachi | 83 |
12 | Saturday | karachi | 81 |
13 | Sunday | karachi | 77 |
14 | Monday | murree | 41 |
15 | Tuesday | murree | 43 |
16 | Wednesday | murree | 45 |
17 | Thursday | murree | 38 |
18 | Friday | murree | 30 |
19 | Saturday | murree | 45 |
20 | Sunday | murree | 47 |
# compute the average temperature of entire dataframe
df1['temperature'].agg('mean')
48.857142857142854
df1[df1['city'] == 'lahore' ]
day | city | temperature | |
---|---|---|---|
0 | Monday | lahore | 32 |
1 | Tuesday | lahore | 30 |
2 | Wednesday | lahore | 28 |
3 | Thursday | lahore | 22 |
4 | Friday | lahore | 30 |
5 | Saturday | lahore | 20 |
6 | Sunday | lahore | 25 |
# compute the average temperature of lahore city only
df1[df1['city'] == 'lahore' ].temperature.agg('mean')
26.714285714285715
df1[df1['city'] == 'karachi' ]
day | city | temperature | |
---|---|---|---|
7 | Monday | karachi | 75 |
8 | Tuesday | karachi | 77 |
9 | Wednesday | karachi | 75 |
10 | Thursday | karachi | 82 |
11 | Friday | karachi | 83 |
12 | Saturday | karachi | 81 |
13 | Sunday | karachi | 77 |
# compute the average temperature of karachi city only
df1[df1['city'] == 'karachi' ].temperature.agg('mean')
78.57142857142857
df.crosstab()
Method¶pd.crosstab()
method is also used for data restructuring and reshaping.pandas.crosstab(index,
columns,
aggfunc=None,
values=None,
margins=False,
normalize=False)
Where,
index
: array-like, Series, or list of arrays/Series (Values to group by in the rows)columns
: array-like, Series, or list of arrays/Series (Values to group by in the columns)values
: array-like, optional (Array of values to aggregate according to the factors. Requires aggfunc be specified)aggfunc
: function, optional If specified, requires values be specified as well.margins
: bool, default False, Add row/column margins (subtotals).normalize
: bool, {‘all’, ‘index’, ‘columns’}, or {0,1}, default False (Normalize by dividing all values by the sum of values)# Reading data from 'datasets/sample.csv' file
import numpy as np
import pandas as pd
df = pd.read_csv('datasets/sample1.csv')
df
city | gender | age | |
---|---|---|---|
0 | Lahore | male | 35 |
1 | Lahore | male | 40 |
2 | Lahore | female | 70 |
3 | Lahore | female | 25 |
4 | Lahore | female | 33 |
5 | Karachi | male | 66 |
6 | Karachi | male | 29 |
7 | Karachi | female | 24 |
8 | Islamabad | female | 20 |
9 | Islamabad | female | 10 |
10 | Islamabad | male | 55 |
11 | Islamabad | male | 15 |
12 | Multan | male | 24 |
13 | Multan | female | 65 |
Suppose we want to get the frequency distribution of males and females. You pass
city
column asindex
argument andgender
column ascolumns
argument to thepd.crosstab()
method. It returns a frequency table containing the male and female count in each city
pd.crosstab(index=df.city, columns=df.gender)
gender | female | male |
---|---|---|
city | ||
Islamabad | 2 | 2 |
Karachi | 1 | 2 |
Lahore | 3 | 2 |
Multan | 1 | 1 |
You can also get the count of total male and female in each city by setting
margins
attribute toTrue
pd.crosstab(index=df.city, columns=df.gender, margins=True)
gender | female | male | All |
---|---|---|---|
city | |||
Islamabad | 2 | 2 | 4 |
Karachi | 1 | 2 | 3 |
Lahore | 3 | 2 | 5 |
Multan | 1 | 1 | 2 |
All | 7 | 7 | 14 |
Instead of getting frequencies in whole number you can also calculate the percentage of male and female in each city. For that you set the
normalize
argument to a value ofTrue
pd.crosstab(index=df.city, columns=df.gender, normalize=True)
gender | female | male |
---|---|---|
city | ||
Islamabad | 0.142857 | 0.142857 |
Karachi | 0.071429 | 0.142857 |
Lahore | 0.214286 | 0.142857 |
Multan | 0.071429 | 0.071429 |
Suppose you want to get the average age of male and female in different cities. To achieve this, set the
values
argument toage
column, and pass the appropariate aggregate function to theaggfunc
argument
pd.crosstab(index=df.city, columns=df.gender, values=df.age, aggfunc=np.mean)
gender | female | male |
---|---|---|
city | ||
Islamabad | 15.000000 | 35.0 |
Karachi | 24.000000 | 47.5 |
Lahore | 42.666667 | 37.5 |
Multan | 65.000000 | 24.0 |