<jupyter_start><jupyter_text>Weclome to Pandas DataFrames exercises In this exercise, we are using Automobile Dataset for data analysis. This Dataset has different characteristics of an auto such as body-style, wheel-base, engine-type, price, mileage, horsepower.you can find the data in the data folder under `Automobile_data.csv`name  Exercise 1:From the given dataset print the first and last five rows![pandas_printing_first_5_rows.webp](attachment:pandas_printing_first_5_rows.webp)Python Pandas printing first 5 rows ![pandas_printing_last_5_rows.webp](attachment:pandas_printing_last_5_rows.webp)Python Pandas printing last 5 rows<jupyter_code>import pandas as pd


df1=pd.read_csv("data/Automobile_data.csv")
df1.head()<jupyter_output><bound method NDFrame.head of     index      company   body-style  wheel-base  length engine-type  \
0       0  alfa-romero  convertible        88.6   168.8        dohc   
1       1  alfa-romero  convertible        88.6   168.8        dohc   
2       2  alfa-romero    hatchback        94.5   171.2        ohcv   
3       3         audi        sedan        99.8   176.6         ohc   
4       4         audi        sedan        99.4   176.6         ohc   
..    ...          ...          ...         ...     ...         ...   
56     81   volkswagen        sedan        97.3   171.7         ohc   
57     82   volkswagen        sedan        97.3   171.7         ohc   
58     86   volkswagen        sedan        97.3   171.7         ohc   
59     87        volvo        sedan       104.3   188.8         ohc   
60     88        volvo        wagon       104.3   188.8         ohc   

   num-of-cylinders  horsepower  average-mileage    price  
0              four         111               21  13495.0[...]<jupyter_text>Exercise 2:**Clean the dataset and update the CSV file**Replace all column values which contain `?`, `n.a`, or `NaN`.<jupyter_code>import numpy as np

features=list(df1.columns)


for item in features[1:]:
    df1[item]=df1[item].replace(np.nan,0)

print(df1.loc[[22]])
df1.info()

#df1.to_cvs("data/updated_automobile_data.csv")<jupyter_output>index company body-style  wheel-base  length engine-type num-of-cylinders  \
22     31   isuzu      sedan        94.5   155.9         ohc             four   

    horsepower  average-mileage  price  
22          70               38    0.0  
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 61 entries, 0 to 60
Data columns (total 10 columns):
 #   Column            Non-Null Count  Dtype  
---  ------            --------------  -----  
 0   index             61 non-null     int64  
 1   company           61 non-null     object 
 2   body-style        61 non-null     object 
 3   wheel-base        61 non-null     float64
 4   length            61 non-null     float64
 5   engine-type       61 non-null     object 
 6   num-of-cylinders  61 non-null     object 
 7   horsepower        61 non-null     int64  
 8   average-mileage   61 non-null     int64  
 9   price             61 non-null     float64
dtypes: float64(3), int64(3), object(4)
memory usage: 4.9+ KB<jupyter_text>Exercise 3:**Find the most expensive car company name**Print most expensive car’s company name and price.**Expected Output:**![pandas_printing_most_costly_car_name.webp](attachment:pandas_printing_most_costly_car_name.webp)<jupyter_code>data=df1[['company','price']]
i=data['price'].idxmax() 
print(data.iloc[i])<jupyter_output>company    mercedes-benz
price            45400.0
Name: 35, dtype: object<jupyter_text>Exercise 4:**Print All Toyota Cars details****Expected Output:**![pandas_printing_all_toyota_car_data.webp](attachment:pandas_printing_all_toyota_car_data.webp)<jupyter_code>df_new = df1[df1['company'] == 'toyota']
print(df_new)<jupyter_output>index company body-style  wheel-base  length engine-type num-of-cylinders  \
48     66  toyota  hatchback        95.7   158.7         ohc             four   
49     67  toyota  hatchback        95.7   158.7         ohc             four   
50     68  toyota  hatchback        95.7   158.7         ohc             four   
51     69  toyota      wagon        95.7   169.7         ohc             four   
52     70  toyota      wagon        95.7   169.7         ohc             four   
53     71  toyota      wagon        95.7   169.7         ohc             four   
54     79  toyota      wagon       104.5   187.8        dohc              six   

    horsepower  average-mileage    price  
48          62               35   5348.0  
49          62               31   6338.0  
50          62               31   6488.0  
51          62               31   6918.0  
52          62               27   7898.0  
53          62               27   8778.0  
54         156               19  15750.0<jupyter_text>Exercise 5:Count total cars per company**Expected Outcome:**![pandas_count_total_cars_per_company.webp](attachment:pandas_count_total_cars_per_company.webp)<jupyter_code>#grouped=df1.groupby('company').count()
grouped = df1['company'].value_counts()
print(grouped)<jupyter_output>toyota           7
bmw              6
mazda            5
nissan           5
audi             4
mercedes-benz    4
mitsubishi       4
volkswagen       4
alfa-romero      3
chevrolet        3
honda            3
isuzu            3
jaguar           3
porsche          3
dodge            2
volvo            2
Name: company, dtype: int64<jupyter_text>Exercise 6:Find each company’s Higesht price car**Expected Outcome:**![pandas_printing_each_companys_higesht_price_car.webp](attachment:pandas_printing_each_companys_higesht_price_car.webp)<jupyter_code>grouped2 = df1.groupby('company')
result = grouped2['price'].max()
print(result)<jupyter_output>company
alfa-romero      16500.0
audi             18920.0
bmw              41315.0
chevrolet         6575.0
dodge             6377.0
honda            12945.0
isuzu             6785.0
jaguar           36000.0
mazda            18344.0
mercedes-benz    45400.0
mitsubishi        8189.0
nissan           13499.0
porsche          37028.0
toyota           15750.0
volkswagen        9995.0
volvo            13415.0
Name: price, dtype: float64<jupyter_text>Exercise 7:Find the average mileage of each car making company**Expected Output:**![pandas_printing_average_mileage_of_each_car_making_company.webp](attachment:pandas_printing_average_mileage_of_each_car_making_company.webp)<jupyter_code>grouped3 = df1.groupby('company')
result = grouped3['average-mileage'].mean()
print(result)<jupyter_output>company
alfa-romero      20.333333
audi             20.000000
bmw              19.000000
chevrolet        41.000000
dodge            31.000000
honda            26.333333
isuzu            33.333333
jaguar           14.333333
mazda            28.000000
mercedes-benz    18.000000
mitsubishi       29.500000
nissan           31.400000
porsche          17.000000
toyota           28.714286
volkswagen       31.750000
volvo            23.000000
Name: average-mileage, dtype: float64<jupyter_text>Exercise 8:Sort all cars by Price column**Expected Output:**![pandas_sort_all_cars_by_price_column.webp](attachment:pandas_sort_all_cars_by_price_column.webp)<jupyter_code>result=df1.sort_values(by=['price'], ascending=False)
print(result.head())<jupyter_output>index        company   body-style  wheel-base  length engine-type  \
35     47  mercedes-benz      hardtop       112.0   199.2        ohcv   
11     14            bmw        sedan       103.5   193.8         ohc   
34     46  mercedes-benz        sedan       120.9   208.1        ohcv   
46     62        porsche  convertible        89.5   168.9        ohcf   
12     15            bmw        sedan       110.0   197.0         ohc   

   num-of-cylinders  horsepower  average-mileage    price  
35            eight         184               14  45400.0  
11              six         182               16  41315.0  
34            eight         184               14  40960.0  
46              six         207               17  37028.0  
12              six         182               15  36880.0<jupyter_text>Exercise 9:Concatenate two data frames using the following conditionsCreate two data frames using the following two dictionaries.```pythonGermanCars = {'Company': ['Ford', 'Mercedes', 'BMV', 'Audi'], 'Price': [23845, 171995, 135925 , 71400]}japaneseCars = {'Company': ['Toyota', 'Honda', 'Nissan', 'Mitsubishi '], 'Price': [29995, 23600, 61500 , 58900]}```**Expected Output:**![pandas_concatenate_two_data_frames_and_create_key_for_each_data_frame.webp](attachment:pandas_concatenate_two_data_frames_and_create_key_for_each_data_frame.webp)<jupyter_code>GermanCars = {'Company': ['Ford', 'Mercedes', 'BMV', 'Audi'], 'Price': [23845, 171995, 135925 , 71400]}
japaneseCars = {'Company': ['Toyota', 'Honda', 'Nissan', 'Mitsubishi '], 'Price': [29995, 23600, 61500 , 58900]}

German=pd.DataFrame(GermanCars)
Japan=pd.DataFrame(japaneseCars)

#pd.concat(objs,axis=0,join="outer",ignore_index=False,keys=None,levels=None,names=None,verify_integrity=False,copy=True,)
frame=pd.concat([German,Japan])
print(frame)<jupyter_output>Company   Price
0         Ford   23845
1     Mercedes  171995
2          BMV  135925
3         Audi   71400
0       Toyota   29995
1        Honda   23600
2       Nissan   61500
3  Mitsubishi    58900<jupyter_text>Exercise 10:**Merge two data frames using the following condition**Create two data frames using the following two Dicts, Merge two data frames, and append the second data frame as a new column to the first data frame.```pythonCar_Price = {'Company': ['Toyota', 'Honda', 'BMV', 'Audi'], 'Price': [23845, 17995, 135925 , 71400]}car_Horsepower = {'Company': ['Toyota', 'Honda', 'BMV', 'Audi'], 'horsepower': [141, 80, 182 , 160]}```**Expected Output:**![merge_two_data_frames_and_append_new_data_frame_as_new-column.webp](attachment:merge_two_data_frames_and_append_new_data_frame_as_new-column.webp)<jupyter_code>Car_Price = {'Company': ['Toyota', 'Honda', 'BMV', 'Audi'], 'Price': [23845, 17995, 135925 , 71400]}
car_Horsepower = {'Company': ['Toyota', 'Honda', 'BMV', 'Audi'], 'horsepower': [141, 80, 182 , 160]}
price=pd.DataFrame(Car_Price)
horsepowah=pd.DataFrame(car_Horsepower)

result = pd.merge(price, horsepowah)
print(result)<jupyter_output>Company   Price  horsepower
0  Toyota   23845         141
1   Honda   17995          80
2     BMV  135925         182
3    Audi   71400         160
{'fraction_non_alphanumeric': 0.08960123587911557, 'fraction_numerical': 0.0962633967365067, 'mean_word_length': 2.1454600668083814, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 32, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '114750', 'license': 'MIT', 'n_tokens_mistral': 4040, 'n_tokens_neox': 3424, 'n_words': 816, 'path': 'exercices/part3.ipynb', 'repo_name': 'minhajar/python-bootcamp', 'size': '10357'}