2

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
Yog
  • 817
  • 1
  • 10
  • 19
  • Try to resis changing your question once asked, if you need to, please ask another question. – Zero Aug 30 '18 at 05:28

2 Answers2

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
piRSquared
  • 285,575
  • 57
  • 475
  • 624
Zero
  • 74,117
  • 18
  • 147
  • 154
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
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • It works can you give it in this format str[3:6] if possible? – Yog Aug 30 '18 at 05:19
  • 1
    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. – piRSquared Aug 30 '18 at 05:22