I have a dataframe with index, features and time data, but the time data is in one column like this:
id date feature
1 date1 feature1
2 date2 feature2
1 date2 feature3
I want to transform it into this:
id date feature
1 date1 feature1 date2 feature3
2 date2 feature2 NaN NaN
Already did this by explicitly defining dataframes, queries and joins, but failed to find a dynamic way. What I wrote:
df = pd.read_excel('some path')
import pandas as pd
list1 = []
list2 = []
list3 = []
def placeholder_lists():
for i in range(7):
if len(str(i)) == 1:
if i not in [8,9]:
i = "0"+str(i+3)
else:
i = str(i+3)
else:
i = str(i+3)
list1.append(i)
for l in range(7):
if len(str(l)) == 1:
if l not in [10,9]:
l = "0"+str(l+2)
else:
l = str(l+2)
else:
l = str(l+2)
list2.append(l)
for g in range(7):
if len(str(g)) == 1:
if g not in [9,8]:
g = "0"+str(g+1)
else:
g = str(g+1)
else:
g = str(g+1)
list3.append(g)
placeholder_lists()
for m,n,u in zip(list1,list2, list3):
df01 = df.query('dw_creation_date == "01-AUG-17" ')
e = str(u)+"-AUG-17"
currentdf = df.query('dw_creation_date == "%s"' % e)
if 1 == "01":
currentdf = df01
first = "df"+m
second = "df"+n
listie = range(50)
first = second.join(currentdf.set_index('unique_identifier'), on='unique_identifier', lsuffix = listie[n])
... And the error I get:
first = second.join(currentdf.set_index('unique_identifier'), lsuffix = listie[n])
TypeError: join() takes no keyword arguments
Any ideas?
You can
groupby
byid
and apply newdf
. Then reshape byunstack
and sort columns bysort_index
in second level ofMultiindex
.Last flattening
Multiindex
in columns andreset_index
.