Expand column in dataframe based on subvalues (opposite of group by?)

Question

Let’s say I have a dataframe. I want to take the values in col1 (A for example) and return multiple more values like A.1, A.2, A.3 etc.

Each of the values in this column correspond to groups of data elsewhere. I want run a custom function that returns a list of values for these and I want to create a new dataframe with new rows identical to the original row except with the values changed in col1:

So:

Col1, Col2, Col3
A, 55, type

If A corresponds to a list such as AB, AJ, AI I want a new dataframe:

Col1,Col2,Col3
AB, 55, type
AJ, 55, type,
AI, 55, type

Answer 1

Assuming the mapping between the col1s is contained in a dict e.g.

mapping = {'AB':'A', 'AJ':'A', 'AI':'A'}

Then you can create a new table of the mapping and join this to the original table:

df = pd.DataFrame({'Col1':['A'], 'Col2':[55], 'Col3':['type']})
df_map = pd.DataFrame(mapping.items(), columns=['Col1_new', 'Col1'])

df_new = pd.merge(df, df_map)

  Col1  Col2  Col3 Col1_new
0    A    55  type       AB
1    A    55  type       AJ
2    A    55  type       AI

Answer 2

# define the two tables
a = pd.DataFrame({'Col1':['A'], 'Col2':[55], 'Col3':['type']})
b = pd.DataFrame({'Col1': ['AB', 'AJ', 'AI']})

# Create a column in your second table that matches `Col1` in your first table
# it appears you want that to be the first letter of `Col1` in table `b`:
b['key'] = b['Col1'].str[0]

# Join the two tables together on that key that matches across both
df = a.set_index('Col1').join(b.set_index('key'))

# Rearrange as you like:
df = df[['Col1', 'Col2', 'Col3']]

Gives df = :

  Col1  Col2  Col3
A   AB    55  type
A   AJ    55  type
A   AI    55  type

Answer 3

This should work:

l = list('BJI')
df.iloc[[0]*len(l)].assign(Col1 = lambda x: x['Col1'].str.cat(l)).reset_index()