I have a DataFrame with 57 columns. Columns 1 through 21 are dimensions. 22 through 57 are metrics. Column 1 is a date column. Column 21 is a bad column that is causing me to have duplicative data.
What I am looking to do is remove column 21 and then take the min of 22 to 57 when 1 through 20 are the same.
No reason to use groupby, you can just use drop and min
To remove a column 21 you can just use drop on the relevant column, removing it by name:
df.drop(columns="column_21_name", inplace=True)
To a select a minimum between several columns you can use min:
df["min_column"] = df.iloc[:, 22:57].min(axis=0)
(First I used iloc to select only relevant columns and then use the minimum omethod)
Maybe it should be 21:56 (if start indexing from 0), depends on how you counted. Just try and see if it is what you desired.
Afterwards you have in df a new column name "min_column" and you drop the rest of the relevant column (21 to 56)
P.S - Please follow StackOverflow Guidelines when publishing a question: You should say what you already try (instead of just asking) and give example of your dataframe (rather then talk generally about "column 20"). I decided to answer this time, but other community members may be less merciful.
I think the following will do the trick for you. You can drop the column if you'd like (df.drop(df.columns[20], axis=1, inplace=True)), but it isn't necessary for this one calculation. Code groupbys by the first 21 columns and then takes min of columns 22 till 57 for each combination. If you decide to drop the column the iloc indexing will change. iloc[a:b] goes from a to b-1.
df.iloc[:, 21:57].groupby(df.iloc[:, :21]).min()
Just needed to drop the column and then drop duplicates. Sorry all.
df.drop(columns="lineItemBudget", inplace=True)
df.drop_duplicates(inplace=True)
