![]() |
|
[Tut] Python – Finding the Most Common Element in a Column - Printable Version +- Sick Gaming (https://sickgaming.net) +-- Forum: Programming (https://sickgaming.net/forum-76.html) +--- Forum: Python (https://sickgaming.net/forum-83.html) +--- Thread: [Tut] Python – Finding the Most Common Element in a Column (/thread-99913.html) |
[Tut] Python – Finding the Most Common Element in a Column - xSicKxBot - 09-06-2022 Python – Finding the Most Common Element in a Column 5/5 – (1 vote) Problem Formulation and Solution Overview This article will show you how to find the most common element in a Pandas Column. To make it more interesting, we have the following running scenario: You have been provided with a downloadable CSV file containing crime statistics for the San Diego area, including their respective NCIC Crime Codes. Question: How would you determine the most common NCIC Crime Code that occurs in San Diego’s jurisdiction?We can accomplish this task by one of the following options:
Preparation Before moving forward, please ensure the Pandas library is installed. Click here if you require instructions. Then, add the following code to the top of each script. This snippet will allow the code in this article to run error-free. Code: import pandas as pdAfter importing the https://blog.finxter.com/numpy-tutorial/Pandas library, this library is referenced by calling the shortcode ( Code: pdMethod 1: Use Pandas mode() This example uses the Code: mode()Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr'])
max_crime = df['crimedescr'].mode()
print(max_crime)The above code reads in the Code: crimedescrCode: dfNext, the Code: crimedescrCode: mode()Code: max_crimeThese results are output to the terminal. Code: 0 10851(A)VC TAKE VEH W/O OWNERName: crimedescr, dtype: objectSo, out of 7,854 rows of crimes committed on a given day for San Diego, the above offense was committed the highest number of times. The above code only provides us with the name of the most common crime; what if we need the crime name and the respective count? Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'ucr_ncic_code'])
max_crime = df['crimedescr'].mode()
max_count = df['ucr_ncic_code'].mode() print(max_crime)
print(max_count)The above code is output to the terminal and displays the following. Code: 0 10851(A)VC TAKE VEH W/O OWNERName: crimedescr, dtype: object0 7000Name: ucr_ncic_code, dtype: int64Now, you are equipped to return to your boss and tell them that 7,000 offenses of 10851 (A) VC TAKE VEH W/O OWNER occurred on a given day in San Diego. Method 2: Use value_counts() This example uses the Code: value_counts()Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'ucr_ncic_code'])
top5_names = df['crimedescr'].value_counts()[:5].index.tolist()
print(top5_names)The above code reads in the Code: crimedescrCode: ucr_ncic_codeCode: dfThen, the Code: crimedescrCode: value_counts()However, since slicing is also appended ( Code: [:5]Code: top5_namesCode: ['10851(A)VC TAKE VEH W/O OWNER', 'TOWED/STORED VEH-14602.6', '459 PC BURGLARY VEHICLE', 'TOWED/STORED VEHICLE', '459 PC BURGLARY RESIDENCE']The above code only provides us with the names of the top 5 most common crimes; what if we need the names and their respective counts? Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'ucr_ncic_code'])
top5 = df['crimedescr'].value_counts()[:5].sort_values(ascending=False)
print(top5)The above output is sent to the terminal. 10851(A)VC TAKE VEH W/O OWNER | 653 TOWED/STORED VEH-14602.6 | 463 459 PC BURGLARY VEHICLE | 462 TOWED/STORED VEHICLE | 434 459 PC BURGLARY RESIDENCE | 356 Name: crimedescr, dtype: int64 | A cleaner way to achieve the same results is to use the following code. Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'ucr_ncic_code'])
top5 = df['crimedescr'].value_counts().nlargest(5)
print(top5)The above code calls the Code: nlargest()10851(A)VC TAKE VEH W/O OWNER | 653 TOWED/STORED VEH-14602.6 | 463 459 PC BURGLARY VEHICLE | 462 TOWED/STORED VEHICLE | 434 459 PC BURGLARY RESIDENCE | 356 Name: crimedescr, dtype: int64 | A much cleaner and more precise output to send to the boss! Method 3: Use value_counts() and idxmax() This example uses Code: value_counts()Code: idxmax()Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'ucr_ncic_code'])
max_crime = df['crimedescr'].value_counts().idxmax()
print(max_crime)The above code reads in the Code: crimedescrCode: ucr_ncic_codeCode: dfThen, the Code: crimedescrCode: value_counts()Next, Code: idxmax()The results save to Code: max_crimeCode: 10851(A)VC TAKE VEH W/O OWNERMethod 4: Use value_counts() and keys() This example uses Code: value_counts()Code: keys()Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'grid', 'ucr_ncic_code'])
top5_grids = df['grid'].value_counts().keys()[:5]
print(top5_grids)The above code reads in the Code: crimedescrCode: gridCode: ucr_ncic_codeCode: dfLet’s break the highlighted line down. If Code: df['grid'].value_counts()Grid # | Grid Total 742 | 115 969 | 105 958 | 100 564 | 80 1084 | 71 Next, the code Code: keys()[:5]Code: Int64Index([742, 969, 958, 564, 1084], dtype='int64')Method 5: Use groupby() This examples uses Code: groupby()Code: df = pd.read_csv('crimes.csv', usecols=['crimedescr', 'ucr_ncic_code']) res = (df.groupby(['ucr_ncic_code','crimedescr']).size() .sort_values(ascending=False) .reset_index(name='count'))
print(res)The above code reads in the Code: crimedescrCode: ucr_ncic_codeCode: dfNext, the Code: groupby()Code: df.groupby(['ucr_ncic_code','crimedescr']).size()Code: print(df.groupby(['ucr_ncic_code','crimedescr']).size())ucr_ncic_code | crimedescr | 909 | | 2 999 | | 1 197 | | 1 664 | | 1 1099 | | 1 As you can see, the other arguments need to be added to turn this into something usable. Sorting the data in descending order and adding a count column will provide the results we are looking for. If the original Method 5 code example was output to the terminal, the following would display. | ucr_ncic_code | crimedescr | count 0 | 2404 | 10851(A)VC TAKE VEH W/O OWNER | 653 1 | 7000 | TOWED/STORED VEH-14602.6 | 463 2 | 2299 | 459 PC BURGLARY VEHICLE | 462 3 | 7000 | TOWED/STORED VEHICLE | 434 4 | 2204 | 459 PC BURGLARY RESIDENCE | 356 Summary This article has provided five (5) ways to find the most common element in a Panda Column. These examples should provide you with enough information to select the one that best meets your coding requirements. Good Luck & Happy Coding! Programming Humor – Python [i]“I wrote 20 short programs in Python yesterday. It was wonderful. Perl, I’m leaving you.” — xkcd[/i]
|