Thaufeki
Reputation: 187
Get the mode value of a column based on another column's value?
I have a dataframe read from a csv file,
>>> df
song artist year \
0 (Iwas)BornToCry Dion 1962
1 (LastNight)IDidn'tGetToSleepAtAll The5thDimension 1972
2 (Sittin'On)TheDockOfTheBay OtisRedding 1968
3 (You'reSoSquare)Baby,IDon'tCare JoniMitchell 1982
4 20-75 WillieMitchell 1964
5 50WaysToLeaveYourLover PaulSimon 1976
6 Abacab Genesis 1982
7 Abraham,MartinAndJohn Dion 1968
8 AbsolutelyRight FiveManElectricalBand 1971
9 ACowboy'sWorkIsNeverDone Sonny&Cher 1972
10 AddictedtoLove RobertPalmer 1986
11 ADreamGoesOnForever ToddRundgren 1974
12 AfterTheLoveHasGone Earth,Wind&Fire 1979
13 AftertheLovin' EngelbertHumperdinck 1977
14 AgainstTheWind BobSeger 1980
15 AHazyShadeOfWinter SimonAndGarfunel 1966
16 Ain'tNoSunshine BillWithers 1971
17 Ain'tTooProudToBeg TheTemptations 1966
18 ALessoninLeavin DottieWest 1980
19 AliveAgain Chicago 1978
20 AllAloneAmI BrendaLee 1962
21 AllIEverNeedIsYou Sonny&Cher 1971
22 Allshewantsis DuranDuran 1989
23 AllThisTime Sting 1991
24 AllThroughTheNight CyndiLauper 1984
25 AlmostbyBeingInLove MichaelJohnson 1978
26 AlmostGrown ChuckBerry 1959
27 AlongComesAWoman Chicago 1985
28 ALoveSong AnneMurray 1974
29 AlreadyGone TheEagles 1974
.. ... ... ...
700 WildHorses RollingStones 1971
701 WillieAndTheHandJive EricClapton 1974
702 WillieNelson BlueEyesCryin' 1975
703 WillTheWolfSurvive LosLobos 1985
704 WillYouLoveMeTomorrow TheShirelles 1961
705 WishSomeoneWouldCare IrmaThomas 1964
706 WithALittleHelpFromMyFriends JoeCocker 1968
707 WithaLittleLuck PaulMcCartney 1978
708 WithOrWithoutYou U2 1987
709 WithYouI'mBornAgain BillyPreston 1979
710 WomanToWoman ShirleyBrown 1975
711 WonderfulWorld,BeautifulPeople JimmyCliff 1969
712 WorldInMyEyes DepecheMode 1990
713 WorriedGuy JohnnyTillotson 1964
714 WouldItMakeAnyDifferenceToYou EttaJames 1963
715 YearsAgo GeorgeHarrison 1981
716 YearsFromNow Dr.Hook 1980
717 You'reTheFirst,TheLast,MyEverything BarryWhite 1974
718 You'vegotaFriend RobertaFlackandDonnyHathaway 1971
719 You'veGotAnotherThingComin' JudasPriest 1982
720 YouCan'tJudgeABookByTheCover BoDiddley 1962
721 YouCan'tRollerSkateInABuffaloHerd RogerMiller 1966
722 YouCanCallMeAl PaulSimon 1986
723 YouDecoratedMyLife KennyRogers 1980
724 YouDon'tOwnMe LesleyGore 1964
725 YouMakeMeFeelLikeDancing LeoSayer 1977
726 YoungHeartsRunFree CandiStaton 1976
727 YourLoveHasLiftedMeHigher RitaCoolidge 1977
728 YouTookTheWordsRightOutOfMyMouth MeatLoaf 1978
729 YvonneElliman IfICan'tHaveYou 1978
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 \
0 1.0 2.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
1 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 6.0 8.0 13.0
2 1.0 1.0 1.0 1.0 5.0 2.0 6.0 4.0 9.0 8.0 9.0 2.0 12.0
3 1.0 1.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 1.0 1.0 4.0 10.0
4 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 7.0 11.0
5 1.0 1.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
6 1.0 1.0 1.0 1.0 5.0 4.0 3.0 1.0 7.0 6.0 8.0 1.0 7.0
7 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
8 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 7.0 11.0
9 1.0 2.0 1.0 4.0 1.0 1.0 1.0 3.0 6.0 3.0 10.0 11.0 9.0
10 1.0 1.0 2.0 2.0 3.0 6.0 4.0 2.0 3.0 9.0 3.0 10.0 3.0
11 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
12 1.0 2.0 3.0 4.0 4.0 5.0 2.0 8.0 2.0 9.0 3.0 8.0 3.0
13 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
14 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
15 1.0 2.0 1.0 1.0 5.0 4.0 3.0 1.0 7.0 6.0 8.0 1.0 7.0
16 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 10.0 9.0 2.0
17 1.0 1.0 2.0 2.0 3.0 6.0 4.0 2.0 3.0 1.0 4.0 3.0 10.0
18 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 6.0 8.0 13.0
19 1.0 2.0 1.0 4.0 1.0 1.0 1.0 3.0 6.0 3.0 5.0 6.0 3.0
20 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 7.0 11.0
21 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
22 1.0 1.0 1.0 1.0 5.0 4.0 6.0 4.0 9.0 6.0 4.0 2.0 12.0
23 1.0 2.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
24 1.0 2.0 3.0 3.0 2.0 3.0 5.0 8.0 5.0 2.0 11.0 7.0 11.0
25 1.0 1.0 1.0 1.0 5.0 4.0 3.0 1.0 7.0 6.0 8.0 1.0 4.0
26 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
27 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 10.0 11.0 9.0
28 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 7.0 11.0
29 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
.. ... ... ... ... ... ... ... ... ... ... ... ... ...
700 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
701 1.0 2.0 1.0 4.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
702 1.0 2.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
703 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
704 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 6.0 8.0 13.0
705 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 6.0 8.0 13.0
706 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 9.0 3.0 10.0 3.0
707 1.0 1.0 2.0 2.0 3.0 6.0 4.0 2.0 3.0 1.0 4.0 3.0 10.0
708 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 10.0 11.0 9.0
709 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 7.0 11.0
710 1.0 1.0 1.0 1.0 5.0 4.0 6.0 4.0 9.0 6.0 4.0 2.0 12.0
711 1.0 2.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
712 1.0 1.0 2.0 2.0 3.0 6.0 4.0 2.0 3.0 1.0 4.0 3.0 10.0
713 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 6.0 8.0 13.0
714 1.0 2.0 1.0 4.0 4.0 5.0 2.0 7.0 1.0 4.0 5.0 6.0 6.0
715 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
716 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 11.0 11.0
717 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
718 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
719 1.0 2.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 9.0 3.0 12.0 1.0
720 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
721 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 5.0 2.0 11.0 7.0 11.0
722 1.0 2.0 1.0 4.0 4.0 5.0 2.0 7.0 1.0 4.0 10.0 11.0 9.0
723 1.0 1.0 2.0 2.0 3.0 6.0 7.0 6.0 8.0 5.0 7.0 9.0 2.0
724 1.0 2.0 1.0 4.0 4.0 5.0 2.0 8.0 2.0 7.0 6.0 8.0 13.0
725 1.0 2.0 3.0 3.0 2.0 3.0 5.0 5.0 4.0 10.0 2.0 5.0 5.0
726 1.0 2.0 2.0 2.0 1.0 1.0 1.0 3.0 6.0 3.0 1.0 4.0 8.0
727 1.0 1.0 1.0 1.0 5.0 2.0 6.0 4.0 9.0 8.0 9.0 2.0 12.0
728 1.0 2.0 1.0 4.0 1.0 1.0 1.0 7.0 6.0 3.0 5.0 6.0 6.0
729 1.0 1.0 2.0 2.0 3.0 6.0 4.0 2.0 3.0 9.0 3.0 10.0 3.0
c14 c15
0 8.0 7.0
1 6.0 1.0
2 1.0 3.0
3 11.0 15.0
4 4.0 10.0
5 8.0 7.0
6 10.0 8.0
7 3.0 2.0
8 4.0 10.0
9 14.0 12.0
10 7.0 6.0
11 9.0 4.0
12 7.0 6.0
13 13.0 4.0
14 5.0 2.0
15 10.0 8.0
16 3.0 8.0
17 11.0 15.0
18 5.0 1.0
19 14.0 6.0
20 4.0 10.0
21 9.0 4.0
22 14.0 5.0
23 8.0 7.0
24 4.0 10.0
25 10.0 9.0
26 3.0 2.0
27 5.0 12.0
28 4.0 10.0
29 3.0 2.0
.. ... ...
700 3.0 2.0
701 8.0 11.0
702 8.0 7.0
703 3.0 2.0
704 6.0 1.0
705 6.0 1.0
706 7.0 6.0
707 11.0 15.0
708 3.0 7.0
709 4.0 10.0
710 14.0 5.0
711 8.0 7.0
712 11.0 15.0
713 6.0 1.0
714 12.0 11.0
715 13.0 14.0
716 4.0 12.0
717 9.0 4.0
718 9.0 4.0
719 2.0 13.0
720 3.0 2.0
721 4.0 10.0
722 14.0 12.0
723 5.0 2.0
724 6.0 1.0
725 13.0 14.0
726 8.0 7.0
727 1.0 3.0
728 5.0 11.0
729 7.0 6.0
What I want to be able to find is the most common value in column 'c15' for any value in 'year'. What would be even better is a small table of the most common values in c15 for any given year.
I know this seems simple enough, but I can't seem to find a solution online.
Upvotes: 1
Views: 539
Answers (1)
YS-L
Reputation: 14738
You can use groupby and apply the value_counts function to extract the most common c15 value within each group:
df.groupby('year').apply(lambda x: x['c15'].value_counts().idxmax())
The output of the above is a Series of most common c15 values indexed by year.
Upvotes: 1
Related Questions
- Return only mode value from DataFrame
- Replace certain values in pandas dataframe with mode of that row
- Find the mode for a pandas column based on filtering on another pandas column
- Return a mode value in a dataframe Python with condition
- How do I find the mode of a column of categorical data?
- How to get the mode of a column in pandas where there are few of the same mode values pandas
- How to calculate mode over two columns in a python dataframe?
- Find the mode of multiple columns for each unique value
- mode of a column in a dataframe based on another column
- Calculate mode of a column in Pandas using other column with same row values