CODEWITHSUNDEEP
pythonpandasnumpyindexing
Gius
Gius

Reputation: 514

Extract data from index in new column in Dataframe

How do I extract data based on index values in different columns?

So far I was able to extract data based on index number in the same column (block of 5).

The Dataframe looks like this:

3017     39517.3886
3018     39517.4211
3019     39517.4683
3020     39517.5005
3021     39517.5486
5652     39628.1622
5653     39628.2104
5654     39628.2424
5655     39628.2897
5656     39628.3229
5677     39629.2020
5678     39629.2342
5679     39629.2825
5680     39629.3304
5681     39629.3628

Where the data extracted in col are +/- 2 rows around the index value

I would like to have something that looks more like this:

  3017-3021   5652-5656   5677-5681
1 39517.3886  39628.1622  39629.2020
2 39517.4211  39628.2104  39629.2342
3 39517.4683  39628.2424  39629.2825
4 39517.5005  39628.2897  39629.3304
5 39517.5486  39628.3229  39629.3628

and so on depending on the number of data that I want to extract.

The code I'm using to extract data based on index is:

## find index based on the first 0 of a 000 - 111 list
a = stim_epoc[1:]
ss = [(num+1) for num,i in enumerate(zip(stim_epoc,a)) if i == (0,1)]

## extract data from a df (GCamp_ps) based on the previous index 'ss'
fin = [i for x in ss for i in range(x-2, x + 2 + 1) if i in range(len(GCaMP_ps))]
df = time_fip.loc[np.unique(fin)]
print(df)

Upvotes: 3

Views: 1378

Answers (1)

ALollz
ALollz

Reputation: 59519

Form groups of 5 consecutive rows (since you pull +/-2 rows from a center). Then create the column and index labels and pivot

df = df.reset_index()
s = df.index//5   # If always 5 consecutive values. I.e. +/-2 rows from a center.    

df['col'] = df.groupby(s)['index'].transform(lambda x: '-'.join(map(str, x.agg(['min', 'max']))))
df['idx'] = df.groupby(s).cumcount()

df.pivot(index='idx', columns='col', values=0)  # Assuming column named `0`

Output:

col   3017-3021   5652-5656   5677-5681
idx                                    
0    39517.3886  39628.1622  39629.2020
1    39517.4211  39628.2104  39629.2342
2    39517.4683  39628.2424  39629.2825
3    39517.5005  39628.2897  39629.3304
4    39517.5486  39628.3229  39629.3628

Upvotes: 5

Related Questions