<jupyter_start><jupyter_text>Problem StatementThe datasets contains the box office collection and social media promotion information about the movies released in 2013 – 2015 period. Following is the information about the columns (names can vary):- Movie Id- Release date of movie- Name of the movie- Release time of movie like Long Weekend (3), Festive Season (1), Holiday Season (2), and Normal (4)- Genre of movie like Romance, Thriller, Action, comedy etc.- Budget of movie in crores- Box office collection of movie in crore- YouTube views of movies trailers- YouTube likes for the movie trailers- YouTube dislikes for the movie trailers- YouTube LikesDislikes<jupyter_code>import pandas as pd
df1 = pd.read_csv("/work/DataSets  Python Scripts/movies-1.csv")
df2 = pd.read_csv("/work/DataSets  Python Scripts/movies-2.csv")
df1.head()
df2.head()<jupyter_output><empty_output><jupyter_text>**As the name of columns for both the datasets are different we need to make it similiar**<jupyter_code>df1.rename(columns={'No': 'Movie_ID',
                    'RelDate': 'Release_Date',
                    'MovieName': 'Movie_Name',
                    'ReleaseTime': 'Release_Time',
                    'Budget': 'BudgetCr',
                    'BoxOfficeCollection': 'BoxOfficeCollectionCr',
                    'YoutubeViews': 'Youtube_Views',
                    'YoutubeLikes': 'Youtube_Likes',
                    'YoutubeDislikes': 'Youtube_Dislikes'},inplace=True)
df1.head()<jupyter_output><empty_output><jupyter_text>2. df2 has a missing column: YoutubeLikeDislikes       * Create this missing columns otherwise the 2 datasets would be of different size and they would not merge<jupyter_code>df2['YoutubeLikeDislikes'] = df2['Youtube_Likes'] + df2['Youtube_Dislikes']
df2.head()<jupyter_output><empty_output><jupyter_text>Checking for unique data values- Lets try to bring all the data values of the different columns into the same format. - A lot of preprocessing needs to be done in order to bring this commanality into the data values.<jupyter_code>#For df1

cols = ['Release_Date','Release_Time','Genre','BudgetCr', 'BoxOfficeCollectionCr']

for col in cols:
    print(col, df1[col].unique())
    print("------------------------------")


#For df2

cols = ['Release_Date','Release_Time','Genre','BudgetCr', 'BoxOfficeCollectionCr']

for col in cols:
    print(col, df2[col].unique())
    print("------------------------------")


# Correct Spelling mistakes for month names in df1-Release_Date

df1['Release_Date'] = df1['Release_Date'].str.replace(r'Sqp', 'Sep')
df1['Release_Date'] = df1['Release_Date'].str.replace(r'Jum', 'Jun')
df1['Release_Date'] = df1['Release_Date'].str.replace(r'juu', 'Jun')
df1['Release_Date'] = df1['Release_Date'].str.replace(r'Feb-21-204', 'Feb-21-2014')
# Standardize the 'Release_Time' for both datasets

df1['Release_Time'].replace({44: 4},inplace=True)
df2['Release_Time'].replace({"N": 4, "LW": 3, "HS":2, "FS":1},inplace=True)
# Standardize Genre Column

df1['Genre'] = df1['Genre'].str.replace(r'Genre', '')
df1['Genre'] = df1['Genre'].str.replace(r'Thriller ', 'Thriller')
df1['Genre'] = df1['Genre'].str.replace(r' Drama', 'Drama')
df1['Genre'] = df1['Genre'].str.replace(r'Drama ', 'Drama')
df1['Genre'] = df1['Genre'].str.replace(r'Action ', 'Action')

df2['Genre'] = df2['Genre'].str.replace(r'Drama ', 'Drama')
# Budget and box office collection are numeric columns, Hence the value cr should be removed before converting it to integer

df1['BudgetCr'] = df1['BudgetCr'].str.strip('Cr')

df1['BoxOfficeCollectionCr'] = df1['BoxOfficeCollectionCr'].str.strip('Cr')

#For df1

cols = ['Release_Date','Release_Time','Genre','BudgetCr', 'BoxOfficeCollectionCr']

for col in cols:
    print(col, df1[col].unique())
    print("------------------------------")


#For df2

cols = ['Release_Date','Release_Time','Genre','BudgetCr', 'BoxOfficeCollectionCr']

for col in cols:
    print(col, df2[col].unique())
    print("------------------------------")<jupyter_output>Release_Date ['4-Jul-14' '22-Mar-13' '4-Apr-14' '29-Mar-13' '9-May-14' '22-Aug-14'
 '5-Apr-13' '5-Sep-14' '12-Apr-13' '19-Apr-13' '17-Jan-14' '26-Apr-13'
 '28-Mar-14' '31-Jan-14' '2-May-14' '3-May-13' '7-Mar-14' '21-Mar-14'
 '10-May-13' '29-Aug-14' '25-Apr-14' '28-Feb-14' '17-May-13' '3-Jan-14'
 '15-Aug-14' '7-Nov-14' '24-May-13' '28-Nov-14' '31-May-13' '10-Jan-14'
 '7-Jun-13' '14-Jun-13' '19-Dec-14' '21-Jun-13' '5-Dec-14' '2-Oct-14'
 '12-Sep-14' '28-Jun-13' '2-Oct-13' '5-Jul-13' '18-Jul-14' '16-May-14'
 '12-Jul-13' '19-Jul-13' '14-Mar-14' '21-Feb-14' '26-Jul-13' '7-Feb-14'
 '11-Apr-14' '9-Aug-13' '15-Aug-13' '23-Aug-13' '30-Aug-13' '1-Nov-13'
 '8-Nov-13' '15-Nov-13' '22-Nov-13' '29-Nov-13' '11-Oct-13' '16-Oct-13'
 '18-Oct-13' '25-Oct-13' '6-Sep-13' '13-Sep-13' '20-Sep-13' '9-Jan-15'
 '16-Jan-15' '23-Jan-15' '30-Jan-15' '6-Feb-15' '13-Feb-15' '20-Feb-15'
 '27-Feb-15' '13-Mar-15' '20-Mar-15' '23-May-14']
------------------------------
Release_Time [4 3 2 1]
-----------------------------[...]<jupyter_text>Merge two datasets for further analysis<jupyter_code>df = pd.concat([df1,df2])
df.head()<jupyter_output><empty_output><jupyter_text>**Get unique date format for both datasets**<jupyter_code>df1.Release_Date.head()
df2.Release_Date.head()
df1['Release_Date'] = pd.to_datetime(df1['Release_Date'], dayfirst=True, errors='coerce')
df1['Release_Date'] = df1['Release_Date'].dt.strftime("%d-%b-%y")
df1.Release_Date.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2078846504836649, 'fraction_numerical': 0.06844314655959116, 'mean_word_length': 3.871111111111111, 'pattern_counts': {'":': 4, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '639167', 'license': 'MIT', 'n_tokens_mistral': 2232, 'n_tokens_neox': 1997, 'n_words': 440, 'path': 'DataSets  Python Scripts/Scripts/Data_Integration.ipynb', 'repo_name': 'shahid-khans/datascience', 'size': '5479'}