Python Forum
Need Help! Pandas EXCEL PIVOT
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need Help! Pandas EXCEL PIVOT
#1
I want to sort column 'PERIOD_START_TIME' from new date to old date.
df_pivot = pd.pivot_table (df, values = 'NTM NR RET NSA SN Total Drops',index = ['CELLCU'], columns = 'PERIOD_START_TIME', aggfunc = 'sum')
Output:
CELLCU 10/13/2022 10/14/2022 10/15/2022 10/16/2022 10/17/2022 A1WA0944C11 580 384 590 644 1464 A1WA0944C21 2065 3649 2687 2220 3205 A1WA0944C31 1874 1829 1901 2617 3111 A1WA6372A11 1327 1507 404 541 1478 A1WA6372A21 2559 35351 16881 9500 2203 A1WA6372A31 748 7422 563 914 1086 A1WA6374A11 84 93 1828 1339 414 A1WA6374A21 119 463 271 2245 628 A1WA6374A31 1532 6173 36461 78170 15273 A1WA6375A11 98 93 223 188 120
Yoriz write Nov-13-2022, 09:07 PM:
Please post all code, output and errors (in their entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

Attached Files

Thumbnail(s)
   
Reply
#2
If this is a question about pivot tables, please provide a sample of the original data and describe the result you want after pivoting. Your problem might be solved by sorting the original dataframe before pivoting.

If you want to sort your pivoted dataframe columns:
from io import StringIO
import pandas as pd
from datetime import datetime

# Reproduce pivoted dataframe
csv_data= StringIO("""
CELLCU 10/13/2022 10/14/2022 10/15/2022 10/16/2022 10/17/2022
A1WA0944C11 580 384 590 644 1464
A1WA0944C21 2065 3649 2687 2220 3205
A1WA0944C31 1874 1829 1901 2617 3111
A1WA6372A11 1327 1507 404 541 1478
A1WA6372A21 2559 35351 16881 9500 2203
A1WA6372A31 748 7422 563 914 1086
A1WA6374A11 84 93 1828 1339 414
A1WA6374A21 119 463 271 2245 628
A1WA6374A31 1532 6173 36461 78170 15273
A1WA6375A11 98 93 223 188 120""")

df = pd.read_csv(csv_data, sep=" ")

# Sort columns[1:].  Month/Day/Year is not good for sorting.  Use datetime key.
columns = list(df.columns)
dates = sorted(columns[1:], key=lambda x:datetime.strptime(x, "%m/%d/%Y"), reverse=True)
df = df.reindex([columns[0]] + dates, axis=1)
print(df)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python and pandas: Aggregate lines form Excel sheet Glyxbringer 12 1,699 Oct-31-2023, 10:21 AM
Last Post: Pedroski55
  Trying to get counts/sum/percentages from pandas similar to pivot table cubangt 6 1,327 Oct-06-2023, 04:32 PM
Last Post: cubangt
  export into excel, how to implement pandas into for-loop deneme2 6 2,362 Sep-01-2022, 05:44 AM
Last Post: deneme2
  group by create pivot table python dawid294 1 1,258 Jun-22-2022, 06:13 PM
Last Post: Larz60+
  Help with Integration Pandas excel - Python Gegemendes 5 1,728 Jun-05-2022, 09:46 PM
Last Post: Gegemendes
  Sum the values in a pandas pivot table specific columns klllmmm 1 4,546 Nov-19-2021, 04:43 PM
Last Post: klllmmm
  pandas pivot table: How to find count for each group in Index and Column JaneTan 0 3,230 Oct-23-2021, 04:35 AM
Last Post: JaneTan
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 4,167 Aug-29-2021, 12:39 PM
Last Post: snippsat
  identical cells in 2 different excel sheets python pandas esso 0 1,598 Jul-19-2020, 07:50 PM
Last Post: esso
  SQL Pivot EAV Quentin 2 2,782 Dec-03-2019, 11:52 PM
Last Post: Quentin

Forum Jump:

User Panel Messages

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