Turning data with row/col index into table in Python

Question

I have a data with position (row/column),

df = pd.DataFrame({'ID': ['a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'b02', 'b02', 'b02','b02', 'b02', 'b02', 'b02', 'b02'],
                   'Row': [1, 1, 1, 2, 2, 2, 3, 3, 3, 1, 1, 2, 2, 3, 3, 3],
                   'Col': [1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 3, 1, 3, 1, 2, 3],
                   'Result': ['p', 'f', 'p', 'p', 'p', 'f', 'p', 'p', 'p', 'p', 'p', 'p', 'f', 'p', 'p', 'p']})

and I am trying to turn it into tables, and calculate how many p and f in the table, like these:

ID: a01
p  f  p
p  p  f
p  p  p

p: 7 f: 2

ID: b02
p     p
p     f
p  p  p

p: 6 f: 1

There are some missing data, but the number of the row and column are fixed, so just leave them blank.

I have no idea how to achieve this, any thought?

Scott Boston · Accepted Answer · 2020-08-20 13:22:04Z

2

You can do it like this:
I did correct and input error in your input dataframe.
(Changed the value in df.loc[8, 'ID'] from b02 to a01)

df = pd.DataFrame({'ID': ['a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'b02', 'b02','b02', 'b02', 'b02', 'b02', 'b02'],
                   'Row': [1, 1, 1, 2, 2, 2, 3, 3, 3, 1, 1, 2, 2, 3, 3, 3],
                   'Col': [1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 3, 1, 3, 1, 2, 3],
                   'Result': ['p', 'f', 'p', 'p', 'p', 'f', 'p', 'p', 'p', 'p', 'p', 'p', 'f', 'p', 'p', 'p']})

df

dfs = {}
for n, g in df.groupby('ID'):
    dfs[n] = g.pivot('Row', 'Col', 'Result').fillna('')
    print(f'ID: {n}')
    print(dfs[n])
    print('\n')
    print(dfs[n].stack().value_counts().to_dict())
    print('\n')

Output:

ID: a01
Col  1  2  3
Row         
1    p  f  p
2    p  p  f
3    p  p  p


{'p': 7, 'f': 2}


ID: b02
Col  1  2  3
Row           
1    p     p
2    p     f
3    p  p  p


{'p': 6, 'f': 1}

Update to add concatenation and output to csv or excel.

pd.concat(dfs, keys=dfs.keys()).to_csv('out.csv')
!type out.csv

Output file contents:

,Row,1,2,3
a01,1,p,f,p
a01,2,p,p,f
a01,3,p,p,p
b02,1,p,,p
b02,2,p,,f
b02,3,p,p,p

edited Aug 20, 2020 at 13:22

answered Aug 19, 2020 at 15:55

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

4 Comments

Joanne Over a year ago

@ Scott Boston - Is there any way to save the result as csv or excel file? I tried dfs.to_csv('Output.csv'), but it doesn't work

Scott Boston Over a year ago

Do you want two different excel worksheets one for each dataframe, or would you like to concat the two dataframes together? Yes, we can get the output dataframe(s) to a csv or excel. Just let me know how you'd like to see them?

Joanne Over a year ago

@ Scott Boston -- I would like to contact the two dataframes together. Thanks

Scott Boston Over a year ago

@Joanne Look at update.... I used pd.concat to concatentation the dictionary entries together and use to_csv to create a file, you can use to_excel also.

YOLO · Accepted Answer · 2020-08-19 15:49:10Z

2

You can do .value_counts on group

dt = df.groupby(['ID'])['Result'].value_counts().rename('count').reset_index()
print(dt)

    ID Result  count
0  a01      p      6
1  a01      f      2
2  b02      p      7
3  b02      f      1

answered Aug 19, 2020 at 15:49

YOLO

22k5 gold badges25 silver badges42 bronze badges

2 Comments

Joanne Over a year ago

This is helpful, thanks! Do you know how to turn the data into table?

YOLO Over a year ago

what do you mean by table?

Collectives™ on Stack Overflow

Turning data with row/col index into table in Python

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related