Combine Columns Based Upon Another Column with Python and Pandas

Question

I have a huge tab-delimited file such as the following :

3 Line1 0 100 A
4 Line1 100 200 A
7 Line1 200 300 B
2 Line1 300 400 B
12 Line1 400 500 C
10 Line1 500 600 C

For all the rows that have the letters (A, B, ect), I need to combine their values based upon the number in the first column. For example, what should be the result is below:

7 A
9 B
22 C

I am currently using Pandas + Python to figure this out.

For the result, is it going to be 7 and 9 for `A` and `B` in example? — niraj, Mar 18 '18 at 19:24
Yes, my mistake. I went ahead and corrected the original post. — Steveman30290, Mar 18 '18 at 19:27

score 1 · Accepted Answer · answered Mar 18 '18 at 19:28

Suppose the df is as below:

   val     id  line  col1 col2
0    3  Line1     0   100    A
1    4  Line1   100   200    A
2    7  Line1   200   300    B
3    2  Line1   300   400    B
4   12  Line1   400   500    C
5   10  Line1   500   600    C

Then, I think you can use groupby followed by sum:

result_df = df.groupby('col2')['val'].sum().to_frame('Sum')
print(result_df)

Result:

score 0 · Answer 2 · answered Mar 18 '18 at 19:28

0

You have to use join() method

Table1.join(table2.set_index(''key"),on='key')

answered Mar 18 '18 at 19:28

Julio CamPlaz

857
8
18

score 0 · Answer 3 · answered Mar 18 '18 at 19:29

0

df = pd.DataFrame({'Col1':[3,4,7,2,12,10],'Col2':['A','A','B','B','C','C']})
df.groupby('Col2').sum()

answered Mar 18 '18 at 19:29

It_is_Chris

13,504
2
23
41

Combine Columns Based Upon Another Column with Python and Pandas

3 Answers3