Hi everyone!
I've looked through the forums for an answer, but had no luck regarding this particular scenario, hence I'm now posting a question.
I have a messy IMDB movie dataset which I want to clean up for a personal project, I've added a mini sheet of the data at the end of the question.
As you can see, some of the data tends to be in one cell and the remainder of the data in the cell next to it and/or the cell next to that one too. The delimiter tends to be the semicolon.
For example in Cell A3 there is the movie id, movie title, release year and genre. In the cell next to it in Cell B3 is the remaining data such as duration, rating, director, score etc.
I would like to split the text into the appropriate columns, so movie id is in movie id column, genre is in genre column etc.
I used the "Convert Text to Columns" Wizard for Column A and the text did split out into different columns. However, if I continue using Convert Text to Columns, I would have to select the cell with the data, go through the Convert Text to Columns wizard again and choose the destination cell for the data to go into, one by one, which would take a long time.
Is there an easier way to get all this data into the right columns?
Assistance would be appreciated, cheers!
I've looked through the forums for an answer, but had no luck regarding this particular scenario, hence I'm now posting a question.
I have a messy IMDB movie dataset which I want to clean up for a personal project, I've added a mini sheet of the data at the end of the question.
As you can see, some of the data tends to be in one cell and the remainder of the data in the cell next to it and/or the cell next to that one too. The delimiter tends to be the semicolon.
For example in Cell A3 there is the movie id, movie title, release year and genre. In the cell next to it in Cell B3 is the remaining data such as duration, rating, director, score etc.
I would like to split the text into the appropriate columns, so movie id is in movie id column, genre is in genre column etc.
I used the "Convert Text to Columns" Wizard for Column A and the text did split out into different columns. However, if I continue using Convert Text to Columns, I would have to select the cell with the data, go through the Convert Text to Columns wizard again and choose the destination cell for the data to go into, one by one, which would take a long time.
Is there an easier way to get all this data into the right columns?
Assistance would be appreciated, cheers!
| messy_IMDB_dataset.csv | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | IMBD title ID;Original titlÊ;Release year;Genrë¨;Duration;Country;Content Rating;Director;;Income; Votes ;Score | ||||||
| 2 | tt0111161;The Shawshank Redemption;1995-02-10;Drama;142;USA;R;Frank Darabont;;$ 28815245;2.278.845;9.3 | ||||||
| 3 | tt0068646;The Godfather;09 21 1972;Crime | Drama;175;USA;R;Francis Ford Coppola;;$ 246120974;1.572.674;9.2 | |||||
| 4 | tt0468569;The Dark Knight; 23 -07-2008;Action | Crime | Drama;152;US;PG-13;Christopher Nolan;;$ 1005455211;2.241.615;9. | ||||
| 5 | tt0071562;The Godfather: Part II;1975-09-25;Crime | Drama;220;USA;R;Francis Ford Coppola;;$ 4o8 | 35 | 783;1.098.714;9 | 0 | ||
| 6 | tt0110912;Pulp Fiction;1994-10-28;Crime | Drama; ;USA;R;Quentin Tarantino;;$ 222831817;1.780.147;8 | 9f | ||||
| 7 | tt0167260;The Lord of the Rings: The Return of the King;22 Feb 04;Action | Adventure | Drama;201;New Zealand;PG-13;Peter Jackson;;$ 1142271098;1.604.280;08.9 | ||||
| 8 | tt0108052;Schindler's List;1994-03-11;Biography | Drama | History;Nan;USA;R;Steven Spielberg;;$ 322287794;1.183.248;8.9 | ||||
| 9 | tt0050083;12 Angry Men;1957-09-04;Crime | Drama;96;USA;Not Rated;Sidney Lumet;;$ 576;668.473;8.9 | |||||
| 10 | tt1375666;Inception;2010-09-24;Action | Adventure | Sci-Fi;148;USA;PG-13;Christopher Nolan;;$ 869784991;2.002.816;8..8 | ||||
| 11 | tt0137523;Fight Club;10-29-99;Drama;Inf;UK;R;David Fincher;;$ 101218804;1.807.440;8.8 | ||||||
| 12 | tt0109830;Forrest Gump;1994-10-06;Drama | Romance;142;USA;PG-13;Robert Zemeckis;;$ 678229452;1.755.490;8:8 | |||||
| 13 | tt0120737;The Lord of the Rings: The Fellowship of the Ring;2002-01-18;Action | Adventure | Drama;178c;New Zesland;PG-13;Peter Jackson;;$ 887934303;1.619.920;8.8 | ||||
| 14 | tt0060196;Il buono | il brutto | il cattivo;23rd December of 1966 ;Western;161;Italy;Approved;Sergio Leone;;$ 25252481;672.499;8.8 | ||||
| 15 | ;;;;;;;;;;; | ||||||
| 16 | tt0133093;The Matrix;1999-05-07;Action | Sci-Fi;NULL;USA;R;Lana Wachowski | Lilly Wachowski;;$ 465718588;1.632.315;++8.7 | ||||
| 17 | tt0167261;The Lord of the Rings: The Two Towers;01/16-03;Action | Adventure | Drama;179;New Zeland;PG-13;Peter Jackson;;$ 951227416;1.449.778;8.7. | ||||
| 18 | tt0080684;Star Wars: Episode V - The Empire Strikes Back;1980-09-19;Action | Adventure | Fantasy;Not Applicable;USA;PG;Irvin Kershner;;$ 549265501;1.132.073;8 | 7.00E+00 | |||
| 19 | tt0099685;Goodfellas;1990-09-20;Biography | Crime | Drama;146;USA;R;Martin Scorsese;;$ 46879633;991.505;8.7 | ||||
| 20 | tt0073486;One Flew Over the Cuckoo's Nest;18/11/1976;Drama;-;USA;R;Milos Forman;;$ 108997629;891.071;8.7 | ||||||
| 21 | tt0816692;Interstellar;2014-11-06;Adventure | Drama | Sci-Fi;169;USA;PG-13;Christopher Nolan;;$ 696742056;1.449.256;8.6 | ||||
| 22 | tt0114369;Se7en;1995-12-15;Crime | Drama | Mystery;127;USA;R;David Fincher;;$ 327333559;1.402.015;8.6 | ||||
| 23 | tt0102926;The Silence of the Lambs;1991-03-05;Crime | Drama | Thriller;118;USA;R;Jonathan Demme;;$ 272753884;1.234.134;8 | 6 | |||
| 24 | tt0076759;Star Wars;1977-10-20;Action | Adventure | Fantasy;121;USA;PG;George Lucas;;$ 775768912;1.204.107;8.6 | ||||
| 25 | tt0120815;Saving Private Ryan;1998-10-30;Drama | War;169;USA;R;Steven Spielberg;;$ 482349603;1.203.825;8.6 | |||||
| 26 | tt0120689;The Green Mile;2000-10-03;Crime | Drama | Fantasy;189;US.;R;Frank Darabont;;$ 286801374;1.112.336;8.6 | ||||
| 27 | tt0317248;Cidade de Deus;2003-05-09;Crime | Drama;130;Brazil;R;Fernando Meirelles | Kátia Lund;;$ 30680793;685.856;8.6 | ||||
| 28 | tt0245429;Sen to Chihiro no kamikakushi;2003-04-18;Animation | Adventure | Family;125;Japan;PG;Hayao Miyazaki;;$ 355467056;626.693;8.6 | ||||
| 29 | tt0118799;La vita B9 bella;1997-12-20;Comedy | Drama | Romance;116;Italy1;#N/A;Roberto Benigni;;$ 230098753;605.648;8.6 | ||||
| 30 | tt6751668;Gisaengchung;2019-11-07;Comedy | Drama | Thriller;132;South Korea;#N/A;Bong Joon Ho;;$ 257604912;470.931;8.6 | ||||
| 31 | tt0038650;It's a Wonderful Life;1948-03-11;Drama | Family | Fantasy;130;USA;PG;Frank Capra;;$ 6130720;388.310;8.6 | ||||
| 32 | tt0047478;Shichinin no samurai;1955-08-19;Action | Adventure | Drama;207;Japan;Unrated;Akira Kurosawa;;$ 322773;307.958;8.6 | ||||
| 33 | tt0172495;Gladiator;2000-05-19;Action | Adventure | Drama;155;USA;R;Ridley Scott;;$ 465361176;1.308.193;8.5 | ||||
| 34 | tt0407887;The Departed;2006-10-27;Crime | Drama | Thriller;151;USA;R;Martin Scorsese;;$ 291465034;1.159.703;8.5 | ||||
| 35 | tt0482571;The Prestige;2006-12-22;Drama | Mystery | Sci-Fi;130;UK;PG-13;Christopher Nolan;;$ 109676311;1.155.723;8.5 | ||||
| 36 | tt0088763;Back to the Future;1985-10-18;Adventure | Comedy | Sci-Fi;116;USA;PG;Robert Zemeckis;;$ 388774684;1.027.330;8.5 | ||||
| 37 | tt0120586;American History X;1999-08-27;Drama;119;USA;R;Tony Kaye;;$ 23875127;1.014.218;8.5 | ||||||
| 38 | tt0110413;Léon;1995-04-07;Action | Crime | Drama;110;France;#N/A;Luc Besson;;$ 19552639;1.007.598;8.5 | ||||
| 39 | tt0103064;Terminator 2: Judgment Day;1991-12-19;Action | Sci-Fi;137;USA;R;James Cameron;;$ 520884847;974.970;8.4 | |||||
| 40 | tt0114814;The Usual Suspects;1995-11-30;Crime | Mystery | Thriller;106;USA;R;Bryan Singer;;$ 23341568;968.947;8.4 | ||||
| 41 | tt0110357;The Lion King;1994-11-25;Animation | Adventure | Drama;88;USA;G;Roger Allers | Rob Minkoff;;$ 968511805;917.248;8.4 | |||
| 42 | tt7286456;Joker;2019-10-03;Crime | Drama | Thriller;122;USA;#N/A;Todd Phillips;;$ 1074251311;855.097;8.4 | ||||
| 43 | tt1675434;Intouchables;2012-02-24;Biography | Comedy | Drama;112;France;#N/A;Olivier Nakache | Éric Toledano;;$ 426588510;736.691;8.4 | |||
| 44 | tt0253474;The Pianist;2002-10-25;Biography | Drama | Music;150;UK;R;Roman Polanski;;$ 120072577;707.942;8.4 | ||||
| 45 | tt2582802;Whiplash;2015-02-12;Drama | Music;106;USA;R;Damien Chazelle;;$ 48983260;690.732;8.4 | |||||
| 46 | tt0054215;Psycho;1960-10-28;Horror | Mystery | Thriller;109;USA;R;Alfred Hitchcock;;$ 32008644;586.765;8.3 | ||||
| 47 | tt0034583;Casablanca;21-11-46;Drama | Romance | War;102;USA;PG;Michael Curtiz;;$ 4374761;509.953;8.3 | ||||
| 48 | tt0064116;C'era una volta il West;1968-12-21;Western;165;Italy;PG-13;Sergio Leone;;$ 112911;295.220;8.3 | ||||||
| 49 | tt0095327;Hotaru no haka;2015-10-11;Animation | Drama | War;89;Japan;#N/A;Isao Takahata;;$ 516962;225.438;8.3 | ||||
| 50 | tt0095765;Nuovo Cinema Paradiso;1988-11-17;Drama;155;Italy;#N/A;Giuseppe Tornatore;;$ 13826605;223.050;8.3 | ||||||
| 51 | tt0027977;Modern Times;1937-03-12;Comedy | Drama | Family;87;USA;G;Charles Chaplin;;$ 457688;211.250;8.3 | ||||
| 52 | tt1345836;The Dark Knight Rises;2012-08-29;Action | Adventure;164;UK;PG-13;Christopher Nolan;;$ 1081133191;1.480.582;8.3 | |||||
| 53 | tt1853728;Django Unchained;2013-01-17;Drama | Western;165;USA;R;Quentin Tarantino;;$ 425368238;1.317.856;8.3 | |||||
| 54 | tt0209144;Memento;2001-01-19;Mystery | Thriller;113;USA;R;Christopher Nolan;;$ 39970386;1.098.879;8.2 | |||||
| 55 | tt0910970;WALL·E;2008-10-17;Animation | Adventure | Family;98;USA;G;Andrew Stanton;;$ 521311860;974.734;8.2 | ||||
| 56 | tt0081505;The Shining;1980-12-22;Drama | Horror;146;UK;R;Stanley Kubrick;;$ 46520613;869.480;8.2 | |||||
| 57 | tt0082971;Raiders of the Lost Ark;1981-06-12;Action | Adventure;115;USA;PG;Steven Spielberg;;$ 390133212;865.510;8.2 | |||||
| 58 | tt4154756;Avengers: Infinity War;2018-04-25;Action | Adventure | Sci-Fi;149;USA;#N/A;Anthony Russo | Joe Russo;;$ 2048359754;796.486;8.2 | |||
| 59 | tt0078748;Alien;1979-10-25;Horror | Sci-Fi;117;UK;R;Ridley Scott;;$ 108110316;768.874;8.2 | |||||
| 60 | tt4154796;Avengers: Endgame;2019-04-24;Action | Adventure | Drama;181;USA;#N/A;Anthony Russo | Joe Russo;;$ 2797800564;754.786;8.2 | |||
| 61 | tt0078788;Apocalypse Now;1979-12-18;Drama | Mystery | War;147;USA;R;Francis Ford Coppola;;$ 91968688;591.251;8.2 | ||||
| 62 | tt0364569;Oldeuboi;2005-05-06;Action | Drama | Mystery;120;South Korea;R;Chan-wook Park;;$ 15002116;501.082;8.1 | ||||
| 63 | tt0057012;Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb;1964-04-03;Comedy;95;UK;PG;Stanley Kubrick;;$ 9443876;441.115;8.1 | ||||||
| 64 | tt0047396;Rear Window;1955-04-14;Mystery | Thriller;112;USA;#N/A;Alfred Hitchcock;;$ 37032034;432.390;8.1 | |||||
| 65 | tt2380307;Coco;2017-12-28;Animation | Adventure | Family;105;USA;#N/A;Lee Unkrich | Adrian Molina;;$ 807083670;352.455;8.1 | |||
| 66 | tt0405094;Das Leben der Anderen;2007-04-06;Drama | Mystery | Thriller;137;Germany;R;Florian Henckel von Donnersmarck;;$ 77356942;349.642;8.1 | ||||
| 67 | tt4633694;Spider-Man: Into the Spider-Verse;2018-12-25;Animation | Action | Adventure;117;USA;#N/A;Bob Persichetti | Peter Ramsey;;$ 375540831;335.892;8.1 | |||
| 68 | tt1187043;3 Idiots;2009-12-25;Comedy | Drama;170;India;#N/A;Rajkumar Hirani;;$ 60262836;332.217;8.1 | |||||
| 69 | tt0119698;Mononoke-hime;2000-05-19;Animation | Adventure | Fantasy;134;Japan;PG-13;Hayao Miyazaki;;$ 169785629;331.045;8.0 | ||||
| 70 | tt0087843;Once Upon a Time in America;1984-09-28;Crime | Drama;229;USA;R;Sergio Leone;;$ 5472914;302.317;8.0 | |||||
| 71 | tt0032553;The Great Dictator;1945-06-25;Comedy | Drama | War;125;USA;#N/A;Charles Chaplin;;$ 969879;197.381;8.0 | ||||
| 72 | tt0043014;Sunset Blvd.;The 6th of marzo | year 1951;Drama | Film-Noir;110;USA;#N/A;Billy Wilder;;$ 299645;195.789;8.0 | ||||
| 73 | tt0361748;Inglourious Basterds;2009-10-02;Adventure | Drama | War;153;Germany;R;Quentin Tarantino;;$ 321455689;1.229.958;8.0 | ||||
| 74 | tt0169547;American Beauty;2000-01-21;Drama;122;USA;R;Sam Mendes;;$ 356296601;1.049.009;8.0 | ||||||
| 75 | tt0112573;Braveheart;1995-12-01;Biography | Drama | History;178;USA;R;Mel Gibson;;$ 213216216;941.683;7.9 | ||||
| 76 | tt0086190;Star Wars: Episode VI - Return of the Jedi;1983-10-21;Action | Adventure | Fantasy;131;USA;PG;Richard Marquand;;$ 475347111;928.036;7.9 | ||||
| 77 | tt0105236;Reservoir Dogs;1992-10-09;Crime | Drama | Thriller;99;USA;R;Quentin Tarantino;;$ 2889963;896.551;7.9 | ||||
| 78 | tt0338013;Eternal Sunshine of the Spotless Mind;2004-10-22;Drama | Romance | Sci-Fi;108;USA;R;Michel Gondry;;$ 74036715;889.875;7.9 | ||||
| 79 | tt0114709;Toy Story;1996-03-22;Animation | Adventure | Comedy;81;USA;G;John Lasseter;;$ 404265438;864.461;7.9 | ||||
| 80 | tt0119217;Good Will Hunting;1998-03-06;Drama | Romance;126;USA;R;Gus Van Sant;;$ 225933435;837.379;7.8 | |||||
| 81 | tt0208092;Snatch;2001-03-16;Comedy | Crime;104;UK;R;Guy Ritchie;;$ 83557872;766.589;7.8 | |||||
| 82 | tt0180093;Requiem for a Dream;2000-12-15;Drama;102;USA;R;Darren Aronofsky;;$ 7390108;748.291;7.8 | ||||||
| 83 | tt0066921;A Clockwork Orange;1972-09-07;Crime | Drama | Sci-Fi;136;UK;#N/A;Stanley Kubrick;;$ 26903440;740.301;7.8 | ||||
| 84 | tt0435761;Toy Story 3;2010-07-07;Animation | Adventure | Comedy;103;USA;G;Lee Unkrich;;$ 1066969703;739.717;7.8 | ||||
| 85 | tt0086250;Scarface;1984-02-34;Crime | Drama;170;USA;R;Brian De Palma;;$ 66023585;721.343;7.8 | |||||
| 86 | tt0075314;Taxi Driver;1976-13-24;Crime | Drama;114;USA;R;Martin Scorsese;;$ 28441292;703.264;7.7 | |||||
| 87 | tt0211915;Le fabuleux destin d'Amélie Poulain;2002-01-25;Comedy | Romance;122;France;R;Jean-Pierre Jeunet;;$ 173924742;690.480;7.7 | |||||
| 88 | tt0093058;Full Metal Jacket;1987-10-09;Drama | War;116;UK;#N/A;Stanley Kubrick;;$ 46357676;658.175;7.7 | |||||
| 89 | tt0090605;Aliens;1986-09-24;Action | Adventure | Sci-Fi;137;USA;R;James Cameron;;$ 131384634;639.500;7.7 | ||||
| 90 | tt0062622;2001: A Space Odyssey;1968-12-12;Adventure | Sci-Fi;149;UK;G;Stanley Kubrick;;$ 68989547;587.866;7.6 | |||||
| 91 | tt0033467;Citizen Kane;1948-11-25;Drama | Mystery;119;USA;#N/A;Orson Welles;;$ 1594107;389.322;7.6 | |||||
| 92 | tt8579674;1917;2020-01-23;Drama | War;119;USA;#N/A;Sam Mendes;;$ 384857224;362.897;7.6 | |||||
| 93 | tt0086879;Amadeus;1985-02-15;Biography | Drama | History;160;USA;R;Milos Forman;;$ 52066791;361.028;7.6 | ||||
| 94 | tt0052357;Vertigo;1958-11-18;Mystery | Romance | Thriller;128;USA;#N/A;Alfred Hitchcock;;$ 7796389;352.786;7.5 | ||||
| 95 | tt0053125;North by Northwest;1959-10-28;Adventure | Mystery | Thriller;136;USA;#N/A;Alfred Hitchcock;;$ 73446;291.628;7.5 | ||||
| 96 | tt2106476;Jagten;2012-11-22;Drama;115;Denmark;R;Thomas Vinterberg;;$ 15843274;269.616;7.5 | ||||||
| 97 | tt0056172;Lawrence of Arabia;1963-10-29;Adventure | Biography | Drama;228;UK;PG;David Lean;;$ 45710874;261.504;7.5 | ||||
| 98 | tt0070735;The Sting;1974-03-21;Comedy | Crime | Drama;129;USA;PG;George Roy Hill;;$ 156000000;236.285;7.5 | ||||
| 99 | tt0082096;Das Boot;1982-03-18;Adventure | Drama | Thriller;149;West Germany;R;Wolfgang Petersen;;$ 11487676;226.427;7.5 | ||||
| 100 | tt0059578;Per qualche dollaro in più;1965-12-20;Western;132;Italy;#N/A;Sergio Leone;;$ 15000000;226.039;7.4 | ||||||
| 101 | tt1832382;Jodaeiye Nader az Simin;2011-10-21;Drama;123;Iran;PG-13;Asghar Farhadi;;$ 22926076;214.165;7.4 | ||||||
| 102 | tt0045152;Singin' in the Rain;1953-02-05;Comedy | Musical | Romance;103;USA;#N/A;Stanley Donen;;$ 1864182;213.152;7.4 | ||||
messy_IMDB_dataset | |||||||
