Compare and Move Values in Pandas DataFrame Columns - Python

dirty

I populate the following pandas DataFrame:

block quote

The ID contained in the first cell of the column matches the IDs in the three cells of the second column. The content of each cell is not fixed (so they are not literal string values, but data taken from variable json api output).

How can I compare the contents of the two columns (and since the contents are fixed, I guess this has to be done variably, not using literal strings), and if there is a match, move the match to the corresponding cell in it next to it? Hope this makes sense, this is the type of output I'm looking for:

block quote

Chris 
# sample data
df = pd.DataFrame(np.array([{'data': [{'id': '12345', 'type': 'education'}, {'id': '23456', 'type': 'education'}, {'id': '34567', 'type': 'education'}]},
                            {'data': [{'id': '45678', 'type': 'education'}, {'id': '56789', 'type': 'education'}]},
                            {'data': [{'id': '78999', 'type': 'education'}]}]), columns=['Edu ID'])

# create a new frame but orient the index and explode
df_e = pd.DataFrame.from_dict(df['Edu ID'].to_dict(), orient='index')['data'].explode()
# take the new frame and convert it to a list then groupby the index and create a list of ids
final_df = df.join(pd.DataFrame(df_e.tolist(), index=df_e.index).groupby(level=0)['id'].agg(list))


                                              Edu ID                     id
0  {'data': [{'id': '12345', 'type': 'education'}...  [12345, 23456, 34567]
1  {'data': [{'id': '45678', 'type': 'education'}...         [45678, 56789]
2   {'data': [{'id': '78999', 'type': 'education'}]}                [78999]

If you need to filter == Education Type

# create a new frame but orient the index and explode
df_e = pd.DataFrame.from_dict(df['Edu ID'].to_dict(), orient='index')['data'].explode()

# take the new frame and convert it to a list and create a new frame
df_edu = pd.DataFrame(df_e.tolist(), index=df_e.index)

# use join but filter type to equal education and then gorupby and convert ids to a list
final_df = df.join(df_edu[df_edu['type'] == 'education'].groupby(level=0)['id'].agg(list))

Related

Compare and Move Values in Pandas DataFrame Columns - Python

dirty I populate the following pandas DataFrame: The ID contained in the first cell of the column matches the IDs in the three cells of the second column. The content of each cell is not fixed (so they are not literal string values, but data taken from variabl

Compare and Move Values in Pandas DataFrame Columns - Python

dirty I populate the following pandas DataFrame: The ID contained in the first cell of the column matches the IDs in the three cells of the second column. The content of each cell is not fixed (so they are not literal string values, but data taken from variabl

Compare and Move Values in Pandas DataFrame Columns - Python

dirty I populate the following pandas DataFrame: The ID contained in the first cell of the column matches the IDs in the three cells of the second column. The content of each cell is not fixed (so they are not literal string values, but data taken from variabl

Compare and Move Values in Pandas DataFrame Columns - Python

dirty I populate the following pandas DataFrame: The ID contained in the first cell of the column matches the IDs in the three cells of the second column. The content of each cell is not fixed (so they are not literal string values, but data taken from variabl

Compare and Move Values in Pandas DataFrame Columns - Python

dirty I populate the following pandas DataFrame: The ID contained in the first cell of the column matches the IDs in the three cells of the second column. The content of each cell is not fixed (so they are not literal string values, but data taken from variabl

Compare columns of pandas dataframe and fill missing values

username I have two pandas dataframes as shown below. list1 = [{'salt': 0.2, 'fat': 0.8}, {'fat': 1.0, 'protein': 0.9}] df1 = pd.DataFrame(line2) # Fill missing values with zeros df1.fillna(0, inplace=True) list2 = [{'salt': 0.1, 'sugar': 0.9}, {'oil': 0.9, '

Compare columns of pandas dataframe and fill missing values

username I have two pandas dataframes as shown below. list1 = [{'salt': 0.2, 'fat': 0.8}, {'fat': 1.0, 'protein': 0.9}] df1 = pd.DataFrame(line2) # Fill missing values with zeros df1.fillna(0, inplace=True) list2 = [{'salt': 0.1, 'sugar': 0.9}, {'oil': 0.9, '

Compare columns of pandas dataframe and fill missing values

username I have two pandas dataframes as shown below. list1 = [{'salt': 0.2, 'fat': 0.8}, {'fat': 1.0, 'protein': 0.9}] df1 = pd.DataFrame(line2) # Fill missing values with zeros df1.fillna(0, inplace=True) list2 = [{'salt': 0.1, 'sugar': 0.9}, {'oil': 0.9, '

Pandas compare dataframe values and update columns

user I have two dataframes. The columns in the first DF contain the values that appear as a list in the second DF, i.e. c2['test1', 'test2'] 1. inp = [{'c1':'test1'}, {'c1': 'test2'}, {'c1':'test3'}] df1 = pd.DataFrame(inp) print (df1) output: c1 0 tes

Compare columns of pandas dataframe and fill missing values

username I have two pandas dataframes as shown below. list1 = [{'salt': 0.2, 'fat': 0.8}, {'fat': 1.0, 'protein': 0.9}] df1 = pd.DataFrame(line2) # Fill missing values with zeros df1.fillna(0, inplace=True) list2 = [{'salt': 0.1, 'sugar': 0.9}, {'oil': 0.9, '

Compare columns of pandas dataframe and fill missing values

username I have two pandas dataframes as shown below. list1 = [{'salt': 0.2, 'fat': 0.8}, {'fat': 1.0, 'protein': 0.9}] df1 = pd.DataFrame(line2) # Fill missing values with zeros df1.fillna(0, inplace=True) list2 = [{'salt': 0.1, 'sugar': 0.9}, {'oil': 0.9, '

Move columns in pandas dataframe

User308827: I have the following dataframe: a b x y 0 1 2 3 -1 1 2 4 6 -2 2 3 6 9 -3 3 4 8 12 -4 How can I shift columns b and x so that they are the last 2 columns of the dataframe? I want to specify b and x by name, but not the other c

Move columns in pandas dataframe

User308827: I have the following dataframe: a b x y 0 1 2 3 -1 1 2 4 6 -2 2 3 6 9 -3 3 4 8 12 -4 How can I shift columns b and x so that they are the last 2 columns of the dataframe? I want to specify b and x by name, but not the other c

Move columns in pandas dataframe

username I have the following dataframe: a b x y 0 1 2 3 -1 1 2 4 6 -2 2 3 6 9 -3 3 4 8 12 -4 How can I shift columns b and x so that they are the last 2 columns of the dataframe? I want to specify b and x by name, but not the other colu

Move columns in pandas dataframe

User308827: I have the following dataframe: a b x y 0 1 2 3 -1 1 2 4 6 -2 2 3 6 9 -3 3 4 8 12 -4 How can I shift columns b and x so that they are the last 2 columns of the dataframe? I want to specify b and x by name, but not the other c

Python Pandas compare datetime values in DataFrame cells

Michalko I ran two sets of speed tests and logged the data into CSV files, which I then read back and converted to DataFrames. When I display the data it looks like this, I have 2 sets of data; one for test #1 and one for test #2 DataFrame result table example

Python Pandas compare datetime values in DataFrame cells

Michalko I ran two sets of speed tests and logged the data into CSV files, which I then read back and converted to DataFrames. When I display the data it looks like this, I have 2 sets of data; one for test #1 and one for test #2 DataFrame result table example

How to move values in a python dataframe using pandas?

Jiayu Zhang I have a dataframe like this, d = {'ID': ["A", "A", "B", "B", "C", "C", "D", "D", "E", "E", "F", "F"], 'value': [23, 23, 52, 52, 36, 36, 46, 46, 9, 9, 110, 110]} df = pd.DataFrame(data=d) ID value 0 A 23 1 A 23 2 B 52

How to move values in a python dataframe using pandas?

Jiayu Zhang I have a dataframe like this, d = {'ID': ["A", "A", "B", "B", "C", "C", "D", "D", "E", "E", "F", "F"], 'value': [23, 23, 52, 52, 36, 36, 46, 46, 9, 9, 110, 110]} df = pd.DataFrame(data=d) ID value 0 A 23 1 A 23 2 B 52

Python Pandas move dataframe with time index values

Annie 27 I am very new to python and struggling shiftwith pandas . I am comparing data but need to adjust it for comparison. In order to align the data, I only need to move one of the index values of the data. Reference data: Data to be

How to move columns in Pandas DataFrame without losing values

careful I want to move a column in a Pandas DataFrame, but I haven't been able to find a way to do this without losing values. (This post is very similar to How to Move Columns in a Pandas DataFrame , but the verified answer doesn't give the desired output, so