To query the columns of a Pandas DataFrame, use the query(). We are querying to filter records. At first, let us create a Pandas DataFramedataFrame = pd.DataFrame({"Product": ["SmartTV", "PenDrive", "Speaker", "Earphone"], "Opening_Stock": [300, 700, 1200, 1500], "Closing_Stock": [200, 500, 1000, 900]})Using query() to query columns with conditions −print(dataFrame.query('Opening_Stock >=500 & Closing_Stock < 1000 & Product.str.startswith("P").values'))ExampleFollowing is the complete code −import pandas as pd dataFrame = pd.DataFrame({"Product": ["SmartTV", "PenDrive", "Speaker", "Earphone"], "Opening_Stock": [300, 700, 1200, 1500], "Closing_Stock": [200, 500, 1000, 900]}) print"DataFrame...", dataFrame # using query() to query columns print"Querying columns to filter records..." print(dataFrame.query('Opening_Stock >=500 & Closing_Stock ... Read More

To select multiple rows from a DataFrame, set the range using the : operator. At first, import the require pandas library with alias −import pandas as pdNow, create a new Pandas DataFrame −dataFrame = pd.DataFrame([[10, 15], [20, 25], [30, 35], [40, 45]], index=['w', 'x', 'y', 'z'], columns=['a', 'b'])Select multiple rows using the : operator −dataFrame[0:2]ExampleFollowing is the code −import pandas as pd # Create DataFrame dataFrame = pd.DataFrame([[10, 15], [20, 25], [30, 35], [40, 45]], index=['w', 'x', 'y', 'z'], columns=['a', 'b']) # DataFrame print"DataFrame...", dataFrame # select rows with loc print"Select rows by passing label..." print(dataFrame.loc['z']) ... Read More

To select a column from a DataFrame, just fetch it using square brackets. Mention the column to select in the brackets and that’s it, for exampledataFrame[‘ColumnName’]At first, import the required library −import pandas as pdNow, create a DataFrame. We have two columns in it −dataFrame = pd.DataFrame( { "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'], "Units": [100, 150, 110, 80, 110, 90] } )To select only a single column, mention the column name using the square bracket as shown below. Here, our ... Read More

To merge Pandas DataFrame, use the merge() function. The outer join is implemented on both the DataFrames by setting under the “how” parameter of the merge() function i.e. −how = “outer”At first, let us import the pandas library with an alias −import pandas as pdLet us create DataFrame1 −dataFrame1 = pd.DataFrame( { "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'], "Units": [100, 150, 110, 80, 110, 90] } )Let us now create DataFrame2 −dataFrame2 = pd.DataFrame( { ... Read More

To merge two Pandas DataFrame with common column, use the merge() function and set the ON parameter as the column name. To set NaN for unmatched values, use the “how” parameter and set it left or right. That would mean, merging left or right.At first, let us import the pandas library with an alias −import pandas as pdLet us create DataFrame1 −dataFrame1 = pd.DataFrame( { "Car": ['BMW', 'Lexus', 'Audi', 'Mustang', 'Bentley', 'Jaguar'], "Units": [100, 150, 110, 80, 110, 90] } )Let us ... Read More

To drop multiple levels from a multi-level column index, use the columns.droplevel() repeatedly. We have used the Multiindex.from_tuples() is used to create indexes column-wise.At first, create indexes column-wise −items = pd.MultiIndex.from_tuples([("Col 1", "Col 1", "Col 1"), ("Col 2", "Col 2", "Col 2"), ("Col 3", "Col 3", "Col 3")])Next, create a multiindex array and form a multiindex dataframe −arr = [np.array(['car', 'car', 'car', 'bike', 'bike', 'bike', 'truck', 'truck', 'truck']), np.array(['valueA', 'valueB', 'valueC', 'valueA', 'valueB', 'valueC', 'valueA', 'valueB', 'valueC'])] # forming multiindex dataframe dataFrame = pd.DataFrame(np.random.randn(9, 3), index=arr, columns=items)Label the index −dataFrame.index.names = ['level 0', 'level 1'] Drop a level ... Read More

To drop a level from a multi-level column index, use the columns.droplevel(). We have used the Multiindex.from_tuples() is used to create indexes column-wise.At first, create indexes column-wise −items = pd.MultiIndex.from_tuples([("Col 1", "Col 1", "Col 1"), ("Col 2", "Col 2", "Col 2"), ("Col 3", "Col 3", "Col 3")])Next, create a multiindex array and form a multiindex dataframearr = [np.array(['car', 'car', 'car', 'bike', 'bike', 'bike', 'truck', 'truck', 'truck']),    np.array(['valueA', 'valueB', 'valueC', 'valueA', 'valueB', 'valueC', 'valueA', 'valueB', 'valueC'])] # forming multiindex dataframe dataFrame = pd.DataFrame(np.random.randn(9, 3), index=arr, columns=items)Label the index −dataFrame.index.names = ['level 0', 'level 1']Drop a level at index ... Read More

To group Pandas dataframe, we use groupby(). To sort grouped dataframe in ascending order, use sort_values(). The size() method is used to get the dataframe size.For ascending order sort, use the following in sort_values() −ascending=TrueAt first, create a pandas dataframe −dataFrame = pd.DataFrame( { "Car": ['BMW', 'Lexus', 'Audi', 'Mercedes', 'Jaguar', 'Bentley'], "Reg_Price": [1000, 1400, 1000, 900, 1700, 900] } )Next, group according to Reg_Price column and sort in ascending order −dataFrame.groupby('Reg_Price').size().sort_values(ascending=True)ExampleFollowing is the code −import pandas as pd # dataframe ... Read More

To filter few rows from DataFrame on the basis of sum, we have considered an example with Student Marks. We need to calculate the sum of a particular subject wherein the total is more than 200 i.e. the total of all 3 students in that particular subject is more than 200. In this way we can fiter our rows with total less than 200.At first, let us create a DataFrame with 3 columns i.e. records of 3 students −dataFrame = pd.DataFrame({'Jacob_Marks': [95, 90, 70, 85, 88], 'Ted_Marks': [60, 50, 65, 85, 70], 'Jamie_Marks': [77, 76, 60, 45, 50]})Filtering on the ... Read More

To fetch the common rows between two DataFrames, use the concat() function. Let us create DataFrame1 with two columns −dataFrame1 = pd.DataFrame(    {       "Car": ['BMW', 'Lexus', 'Audi', 'Tesla', 'Bentley', 'Jaguar'],       "Reg_Price": [1000, 1500, 1100, 800, 1100, 900] } )Create DataFrame2 with two columns −dataFrame2 = pd.DataFrame(    { "Car": ['BMW', 'Lexus', 'Audi', 'Tesla', 'Bentley', 'Jaguar'], "Reg_Price": [1200, 1500, 1000, 800, 1100, 1000] } )Finding common rows between two DataFrames with concat() −dfRes = pd.concat([dataFrame1, dataFrame2])Reset index −dfRes = dfRes.reset_index(drop=True)Groupby columns −dfGroup = dfRes.groupby(list(dfRes.columns))Getting the length of each row to calculate the count. If ... Read More