Re-arranging Dataframe columns based on substrings in column names

Question

I have a dataframe, df, that has values of two characters over quarters of the year. [Q1 = Jan, Feb, Mar; Q2 = Apr, May, Jun, ...etc].

The quarters are the previous four quarters trailing the current quarter (ex. if Current Quarter = Q3 17, quarters measured are Q3 16, Q4 16, Q1 17, Q2 17).

Quarter      Q1 17   Q2 17  Q3 16  Q4 16
Character
Sonic         10.0   6.0    19.0    3.0
Shadow        5.0    5.0    7.0     23.0

I would like to reorder the columns in a time applied manner, Q3 16, Q4 16, Q1 17, Q2 17, to get this:

Quarter      Q3 16   Q4 16  Q1 17  Q2 17
Character
Sonic         19.0   3.0    10.0    6.0
Shadow        7.0    23.0   5.0     5.0

I've thought about sorting by substring where the year is contained. But is there a cleaner and quicker way to do this?

[EDIT]

For clarification, I created substrings of the year:

columns = list(scar_pivot.columns.values)
sort_columns_years = [int(c[3:5]) for c in columns]
sort_columns_years=
[17, 17, 16, 16]

Since this code in question is used periodically over time, a user would be measuring different quarters depending on when the code is used. (ex. Current quarter is Q2 17, there would be three values of 16 and one value of 17 in sort_columns_years, for Q2-4 16, and Q1 17.

After sorting sort_columns_years, I am thinking I can use count to measure how many of a smaller value exists, to do something in the case of hree quarters in the preceding year like this:

new_list = []
`if sort_columns_years.count(16) == 3:
     sort_quarters = ['Q2','Q3','Q4','Q1']
     for each in range(0,len(sort_quarters)):
        new_list.append(sort_column_years[each] + sort_column_years(sort_quarters[each]))

to get

new_list = ['Q2 16', 'Q3 16', 'Q4 16', 'Q1 17']

but I don't know how to do something similar avoiding hard coding 16 or 17.

@florence-y your output does not align with the input. I got what you want. Edit your expected output. — mad_
– mad_, Commented Aug 17, 2018 at 18:01

mad_ · Accepted Answer · 2018-08-17 18:04:14Z

1

Though I believe there would be some way to do it using series or numpy But, if you think the problem can be solved if the year component is brought in front of the quater like 16Q1 and then sorting would be easy and more meaningful. Here is the example:

def restructure(l):
    temp_lis=[]
    for i in l:
        temp=i.split(" ")
        temp_lis.append(temp[1] +' '+temp[0] )
    return temp_lis

a=['Q1 17', 'Q2 17','Q3 16','Q4 16']
new_list=[]

new_list=sorted(restructure(a))
final=restructure(new_list)

print(final)

Output:

['Q3 16', 'Q4 16', 'Q1 17', 'Q2 17']

If you need to define your df with columns.

df[[final]] # should be enough

edited Aug 17, 2018 at 18:04

answered Aug 17, 2018 at 17:52

mad_

8,2832 gold badges32 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Re-arranging Dataframe columns based on substrings in column names

[EDIT]

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

[EDIT]

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related