Compare data and do group by or filter with pandas just like in excel
make key for vlookup function
df1['key'] = df1.apply(lambda row: row['sales_calculate_date'] + str(row['buyer']) + str(row['product']), axis=1) # generate a key for vlookup function
Make vlookup function with key setting to the second dataframe
e.g. df is normal dataframe and df1 is series object (dataframe with set index function)
df['dept']=df.sku.map(df1.dept) # use map do filtering
or use merge and join (My not be able to define columne name)
df.merge(df1, left_index=True, right_index=True, how='left') # merge two dataframes
df.merge(df1, on='sku', how='left') # merge with easy way
calculate column differences
easy way to calculate math differences
df['C'] = df['A'] + df['B'] # easy math calculation
df['C'] = df.apply(lambda row: row['A'] + row['B'], axis=1) # can be applied to all kinds of operations
select rows by condition
To select rows whose column value equals a scalar, some_value, use ==:
df.loc[df['column_name'] == some_value]
To select rows whose column value is in an iterable, some_values, use isin:
df.loc[df['column_name'].isin(some_values)]
Combine multiple conditions with &:
df.loc[(df['column_name'] >= A) & (df['column_name'] <= B)]
Note the parentheses. Due to Python’s operator precedence rules, & binds more tightly than <= and >=. Thus, the parentheses in the last example are necessary. Without the parentheses
df['column_name'] >= A & df['column_name'] <= B
is parsed as
df['column_name'] >= (A & df['column_name']) <= B
which results in a Truth value of a Series is ambiguous error.
To select rows whose column value does not equal some_value, use !=:
df.loc[df['column_name'] != some_value]
isin returns a boolean Series, so to select rows whose value is not in some_values, negate the boolean Series using ~:
df.loc[~df['column_name'].isin(some_values)]
Others
list = df['column1'].to_list() # convert column to list
df['column1'].fillna(value=0, inplace=True) # fill zero to na field
df.iloc[0] # display the first row of dataframe
df.to_csv(filename, encoding, sep) # dump dataframe to csv files
series.to_frame() # series object to dataframe
len(df.index) # row number of dataframe
rename columns
df.rename(columns={'oldName1': 'newName1', 'oldName2': 'newName2'}, inplace=True)