Sorting Data in Pandas

user_18781205 0
Viewed 50

I have the dataset shown below. I am trying to sort it so that the columns are in this order: Week End, Australia, Germany, France, etc...

I have tried using loc and assigning each of the data sets as variables but when I create a new DataFrame it causes an error. Any help would be appreciated.

This is the data before any changes:

Region Week End Value
Australia 2014-01-11 1.480510
Germany 2014-01-11 1.481258
France 2014-01-11 0.986507
United Kingdom 2014-01-11 1.973014
Italy 2014-01-11 0.740629

This is my desired output:

Week End Australia Germany France United Kingdom Italy
2014-01-11 1.480510 1.481258 0.986507 1.973014 0.740629

What I've tried:

cols = (['Region','Week End','Value'])
df = GS.loc[GS['Brand'].isin(rows)]
df = df[cols]
AUS = df.loc[df['Region'] == 'Australia']
JPN = df.loc[df['Region'] == 'Japan']
US = df.loc[df['Region'] == 'United States of America']
jupyter-notebook pandas dataframe python
2 Answers

I think that you could actually just do:

df.pivot(index="Week End", columns="Region", values="Value")
user_47131690

User 965311532's answer is much more concise, but an alternative approach using dictionaries would be:

new_df = {'Week End': df['Week End'][0]}
new_df.update({region: value for region, value in zip(df['Region'], df['Value'])})
new_df = pd.DataFrame(new_df, index = [0])

As user 965311532 pointed out, the above code will not work if there are more dates. In this case, we could use pandas groupby:

dates = []
for date, group in df.groupby('Week End'):
    date_df = {'Week End': date}
    date_df.update({region: value for region, value in zip(df['Region'], df['Value'])})
    date_df = pd.DataFrame(date_df, index = [0])
    dates.append(date_df)
new_df = pd.concat(dates)
user_156632900
Related