MA346 HW Nov 12

import pandas as pd

df = pd.read_csv('jams.tsv', sep = '\t', error_bad_lines=False) df

b'Skipping line 7872: expected 7 fields, saw 8\nSkipping line 11730: expected 7 fields, saw 9\nSkipping line 14131: expected 7 fields, saw 8\nSkipping line 58054: expected 7 fields, saw 8\nSkipping line 58754: expected 7 fields, saw 8\n'
b'Skipping line 847129: expected 7 fields, saw 8\n'
b'Skipping line 1091153: expected 7 fields, saw 8\nSkipping line 1175375: expected 7 fields, saw 8\n'
b'Skipping line 1225935: expected 7 fields, saw 8\nSkipping line 1255357: expected 7 fields, saw 8\nSkipping line 1279671: expected 7 fields, saw 8\n'
b'Skipping line 1330675: expected 7 fields, saw 8\n'
b'Skipping line 1448033: expected 7 fields, saw 8\nSkipping line 1543893: expected 7 fields, saw 8\n'
b'Skipping line 1579569: expected 7 fields, saw 8\nSkipping line 1612448: expected 7 fields, saw 8\n'
b'Skipping line 1784588: expected 7 fields, saw 8\n'

	jam_idobject	user_idobject	artistobject	titleobject	creation_dateobject	linkobject	spotify_uriobject
0	c2e76bb92c7fa733fdfc9be40bb0e4ea	b99ebf68a8d93f024e56c65e2f949b57	Orange Juice	Rip It Up	2011-08-26	nan	spotify:track:6AGhDIyDbRonzGTdbIsNXa
1	5d2bc46196d7903a5580f0dbedc09610	c1066039fa61eede113878259c1222d1	Erkin Koray	Hele Yar	2011-08-26	nan	nan
2	4849f8c893a792032dbc15eb77cfd0cd	d8a96caeef5c7d8dbdc9cec28aa9c044	Stereo	Somewhere In the Night	2011-08-26	nan	spotify:track:2ZM9sVP0blBj1lNIEg4dgn
3	8940ff2e8e38c8f1a4676e09d152c0cd	b1c81a51f5286d70e5820ecb85aeba96	The Mills Brothers	I Got Her Off My Hands	2011-08-26	nan	spotify:track:096elxAmYuyAFJKPLrS5oY
4	4020b45a4c6d5515a3b62c489e169828	4fdc0257bc476bffd3e73564ecaa75e0	Various	Zum Volksfest	2011-08-26	nan	nan
Expand rows 5 - 2090062
2090063	5e29cb7b29af3a81fb5f2266e33684aa	593241a48971d4a71eab9e45e706b9ae	J Dilla	Bye.	2015-09-26	http://www.youtube.com/watch?v=2-hKtCCgIMw	spotify:track:76P9ddkwMxFCv0Wycl8ThZ
2090064	f5af48d411ffb01e9ffcebf1c95760aa	99a05a8f7d50f85712cb59a3cabbd83f	JJ Abrams	Fringe	2015-09-26	http://www.youtube.com/watch?v=F3yvyXTVtT4	spotify:track:1FZWZgg4YmZkEC2C1GYxnI
2090065	59e0a1b6d9e3ec6a7e1e05ee586612ba	4d15fea36c0a3d24495e860f1bf7a9e0	Ane Brun	Do You Remember	2015-09-26	http://vimeo.com/24627062	spotify:track:2iSVttzrn895D58uhN3uhM
2090066	2a1133f69bd31d232870e122776d9e1a	1d7d770b79f30be995b9ac21049897fb	flyingdoctor1988	Talking Heads Heaven (HQ)	2015-09-26	http://www.youtube.com/watch?v=9bKW7JkHKm8	nan
2090067	6484da82f1fb797558e7430bedf683f7	cd0f6f4b09420e67f0025f885a2e245c	:'-(	We'll Meet Again	2015-09-26	http://www.youtube.com/watch?v=s4VlruVG81w	spotify:track:2iAlDwRozfuIgjvqSJWsw4
2090068 rows × 7 columns

df1 = df['user_id'] df_userid = pd.Series(data=df1, name='Series') df_userid

df_userid.sample(1000)

sample_df = df_userid.sample(1000) sample_df = sample_df.to_frame('user_id')

df2 = df[['jam_id', 'user_id', 'artist', 'title']] df2

	jam_idobject	user_idobject	artistobject	titleobject
0	c2e76bb92c7fa733fdfc9be40bb0e4ea	b99ebf68a8d93f024e56c65e2f949b57	Orange Juice	Rip It Up
1	5d2bc46196d7903a5580f0dbedc09610	c1066039fa61eede113878259c1222d1	Erkin Koray	Hele Yar
2	4849f8c893a792032dbc15eb77cfd0cd	d8a96caeef5c7d8dbdc9cec28aa9c044	Stereo	Somewhere In the Night
3	8940ff2e8e38c8f1a4676e09d152c0cd	b1c81a51f5286d70e5820ecb85aeba96	The Mills Brothers	I Got Her Off My Hands
4	4020b45a4c6d5515a3b62c489e169828	4fdc0257bc476bffd3e73564ecaa75e0	Various	Zum Volksfest
Expand rows 5 - 2090062
2090063	5e29cb7b29af3a81fb5f2266e33684aa	593241a48971d4a71eab9e45e706b9ae	J Dilla	Bye.
2090064	f5af48d411ffb01e9ffcebf1c95760aa	99a05a8f7d50f85712cb59a3cabbd83f	JJ Abrams	Fringe
2090065	59e0a1b6d9e3ec6a7e1e05ee586612ba	4d15fea36c0a3d24495e860f1bf7a9e0	Ane Brun	Do You Remember
2090066	2a1133f69bd31d232870e122776d9e1a	1d7d770b79f30be995b9ac21049897fb	flyingdoctor1988	Talking Heads Heaven (HQ)
2090067	6484da82f1fb797558e7430bedf683f7	cd0f6f4b09420e67f0025f885a2e245c	:'-(	We'll Meet Again
2090068 rows × 4 columns

df_merge = pd.merge(sample_df, df2, on = 'user_id' ) df_merge = df_merge.drop('jam_id', axis=1) df_merge

	user_idobject	artistobject	titleobject
0	0a1c750e8ecc453c305bd515d920954c	Kuedo	Flight Path
1	0a1c750e8ecc453c305bd515d920954c	Sasha	Who Killed Sparky?
2	0a1c750e8ecc453c305bd515d920954c	Plaid	tender hooks
3	0a1c750e8ecc453c305bd515d920954c	The Weeknd	Initiation
4	0a1c750e8ecc453c305bd515d920954c	Unquote	Cold Tenderness (DFRNT Remix)
Expand rows 5 - 110405
110406	848b21dad2f3c7a785ee05df2012d51c	Kenton Slash Demon Remix	Last Land
110407	848b21dad2f3c7a785ee05df2012d51c	A Sides & Makoto	Breathe
110408	848b21dad2f3c7a785ee05df2012d51c	Mos Def	Sensei on the block
110409	848b21dad2f3c7a785ee05df2012d51c	Ninetoes	Finder
110410	848b21dad2f3c7a785ee05df2012d51c	The Passion HIFI	So Glad I Found You
110411 rows × 3 columns

df_merge['song_and_artist'] = df_merge['title'] + ', by ' + df_merge['artist'] df_merge = df_merge.drop(['artist', 'title'], axis=1)

df_merge

	user_idobject	song_and_artistobject
0	0a1c750e8ecc453c305bd515d920954c	Flight Path, by Kuedo
1	0a1c750e8ecc453c305bd515d920954c	Who Killed Sparky?, by Sasha
2	0a1c750e8ecc453c305bd515d920954c	tender hooks, by Plaid
3	0a1c750e8ecc453c305bd515d920954c	Initiation, by The Weeknd
4	0a1c750e8ecc453c305bd515d920954c	Cold Tenderness (DFRNT Remix), by Unquote
Expand rows 5 - 110405
110406	848b21dad2f3c7a785ee05df2012d51c	Last Land, by Kenton Slash Demon Remix
110407	848b21dad2f3c7a785ee05df2012d51c	Breathe, by A Sides & Makoto
110408	848b21dad2f3c7a785ee05df2012d51c	Sensei on the block, by Mos Def
110409	848b21dad2f3c7a785ee05df2012d51c	Finder, by Ninetoes
110410	848b21dad2f3c7a785ee05df2012d51c	So Glad I Found You, by The Passion HIFI
110411 rows × 2 columns

df_merge.to_csv('jam-sample.csv', header=True)