Hello I have the below data set. I am trying to sort it so that I have columns in this order: Week End, Australia, Germany, France ... I have tried using loc and assigning each of the data sets as variables but when I create a new data frame it causes an error. Any help would be appreciated.

This is the data before any changes:

This is my objective

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']

CodePudding user response：

I think that you could actually just do:

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

CodePudding user response：

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)