<jupyter_start><jupyter_text>Assignment<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Link for Datasets(https://drive.google.com/drive/folders/105ftuIwN9kqyPNEEm3E6IM7LqywjyvJa?usp=sharing) Q21. Write a pandas program to import three datasheets from a givenexcel data (coalpublic2013.xlsx ) in to a single dataframe. Note: Structure of three datasheets are same.<jupyter_code>df1 = pd.read_excel('coalpublic2013.xlsx', sheet_name='Sheet-1')
df2 = pd.read_excel('coalpublic2013.xlsx', sheet_name='Sheet-2')
df3 = pd.read_excel('coalpublic2013.xlsx', sheet_name='Sheet-3')

df = pd.concat([df1, df2, df3], axis = 0)
df<jupyter_output><empty_output><jupyter_text>Q 22. Write a pandas program to import three datasheets from a givenexcel data (employee.xlsx ) into a single data frame and export theresult into new Excel file. Note: Structure of three datasheets are same.<jupyter_code>df1 = pd.read_excel('employee.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel('employee.xlsx', sheet_name='Sheet2')
df3 = pd.read_excel('employee.xlsx', sheet_name='Sheet3')
df = pd.concat([df1, df2, df3])
df.to_excel('employee-1.xlsx', index = False)<jupyter_output><empty_output><jupyter_text>Q23. Write a pandas program to create the Pivot table with multipleindexes from the data set of the titanic.csv.<jupyter_code>df = pd.read_csv('titanic.csv')
print(pd.pivot_table(df, index = ['sex', 'age'], aggfunc=np.sum))<jupyter_output>Unnamed: 15  adult_male  alone      fare  parch  pclass  sibsp  \
sex    age                                                                     
female 0.75           0.0         0.0    0.0   38.5166      2       6      4   
       1.00           0.0         0.0    0.0   26.8750      3       6      1   
       2.00           0.0         0.0    0.0  259.4750      9      15      9   
       3.00           0.0         0.0    0.0   62.6542      3       5      4   
       4.00           0.0         0.0    0.0  114.1417      6      13      4   
...                   ...         ...    ...       ...    ...     ...    ...   
male   70.00          0.0         2.0    1.0   81.5000      1       3      1   
       70.50          0.0         1.0    1.0    7.7500      0       3      0   
       71.00          0.0         2.0    2.0   84.1584      0       2      0   
       74.00          0.0         1.0    1.0    7.7750      0       3      0   
       80.00          0.0         1.0   [...]<jupyter_text>Q24. Write a Pandas program to create the Pivot table and find survivalrate by gender?<jupyter_code>df = pd.read_csv('titanic.csv')
pd.pivot_table(df, index=['sex'], values=['survived'],aggfunc=np.sum)<jupyter_output><empty_output><jupyter_text>Q25. Write a pandas program to make partition each of the passengersinto 4 categories based on their age. Note: Age categories- (0, 10), (10, 30), (30, 60), (60, 80)<jupyter_code>df = pd.read_csv('titanic.csv')
result = pd.cut(df['age'], bins= [0, 10, 30, 60, 80])
print(result)<jupyter_output>0      (10.0, 30.0]
1      (30.0, 60.0]
2      (10.0, 30.0]
3      (30.0, 60.0]
4      (30.0, 60.0]
           ...     
886    (10.0, 30.0]
887    (10.0, 30.0]
888             NaN
889    (10.0, 30.0]
890    (30.0, 60.0]
Name: age, Length: 891, dtype: category
Categories (4, interval[int64]): [(0, 10] < (10, 30] < (30, 60] < (60, 80]]<jupyter_text>Q26. Write a pandas program to create the Pivot table and find survivalrate by the gender, age of the different categories of variousclasses.<jupyter_code>df = pd.read_csv('titanic.csv')
age = pd.cut(df['age'], bins = [0, 20, 55])
df1 = df.pivot_table('survived', index=['sex', age], columns='class')
df1<jupyter_output><empty_output><jupyter_text>Q27. Write a pandas program to create the Pivot table and calculatenumber of women and men were in a particular cabin class.<jupyter_code>df = pd.read_csv('titanic.csv')
df1 = df.pivot_table(index=['sex'], columns=['pclass'], aggfunc='count')
df1<jupyter_output><empty_output><jupyter_text>Q28. Write a pandas program to create the Pivot table and separatethe gender according to whether they travelled alone or not to getthe probability of survival<jupyter_code>df = pd.read_csv('titanic.csv')
df1 = df.pivot_table('survived', index=['sex', 'alone'])
df1<jupyter_output><empty_output><jupyter_text>Q29. Write a pandas program to create the Pivot table and find theprobability of survival by class, gender, solo boarding, and the portof embarkation.<jupyter_code>df = pd.read_csv('titanic.csv')
df1 = df.pivot_table('survived', index=['sex', 'alone'], columns=['embark_town', 'class'])
print(df1)<jupyter_output>embark_town  Cherbourg                     Queenstown                   \
class            First    Second     Third      First Second     Third   
sex    alone                                                             
female False  1.000000  1.000000  0.611111        1.0    NaN  0.625000   
       True   0.944444  1.000000  0.800000        NaN    1.0  0.760000   
male   False  0.473684  0.166667  0.500000        0.0    NaN  0.100000   
       True   0.347826  0.250000  0.151515        NaN    0.0  0.068966   

embark_town  Southampton                      
class              First    Second     Third  
sex    alone                                  
female False    0.941176  0.923077  0.327586  
       True     1.000000  0.892857  0.466667  
male   False    0.407407  0.300000  0.142857  
       True     0.326923  0.089552  0.123762<jupyter_text>Q30. Write a pandas program to get current date, oldest date andnumber of days between Current date and the oldest date of Ufodataset.<jupyter_code>df = pd.read_csv(r'ufo-1.csv')
df['Date_time'] = df['Date_time'].astype('datetime64[ns]')
print("Original Dataframe:")
print(df.head())
print("\nCurrent date of Ufo dataset:")
print(df.Date_time.max())
print("\nOldest date of Ufo dataset:")
print(df.Date_time.min())
print("\nNumber of days between Current date and oldest date of Ufo dataset:")
print((df.Date_time.max() - df.Date_time.min()).days)<jupyter_output>Original Dataframe:
            Date_time                  city state/province country UFO_shape  \
0 1910-06-01 15:00:00           wills point             tx      us     cigar   
1 1920-06-11 21:00:00                cicero             in      us   unknown   
2 1929-07-05 14:00:00  buchanan  (or burns)             or      us      disk   
3 1931-06-01 13:00:00               abilene             ks      us      disk   
4 1939-06-01 20:00:00              waterloo             al      us  fireball   

   length_of_encounter_seconds described_duration_of_encounter  \
0                          120                       2 minutes   
1                           60                        1 minute   
2                           60                            1min   
3                         1800                 30 min. approx.   
4                          300                       5 minutes   

                                         description date_documented  \
0       Cigar shaped object mo[...]<jupyter_text>Q31. Write a pandas program to get all sighting days of theunidentified flying object (ufo) between 1950-10-10 and 1960-10-10.<jupyter_code>selected_period = df[(df['Date_time'] >= '01-01-1950 00:00:00') & (df['Date_time'] <= '31-12-1960 23:59:59')]
selected_period<jupyter_output><empty_output><jupyter_text>Q32. Write a Pandas program to extract the year, month, day, hour,minute, second, and weekday from unidentified flying object (UFO)reporting date.<jupyter_code>print('Years')
print(pd.DatetimeIndex(df['Date_time']).year)
print('\nMonth')
print(pd.DatetimeIndex(df['Date_time']).month)
print('\nDay')
print(pd.DatetimeIndex(df['Date_time']).day)
print('\nHour')
print(pd.DatetimeIndex(df['Date_time']).hour)
print('\nMinute')
print(pd.DatetimeIndex(df['Date_time']).minute)
print('\nSecond')
print(pd.DatetimeIndex(df['Date_time']).second)
print('\nWeekday')
print(pd.DatetimeIndex(df['Date_time']).weekday_name)<jupyter_output>Years
Int64Index([1910, 1920, 1929, 1931, 1939, 1939, 1941, 1942, 1944, 1944,
            ...
            2003, 2003, 2003, 2004, 2004, 2004, 2004, 2004, 2004, 2004],
           dtype='int64', name='Date_time', length=347)

Month
Int64Index([ 6,  6,  7,  6,  6,  7,  6,  6,  1,  6,
            ...
             4,  3,  7,  2, 10,  4, 10, 11, 12,  2],
           dtype='int64', name='Date_time', length=347)

Day
Int64Index([ 1, 11,  5,  1,  1,  7,  1,  1,  1,  1,
            ...
             9, 10, 11,  1,  2,  5,  6,  7,  8, 10],
           dtype='int64', name='Date_time', length=347)

Hour
Int64Index([15, 21, 14, 13, 20,  2, 13, 22, 12, 12,
            ...
            21, 20, 20,  1, 18, 20, 23, 20,  5,  5],
           dtype='int64', name='Date_time', length=347)

Minute
Int64Index([ 0,  0,  0,  0,  0,  0,  0, 30,  0,  0,
            ...
             0, 52, 50,  0, 20, 35,  0, 30, 30, 15],
           dtype='int64', name='Date_time', length=347)

Second
Int64Index([0, 0, 0, 0, 0, 0, 0, 0,[...]<jupyter_text>Q33. Write a pandas program to count year-country wise frequency ofreporting dates of the unidentified flying object(UFO).<jupyter_code>df['Year'] = pd.DatetimeIndex(df['Date_time']).year
result = df.groupby(['Year', 'country']).size()
print('count year-country wise frequency of reporting dates of the unidentified flying object(UFO)')
print(result)<jupyter_output>count year-country wise frequency of reporting dates of the unidentified flying object(UFO)
Year  country
1910  us          1
1920  us          1
1929  us          1
1931  us          1
1939  us          2
                 ..
2002  ca          1
      us         13
2003  us          9
2004  ca          1
      us          6
Length: 79, dtype: int64<jupyter_text>Q34. Write a pandas program to get the difference (in days) betweendocumented date and reporting date of unidentified flying object(UFO).<jupyter_code>df.head(1)
df['Date_time'] = pd.to_datetime(df['Date_time'])
df['date_documented'] = pd.to_datetime(df['date_documented'])
df['Difference'] = (df['date_documented'] - df['Date_time']).dt.days
print(df.head())<jupyter_output>Date_time                  city state/province country UFO_shape  \
0 1910-06-01 15:00:00           wills point             tx      us     cigar   
1 1920-06-11 21:00:00                cicero             in      us   unknown   
2 1929-07-05 14:00:00  buchanan  (or burns)             or      us      disk   
3 1931-06-01 13:00:00               abilene             ks      us      disk   
4 1939-06-01 20:00:00              waterloo             al      us  fireball   

   length_of_encounter_seconds described_duration_of_encounter  \
0                          120                       2 minutes   
1                           60                        1 minute   
2                           60                            1min   
3                         1800                 30 min. approx.   
4                          300                       5 minutes   

                                         description date_documented  \
0       Cigar shaped object moving from West to Ea[...]<jupyter_text>Q35. Write a pandas program to generate sequences of fixedfrequency dates and time spans.<jupyter_code>dtr = pd.date_range('2019-01-01', periods=12, freq='H')
print('Hourly Frequence')
print(dtr)

dtr = pd.date_range('2019-01-01', periods=12, freq='min')
print('\nMinutely Frequence')
print(dtr)

dtr = pd.date_range('2019-01-01', periods=12, freq='S')
print('\nSecondly Frequence')
print(dtr)<jupyter_output>Hourly Frequence
DatetimeIndex(['2019-01-01 00:00:00', '2019-01-01 01:00:00',
               '2019-01-01 02:00:00', '2019-01-01 03:00:00',
               '2019-01-01 04:00:00', '2019-01-01 05:00:00',
               '2019-01-01 06:00:00', '2019-01-01 07:00:00',
               '2019-01-01 08:00:00', '2019-01-01 09:00:00',
               '2019-01-01 10:00:00', '2019-01-01 11:00:00'],
              dtype='datetime64[ns]', freq='H')

Minutely Frequence
DatetimeIndex(['2019-01-01 00:00:00', '2019-01-01 00:01:00',
               '2019-01-01 00:02:00', '2019-01-01 00:03:00',
               '2019-01-01 00:04:00', '2019-01-01 00:05:00',
               '2019-01-01 00:06:00', '2019-01-01 00:07:00',
               '2019-01-01 00:08:00', '2019-01-01 00:09:00',
               '2019-01-01 00:10:00', '2019-01-01 00:11:00'],
              dtype='datetime64[ns]', freq='T')

Secondly Frequence
DatetimeIndex(['2019-01-01 00:00:00', '2019-01-01 00:00:01',
               '2019-01-01 00:00:02', '2019-01-01 00[...]<jupyter_text>Q36. Write a pandas program to manipulate and convert date timeswith timezone information.<jupyter_code>dt = pd.date_range('2019-01-01', periods=12, freq='H')
dt = dt.tz_localize('UTC')
print(dt)
print("\nFrom UTC to Asia/India:")
dt = dt.tz_convert('Asia/Kolkata')
print(dt)<jupyter_output>DatetimeIndex(['2019-01-01 00:00:00+00:00', '2019-01-01 01:00:00+00:00',
               '2019-01-01 02:00:00+00:00', '2019-01-01 03:00:00+00:00',
               '2019-01-01 04:00:00+00:00', '2019-01-01 05:00:00+00:00',
               '2019-01-01 06:00:00+00:00', '2019-01-01 07:00:00+00:00',
               '2019-01-01 08:00:00+00:00', '2019-01-01 09:00:00+00:00',
               '2019-01-01 10:00:00+00:00', '2019-01-01 11:00:00+00:00'],
              dtype='datetime64[ns, UTC]', freq='H')

From UTC to Asia/India:
DatetimeIndex(['2019-01-01 05:30:00+05:30', '2019-01-01 06:30:00+05:30',
               '2019-01-01 07:30:00+05:30', '2019-01-01 08:30:00+05:30',
               '2019-01-01 09:30:00+05:30', '2019-01-01 10:30:00+05:30',
               '2019-01-01 11:30:00+05:30', '2019-01-01 12:30:00+05:30',
               '2019-01-01 13:30:00+05:30', '2019-01-01 14:30:00+05:30',
               '2019-01-01 15:30:00+05:30', '2019-01-01 16:30:00+05:30'],
              dtype='datetime64[ns, Asia/Kol[...]<jupyter_text>Q37. Write a pandas program to create the graphical analysis of UFO(unidentified flying object) Sightings year.<jupyter_code>df['Date_time'] = pd.to_datetime(df['Date_time'], errors='coerce')
df['date_documented'] = pd.to_datetime(df['date_documented'])
df['year'] = pd.DatetimeIndex(df['Date_time']).year
years_data = df['year'].value_counts()

years_index = years_data.index
year_values = years_data.get_values()
%matplotlib inline
plt.figure(figsize=(15,8))
plt.xticks(rotation = 60)
plt.title('UFO Sightings by year')
plt.xlabel('Year')
plt.ylabel('Number of reports')
plt.bar(years_index, year_values)<jupyter_output><empty_output><jupyter_text>Q38. Write a pandas program to create a comparison of the top 10years in which the (UFO) was sighted VS each Month.<jupyter_code>def is_top_years(year):
    if year in most_sightings_years.index:
        return year
df = pd.read_csv(r'ufo-1.csv')
df['Date_time'] = pd.to_datetime(df['Date_time'], errors='coerce')
columns=df['Date_time'].dt.month
index=df['Date_time'].dt.year.apply(is_top_years)
aggfunc='count'
values='city'

columns = columns.get_values()# columns.reset_index(level=1, drop=False,inplace=False)
month_vs_year = df .pivot_table(columns=columns,index=index, aggfunc='count',values='city')
print("\nComparison of the top 10 years in which the UFO was sighted vs each month:")
print(month_vs_year.head(10))<jupyter_output>Comparison of the top 10 years in which the UFO was sighted vs each month:
            1    2    3    4    5    6    7    8    9    10   11   12
Date_time                                                            
1993.0     NaN  NaN  1.0  1.0  NaN  1.0  3.0  2.0  3.0  NaN  1.0  NaN
1994.0     2.0  NaN  3.0  2.0  2.0  NaN  NaN  1.0  NaN  NaN  NaN  1.0
1995.0     2.0  1.0  NaN  1.0  1.0  1.0  3.0  NaN  1.0  NaN  2.0  NaN
1996.0     NaN  1.0  NaN  1.0  1.0  1.0  3.0  3.0  1.0  NaN  1.0  NaN
1997.0     NaN  2.0  1.0  NaN  2.0  1.0  3.0  1.0  1.0  1.0  1.0  1.0
1998.0     1.0  2.0  1.0  3.0  NaN  2.0  1.0  NaN  NaN  1.0  NaN  2.0
1999.0     NaN  NaN  2.0  NaN  1.0  2.0  4.0  NaN  NaN  1.0  NaN  1.0
2000.0     NaN  3.0  2.0  NaN  2.0  1.0  1.0  NaN  NaN  NaN  1.0  2.0
2001.0     2.0  1.0  2.0  2.0  1.0  2.0  NaN  1.0  2.0  NaN  1.0  1.0
2002.0     3.0  1.0  1.0  NaN  3.0  NaN  2.0  1.0  2.0  1.0  NaN  NaN<jupyter_text>Q39. Write a pandas program to create a heatmap (rectangular data asa colour-encoded matrix) for comparison of top 10 years inwhich (UFO ) was sighted VS each Month.<jupyter_code>df =pd.read_csv(r'ufo-1.csv')
df['Date_time'] = pd.to_datetime(df['Date_time'])

df['year'] = pd.DatetimeIndex(df['Date_time']).year
years_data = df['year'].value_counts()
most_sightings_years  = years_data.head(10)

def is_top_years(year):
    if year in most_sightings_years.index:
        return year

columns=df['Date_time'].dt.month
index=df['Date_time'].dt.year.apply(is_top_years)
aggfunc='count'
values='city'

columns = columns.get_values()# columns.reset_index(level=1, drop=False,inplace=False)
month_vs_year = df .pivot_table(columns=columns,index=index, aggfunc='count',values='city')
month_vs_year.columns = month_vs_year.columns.astype(int)
print("\nHeatmap for comparison of the top 10 years in which the UFO was sighted vs each month:")
plt.figure(figsize=(10,8))
plt.imshow(month_vs_year, cmap='hot', interpolation='nearest')
plt.show()<jupyter_output>E:\Anaconda3\envs\dl\lib\site-packages\ipykernel_launcher.py:17: FutureWarning: The 'get_values' method is deprecated and will be removed in a future version. Use '.to_numpy()' or '.array' instead.<jupyter_text>Q40. Write a pandas program to create a Timewheel of Hour VS Yearcomparison of the top 10 years in which the (UFO) was sighted<jupyter_code>import matplotlib.pyplot as plt 
import matplotlib as mpl 
import matplotlib.cm as cm

def is_top_years(year):
    if year in most_sightings_years.index:
        return year
most_sightigs_yeara = df['Date_time'].dt.year.value_counts().head(10)


columns=df['Date_time'].dt.month
index=df['Date_time'].dt.year.apply(is_top_years)
columns = columns.get_values()
month_vs_year = df.pivot_table(columns=columns,
                               index=index,
                               aggfunc='count',
                               values='city'
                              )
month_vs_year.index = month_vs_year.index.astype(int)
month_vs_year.columns = month_vs_year.columns.astype(int)
print("\nComparison of the top 10 years in which the UFO was sighted vs each month:")
def pie_heatmap(table, cmap='coolwarm_r', vmin=None, vmax=None,inner_r=0.25, pie_args={}):
   n, m = table.shape
   vmin= table.min().min() if vmin is None else vmin
   vmax= table.max().max() if vmax is None else vmax

   centre_circle = plt.Circle((0,0),inner_r,edgecolor='black',facecolor='white',fill=True,linewidth=0.25)
   plt.gcf().gca().add_artist(centre_circle)
   norm = mpl.colors.Normalize(vmin=vmin, vmax=vmax)
   cmapper = cm.ScalarMappable(norm=norm, cmap=cmap)

   for i, (row_name, row) in enumerate(table.iterrows()):
       labels = None if i > 0 else table.columns
       wedges = plt.pie([1] * m,radius=inner_r+float(n-i)/n, colors=[cmapper.to_rgba(x) for x in row.values],
           labels=labels, startangle=90, counterclock=False, wedgeprops={'linewidth':-1}, **pie_args)
       plt.setp(wedges[0], edgecolor='grey',linewidth=1.5)
       wedges = plt.pie([1], radius=inner_r+float(n-i-1)/n, colors=['w'], labels=[row_name], startangle=-90, wedgeprops={'linewidth':0})
       plt.setp(wedges[0], edgecolor='grey',linewidth=1.5)
plt.figure(figsize=(8,8))
plt.title("Timewheel of Hour Vs Year",y=1.08,fontsize=30)
pie_heatmap(month_vs_year, vmin=-20,vmax=80,inner_r=0.2)<jupyter_output>Comparison of the top 10 years in which the UFO was sighted vs each month:
{'fraction_non_alphanumeric': 0.13209757091205376, 'fraction_numerical': 0.11687121250700208, 'mean_word_length': 2.4422436459246275, 'pattern_counts': {'":': 0, '<': 77, '<?xml version=': 0, '>': 75, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 61, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '512495', 'license': 'MIT', 'n_tokens_mistral': 8463, 'n_tokens_neox': 7021, 'n_words': 1700, 'path': 'Deep Learning/Subjective Assignment - 9 - Pandas 2.ipynb', 'repo_name': 'deepchatterjeevns/Ineuron.ai', 'size': '19637'}