Creating table python

Question

I have a data set in excel. A sample of the data is given below. Each row contains a number of items; one item in each column. The data has no headers either.

a b a d

g z f d a

e

dd gg dd g f r t

want to create a table which should look like below. It should count the items in each row and display the count by the row. I dont know apriori how many items are in the table.

row# a b d g z f e dd gg r t

1 2 1 1 0 0 0 0 0 0 0 0

2 1 0 1 1 1 1 0 0 0 0 0

3 0 0 0 0 0 0 1 0 0 0 0

4 0 0 0 1 0 1 0 2 1 1 1

I am not an expert in python and any assistance is very much appreciated.

cs95 · Accepted Answer · 2017-11-03 13:21:06Z

1

Use get_dummies + sum:

df = pd.read_csv(file, names=range(100)).stack() # setup to account for missing values
df.str.get_dummies().sum(level=0)

   a  b  d  dd  e  f  g  gg  r  t  z
0  2  1  1   0  0  0  0   0  0  0  0
1  1  0  1   0  0  1  1   0  0  0  1
2  0  0  0   0  1  0  0   0  0  0  0
3  0  0  0   2  0  1  1   1  1  1  0

edited Nov 3, 2017 at 13:21

answered Nov 3, 2017 at 2:25

cs95

406k106 gold badges744 silver badges797 bronze badges

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

2 Comments

M. Nair Over a year ago

Thanks for the reply. Unfortunately, the code is giving me an error. The error is as follows "AttributeError: 'list' object has no attribute 'stack'".

cs95 Over a year ago

@M.Nair I was missing a closing bracket. It should’ve thrown a syntax error. Check my edit.

Collectives™ on Stack Overflow

Creating table python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related