0

I am trying to create a Crosstab of my data that I pull from an SQL server. On other solutions I see people have provided solutions like below:

df = pd.DataFrame([[1,2,3,4]], columns=['a', 'b', 'v', 'w'])

However I already have a dataframe built out by pulling data from a SQL server, looping and appending each item to a list, then created a DataFrame from the list.

Res = c.execute(cmd)
rows=[]
[rows.append(list(row)) for row in Res]
df= pd.DataFrame.from_records(rows)

df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 124 entries, 0 to 123
Data columns (total 14 columns):

I try to run the code below and get the ValueError message below.

pd.crosstab([df['Term'], df['ID']], df['Class'], margins = True)]

ValueError: Shape of passed values is (1, 3), indices imply (124, 3).

Any help would be greatly appreciated. Thank you.

Tom Kim
  • 39
  • 3
  • 1
    What did you try to run? – Quang Hoang Sep 24 '19 at 15:46
  • The reason other solutions show simplified data like the sample you posted, is that in od\reder to help, we need a [mcve] including partial or sample input and preferred output. See [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – G. Anderson Sep 24 '19 at 15:54
  • @QuangHoang I was trying to run pd.crosstab([df['Term'], df['ID']], df['Class'], margins = True)] to produce a cross table. – Tom Kim Sep 24 '19 at 16:09
  • You could do `df.pivot_table(index=['Term','ID'], columns='Class'], agg_func='count')` or `df.groupby(['Term','ID'])['Class'].value_counts()`. – Quang Hoang Sep 24 '19 at 16:10
  • @G.Anderson I updated the how to I produced the initial dataframe by executing a SQL command through Python. – Tom Kim Sep 24 '19 at 16:12
  • @QuangHoang With your code I am receiving the ValueError: Grouper for 'Term' not 1-dimensional – Tom Kim Sep 24 '19 at 16:17

0 Answers0