How to combine numbers into a single row

user_13973807 0
Viewed 98

I have a dataframe with two columns. The columns is Word and Tag. The df looks as follows:

                     Word   Tag
0                     DNA   O
1                       ,   O
2                  adalah   O
3                       )   X
4                       ,   Y
5                  ikatan   P
6                      10   O
7                       ,   O
8                       4   Q
9                pasangan   Q
10                   abad   A
11                     20   B
12                      ,   C
13              bersamaan   D

and I'd like to merge some row contains numbers with commas , number+,+number to one row. so the output should be the following:

                     Word   Tag
0                     DNA   O
1                       ,   O
2                  adalah   O
3                       )   X
4                       ,   Y
5                  ikatan   P
6                    10,4   O
7                pasangan   Q
8                    abad   A
9                      20   B
10                      ,   C
11              bersamaan   D

Any ideas? Thanks in advance.

I have tried:

coma = df['Word'].shift().ne(',').mul(df['Word'].ne(',')).cumsum()
new_df = df.groupby(coma, as_index=False).agg({'Word' : ''.join, 'Tag' : 'first'})
print(new_df)

but it also combines words and commas, meanwhile I just want to combine numbers and commas

pandas dataframe merge numpy python
1 Answers

One idea is idetify values with test numeric before and after ,, create groups and aggregate join and first:

m1 = df['Word'].str.isnumeric() & df['Word'].shift(-1).eq(',')
m2 = df['Word'].eq(',') & df['Word'].shift().str.isnumeric()

m3 = df['Word'].str.isnumeric() & df['Word'].shift().eq(',')
m4 = df['Word'].eq(',') & df['Word'].shift(-1).str.isnumeric()

m = (m1 | m2 | m3 | m4)
g = (~m).cumsum().where(m)

m5 = g.map(g.value_counts()) == 3
g1 = g.where(m5, np.arange(len(df)) + .5)

df1 = (df.groupby(g1, as_index=False)
          .agg({'Word': ''.join, 'Tag' :'first'}))
print(df1)
         Word Tag
0         DNA   O
1           ,   O
2      adalah   O
3           )   X
4           ,   Y
5      ikatan   P
6        10,4   O
7    pasangan   Q
8        abad   A
9          20   B
10          ,   C
11  bersamaan   D
user_29010020
Related