How can I regroup a dataframe and accumulate a colume's values?

» python » How can I regroup a dataframe and accumulate a colume's values?

By : Savana Servantez

Date : November 23 2020, 03:01 PM

I hope this helps . You can separate positive and negative values using np.where, then use pivot table with index as grouper with freq as 5 mins and then use aggfunc as count (it ignores nan values).

code :

df['pos_vol'] = np.where(df['volume']>0,df['volume'],np.nan)
df['neg_vol'] = np.where(df['volume']<0,df['volume'],np.nan)

ndf = df.pivot_table(values=['pos_vol','neg_vol'],index=[pd.Grouper(key='date', freq='5min'),'price'],aggfunc='count')

                           neg_vol  pos_vol
date                price                  
2017-10-17 01:00:00 51.89        4        0
                    51.90        2        6
                    51.91        0        3

                          neg_vol  pos_vol
date                price                  
2017-10-17 01:00:00 51.91        0        3
                    51.90        2        6
                    51.89        4        0

Share :

How to regroup records with different values in one SQL query

By : Jayson

Date : March 29 2020, 07:55 AM

hop of those help? Let's say I have the following table :

code :

             Select * from (select *,case when Country ='Switzerland' then 'A'
                         when Country ='Italy' then 'A'  
                         when Country ='France' then 'B'  
                         when Country ='England' then 'B'  
                    else 'C' end) classification from table1)
         order by classification

SQL regroup by column values into row

By : Ryan Socratous

Date : March 29 2020, 07:55 AM

Does that help I have a software that gives me data that look like those. , SO usable data becomes (after where conditions):

code :

 Date      Programme         Groupe      Rat     Actif   RFS     Inactif      Relevance        Etape  
120227   ANS-FR2-85s TO        5          9       20      5        10        Rats finaux     Training 
120228   ANS-FR2-85s TO        5          9       10      3         4        Rats finaux     Training 
120231   ANS-FR2-85s TO        5          9       100    20        50        Rats finaux     Training 

120227   ANS-FR2-85s TO        5         10       20      5        10        Rats finaux     Training 
120228   ANS-FR2-85s TO        5         10       10      3         4        Rats finaux     Training

Add values in Array based on other multiple values then regroup

By : Dave

Date : March 29 2020, 07:55 AM

To fix this issue I have this data:

code :

var input = [
  [1, "San Miguel National Central High School", "School", 1],
  [1, "San Miguel Central Elementary School", "School", 2],
  [2, "Medrano's Rice Mill and Warehouse", "Warehouse", 3],
  [1, "Unknown", "Residential", 341],
  [2, "Unknown", "Residential", 532],
  [3, "Unknown", "Residential", 257],
  [2, "Unknown", "Gas Station", 1]
];
    
var output = [];
    
function init() {
    var index = {};
    for (let building of input) {
        if (!index.hasOwnProperty(building[2])) {
            index[building[2]] = output.length;
            output.push([ building[2], 0, 0, 0 ]);
        }
        output[index[building[2]]][building[0]] += building[3];
    }
    console.log(output);
}
document.addEventListener( "DOMContentLoaded", init, false );

Regroup column values in a pandas df

By : renzh2010

Date : March 29 2020, 07:55 AM

Any of those help As far as I understand, you're happy with everything before the Person allocation. So here's a plug and play solution to "merge" Persons with less than 3 unique values so each Person ends up with 3 unique values except for the last one obviously (based on the second to last df you posted ("Output:") without touching the ones that have already 3 unique values and just merges the others.
EDIT: Greatly simplified code. Again, taking your df as input:

code :

n = 3
df['complete'] = df.Person.apply(lambda x: 1 if df.Person.tolist().count(x) == n else 0)
df['num'] = df.Person.str.replace('Person ','')
df.sort_values(by=['num','complete'],ascending=True,inplace=True) #get all persons that are complete to the top

c = 0
person_numbers = []
for x in range(0,999): #Create the numbering [1,1,1,2,2,2,3,3,3,...] with n defining how often a person is 'repeated'
    if x % n == 0:
        c += 1        
    person_numbers.append(c) 

df['Person_new'] = person_numbers[0:len(df)] #Add the numbering to the df
df.Person = 'Person ' + df.Person_new.astype(str) #Fill the person column with the new numbering
df.drop(['complete','Person_new','num'],axis=1,inplace=True)

Can we accumulate in a numpy array using values in a pandas dataframe columns in a vectorized way?

By : Crax

Date : March 29 2020, 07:55 AM

wish help you to fix your issue 1st Create your range , then make the range become a list , then the problem become a unnesting problem

code :

df['key']=[list(range(x,y))for x , y in zip(df.start,df.end)]
unnesting(df,['key']).groupby('key').signal.sum()
key
1    1
2    2
3    3
4    4
Name: signal, dtype: int64

unnesting(df, ['key']).groupby('key').signal.sum().values
array([1, 2, 3, 4], dtype=int64)