CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
user2839288
user2839288

Reputation: 627

How can I restructure this dataframe?

I'm trying to get data from a sqlite query into a format for pygal.

I need to get from something like this -

count(id)  pdate         station
1          2019-05-19    Gem 106
1          2019-05-14    Absolute Radio
2          2019-04-26    Gem 106
2          2019-05-01    Gem 106
2          2019-04-27    Gem 106
1          2019-05-17    Absolute Radio
1          2019-05-05    Gem 106
3          2019-05-12    Kiss

to something like this -

pdate       Gem 106   Absolute Radio    Kiss
2019-05-19  3         0                 5
2019-05-20  6         6                 5

I don't think pivots are the answer but don't understand stack / unstack well enough - could do with a pointer or two?

Many thanks

Upvotes: 0

Views: 104

Answers (2)

user2839288
user2839288

Reputation: 627

Duh - yeah, it's a pivot table. Funny how you can't see things sometimes - especially when it's late.

Apols for an imperfect question - the example I gave didn't add up.

Thanks for the replies though.

Upvotes: 0

Quang Hoang
Quang Hoang

Reputation: 150765

IIUC, this is a case for pivot_table and your output doesn't reflect the sample data:

# toy data
np.random.seed(1)
df = pd.DataFrame({
    'pdate': np.random.choice(['2019-05-19', '2019-05-20'], 30),
    'station': np.random.choice(['Gem 106', 'Absolute Radio', 'Kiss'], 30)
})

# pivot_table
df.pivot_table(index='pdate', columns='station', aggfunc='size')

Output:

station     Absolute Radio  Gem 106     Kiss
pdate           
2019-05-19  10              4           2
2019-05-20  4               5           5

Upvotes: 1

Related Questions