0

I have normal dataframe.

id  name    age city        date
1   Jane    43  London      2020-01-12
2   Jose    34  London      2020-01-12
3   Poul    53  Leed        2020-01-12
4   Mark    29  Manchester  2020-02-12
5   Zak     36  London      2020-02-12
6   Lin     75  Birmingham  2020-03-12
7   Word    55  York        2020-04-12
8   Gene    33  Leed        2020-04-12

I would like convert to time series dataframe, could you teach me how to do?

In real dataset, there are a lot of cities. I want it automatically generating table.

My expect time series is:

date        London  Leed    Manchester  Birmingham  York    
2020-01-12  2       1       0           0            0
2020-02-12  1       0       1           0            0
2020-03-12  0       0       0           1            0
2020-04-12  0       1       0           0            1
Bella
  • 43
  • 4
  • `could you teach me how to do?` Welcome to SO. This isn't a discussion forum or tutorial. Please take the [tour] and take the time to read [ask] and the other links found on that page. Pandas has [good documentation](https://pandas.pydata.org/docs/user_guide/10min.html#reshaping) - spend some time with it. – wwii Dec 26 '20 at 16:19
  • I think this is a great question but formulated in a bad way. Asked it here in a form which I think is more appropriate: https://stackoverflow.com/questions/65458681/cooccurence-table-of-values-of-two-columns-with-non-overlapping-set-of-entries – zabop Dec 26 '20 at 17:02

1 Answers1

1

You can use pivot_table:

df.pivot_table(index='date', columns='city', aggfunc='size', fill_value=0)

city        Birmingham  Leed  London  Manchester  York
date
2020-01-12           0     1       2           0     0
2020-02-12           0     0       1           1     0
2020-03-12           1     0       0           0     0
2020-04-12           0     1       0           0     1

You can also use pd.crosstab(df.date, df.city)

mck
  • 40,932
  • 13
  • 35
  • 50