Pivot table conversion generating value error in pands

up vote
0
down vote

favorite

I am fetching data from the table which contains

execution_time, type, status, process, sub_process, value 

2018-11-12 16:09:48.179547, backlog, started, p1, s1, 100

2018-11-12 16:09:48.179547, backlog, created, p1, s1, 1005

2018-11-12 16:09:48.179547, backlog, started, p2, s1, 500

2018-11-12 16:09:48.179547, V1, created, p1, s1, 10

2018-11-12 16:09:48.179547, V2, created, p1, s1, 15

2018-11-12 16:09:48.179547, backlog, started, p3, s1, 0

2018-11-12 16:09:48.179547, backlog, started, p4, s1, 45

2018-11-12 16:09:48.179547, V1, started, p4, s1, 400

I want to pivot this data such that it should be like this:

status, process, sub_process, backlog, V1, V2

(backlog, V1 and V2 should contain corresponding values if exists)
Here is my code. I am getting error as

pivot_table() got multiple values for argument 'values'

sql = "select * from temp.data_table"

cursor.execute(sql)

results = cursor.fetchall()

df = pd.DataFrame(results)

df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

edited Nov 13 at 3:45

Aqueous Carlos

301113

asked Nov 13 at 1:55

Madhura Mhatre

4119

add a comment |

up vote
0
down vote

favorite

I am fetching data from the table which contains

execution_time, type, status, process, sub_process, value 

2018-11-12 16:09:48.179547, backlog, started, p1, s1, 100

2018-11-12 16:09:48.179547, backlog, created, p1, s1, 1005

2018-11-12 16:09:48.179547, backlog, started, p2, s1, 500

2018-11-12 16:09:48.179547, V1, created, p1, s1, 10

2018-11-12 16:09:48.179547, V2, created, p1, s1, 15

2018-11-12 16:09:48.179547, backlog, started, p3, s1, 0

2018-11-12 16:09:48.179547, backlog, started, p4, s1, 45

2018-11-12 16:09:48.179547, V1, started, p4, s1, 400

I want to pivot this data such that it should be like this:

status, process, sub_process, backlog, V1, V2

(backlog, V1 and V2 should contain corresponding values if exists)
Here is my code. I am getting error as

pivot_table() got multiple values for argument 'values'

sql = "select * from temp.data_table"

cursor.execute(sql)

results = cursor.fetchall()

df = pd.DataFrame(results)

df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

edited Nov 13 at 3:45

Aqueous Carlos

301113

asked Nov 13 at 1:55

Madhura Mhatre

4119

add a comment |

up vote
0
down vote

favorite

I am fetching data from the table which contains

execution_time, type, status, process, sub_process, value 

2018-11-12 16:09:48.179547, backlog, started, p1, s1, 100

2018-11-12 16:09:48.179547, backlog, created, p1, s1, 1005

2018-11-12 16:09:48.179547, backlog, started, p2, s1, 500

2018-11-12 16:09:48.179547, V1, created, p1, s1, 10

2018-11-12 16:09:48.179547, V2, created, p1, s1, 15

2018-11-12 16:09:48.179547, backlog, started, p3, s1, 0

2018-11-12 16:09:48.179547, backlog, started, p4, s1, 45

2018-11-12 16:09:48.179547, V1, started, p4, s1, 400

I want to pivot this data such that it should be like this:

status, process, sub_process, backlog, V1, V2

(backlog, V1 and V2 should contain corresponding values if exists)
Here is my code. I am getting error as

pivot_table() got multiple values for argument 'values'

sql = "select * from temp.data_table"

cursor.execute(sql)

results = cursor.fetchall()

df = pd.DataFrame(results)

df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

edited Nov 13 at 3:45

Aqueous Carlos

301113

asked Nov 13 at 1:55

Madhura Mhatre

4119

I am fetching data from the table which contains

execution_time, type, status, process, sub_process, value 

2018-11-12 16:09:48.179547, backlog, started, p1, s1, 100

2018-11-12 16:09:48.179547, backlog, created, p1, s1, 1005

2018-11-12 16:09:48.179547, backlog, started, p2, s1, 500

2018-11-12 16:09:48.179547, V1, created, p1, s1, 10

2018-11-12 16:09:48.179547, V2, created, p1, s1, 15

2018-11-12 16:09:48.179547, backlog, started, p3, s1, 0

2018-11-12 16:09:48.179547, backlog, started, p4, s1, 45

2018-11-12 16:09:48.179547, V1, started, p4, s1, 400

I want to pivot this data such that it should be like this:

status, process, sub_process, backlog, V1, V2

(backlog, V1 and V2 should contain corresponding values if exists)
Here is my code. I am getting error as

pivot_table() got multiple values for argument 'values'

sql = "select * from temp.data_table"

cursor.execute(sql)

results = cursor.fetchall()

df = pd.DataFrame(results)

df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

python pandas cursor pivot

edited Nov 13 at 3:45

Aqueous Carlos

301113

asked Nov 13 at 1:55

Madhura Mhatre

4119

edited Nov 13 at 3:45

Aqueous Carlos

301113

asked Nov 13 at 1:55

Madhura Mhatre

4119

edited Nov 13 at 3:45

Aqueous Carlos

301113

edited Nov 13 at 3:45

Aqueous Carlos

301113

edited Nov 13 at 3:45

Aqueous Carlos

301113

asked Nov 13 at 1:55

Madhura Mhatre

4119

asked Nov 13 at 1:55

Madhura Mhatre

4119

asked Nov 13 at 1:55

Madhura Mhatre

4119

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

Using (remove df in pivot_table )

df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

Out[85]: 

type                             V1    V2   backlog

status   process sub_process                       

 created  p1      s1           10.0  15.0    1005.0

 started  p1      s1            NaN   NaN     100.0

          p2      s1            NaN   NaN     500.0

          p3      s1            NaN   NaN       0.0

          p4      s1          400.0   NaN      45.0

Or pd.pivot_table

pd.pivot_table(df,index=['status','process','sub_process'],columns='type',values=['value'], aggfunc = 'sum')

Out[86]: 

                              value               

type                             V1    V2  backlog

status   process sub_process                      

 created  p1      s1           10.0  15.0   1005.0

 started  p1      s1            NaN   NaN    100.0

          p2      s1            NaN   NaN    500.0

          p3      s1            NaN   NaN      0.0

          p4      s1          400.0   NaN     45.0

answered Nov 13 at 2:21

W-B

93.3k72755

hey @W-B I removed df but I am getting error as "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:21

@MadhuraMhatre check you df columns by df.columns , i think you have white space there
– W-B
Nov 13 at 3:22

This is what my revised line looks like df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value',aggfunc ='sum')
– Madhura Mhatre
Nov 13 at 3:28

@MadhuraMhatre runing fine on my side .
– W-B
Nov 13 at 3:31

Thats interesting I still get that "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:46

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53272669%2fpivot-table-conversion-generating-value-error-in-pands%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

Using (remove df in pivot_table )

df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

Out[85]: 

type                             V1    V2   backlog

status   process sub_process                       

 created  p1      s1           10.0  15.0    1005.0

 started  p1      s1            NaN   NaN     100.0

          p2      s1            NaN   NaN     500.0

          p3      s1            NaN   NaN       0.0

          p4      s1          400.0   NaN      45.0

Or pd.pivot_table

pd.pivot_table(df,index=['status','process','sub_process'],columns='type',values=['value'], aggfunc = 'sum')

Out[86]: 

                              value               

type                             V1    V2  backlog

status   process sub_process                      

 created  p1      s1           10.0  15.0   1005.0

 started  p1      s1            NaN   NaN    100.0

          p2      s1            NaN   NaN    500.0

          p3      s1            NaN   NaN      0.0

          p4      s1          400.0   NaN     45.0

answered Nov 13 at 2:21

W-B

93.3k72755

hey @W-B I removed df but I am getting error as "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:21

@MadhuraMhatre check you df columns by df.columns , i think you have white space there
– W-B
Nov 13 at 3:22

This is what my revised line looks like df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value',aggfunc ='sum')
– Madhura Mhatre
Nov 13 at 3:28

@MadhuraMhatre runing fine on my side .
– W-B
Nov 13 at 3:31

Thats interesting I still get that "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:46

add a comment |

up vote
0
down vote

Using (remove df in pivot_table )

df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

Out[85]: 

type                             V1    V2   backlog

status   process sub_process                       

 created  p1      s1           10.0  15.0    1005.0

 started  p1      s1            NaN   NaN     100.0

          p2      s1            NaN   NaN     500.0

          p3      s1            NaN   NaN       0.0

          p4      s1          400.0   NaN      45.0

Or pd.pivot_table

pd.pivot_table(df,index=['status','process','sub_process'],columns='type',values=['value'], aggfunc = 'sum')

Out[86]: 

                              value               

type                             V1    V2  backlog

status   process sub_process                      

 created  p1      s1           10.0  15.0   1005.0

 started  p1      s1            NaN   NaN    100.0

          p2      s1            NaN   NaN    500.0

          p3      s1            NaN   NaN      0.0

          p4      s1          400.0   NaN     45.0

answered Nov 13 at 2:21

W-B

93.3k72755

hey @W-B I removed df but I am getting error as "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:21

@MadhuraMhatre check you df columns by df.columns , i think you have white space there
– W-B
Nov 13 at 3:22

This is what my revised line looks like df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value',aggfunc ='sum')
– Madhura Mhatre
Nov 13 at 3:28

@MadhuraMhatre runing fine on my side .
– W-B
Nov 13 at 3:31

Thats interesting I still get that "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:46

add a comment |

up vote
0
down vote

Using (remove df in pivot_table )

df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

Out[85]: 

type                             V1    V2   backlog

status   process sub_process                       

 created  p1      s1           10.0  15.0    1005.0

 started  p1      s1            NaN   NaN     100.0

          p2      s1            NaN   NaN     500.0

          p3      s1            NaN   NaN       0.0

          p4      s1          400.0   NaN      45.0

Or pd.pivot_table

pd.pivot_table(df,index=['status','process','sub_process'],columns='type',values=['value'], aggfunc = 'sum')

Out[86]: 

                              value               

type                             V1    V2  backlog

status   process sub_process                      

 created  p1      s1           10.0  15.0   1005.0

 started  p1      s1            NaN   NaN    100.0

          p2      s1            NaN   NaN    500.0

          p3      s1            NaN   NaN      0.0

          p4      s1          400.0   NaN     45.0

answered Nov 13 at 2:21

W-B

93.3k72755

Using (remove df in pivot_table )

df.pivot_table(index=['status','process','sub_process'],columns='type',values='value', aggfunc = 'sum')

Out[85]: 

type                             V1    V2   backlog

status   process sub_process                       

 created  p1      s1           10.0  15.0    1005.0

 started  p1      s1            NaN   NaN     100.0

          p2      s1            NaN   NaN     500.0

          p3      s1            NaN   NaN       0.0

          p4      s1          400.0   NaN      45.0

Or pd.pivot_table

pd.pivot_table(df,index=['status','process','sub_process'],columns='type',values=['value'], aggfunc = 'sum')

Out[86]: 

                              value               

type                             V1    V2  backlog

status   process sub_process                      

 created  p1      s1           10.0  15.0   1005.0

 started  p1      s1            NaN   NaN    100.0

          p2      s1            NaN   NaN    500.0

          p3      s1            NaN   NaN      0.0

          p4      s1          400.0   NaN     45.0

answered Nov 13 at 2:21

W-B

93.3k72755

answered Nov 13 at 2:21

W-B

93.3k72755

answered Nov 13 at 2:21

W-B

93.3k72755

answered Nov 13 at 2:21

W-B

93.3k72755

hey @W-B I removed df but I am getting error as "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:21

@MadhuraMhatre check you df columns by df.columns , i think you have white space there
– W-B
Nov 13 at 3:22

This is what my revised line looks like df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value',aggfunc ='sum')
– Madhura Mhatre
Nov 13 at 3:28

@MadhuraMhatre runing fine on my side .
– W-B
Nov 13 at 3:31

Thats interesting I still get that "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:46

add a comment |

hey @W-B I removed df but I am getting error as "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:21

@MadhuraMhatre check you df columns by df.columns , i think you have white space there
– W-B
Nov 13 at 3:22

This is what my revised line looks like df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value',aggfunc ='sum')
– Madhura Mhatre
Nov 13 at 3:28

@MadhuraMhatre runing fine on my side .
– W-B
Nov 13 at 3:31

Thats interesting I still get that "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:46

hey @W-B I removed df but I am getting error as "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:21

@MadhuraMhatre check you df columns by df.columns , i think you have white space there
– W-B
Nov 13 at 3:22

This is what my revised line looks like df=df.pivot_table(index=['status','process','sub_process'],columns='type',values='value',aggfunc ='sum')
– Madhura Mhatre
Nov 13 at 3:28

@MadhuraMhatre runing fine on my side .
– W-B
Nov 13 at 3:31

Thats interesting I still get that "KeyError: 'value'"
– Madhura Mhatre
Nov 13 at 3:46

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrgtkky