How to groupby splitted text and count their count using python pandas?

Question

Input:

import pandas as pd

df=pd.DataFrame({
    'Station':['001ABC006','002ABD008','005ABX009','007ABY010','001ABC006','002ABD008'],
    'Trains Passing':[55,56,59,96,95,96],
    'Destination':['MRK','MRK','MRS','MTS','KPS','KPS']

})

I need to Split the Station text from '001ABC006' to 'ABC' and create a list. Count only the values present in the list. Also group by destination. How could I do it?

Output:

  StationId ABC ABD ABX ABY
  MRK       1   1   0   0
  MRS       0   0   1   0
  MTS       0   0   0   1
  KPS       1   1   0   0

Try to resis changing your question once asked, if you need to, please ask another question. — Zero
– Zero, Commented Aug 30, 2018 at 5:28

piRSquared · Accepted Answer · 2018-08-30 05:28:28Z

3

Updated

In [180]: pd.crosstab(df.Destination, df.Station.str[3:6])
Out[180]:
Station      ABC  ABD  ABX  ABY
Destination                    
KPS            1    1    0    0
MRK            1    1    0    0
MRS            0    0    1    0
MTS            0    0    0    1

You could use

In [160]: pd.DataFrame([df.Station.str[3:6].value_counts().to_dict()])
Out[160]:
   ABC  ABD  ABX  ABY
0    2    2    1    1

Or,

In [149]: df.Station.str[3:6].value_counts().to_frame().T
Out[149]:
         ABC  ABD  ABX  ABY
Station    2    2    1    1

Details

In [162]: df.Station.str[3:6]
Out[162]:
0    ABC
1    ABD
2    ABX
3    ABY
4    ABC
5    ABD
Name: Station, dtype: object

In [163]: df.Station.str[3:6].value_counts()
Out[163]:
ABC    2
ABD    2
ABX    1
ABY    1
Name: Station, dtype: int64

edited Aug 30, 2018 at 5:28

piRSquared

296k68 gold badges509 silver badges654 bronze badges

answered Aug 30, 2018 at 4:42

Zero

77.4k22 gold badges154 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yog Over a year ago

If there is another column and how can I groupby from it?

Yog Over a year ago

please check the question now

piRSquared Over a year ago

@YogeshSP this post has been updated. Please accept this answer instead.

piRSquared · Accepted Answer · 2018-08-30 05:16:39Z

2

This is called a cross tabulation and the link below shows several ways to do this.
See: how-to-pivot-a-dataframe

`crosstab`

pd.crosstab(df.Destination, df.Station.str.replace('\d', ''))

Station      ABC  ABD  ABX  ABY
Destination                    
KPS            1    1    0    0
MRK            1    1    0    0
MRS            0    0    1    0
MTS            0    0    0    1

Replace digits

df.Station.str.replace('\d', '').value_counts()

ABC    2
ABD    2
ABY    1
ABX    1
Name: Station, dtype: int64

`findall`

import pandas as pd
import numpy as np
import re

i, r = pd.factorize(re.findall('(?i)([a-z]+)', '|'.join(df.Station)))
pd.Series(np.bincount(i), r)

ABC    2
ABD    2
ABX    1
ABY    1
dtype: int64

edited Aug 30, 2018 at 5:16

answered Aug 30, 2018 at 4:45

piRSquared

296k68 gold badges509 silver badges654 bronze badges

2 Comments

Yog Over a year ago

It works can you give it in this format str[3:6] if possible?

piRSquared Over a year ago

Just replace df.Station.str.replace('\d', '') with df.Station.str[3:6]. And accept @Zero's answer since it is his idea you are going with. Your changing the question shouldn't take away from him giving you the answer you needed. By all rights, you should've asked a new question. But that's all fine, I'd just feel better if you selected his answer instead.

Collectives™ on Stack Overflow

How to groupby splitted text and count their count using python pandas?

2 Answers 2

3 Comments

`crosstab`

Replace digits

`findall`

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

crosstab

Replace digits

findall

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`crosstab`

`findall`