Python and Pandas: Part 3. Baby Names, 1880-2010

Hello Readers,

Here in the third part of the Python and Pandas series, we analyze over 1.6 million baby name records from the United States Social Security Administration from 1880 to 2010. A particular name must have at least 5 occurrences for inclusion into the data set. We will explore reading in multiple raw data files, merging them into one DataFrame, subsetting desired portions of the data, creating new variable metrics, and visualizing results.

As usual, start IPython in your command prompt if you want to follow along. You can find the data here, under National data (it unzips to 'names' folder). Let's jump in.

Data Preview

The baby name files are split by year of birth, all in a similar format: 'yob1880.txt', 'yob1881.txt', and so on to 'yob2010.txt'. You can go ahead and import 'pandas', 'pylab', and 'numpy' modules now or when they required later.

Use the '.read_csv()' method to access the first text file of baby names in 1880. We see that there were 2,000 boy and girl names from the data that year (n>=5), with 3 variables: the name, the sex of the baby, and the birth count for that name.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

C:\Users\wayne>cd .\Documents\python\dataAnalysis\git\ch02 C:\Users\wayne\Documents\python\dataAnalysis\git\ch02>ipython --matplotlib Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] Type "copyright", "credits" or "license" for more information. IPython 2.1.0 -- An enhanced Interactive Python. ? -> Introduction and overview of IPython's features. %quickref -> Quick reference. help -> Python's own help system. object? -> Details about 'object', use 'object??' for extra details. Using matplotlib backend: Qt4Agg In [1]: import pandas as pd In [2]: names1880 = pd.read_csv('names\yob1880.txt',names=['name','sex','births']) In [3]: names1880 Out[3]: <class 'pandas.core.frame.DataFrame'> Int64Index: 2000 entries, 0 to 1999 Data columns (total 3 columns): name 2000 non-null values sex 2000 non-null values births 2000 non-null values dtypes: int64(1), object(2) In [4]: names1880.groupby('sex').births.sum() Out[4]: sex F 90993 M 110493 Name: births, dtype: int64

Performing a quick tab, we group the data by 'sex' and view the count of 'births'. There are 90,993 girls, and 110,493 boys in the 1880 data.

Assembling the Data

Now that we have an idea of the data contents, and we know the pattern of the text file names, we can create a loop to read in the data. At the same time, we add another variable denoting the year for a particular name entry for when all the years are together.

Create a 'years' variable which we will use to iterate through each year text file. Then we read the data, add a 'year' column, append the data to DataFrame 'pieces', then merge them together. Using the '%d' string formatter, we can replace that space with a given variable, 'year'. After using the '.append()' method to add the current 'frame' object to 'pieces', we take advantage of '.concat()' to merge the frames in 'pieces' by row for a completed DataFrame in 'names'. 'ignore_index' should be True because we do not want to keep the original indexes.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

# data split by year # so assemble data into single DataFrame and add year field # use %d string formatter to iterate through the years # add 'year' column # append pieces together # .concat merges by row and do not preserve original row numbers In [9]: pieces = [] In [10]: columns = ['name', 'sex', 'births'] In [11]: years = range(1880, 2011) In [12]: for year in years: ....: path = 'names/yob%d.txt' % year ....: frame = pd.read_csv(path, names=columns) ....: frame['year'] = year ....: pieces.append(frame) ....: names = pd.concat(pieces, ignore_index=True) ....: In [13]: names Out[13]: <class 'pandas.core.frame.DataFrame'> Int64Index: 1690784 entries, 0 to 1690783 Data columns (total 4 columns): name 1690784 non-null values sex 1690784 non-null values births 1690784 non-null values year 1690784 non-null values dtypes: int64(2), object(2) # 1,690,783 rows of data with 4 columns In [14]: names.save('names.pkl')

In the 'names' DataFrame, we have 1,690,783 names from years 1880 to 2010 with 4 columns, including the year. Remember to pickle the DataFrame with '.save()', in other words, save it. It is a hefty file, around 63 MB in size, but Python will do all the heavy lifting!

Exploring the Data

First off, a pivot table is in order. Let's move the 'sex' to the columns, and the 'year' in the rows, while positioning the 'births' in values. Calling '.tail()' will give us the last 5 rows in the table. To get a bigger picture, plot the table of births stratified by sex and year.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

In [17]: total_births = names.pivot_table('births', rows='year', cols='sex', aggfunc=sum) In [19]: total_births.tail() Out[19]: sex F M year 2006 1896468 2050234 2007 1916888 2069242 2008 1883645 2032310 2009 1827643 1973359 2010 1759010 1898382 In [20]: total_births.plot(title='Total Births by sex and year') Out[20]: <matplotlib.axes.AxesSubplot at 0x1a5f4730>

Figure 1. Total births by sex and year

We can observe the birth trends rise and fall based on economic trends- they births tend to fall in times of recession, and male births started to outpace female births after WWII.

Adding Proportion and Subsetting Top 1000 Names

Here we add the column of proportions to our 'names' DataFrame. The proportions will be the number of births out of each total births grouped by year and sex. We define a new method, 'add_prop()' and convert the argument value to a float type for non-integer division purposes. Then we divide the births by the sum of births in the grouping, and return the number.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

# group by year and sex # add proportion of babies given certain name relative to number of births In [23]: def add_prop(group): ....: # integer division floors ....: births = group.births.astype(float) ....: group['prop'] = births / births.sum() ....: return group ....: In [24]: names = names.groupby(['year','sex']).apply(add_prop) In [25]: names Out[25]: <class 'pandas.core.frame.DataFrame'> Int64Index: 1690784 entries, 0 to 1690783 Data columns (total 5 columns): name 1690784 non-null values sex 1690784 non-null values births 1690784 non-null values year 1690784 non-null values prop 1690784 non-null values dtypes: float64(1), int64(2), object(2) # check to see values by group sum to 1 In [28]: import numpy as np In [29]: np.allclose(names.groupby(['year','sex']).prop.sum(),1) Out[29]: True # subset top 1000 births In [30]: def get_top1000(group): ....: return group.sort_index(by='births', ascending=False)[:1000] ....: In [31]: grouped = names.groupby(['year','sex']) In [32]: top1000 = grouped.apply(get_top1000) In [37]: top1000 Out[37]: <class 'pandas.core.frame.DataFrame'> MultiIndex: 261877 entries, (1880, F, 0) to (2010, M, 1677643) Data columns (total 5 columns): name 261877 non-null values sex 261877 non-null values births 261877 non-null values year 261877 non-null values prop 261877 non-null values dtypes: float64(1), int64(2), object(2)

We pass the groups by year and sex to the 'add_prop()' method using '.apply()'. Confirming the new 'prop' column, the new 'names' DataFrame now has 5 columns. To ensure the birth proportions by groups are accurate, we verify using '.allclose()' method in the 'numpy' module, and compare the sum to 1. Python returns 'True', and we are assured the column values are correct.

With this new DataFrame, we now will subset the top 1000 names by birth in each year and sex grouping. Define a new method, 'get_top1000()', and which sorts the births in descending order, and returns the first 1000 entries. We pass the 'names' DataFrame grouped by year and sex to the 'get_top1000()' method into our new DataFrame, 'top1000'. Instead of over 1.6 million entries, we now have 261,877 entries with which to work.

Some Naming Trends

Because the data include information spanning from 1880 to 2010, we can examine trends in time of baby names for any changes. As usual, remember to save and pickle the 'top1000' DataFrame as we go along the analysis. Begin by separating sex into two different DataFrames for later use. 

Now create a pivot table from 'top1000', with births as summed values, years in rows, and names in the columns. There are 131 rows, one for each year and 6,865 columns, or names. We will subset by column, take only specific names, and plot the births for the selected names by year in a single plot. You can choose different names, and I chose John, Harry, Mary, and Marilyn as sample names.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

# analyze naming trends In [38]: top1000.save('top1000.pkl') In [39]: boys = top1000[top1000.sex == 'M'] In [40]: girls = top1000[top1000.sex == 'F'] In [41]: total_births = top1000.pivot_table('births', rows='year', cols='name', aggfunc=sum) In [42]: total_births Out[42]: <class 'pandas.core.frame.DataFrame'> Int64Index: 131 entries, 1880 to 2010 Columns: 6865 entries, Aaden to Zuri dtypes: float64(6865) In [43]: subset = total_births[['John','Harry','Mary','Marilyn']] In [46]: subset.plot(subplots=True, figsize=(12,10),grid=False,title='Number of births per year ') Out[46]: array([<matplotlib.axes.AxesSubplot object at 0x1A7144D0>, <matplotlib.axes.AxesSubplot object at 0x14C13E30>, <matplotlib.axes.AxesSubplot object at 0x1AA527F0>, <matplotlib.axes.AxesSubplot object at 0x172A05B0>], dtype=object) # plot 'Wayne' In [47]: subsetw = total_births[['Wayne']] In [48]: subsetw.plot(title='Wayne') Out[48]: <matplotlib.axes.AxesSubplot at 0x14c0f2f0> # names growing out of favor?

Figure 2. Number of Births per Year, Selected Names

For John, Harry, and Mary, they have bimodal peaks about 1920s and 1950s. For Marilyn, the name became steadily popular from the 1930's to the late 1950's. For all four names, we observe a fall in births per year. Are those names really becoming more uncommon? We will discover what is happening in the data below.

Curious, I plotted my name to see the birth time trends of 'Wayne'. It follows the same rise, peak, and fall around the 1950's, though it followed less of a bimodal distribution.

Figure 3. Number of Births per Year, For Name: Wayne

Baby Name Diversity

First, a spoiler: the drop in births for certain names have something to do with the name diversity- what parents choose to name their child. The trend changes from 1950's onwards. To examine this change, we turn to the variable we created earlier, the proportion of births in each group by year and sex. So we create a pivot table from the 'top1000', but this time with the sum values as 'prop', 'year' as rows, and 'sex' as columns.

This will allow us to plot Figure 4. Note how proportion total starts at 1.0 in 1880, and slowly drops in 1960 for females and in 1970 for males. The decline in proportion of births accounted by the top 1000 names has declined to around 74% for females and 85% for males by 2010. That means the share of births for other names outside of the top 1000 has risen. More parents are choosing different, more uncommon names to call their newborns.

Figure 4. Proportion of Top Births by Year and Sex

We can check this by examining the boys and girls DataFrames we created earlier. We subset the year 2010, sort by proportion in descending order, then take the cumulative sum of the proportions of births. Taking the first 10 names, we see that the top name were roughly 1.15% of the total male births in 2010. Using '.searchsorted(0.5)' to find the sorted index of the 50th percentile, Python returns 116. Therefore 117 names consist of 50% of the male births in 2010. We compare this number to the 50% percentile of male births in 1880, which is 25. From 1880 to 2010, the number of names in the top 50% percentile of male births increased over 350% from 25 to 117. Male name diversity sure increased over the years.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79

# exploring increases in naming diversity # fewer parents choosing common names for children In [15]: table = top1000.pivot_table('prop', rows= ....: 'year',cols='sex', aggfunc=sum) In [17]: table.plot(title='Sum of table1000.prop by year and sex', \ ....: yticks=np.linspace(0,1.2,13), xticks=range(1880,2020,10)) Out[17]: <matplotlib.axes.AxesSubplot at 0x6a1ead0> # names proportion going down from 1 from top 1000 names In [20]: df = boys[boys.year==2010] In [21]: df Out[21]: <class 'pandas.core.frame.DataFrame'> MultiIndex: 1000 entries, (2010, M, 1676644) to (2010, M, 1677643) Data columns (total 5 columns): name 1000 non-null values sex 1000 non-null values births 1000 non-null values year 1000 non-null values prop 1000 non-null values dtypes: float64(1), int64(2), object(2) In [22]: prop_cumsum = df.sort_index(by='prop', ascending=False).prop.cumsum() In [23]: prop_cumsum[:10] Out[23]: year sex 2010 M 1676644 0.011523 1676645 0.020934 1676646 0.029959 1676647 0.038930 1676648 0.047817 1676649 0.056579 1676650 0.065155 1676651 0.073414 1676652 0.081528 1676653 0.089621 dtype: float64 In [24]: prop_cumsum.searchsorted(0.5) Out[24]: 116 # index 116, so 117 names in top 50% in 2010 In [25]: df1900 = boys[boys.year==1900] In [26]: prop1900 = df1900.sort_index(by='prop', ascending=False).prop.cumsum() In [27]: prop1900.searchsorted(0.5)+1 Out[27]: 25 # in 1900, top 50% of names covered with 25 names # so there is a large increase in name diversity In [28]: def get_quantile(group, q=0.5): ....: group = group.sort_index(by='prop', ascending=False) ....: return group.prop.cumsum().searchsorted(q)+1 ....: In [29]: diversity = top1000.groupby(['year','sex']).apply(get_quantile) In [30]: diversity = diversity.unstack('sex') In [31]: diversity.head() Out[31]: sex F M year 1880 38 14 1881 38 14 1882 38 15 1883 39 15 1884 39 16 In [32]: diversity.plot(title='Number of popular names in top 50%') Out[32]: <matplotlib.axes.AxesSubplot at 0x2075c1f0>

This diversity increase can be said for female births as well. Rather than take the number of names in the top 50th percentile for 1880 and 2010, we calculate them for all the years, and both male and female names. Define a new function, 'get_quantile(group, q=0.5)', which sorts the 'group' argument by variable 'prop' in descending order, and returns the index of the sorted cumulative sum value at 0.5, adding 1 at the end to account for the index. 

Pass this method through the top 1000 names grouped by year and sex into the 'diversity' DataFrame. Reconfigure the DataFrame table by placing 'sex' in the columns with '.unstack()' to finalize the table. Take a peak at the first 5 years of the 'diversity' data with '.head()', and observe that 38 female names and 14 male names accounted for 50% of the top births in 1880. Lastly we plot the 'diversity' DataFrame, shown below.

Figure 5. Popular Baby Names in 50% percentile

We see a distinct increase in name diversity around 1985 for both males and females. Historically, female names were more diverse than male names. By 2010, the number of top female names accounting for the top 50 birth percentile more than doubled the male name counterpart.

Again, we reach the end of another lengthy, but I hope, enjoyable post in Python and Pandas concerning baby names. We explored and manipulated a dataset of 1.6 million rows, re-organized DataFrames, created new variables, and visualized various name metrics, all after accessing data split into 131 text files. There is more on baby names we will explore in Part B of this post. So stay tuned for more!


Thanks for reading,

Wayne
@beyondvalence
LinkedIn

Python and Pandas Series:
1. Python and Pandas: Part 1: bit.ly and Time Zones
2. Python and Pandas: Part 2. Movie Ratings
3. Python and Pandas: Part 3. Baby Names, 1880-2010
4. Python and Pandas: Part 4. More Baby Names
.

Natural Language Processing in Python: Part 3. Indexing Lists

Hello Readers,

Here we continue the Text Analysis in Python blog series by examining list manipulation. Keeping in mind we work with large amounts of text, we increase our efficiency by knowing how to manipulate it in Python. Working with lists is crucial towards natural language processing. As usual, we will use the nltk module and IPython. Keep an eye out for Monty Python.

Start IPython from the command line and let us begin.

Sentences as Lists

Previously when we worked with nltk we used the texts and terms inside them. However, nltk also provides the first sentence from each of the 9 texts as variables we can use as well (sent1, sent2... sent9). The sentences are of list type, which are enclosed in brackets [ ]. Each element in a list can be composed of strings, numbers, even other lists.

So by typing the "sent" and the sentence number, we can print the tokens in the list.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

C:\Users\wayne>ipython --matplotlib Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] Type "copyright", "credits" or "license" for more information. IPython 2.1.0 -- An enhanced Interactive Python. ? -> Introduction and overview of IPython's features. %quickref -> Quick reference. help -> Python's own help system. object? -> Details about 'object', use 'object??' for extra details. Using matplotlib backend: Qt4Agg In [1]: from nltk.book import * *** Introductory Examples for the NLTK Book *** Loading text1, ..., text9 and sent1, ..., sent9 Type the name of the text or sentence to view it. Type: 'texts()' or 'sents()' to list the materials. text1: Moby Dick by Herman Melville 1851 text2: Sense and Sensibility by Jane Austen 1811 text3: The Book of Genesis text4: Inaugural Address Corpus text5: Chat Corpus text6: Monty Python and the Holy Grail text7: Wall Street Journal text8: Personals Corpus text9: The Man Who Was Thursday by G . K . Chesterton 1908 In [29]: sents() sent1: Call me Ishmael . sent2: The family of Dashwood had long been settled in Sussex . sent3: In the beginning God created the heaven and the earth . sent4: Fellow - Citizens of the Senate and of the House of Representatives : sent5: I have a problem with people PMing me to lol JOIN sent6: SCENE 1 : [ wind ] [ clop clop clop ] KING ARTHUR : Whoa there ! sent7: Pierre Vinken , 61 years old , will join the board as a nonexecutive director Nov. 29 . sent8: 25 SEXY MALE , seeks attrac older single lady , for discreet encounters . sent9: THE suburb of Saffron Park lay on the sunset side of London , as red and ragged as a clo ud of sunset . In [2]: sent1 Out[2]: ['Call', 'me', 'Ishmael', '.']

Modifying Lists

We can modify the list by appending elements, which add those elements to the end of the list. Using ".append()" we can change the list to our liking, and also use an operation called concatenation, where we join two lists together with a "+" sign. It is like addition for lists and strings.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

# appending In [3]: sent1.append('hello there') In [4]: sent1 Out[4]: ['Call', 'me', 'Ishmael', '.', 'hello there'] In [5]: sent1[::-1] Out[5]: ['hello there', '.', 'Ishmael', 'me', 'Call'] # concatenation In [19]: new = sent2+sent3 In [20]: new Out[20]: ['The', 'family', 'of', 'Dashwood', 'had', 'long', 'been', 'settled', 'in', 'Sussex', '.', 'In', 'the', 'beginning', 'God', 'created', 'the', 'heaven', 'and', 'the', 'earth', '.']

As we can see, appending 'hello there' simply adds it to the end of "sent1". Calling "sent1", again we see the extra element at the end, and the change is permanent. With concatenation, we join "sent2" and "sent3" together with "+", and we end up with two mashed first sentences in one list.

List Indexes

Why do Python indexes start at zero?

Remember that in Python, lists indexes start at zero, and the last element is n-1. It is not because computer scientists want to confuse people. This is due to how Python reads the lists. Think of it this way: when Python first accesses the list, the first element is 'read', and to read the next (second) element, Python has to move once, and to read the third element, Python has to move again. Reading the third element Python has moved twice, so the third element index is 2. So by default (0), the pointer is aimed at first element, the indexes represents the number of iterations Python must move. For example, to reach the fifth element, Python must move 4 times.

We can use this concept to find indexes of certain tokens, or retrieve elements in certain indexes in lists. In index 15 of "text1", or the 16th token- not word because tokens include punctuation- is the word 'Consumptive' or someone likely with tuberculosis. Note that when we use ".index('Consumptive)", it finds the first occurrence of the word and returns its position. 

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

In [11]: text1[15] Out[11]: u'Consumptive' In [12]: text1.index('Consumptive') Out[12]: 15 In [13]: text1.index('consumptive') --------------------------------------------------------------------------- ValueError Traceback (most recent call last) <ipython-input-13-e45cff026a60> in <module>() ----> 1 text1.index('consumptive') C:\Python27\lib\site-packages\nltk\text.pyc in index(self, word) 369 Find the index of the first occurrence of the word in the text. 370 """ --> 371 return self.tokens.index(word) 372 373 def readability(self, method): ValueError: 'consumptive' is not in list

However, because it is case sensitive, 'consumptive' is not the same as 'Consumptive' so finding it's index will not return 15. Instead it returns an ValueError because there are no lowercase consumptives tokens in the text!

Slicing Lists

With slicing, we can take sections of elements from the text lists, by denoting the start and stop index points. The stop index point indicates index limit, and will not print out that index. So for an slice of index from 10 to 20, it will return elements from index 10 to 19- starting at 10 and up to 20. Below we take the index of Monty Python and the Holy Grail in "text6", with starting index of 2301 up to 2320.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

# slicing In [14]: text6 Out[14]: <Text: Monty Python and the Holy Grail> In [15]: text6[2301:2320] Out[15]: [u'for', u'no', u'man', u'.', u'ARTHUR', u':', u'So', u'be', u'it', u'!', u'ARTHUR', u'and', u'BLACK', u'KNIGHT', u':', u'Aaah', u'!,', u'hiyaah', u'!,'] # index starts at zero In [16]: sent6 Out[16]: ['SCENE', '1', ':', '[', 'wind', ']', '[', 'clop', 'clop', 'clop', ']', 'KING', 'ARTHUR', ':', 'Whoa', 'there', '!'] In [17]: sent6[0] Out[17]: 'SCENE' In [18]: sent6[1] Out[18]: '1'

Reading the output list, we notice this is where King Arthur encounters and duels the Black Knight:

Also, the first sentence in Monty Python introduces the (in)famous coconut horses and King Arthur.

More Slicing

There is more than one way to slice bread, and likewise there is more than one way to slice a list. For example, Python allows us to use negative indexes. Negative, you might ask? It is counter-intuitive, but simple to reorient yourself with negative indexes by starting at the end of the list. When you think of the first element as zero, simply going negative takes you to the other end- the last element. So negative indexes start from -1 to -n, where n is the length of the list, and -n is the first element (index 0).

Using the same previous Monty Python sentence, we find the last index by subtracting 1 from the length of the list. To confirm that an index of -1 is indeed the last element in the list, we compare it to our "!" result. And yes, they are the same. Taking the second to last element gives us "there" which is the correct element.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

# slicing indexes - last and negative values In [21]: len(sent6) Out[21]: 17 In [22]: sent6[16] Out[22]: '!' In [23]: sent6[-1] Out[23]: '!' In [24]: sent6[-2] Out[24]: 'there' # slicing negative values In [26]: sent6[-2:] Out[26]: ['there', '!'] In [27]: sent6[:-2] Out[27]: ['SCENE', '1', ':', '[', 'wind', ']', '[', 'clop', 'clop', 'clop', ']', 'KING', 'ARTHUR', ':', 'Whoa']

What if we take a slice using a negative starting point? For example, if we choose -2 as our starting index, what will Python return? Take a look at the code above. Python returns the second to last result to the end of the list. And what about a negative index for the ending point? Just what you guessed. It returns the elements at beginning of the list, all the way up to, but not including, the second to last element, "there".

Replacing Elements

We can assign specific values to elements in lists using the "=" sign. Remember the concatenated list of sentence 2 and 3? We will use that new sentence and replace the first element, "The", with another element, "polar bears". Using the zero index for the first element, we assign it 'polar bear's with an equals sign. Then our new sentence will reflect the change.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

# replacement In [39]: new Out[39]: ['The', 'family', 'of', 'Dashwood', 'had', 'long', 'been', 'settled', 'in', 'Sussex', '.', 'In', 'the', 'beginning', 'God', 'created', 'the', 'heaven', 'and', 'the', 'earth', '.'] In [40]: new[0] = 'polar bears' In [41]: new Out[41]: ['polar bears', 'family', 'of', 'Dashwood', 'had', 'long', 'been', 'settled', 'in', 'Sussex', '.', 'In', 'the', 'beginning', 'God', 'created', 'the', 'heaven', 'and', 'the', 'earth', '.'] In [43]: new[1:4] = ['do not','live in','antarctica'] In [44]: new Out[44]: ['polar bears', 'do not', 'live in', 'antarctica', 'had', 'long', 'been', 'settled', 'in', 'Sussex', '.', 'In', 'the', 'beginning', 'God', 'created', 'the', 'heaven', 'and', 'the', 'earth', '.']

Additionally we can replace multiple entries at the same time by assigning the desired new list to the slice which we want to replace. For example, if we want to replace the second through fourth elements in the list, we can assign them new elements from a different list. Then we can view the modified new list, saying that polar bears do not live in Antarctica.

OK folks, here we explored how to manipulate lists, target certain elements of a list with indexes, and replace elements. Stay tuned for more on natural language processing with Python.


Thanks for reading,

Wayne
@beyondvalence
LinkedIn

Text Analysis Series:
1. Natural Language Processing in Python: Part 1. Texts
2. Natural Language Processing in Python: Part 2. Counting Vocabulary
3. Natural Language Processing in Python: Part 3. Indexing Lists
4. Natural Language Processing in Python: Part 4. Frequency Distributions, Word Selections, & Collocations
.

Python and Pandas: Part 2. Movie Ratings

Hello Readers,

Here is Part 2 of the Pandas and Python series, where we examine movie ratings data from University of Minnesota's Movielens recommendation system. The 1 million rows of data are available here as a 'zip' and 'readme' file. We will use Pandas in Python to read, manipulate, and massage the rating data. The sizable data include over 1 million ratings from 6,000 users, concerning 4,000 movies.

As a reference, I am using Python 2.7.8 via IPython 2.1.0. Start IPython and let's jump in.

Loading the Rating Data

After navigating to the proper directory with the three ending in .dat, start IPython with "matplotlib". Immediately import "pylab" and "pandas". While loading the user, rating and movie data files, we create the variable names for each DataFrame. 

For example, the "users" DataFrame has "user_id", "gender", "age", "occupation", and "zip" code for its variables. Preview the first 5 rows by slicing "[:5]".

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

C:\Users\wayne\Documents\python\dataAnalysis\git\ch02>ipython --matplotlib Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit (Intel)] Type "copyright", "credits" or "license" for more information. IPython 2.1.0 -- An enhanced Interactive Python. ? -> Introduction and overview of IPython's features. %quickref -> Quick reference. help -> Python's own help system. object? -> Details about 'object', use 'object??' for extra details. Using matplotlib backend: Qt4Agg In [1]: import pylab In [2]: import pandas as pd In [3]: unames = ['user_id','gender','age','occupation','zip'] In [4]: users = pd.read_table('movielens/users.dat',sep='::',header=None, ...: names=unames) In [5]: users[:5] Out[5]: user_id gender age occupation zip 0 1 F 1 10 48067 1 2 M 56 16 70072 2 3 M 25 15 55117 3 4 M 45 7 02460 4 5 M 25 20 55455 In [16]: rnames = ['user_id','movie_id','rating','timestamp'] In [17]: ratings = pd.read_table('movielens/ratings.dat',sep='::',header=None,names=rnames) In [18]: ratings[:5] Out[18]: user_id movie_id rating timestamp 0 1 1193 5 978300760 1 1 661 3 978302109 2 1 914 3 978301968 3 1 3408 4 978300275 4 1 2355 5 978824291 In [19]: mnames = ['movie_id','title','genres'] In [20]: movies = pd.read_table('movielens/movies.dat',sep='::',header=None,names=mnames) In [21]: movies[:5] Out[21]: movie_id title genres 0 1 Toy Story (1995) Animation|Children's|Comedy 1 2 Jumanji (1995) Adventure|Children's|Fantasy 2 3 Grumpier Old Men (1995) Comedy|Romance 3 4 Waiting to Exhale (1995) Comedy|Drama 4 5 Father of the Bride Part II (1995) Comedy In [22]: ratings Out[22]: <class 'pandas.core.frame.DataFrame'> Int64Index: 1000209 entries, 0 to 1000208 Data columns (total 4 columns): user_id 1000209 non-null values movie_id 1000209 non-null values rating 1000209 non-null values timestamp 1000209 non-null values dtypes: int64(4)

Looking at the "ratings" DataFrame specifically, we find that it has 1,000,209 entries (rows), with 4 columns- "user_id", "movie_id", "rating", and "timestamp". We see that the ratings are integers up to 5. The common variables among the 3 DataFrames are "user_id" and "movie_id".

Merging DataFrames

Working with three data from three separate DataFrames will hamper our efficiency in querying rows we require for analysis. The files were normalized for size efficiency for downloading, and now since we have them on our hard drive, we can go ahead and merge them together (de-normalize). 

Once we merge the DataFrames, there will be redundant elements, as user information will be repeated for various movies they rated, and movie information will be repeated as well since movies have multiple users rating them. That is why the file will be larger in size (think megabytes vs kilobytes). For storage purposes, a normalized format is more optimal, whereas for fast read/write times, de-normalized tables are more efficient.

From pandas, use the ".merge()" method twice to merge all three DataFrames together. The method will merge by similar column names, which is why we named them "user_id", "movie_id". 

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

In [23]: data = pd.merge(pd.merge(ratings,users),movies) In [24]: data Out[24]: <class 'pandas.core.frame.DataFrame'> Int64Index: 1000209 entries, 0 to 1000208 Data columns (total 10 columns): user_id 1000209 non-null values movie_id 1000209 non-null values rating 1000209 non-null values timestamp 1000209 non-null values gender 1000209 non-null values age 1000209 non-null values occupation 1000209 non-null values zip 1000209 non-null values title 1000209 non-null values genres 1000209 non-null values dtypes: int64(6), object(4) In [25]: data.ix[0] Out[25]: user_id 1 movie_id 1193 rating 5 timestamp 978300760 gender F age 1 occupation 10 zip 48067 title One Flew Over the Cuckoo's Nest (1975) genres Drama Name: 0, dtype: object

Now the complete table has 1,000,209 rows with 10 variable columns. Taking the first row index with ".ix[0]", we can examine the merge results within the first row. This female gave the movie "One Flew Over the Cuckoo's Nest" the highest 5 star rating. And looking at her age, 1, we only know that she is less than 18 years of age. Note that the occupation categories are explained in the included README file. (Go ahead, read it).

Pivot Table

Since there are 10 variables, we can massage the data into showing us some patterns between the variables. Not only can we see the average ratings for each movie, we can stratify them by gender. We accomplish this with ".pivot_table()", while specifying the value we want stratified, the variables we want to see in the rows and columns, and how we want to values to be presented (mean).

Taking the first 8 movies, and observe that they are ordered alphabetically by symbol/punctuation, numbers, capitalized letters, and lowercase letters. We can see the average ratings for the movies by gender.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

In [26]: mean_ratings = data.pivot_table('rating',rows='title',cols='gender',aggfunc='mean') In [27]: mean_ratings[:8] Out[27]: gender F M title $1,000,000 Duck (1971) 3.375000 2.761905 'Night Mother (1986) 3.388889 3.352941 'Til There Was You (1997) 2.675676 2.733333 'burbs, The (1989) 2.793478 2.962085 ...And Justice for All (1979) 3.828571 3.689024 1-900 (1994) 2.000000 3.000000 10 Things I Hate About You (1999) 3.646552 3.311966 101 Dalmatians (1961) 3.791444 3.500000

For the cartoon, 101 Dalmations released in 1961, the average female rating was ~3.79, while the average male rating was slightly lower at 3.50. Sometimes the average rating would be close, and others one gender would rate a movie more favorably than the other gender.

Movie Rating Subsetting and Sorting

Here we aim to work with a robust set of movies with 250 or more ratings by subsetting the data. First we create an index in active_titles of movies grouped by their title using ".groupby('title').size()" or their count. Then using the ratings_by_title, we select only those with 250 or more ratings through the ".index[]" into active_titles.

From the gender stratified mean_ratings created above, we can subset those movies titles with 250 or more ratings, with ".ix[]" for index. Now the DataFrame is reduced to 1,216 movies, from 6,000.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

In [9]: ratings_by_title = data.groupby('title').size() In [10]: ratings_by_title[:10] Out[10]: title $1,000,000 Duck (1971) 37 'Night Mother (1986) 70 'Til There Was You (1997) 52 'burbs, The (1989) 303 ...And Justice for All (1979) 199 1-900 (1994) 2 10 Things I Hate About You (1999) 700 101 Dalmatians (1961) 565 101 Dalmatians (1996) 364 12 Angry Men (1957) 616 dtype: int64 In [11]: active_titles = ratings_by_title.index[ratings_by_title >= 250] In [12]: active_titles Out[12]: Index(['burbs, The (1989), 10 Things I Hate About You (1999), 101 Dalmatians (1961), ..., Young Sherlock Holmes (1985), Zero Effect (1998), eXistenZ (1999)], dtype=object) In [13]: mean_ratings = mean_ratings.ix[active_titles] In [14]: mean_ratings Out[14]: <class 'pandas.core.frame.DataFrame'> Index: 1216 entries, 'burbs, The (1989) to eXistenZ (1999) Data columns (total 2 columns): F 1216 non-null values M 1216 non-null values dtypes: float64(2) In [15]: top_female_ratings = mean_ratings.sort_index(by='F',ascending=False) In [16]: top_female_ratings[:10] Out[16]: gender F M title Close Shave, A (1995) 4.644444 4.473795 Wrong Trousers, The (1993) 4.588235 4.478261 Sunset Blvd. (a.k.a. Sunset Boulevard) (1950) 4.572650 4.464589 Wallace & Gromit: The Best of Aardman Animation (1996) 4.563107 4.385075 Schindler's List (1993) 4.562602 4.491415 Shawshank Redemption, The (1994) 4.539075 4.560625 Grand Day Out, A (1992) 4.537879 4.293255 To Kill a Mockingbird (1962) 4.536667 4.372611 Creature Comforts (1990) 4.513889 4.272277 Usual Suspects, The (1995) 4.513317 4.518248

From this refined data, we can reorder and display the top rated titles by females with ".sort_index()". The top female averaged rated title goes to 'A Close Shave', with 'The Wrong Trousers' coming in second. While the average male ratings were not ordered, they are similar in range, due to the quality of the movies. So, users enjoying a movie would be more inclined to rate a movie, thus this subset of movies with 250+ ratings is slightly more biased than the original total data.

Measuring Rating Disagreement

However females and males did not rate movies similarly. We can create a new variable, 'diff', for rating difference of males - females. Then we can sort the mean_ratings with the largest difference first. The biggest disparity in rating where female movie ratings measured higher than males belonged to 'Dirty Dancing', 'Jumpin' Jack Flash', and 'Grease'.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

## measuring rating disagreement In [17]: mean_ratings['diff'] = mean_ratings['M'] - mean_ratings['F'] In [19]: sorted_by_diff = mean_ratings.sort_index(by='diff') In [20]: sorted_by_diff[:10] Out[20]: gender F M diff title Dirty Dancing (1987) 3.790378 2.959596 -0.830782 Jumpin' Jack Flash (1986) 3.254717 2.578358 -0.676359 Grease (1978) 3.975265 3.367041 -0.608224 Little Women (1994) 3.870588 3.321739 -0.548849 Steel Magnolias (1989) 3.901734 3.365957 -0.535777 Anastasia (1997) 3.800000 3.281609 -0.518391 Rocky Horror Picture Show, The (1975) 3.673016 3.160131 -0.512885 Color Purple, The (1985) 4.158192 3.659341 -0.498851 Age of Innocence, The (1993) 3.827068 3.339506 -0.487561 Free Willy (1993) 2.921348 2.438776 -0.482573 In [21]: sorted_by_diff[::-1][:15] Out[21]: gender F M diff title Good, The Bad and The Ugly, The (1966) 3.494949 4.221300 0.726351 Kentucky Fried Movie, The (1977) 2.878788 3.555147 0.676359 Dumb & Dumber (1994) 2.697987 3.336595 0.638608 Longest Day, The (1962) 3.411765 4.031447 0.619682 Cable Guy, The (1996) 2.250000 2.863787 0.613787 Evil Dead II (Dead By Dawn) (1987) 3.297297 3.909283 0.611985 Hidden, The (1987) 3.137931 3.745098 0.607167 Rocky III (1982) 2.361702 2.943503 0.581801 Caddyshack (1980) 3.396135 3.969737 0.573602 For a Few Dollars More (1965) 3.409091 3.953795 0.544704 Porky's (1981) 2.296875 2.836364 0.539489 Animal House (1978) 3.628906 4.167192 0.538286 Exorcist, The (1973) 3.537634 4.067239 0.529605 Fright Night (1985) 2.973684 3.500000 0.526316 Barb Wire (1996) 1.585366 2.100386 0.515020 In [33]: rating_std_by_title = data.groupby('title')['rating'].std() In [34]: rating_std_by_title = rating_std_by_title.ix[active_titles] In [35]: rating_std_by_title.order(ascending=False)[:10] Out[35]: title Dumb & Dumber (1994) 1.321333 Blair Witch Project, The (1999) 1.316368 Natural Born Killers (1994) 1.307198 Tank Girl (1995) 1.277695 Rocky Horror Picture Show, The (1975) 1.260177 Eyes Wide Shut (1999) 1.259624 Evita (1996) 1.253631 Billy Madison (1995) 1.249970 Fear and Loathing in Las Vegas (1998) 1.246408 Bicentennial Man (1999) 1.245533 Name: rating, dtype: float64

Conversely, if we flipped the order around, where the male ratings were higher than female ratings, the differential pointed to 'The Good, The Bad and the Ugly', 'The Kentucky Fried Movie', and 'Dumb & Dumber'. Both sets of films, while good, cater stereo-typically to different genders.

Another way to measure the differential is through the spread, otherwise known as the standard deviation. Among all movies and users, 'Dumb & Dumber' took the top prize with the highest standard deviation of 1.321. Therefore, the ratings users assigned to 'Dumb & Dumber' differed more among each other than for other movies, although 'The Blair Witch Project' came in a close second at 1.316

Average User Age by Movie

Grouping by movie, we can find the average age of the user who rates the movies. We also use active_titles to subset those movies with a large number of ratings. The movies with the youngest users rating them are: 'Can't Hardly Wait', 'Friday', and 'Empire Records', all just over 21. Note the year of the movies are more recent in the 1990s or 2000.

On the older end, the highest average age rated movies turned out to be: 'Hud', 'Klute', and 'Around the World in 80 Days'. Again, note the movie dates as they are in the 1960s, 1970s, and 1980s, more resonant with more esteemed users who have seen the movies when they were younger.

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

In [37]: avg_age = data.groupby('title')['age'].mean() In [45]: avg_age = avg_age.ix[active_titles] In [47]: avg_age.order(ascending=True)[:10] Out[47]: title Can't Hardly Wait (1998) 21.284314 Friday (1995) 21.768025 Empire Records (1995) 21.940959 Billy Madison (1995) 22.253521 Mallrats (1995) 22.432373 Road Trip (2000) 22.584726 Scream 3 (2000) 22.818024 Teenage Mutant Ninja Turtles II: The Secret of the Ooze (1991) 23.107570 Skulls, The (2000) 23.463576 Tommy Boy (1995) 23.585366 Name: age, dtype: float64 In [48]: avg_age.order(ascending=False)[:10] Out[48]: title Hud (1963) 41.971326 Klute (1971) 40.817881 Around the World in 80 Days (1956) 40.732342 Tender Mercies (1983) 40.196970 Breaker Morant (1980) 39.655556 Cat Ballou (1965) 39.575000 Atlantic City (1980) 39.483553 Mister Roberts (1955) 39.323040 Taking of Pelham One Two Three, The (1974) 39.217391 In the Heat of the Night (1967) 38.681034

Take heed though, of the age group less than 18. It is numerated with 1. So taking the average age is somewhat misleading, as those less than one skew the mean more than their actual age would. I assume that one year-olds would not be rating movies. We will have to correct this issue later.


Well, congratulations on reaching the end of this picture-less post! Here we used pandas in Python to create and merge DataFrames, and created more specific-use DataFrames through subsetting, and viewing the various orders through sorting. I hope you have an idea of the versatility of Python for data analysis with pandas by reading this series! Stay tuned for more posts!



Thanks for reading,

Wayne
@beyondvalence
LinkedIn


Python and Pandas Series:
1. Python and Pandas: Part 1: bit.ly and Time Zones
2. Python and Pandas: Part 2. Movie Ratings
3. Python and Pandas: Part 3. Baby Names, 1880-2010
4. Python and Pandas: Part 4. More Baby Names
.