CODEWITHSUNDEEP
pythonpandas
hyukkyulee
hyukkyulee

Reputation: 1194

Pandas Merge (subtract) Two Rows with same absolute value

              Quantity  frequency
0                  200        158
1                 -200        116
2                  500         85
3                 1000         62
4                  300         57
5                 -500         51
6                 -300         50

I am trying to subtract two frequencies having the same abs(Quantity) and updating column['frequency'] and order by frequency.

Output:

              Quantity  frequency
0                 1000         62
1                  200         42
2                  500         34
3                  300          7
...

Upvotes: 0

Views: 320

Answers (3)

Dylon
Dylon

Reputation: 1750

This will yield the results you seek:

query = df.copy()
query["abs_quantity"] = query["Quantity"].abs()
abs_freq = pd.DataFrame(data=query.abs_quantity.value_counts()) \
             .reset_index(level=0) \
             .rename(columns={"index": "abs_quantity",
                              "abs_quantity": "abs_freq"})
results = query.merge(abs_freq, on="abs_quantity") \
               .query("abs_freq == 1")[["Quantity", "frequency"]] \
               .sort_values(by="frequency", ascending=False)

Upvotes: 1

Isha Nema
Isha Nema

Reputation: 469

You can try below code snippet:

for index,row in df.iterrows():
if int(row["Quantity"])<0:
    # Make all quantities as positive
    row["Quantity"]=row["Quantity"]*-1
    # Transfer the quantity sign to freq
    row["Freq"]=row["Freq"]*-1

This will change the sign. 

df.groupby(['Quantity']).sum()

This will group it by the quantity.

Upvotes: 0

moys
moys

Reputation: 8033

ONe way of doing it.

a = abs(df.Quantity)
b = df[df.groupby(a)["frequency"].transform('count')>1]
c = df[df.groupby(a)["frequency"].transform('count')==1]
d = b.groupby(a)['frequency'].apply(lambda x: x.values[0]-x.values[-1]).reset_index()
d.append(c)

Output

Quantity    frequency
0   200     42
1   300     7
2   500     34
3   1000    62

Upvotes: 1

Related Questions