Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pandas confused
#1
I use pandas occasionally to read excel files.
I found out that I can also use it to calculate subtotals
using group(...). Works fine. Except when i print the result
of the subtotal after grouping, it displays 0,1... on top,
are these indexes? Example sites don't seem comment on that,
so I must be missing something. What ?
thx,
Paul
import pandas as panda

Q = [['AF-10', 744.42],
['AF-10', 1243.68],
['AF-20', 90.0],
['BA-40', -1425.0],
['BA-40', 1425.0],
['BA-40', 1425.0],
['BA-40', 1425.0],
['BA-50', 150.0],
['BO-30', 16514.61],
['BR-10', 528.35]]  # etc....
Qdf = panda.DataFrame(Q)
print(Qdf.groupby([0]).sum())
Output:
1 0 AF-10 1988.10 AF-20 90.00 BA-40 2850.00 BA-50 150.00 BO-30 16514.61 BR-10 528.35
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu)
Reply
#2
(Sep-18-2021, 07:26 AM)DPaul Wrote: Except when i print the result
of the subtotal after grouping, it displays 0,1... on top,
are these indexes?
No only 1 are index use df.columns to see,have to reset the index.
>>> df = Qdf.groupby([0]).sum()
>>> df
              1
0              
AF-10   1988.10
AF-20     90.00
BA-40   2850.00
BA-50    150.00
BO-30  16514.61
BR-10    528.35
>>> df.columns
Int64Index([1], dtype='int64')
>>> 
>>> df = df.reset_index()
>>> df.columns
Int64Index([0, 1], dtype='int64')
>>> df
       0         1
0  AF-10   1988.10
1  AF-20     90.00
2  BA-40   2850.00
3  BA-50    150.00
4  BO-30  16514.61
5  BR-10    528.35

>>> df[0]
0    AF-10
1    AF-20
2    BA-40
3    BA-50
4    BO-30
5    BR-10
Name: 0, dtype: object
Reply
#3
Thanks for your speedy answer. Leaves me speechless.
I wonder if somebody thought of the KISS rule,
when he/she implemented this Smile
Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu)
Reply
#4
This also seems to do the trick:

Qlst = Qdf.values.tolist()
print(Qlst)

Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu)
Reply
#5
Yes if the goal to take data out Pandas to a list then it will work,as column index don't get used.
If take that list back into DataFrame then it will automatically give column index.
>>> data = Qdf.values.tolist()
>>> data
[['AF-10', 744.42],
 ['AF-10', 1243.68],
 ['AF-20', 90.0],
 ['BA-40', -1425.0],
 ['BA-40', 1425.0],
 ['BA-40', 1425.0],
 ['BA-40', 1425.0],
 ['BA-50', 150.0],
 ['BO-30', 16514.61],
 ['BR-10', 528.35]]
>>> type(data) # A normal Python list
<class 'list'>
>>> 
>>> import pandas as pd
>>> 
>>> df = panda.DataFrame(data)
>>> df
       0         1
0  AF-10    744.42
1  AF-10   1243.68
2  AF-20     90.00
3  BA-40  -1425.00
4  BA-40   1425.00
5  BA-40   1425.00
6  BA-40   1425.00
7  BA-50    150.00
8  BO-30  16514.61
9  BR-10    528.35
>>> df = df.rename(columns={0: 'Name', 1: 'Cost'})
>>> df
    Name      Cost
0  AF-10    744.42
1  AF-10   1243.68
2  AF-20     90.00
3  BA-40  -1425.00
4  BA-40   1425.00
5  BA-40   1425.00
6  BA-40   1425.00
7  BA-50    150.00
8  BO-30  16514.61
9  BR-10    528.35
>>> type(df) # A DataFrame
<class 'pandas.core.frame.DataFrame'>
Reply
#6
They could have made the default column names A, B, C like spreadsheets do, but once you get past 26 columns it works better to use numbers, IMHO.
Reply
#7
Thing is, in this case I do not care about the headers,
but i want the row labels.
".reset_index" and "for column in df..." give me what i want.
Thanks for all the help.
Paul
It is more important to do the right thing, than to do the thing right.(P.Drucker)
Better is the enemy of good. (Montesquieu)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  is and '==' i'm confused hshivaraj 6 499 Sep-15-2021, 09:45 AM
Last Post: snippsat
  Confused with 'flags' tester_V 10 1,412 Apr-12-2021, 03:03 AM
Last Post: tester_V
  Simple Tic Tac Toe but I'm confused Izith 1 736 Sep-26-2020, 04:42 PM
Last Post: Larz60+
  I am really confused with this error. Runar 3 937 Sep-14-2020, 09:27 AM
Last Post: buran
  Confused by 'break' in the official documents Chuck_Norwich 2 898 Apr-12-2020, 09:26 PM
Last Post: Chuck_Norwich
  Confused on how to go about writing this or doing this... pythonforumuser 3 1,053 Feb-10-2020, 09:15 AM
Last Post: snippsat
  I always get 'None' returned. Confused. What did I miss? jpezz 2 1,154 Apr-07-2019, 10:06 AM
Last Post: jpezz
  Confused by order of operations ward 4 1,311 Jan-22-2019, 08:53 PM
Last Post: Larz60+
  Confused by this modulo equation VikramSuh 3 1,541 Sep-05-2018, 12:43 PM
Last Post: VikramSuh
  I am new to python , i am confused with this syntax , please help aankrose 3 1,482 Aug-03-2018, 06:49 PM
Last Post: aankrose

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020