I have a dataframe in a "yes/no" format like
7 22
1 NaN t
25 t NaN
where "t" stands for yes and I need to transform it to a X-Y table since the column name is the X coordinate and the index is the Y coordinate:
X Y
1 22 1
2 7 25
a pseudo-code like:
if a cell = "t":
newdf.X = df.column(t)
newdf.Y = df.index(t)
Try this:
# Use np.where to get the integer location of the 't's in the dataframe
r, c = np.where(df == 't')
# Use dataframe constructor with dataframe indexes to define X, Y
df_out = pd.DataFrame({'X':df.columns[c], 'Y':df.index[r]})
df_out
Output:
X Y
0 22 1
1 7 25
Update to address @RajeshC comment:
Given df,
7 22
1 NaN t
13 NaN NaN
25 t NaN
Then:
r, c = np.where(df == 't')
df_out = pd.DataFrame({'X':df.columns[c], 'Y':df.index[r]}, index=r)
df_out = df_out.reindex(range(df.shape[0]))
df_out
Output:
X Y
0 22 1.0
1 NaN NaN
2 7 25.0
Another option with stack:
pd.DataFrame.from_records(
df.stack().index.swaplevel(),
columns=['X', 'Y'])
Output:
X Y
0 22 1
1 7 25
Based on the magic of stacking, my answer is a tweak of @Perl's answer (couldn't comment because reps < 50)-
df.stack().to_frame().reset_index().drop(0,axis = 1).rename(columns = {'level_0':"Y","level_1":"X"}).reindex(columns=["X","Y"])
PS - Thanks Kristian Canler for the edit.
