I have a dataframe as shown below.
A B
timestamp
2022-05-09 09:28:00 0 45
2022-05-09 09:28:01 3 NaN
2022-05-09 09:28:02 4 30
2022-05-09 09:28:03 5 20
2022-05-09 09:28:04 8 10
I want the values in column B to be split and represented in multiple columns depending on the values in column A as shown below.
A_0 A_3 A_4 A_5 A_8
timestamp
2022-05-09 09:28:00 45 NaN NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN NaN
2022-05-09 09:28:02 NaN NaN 30 NaN NaN
2022-05-09 09:28:03 NaN NaN NaN 20 NaN
2022-05-09 09:28:04 NaN NaN NaN NaN 10
Since the splitting depends on the values present in another column and there is no delimiter present, the solution to split the column depending on a delimiter does not work.
Thanks in advance!
EDIT: If I want to do it for multiple columns, for eg:
A B C D
timestamp
2022-05-09 09:28:00 0 45 3 20
2022-05-09 09:28:01 3 NaN 2 15
2022-05-09 09:28:02 4 30 5 34
2022-05-09 09:28:03 5 20 4 NaN
2022-05-09 09:28:04 8 10 8 NaN
And I want to convert to this
A_0 A_3 A_4 A_5 A_8 C_3 C_2 C_5 C_4 C_8
timestamp
2022-05-09 09:28:00 45 NaN NaN NaN NaN 20 NaN NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN NaN NaN 15 NaN NaN NaN
2022-05-09 09:28:02 NaN NaN 30 NaN NaN NaN NaN NaN 48 NaN
2022-05-09 09:28:03 NaN NaN NaN 20 NaN NaN NaN NaN NaN NaN
2022-05-09 09:28:04 NaN NaN NaN NaN 10 NaN NaN NaN NaN NaN
Is this still possible or do I have to do separetly and merge or concat them?
CodePudding user response：
Use DataFrame.set_index
with append=True for MultiIndex and reshape by Series.unstack:
df1=df.set_index('A',append=True)['B'].unstack().add_prefix('A_').rename_axis(columns=None)
print (df1)
A_0 A_3 A_4 A_5 A_8
timestamp
2022-05-09 09:28:00 45.0 NaN NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN NaN
2022-05-09 09:28:02 NaN NaN 30.0 NaN NaN
2022-05-09 09:28:03 NaN NaN NaN 20.0 NaN
2022-05-09 09:28:04 NaN NaN NaN NaN 10.0
Or use DataFrame.reset_index with DataFrame.pivot:
df1 = df.reset_index().pivot('timestamp','A','B').add_prefix('A_').rename_axis(columns=None)
EDIT from comment under answer - use DataFrame.pivot_table with aggregate function, default is mean with DataFrame.reindex for add timestamp with missing values in B column:
print (df)
A B
timestamp
2022-05-09 09:28:00 0 45.0 <-duplicated timestamp, A values
2022-05-09 09:28:00 0 30.0 <-duplicated timestamp, A values
2022-05-09 09:28:01 3 NaN
2022-05-09 09:28:02 4 30.0
2022-05-09 09:28:03 5 20.0
2022-05-09 09:28:04 8 10.0
df2 = (df.reset_index()
.pivot_table(index='timestamp',columns='A',values='B', aggfunc='mean')
.add_prefix('A_')
.reindex(df.index.unique())
.rename_axis(columns=None))
print (df2)
A_0 A_4 A_5 A_8
timestamp
2022-05-09 09:28:00 37.5 NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN
2022-05-09 09:28:02 NaN 30.0 NaN NaN
2022-05-09 09:28:03 NaN NaN 20.0 NaN
2022-05-09 09:28:04 NaN NaN NaN 10.0
Aggregation by mean used for first part of solution - row with missing values is not removed:
df2 = (df.groupby(['timestamp','A'])['B']
.mean()
.unstack()
.add_prefix('A_')
.rename_axis(columns=None))
print (df2)
A_0 A_3 A_4 A_5 A_8
timestamp
2022-05-09 09:28:00 37.5 NaN NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN NaN
2022-05-09 09:28:02 NaN NaN 30.0 NaN NaN
2022-05-09 09:28:03 NaN NaN NaN 20.0 NaN
2022-05-09 09:28:04 NaN NaN NaN NaN 10.0
EDIT: Dirst create pairs with same values after _ with rename:
df = df.rename(columns={'A':'V_A', 'C':'V_C',
'B':'I_A', 'D':'I_C'})
Then use wide_to_long:
df3 = (pd.wide_to_long(df.reset_index(), stubnames=['V', 'I'],
i='timestamp',
j='i',
sep='_',
suffix=r'\w '
).reset_index()
)
print (df3)
timestamp i V I
0 2022-05-09 09:28:00 A 0 45.0
1 2022-05-09 09:28:01 A 3 NaN
2 2022-05-09 09:28:02 A 4 30.0
3 2022-05-09 09:28:03 A 5 20.0
4 2022-05-09 09:28:04 A 8 10.0
5 2022-05-09 09:28:00 C 3 20.0
6 2022-05-09 09:28:01 C 2 15.0
7 2022-05-09 09:28:02 C 5 34.0
8 2022-05-09 09:28:03 C 4 NaN
9 2022-05-09 09:28:04 C 8 NaN
And last aggregate mean with reshape:
df4 = (df3.groupby(['timestamp','V','i'])['I']
.mean()
.unstack(['i','V'])
.sort_index(axis=1)
)
df4.columns = df4.columns.map(lambda x: f'{x[0]}_{x[1]}')
print (df4)
A_0 A_3 A_4 A_5 A_8 C_2 C_3 C_4 C_5 C_8
timestamp
2022-05-09 09:28:00 45.0 NaN NaN NaN NaN NaN 20.0 NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN NaN 15.0 NaN NaN NaN NaN
2022-05-09 09:28:02 NaN NaN 30.0 NaN NaN NaN NaN NaN 34.0 NaN
2022-05-09 09:28:03 NaN NaN NaN 20.0 NaN NaN NaN NaN NaN NaN
2022-05-09 09:28:04 NaN NaN NaN NaN 10.0 NaN NaN NaN NaN NaN
CodePudding user response：
Using a simple pivot would only work when there is no duplicated combinations for timestamp/A :
df.reset_index().pivot('timestamp', 'A', 'B').add_prefix('A')
output:
A_0 A_3 A_4 A_5 A_8
timestamp
2022-05-09 09:28:00 45.0 NaN NaN NaN NaN
2022-05-09 09:28:01 NaN NaN NaN NaN NaN
2022-05-09 09:28:02 NaN NaN 30.0 NaN NaN
2022-05-09 09:28:03 NaN NaN NaN 20.0 NaN
2022-05-09 09:28:04 NaN NaN NaN NaN 10.0
If you have duplicated indices you need to decide on how to handle it. Either aggregate using pivot_table or deduplicate the duplicates first:
output:
(df.reset_index()
.pivot_table(index='timestamp', columns='A', values='B',
aggfunc='sum') # chose the method to aggregate, default is 'mean'
)